Hello Tatsuo,<br><br>Here is cumulative patch to be applied on pgpool master branch with following fixes included:<br><ol><li>fix for health check bug</li><ol><li>it was not possible to allow backend failover only on failed health check(s);</li>
<li>to achieve this one just configures backend to DISALLOW_TO_FAILOVER, sets fail_over_on_backend_error to off, and configures health checks;</li><li>for this fix in code an unwanted check was removed in main.c, after health check failed if DISALLOW_TO_FAILOVER was set for backend failover would have been always prevented, even when one configures health check whose sole purpose is to control failover<br>
</li></ol><li>fix for health check bug</li><ol><li>health check timeout was not being respected in all conditions (icmp host unreachable messages dropped for security reasons, or no active network component to send those message)</li>
<li>for this fix in code (main.c, pool.h, pool_connection_pool.c) inet connections have been made to be non blocking, and during connection retries status of now global health_check_timer_expired variable is being checked<br>
</li></ol><li>fix for failback bug</li><ol><li>in raw mode, after failback (through pcp_attach_node) standby node/backend would remain in invalid state (it would be in CON_UP, so on failover after failback pgpool would not be able to connect to standby as get_next_master_node expects standby nodes/backends in raw mode to be in CON_CONNECT_WAIT state when finding next master node)</li>
<li>for this fix in code, when in raw mode on failback status of all nodes/backends with CON_UP state is set to CON_CONNECT_WAIT - all children are restarted anyway<br></li></ol></ol><p>Neither of these fixes changes expected behaviour of related features so there are no changes to the documentation.<br>
</p><p><br></p><p>Kind regards,</p><p>Stevo.<br></p><br><br><div class="gmail_quote">2012/1/24 Tatsuo Ishii <span dir="ltr"><<a href="mailto:ishii@postgresql.org">ishii@postgresql.org</a>></span><br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div class="im">> Additional testing confirmed that this fix ensures health check timer gets<br>
> respected (should I create a ticket on some issue tracker? send cumulative<br>
> patch with all changes to have it accepted?).<br>
<br>
</div>We have problem with Mantis bug tracker and decided to stop using<br>
it(unless someone volunteers to fix it). Please send cumulative patch<br>
againt master head to this list so that we will be able to look<br>
into(be sure to include English doc changes).<br>
<div class="HOEnZb"><div class="h5">--<br>
Tatsuo Ishii<br>
SRA OSS, Inc. Japan<br>
English: <a href="http://www.sraoss.co.jp/index_en.php" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
Japanese: <a href="http://www.sraoss.co.jp" target="_blank">http://www.sraoss.co.jp</a><br>
<br>
> Problem is that with all the testing another issue has been encountered,<br>
> now with pcp_attach_node.<br>
><br>
> With pgpool in raw mode and two backends in postgres 9 streaming<br>
> replication, when backend0 fails, after health checks retries pgpool calls<br>
> failover command and degenerates backend0, backend1 gets promoted to new<br>
> master, pgpool can connect to that master, and two backends are in pgpool<br>
> state 3/2. And this is ok and expected.<br>
><br>
> Once backend0 is recovered, it's attached back to pgpool using<br>
> pcp_attach_node, and pgpool will show two backends in state 2/2 (in logs<br>
> and in show pool_nodes; query) with backend0 taking all the load (raw<br>
> mode). If after that recovery and attachment of backend0 pgpool is not<br>
> restarted, and afetr some time backend0 fails again, after health check<br>
> retries backend0 will get degenerated, failover command will get called<br>
> (promotes standby to master), but pgpool will not be able to connect to<br>
> backend1 (regardless if unix or inet sockets are used for backend1). Only<br>
> if pgpool is restarted before second (complete) failure of backend0, will<br>
> pgpool be able to connect to backend1.<br>
><br>
> Following code, pcp_attach_node (failback of backend0) will actually<br>
> execute same code as for failover. Not sure what, but that failover does<br>
> something with backend1 state or in memory settings, so that pgpool can no<br>
> longer connect to backend1. Is this a known issue?<br>
><br>
> Kind regards,<br>
> Stevo.<br>
><br>
> 2012/1/20 Stevo Slaviĉ <<a href="mailto:sslavic@gmail.com">sslavic@gmail.com</a>><br>
><br>
>> Key file was missing from that commit/change - pool.h where<br>
>> health_check_timer_expired was made global. Included now attached patch.<br>
>><br>
>> Kind regards,<br>
>> Stevo.<br>
>><br>
>><br>
>> 2012/1/20 Stevo Slaviĉ <<a href="mailto:sslavic@gmail.com">sslavic@gmail.com</a>><br>
>><br>
>>> Using exit_request was wrong and caused a bug. 4th patch needed -<br>
>>> health_check_timer_expired is global now so it can be verified if it was<br>
>>> set to 1 outside of main.c<br>
>>><br>
>>><br>
>>> Kind regards,<br>
>>> Stevo.<br>
>>><br>
>>> 2012/1/19 Stevo Slaviĉ <<a href="mailto:sslavic@gmail.com">sslavic@gmail.com</a>><br>
>>><br>
>>>> Using exit_code was not wise. Tested and encountered a case where this<br>
>>>> results in a bug. Have to work on it more. Main issue is how in<br>
>>>> pool_connection_pool.c connect_inet_domain_socket_by_port function to know<br>
>>>> that health check timer has expired (set to 1). Any ideas?<br>
>>>><br>
>>>> Kind regards,<br>
>>>> Stevo.<br>
>>>><br>
>>>><br>
>>>> 2012/1/19 Stevo Slaviĉ <<a href="mailto:sslavic@gmail.com">sslavic@gmail.com</a>><br>
>>>><br>
>>>>> Tatsuo,<br>
>>>>><br>
>>>>> Here are the patches which should be applied to current pgpool head for<br>
>>>>> fixing this issue:<br>
>>>>><br>
>>>>> Fixes-health-check-timeout.patch<br>
>>>>> Fixes-health-check-retrying-after-failover.patch<br>
>>>>> Fixes-clearing-exitrequest-flag.patch<br>
>>>>><br>
>>>>> Quirk I noticed in logs was resolved as well - after failover pgpool<br>
>>>>> would perform healthcheck and report it is doing (max retries + 1) th<br>
>>>>> health check which was confusing. Rather I've adjusted that it does and<br>
>>>>> reports it's doing a new health check cycle after failover.<br>
>>>>><br>
>>>>> I've tested and it works well - when in raw mode, backends set to<br>
>>>>> disallow failover, failover on backend failure disabled, and health checks<br>
>>>>> configured with retries (30sec interval, 5sec timeout, 2 retries, 10sec<br>
>>>>> delay between retries).<br>
>>>>><br>
>>>>> Please test, and if confirmed ok include in next release.<br>
>>>>><br>
>>>>> Kind regards,<br>
>>>>><br>
>>>>> Stevo.<br>
>>>>><br>
>>>>><br>
>>>>> 2012/1/16 Stevo Slaviĉ <<a href="mailto:sslavic@gmail.com">sslavic@gmail.com</a>><br>
>>>>><br>
>>>>>> Here is pgpool.log, strace.out, and pgpool.conf when I tested with my<br>
>>>>>> latest patch for health check timeout applied. It works well, except for<br>
>>>>>> single quirk, after failover completed in log files it was reported that<br>
>>>>>> 3rd health check retry was done (even though just 2 are configured, see<br>
>>>>>> pgpool.conf) and that backend has returned to healthy state. That<br>
>>>>>> interesting part from log file follows:<br>
>>>>>><br>
>>>>>> Jan 16 01:31:45 sslavic pgpool[1163]: 2012-01-16 01:31:45 DEBUG: pid<br>
>>>>>> 1163: retrying 3 th health checking<br>
>>>>>> Jan 16 01:31:45 sslavic pgpool[1163]: 2012-01-16 01:31:45 DEBUG: pid<br>
>>>>>> 1163: health_check: 0 th DB node status: 3<br>
>>>>>> Jan 16 01:31:45 sslavic pgpool[1163]: 2012-01-16 01:31:45 LOG: pid<br>
>>>>>> 1163: after some retrying backend returned to healthy state<br>
>>>>>> Jan 16 01:32:15 sslavic pgpool[1163]: 2012-01-16 01:32:15 DEBUG: pid<br>
>>>>>> 1163: starting health checking<br>
>>>>>> Jan 16 01:32:15 sslavic pgpool[1163]: 2012-01-16 01:32:15 DEBUG: pid<br>
>>>>>> 1163: health_check: 0 th DB node status: 3<br>
>>>>>><br>
>>>>>><br>
>>>>>> As can be seen in pgpool.conf, there is only one backend configured.<br>
>>>>>> pgpool did failover well after health check max retries has been reached<br>
>>>>>> (pgpool just degraded that single backend to 3, and restarted child<br>
>>>>>> processes).<br>
>>>>>><br>
>>>>>> After this quirk has been logged, next health check logs were as<br>
>>>>>> expected. Except those couple weird log entries, everything seems to be ok.<br>
>>>>>> Maybe that quirk was caused by single backend only configuration corner<br>
>>>>>> case. Will try tomorrow if it occurs on dual backend configuration.<br>
>>>>>><br>
>>>>>> Regards,<br>
>>>>>> Stevo.<br>
>>>>>><br>
>>>>>><br>
>>>>>> 2012/1/16 Stevo Slaviĉ <<a href="mailto:sslavic@gmail.com">sslavic@gmail.com</a>><br>
>>>>>><br>
>>>>>>> Hello Tatsuo,<br>
>>>>>>><br>
>>>>>>> Unfortunately, with your patch when A is on<br>
>>>>>>> (pool_config->health_check_period > 0) and B is on, when retry count is<br>
>>>>>>> over, failover will be disallowed because of B being on.<br>
>>>>>>><br>
>>>>>>> Nenad's patch allows failover to be triggered only by health check.<br>
>>>>>>> Here is the patch which includes Nenad's fix but also fixes issue with<br>
>>>>>>> health check timeout not being respected.<br>
>>>>>>><br>
>>>>>>> Key points in fix for health check timeout being respected are:<br>
>>>>>>> - in pool_connection_pool.c connect_inet_domain_socket_by_port<br>
>>>>>>> function, before trying to connect, file descriptor is set to non-blocking<br>
>>>>>>> mode, and also non-blocking mode error codes are handled, EINPROGRESS and<br>
>>>>>>> EALREADY (please verify changes here, especially regarding closing fd)<br>
>>>>>>> - in main.c health_check_timer_handler has been changed to signal<br>
>>>>>>> exit_request to health check initiated connect_inet_domain_socket_by_port<br>
>>>>>>> function call (please verify this, maybe there is a better way to check<br>
>>>>>>> from connect_inet_domain_socket_by_port if in health_check_timer_expired<br>
>>>>>>> has been set to 1)<br>
>>>>>>><br>
>>>>>>> These changes will practically make connect attempt to be<br>
>>>>>>> non-blocking and repeated until:<br>
>>>>>>> - connection is made, or<br>
>>>>>>> - unhandled connection error condition is reached, or<br>
>>>>>>> - health check timer alarm has been raised, or<br>
>>>>>>> - some other exit request (shutdown) has been issued.<br>
>>>>>>><br>
>>>>>>><br>
>>>>>>> Kind regards,<br>
>>>>>>> Stevo.<br>
>>>>>>><br>
>>>>>>> 2012/1/15 Tatsuo Ishii <<a href="mailto:ishii@postgresql.org">ishii@postgresql.org</a>><br>
>>>>>>><br>
>>>>>>>> Ok, let me clarify use cases regarding failover.<br>
>>>>>>>><br>
>>>>>>>> Currently there are three parameters:<br>
>>>>>>>> a) health_check<br>
>>>>>>>> b) DISALLOW_TO_FAILOVER<br>
>>>>>>>> c) fail_over_on_backend_error<br>
>>>>>>>><br>
>>>>>>>> Source of errors which can trigger failover are 1)health check<br>
>>>>>>>> 2)write<br>
>>>>>>>> to backend socket 3)read backend from socket. I represent each 1) as<br>
>>>>>>>> A, 2) as B, 3) as C.<br>
>>>>>>>><br>
>>>>>>>> 1) trigger failover if A or B or C is error<br>
>>>>>>>> a = on, b = off, c = on<br>
>>>>>>>><br>
>>>>>>>> 2) trigger failover only when B or C is error<br>
>>>>>>>> a = off, b = off, c = on<br>
>>>>>>>><br>
>>>>>>>> 3) trigger failover only when B is error<br>
>>>>>>>> Impossible. Because C error always triggers failover.<br>
>>>>>>>><br>
>>>>>>>> 4) trigger failover only when C is error<br>
>>>>>>>> a = off, b = off, c = off<br>
>>>>>>>><br>
>>>>>>>> 5) trigger failover only when A is error(Stevo wants this)<br>
>>>>>>>> Impossible. Because C error always triggers failover.<br>
>>>>>>>><br>
>>>>>>>> 6) never trigger failover<br>
>>>>>>>> Impossible. Because C error always triggers failover.<br>
>>>>>>>><br>
>>>>>>>> As you can see, C is the problem here (look at #3, #5 and #6)<br>
>>>>>>>><br>
>>>>>>>> If we implemented this:<br>
>>>>>>>> >> However I think we should disable failover if<br>
>>>>>>>> DISALLOW_TO_FAILOVER set<br>
>>>>>>>> >> in case of reading data from backend. This should have been done<br>
>>>>>>>> when<br>
>>>>>>>> >> DISALLOW_TO_FAILOVER was introduced because this is exactly what<br>
>>>>>>>> >> DISALLOW_TO_FAILOVER tries to accomplish. What do you think?<br>
>>>>>>>><br>
>>>>>>>> 1) trigger failover if A or B or C is error<br>
>>>>>>>> a = on, b = off, c = on<br>
>>>>>>>><br>
>>>>>>>> 2) trigger failover only when B or C is error<br>
>>>>>>>> a = off, b = off, c = on<br>
>>>>>>>><br>
>>>>>>>> 3) trigger failover only when B is error<br>
>>>>>>>> a = off, b = on, c = on<br>
>>>>>>>><br>
>>>>>>>> 4) trigger failover only when C is error<br>
>>>>>>>> a = off, b = off, c = off<br>
>>>>>>>><br>
>>>>>>>> 5) trigger failover only when A is error(Stevo wants this)<br>
>>>>>>>> a = on, b = on, c = off<br>
>>>>>>>><br>
>>>>>>>> 6) never trigger failover<br>
>>>>>>>> a = off, b = on, c = off<br>
>>>>>>>><br>
>>>>>>>> So it seems my patch will solve all the problems including yours.<br>
>>>>>>>> (timeout while retrying is another issue of course).<br>
>>>>>>>> --<br>
>>>>>>>> Tatsuo Ishii<br>
>>>>>>>> SRA OSS, Inc. Japan<br>
>>>>>>>> English: <a href="http://www.sraoss.co.jp/index_en.php" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
>>>>>>>> Japanese: <a href="http://www.sraoss.co.jp" target="_blank">http://www.sraoss.co.jp</a><br>
>>>>>>>><br>
>>>>>>>> > I agree, fail_over_on_backend_error isn't useful, just adds<br>
>>>>>>>> confusion by<br>
>>>>>>>> > overlapping with DISALLOW_TO_FAILOVER.<br>
>>>>>>>> ><br>
>>>>>>>> > With your patch or without it, it is not possible to failover only<br>
>>>>>>>> on<br>
>>>>>>>> > health check (max retries) failure. With Nenad's patch, that part<br>
>>>>>>>> works ok<br>
>>>>>>>> > and I think that patch is semantically ok - failover occurs even<br>
>>>>>>>> though<br>
>>>>>>>> > DISALLOW_TO_FAILOVER is set for backend but only when health check<br>
>>>>>>>> is<br>
>>>>>>>> > configured too. Configuring health check without failover on<br>
>>>>>>>> failed health<br>
>>>>>>>> > check has no purpose. Also health check configured with allowed<br>
>>>>>>>> failover on<br>
>>>>>>>> > any condition other than health check (max retries) failure has no<br>
>>>>>>>> purpose.<br>
>>>>>>>> ><br>
>>>>>>>> > Kind regards,<br>
>>>>>>>> > Stevo.<br>
>>>>>>>> ><br>
>>>>>>>> > 2012/1/15 Tatsuo Ishii <<a href="mailto:ishii@postgresql.org">ishii@postgresql.org</a>><br>
>>>>>>>> ><br>
>>>>>>>> >> fail_over_on_backend_error has different meaning from<br>
>>>>>>>> >> DISALLOW_TO_FAILOVER. From the doc:<br>
>>>>>>>> >><br>
>>>>>>>> >> If true, and an error occurs when writing to the backend<br>
>>>>>>>> >> communication, pgpool-II will trigger the fail over procedure .<br>
>>>>>>>> This<br>
>>>>>>>> >> is the same behavior as of pgpool-II 2.2.x or earlier. If set to<br>
>>>>>>>> >> false, pgpool will report an error and disconnect the session.<br>
>>>>>>>> >><br>
>>>>>>>> >> This means that if pgpool failed to read from backend, it will<br>
>>>>>>>> trigger<br>
>>>>>>>> >> failover even if fail_over_on_backend_error to off. So<br>
>>>>>>>> unconditionaly<br>
>>>>>>>> >> disabling failover will lead backward imcompatibilty.<br>
>>>>>>>> >><br>
>>>>>>>> >> However I think we should disable failover if<br>
>>>>>>>> DISALLOW_TO_FAILOVER set<br>
>>>>>>>> >> in case of reading data from backend. This should have been done<br>
>>>>>>>> when<br>
>>>>>>>> >> DISALLOW_TO_FAILOVER was introduced because this is exactly what<br>
>>>>>>>> >> DISALLOW_TO_FAILOVER tries to accomplish. What do you think?<br>
>>>>>>>> >> --<br>
>>>>>>>> >> Tatsuo Ishii<br>
>>>>>>>> >> SRA OSS, Inc. Japan<br>
>>>>>>>> >> English: <a href="http://www.sraoss.co.jp/index_en.php" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
>>>>>>>> >> Japanese: <a href="http://www.sraoss.co.jp" target="_blank">http://www.sraoss.co.jp</a><br>
>>>>>>>> >><br>
>>>>>>>> >> > For a moment I thought we could have set<br>
>>>>>>>> fail_over_on_backend_error to<br>
>>>>>>>> >> off,<br>
>>>>>>>> >> > and have backends set with ALLOW_TO_FAILOVER flag. But then I<br>
>>>>>>>> looked in<br>
>>>>>>>> >> > code.<br>
>>>>>>>> >> ><br>
>>>>>>>> >> > In child.c there is a loop child process goes through in its<br>
>>>>>>>> lifetime.<br>
>>>>>>>> >> When<br>
>>>>>>>> >> > fatal error condition occurs before child process exits it will<br>
>>>>>>>> call<br>
>>>>>>>> >> > notice_backend_error which will call degenerate_backend_set<br>
>>>>>>>> which will<br>
>>>>>>>> >> not<br>
>>>>>>>> >> > take into account fail_over_on_backend_error is set to off,<br>
>>>>>>>> causing<br>
>>>>>>>> >> backend<br>
>>>>>>>> >> > to be degenerated and failover to occur. That's why we have<br>
>>>>>>>> backends set<br>
>>>>>>>> >> > with DISALLOW_TO_FAILOVER but with our patch applied, health<br>
>>>>>>>> check could<br>
>>>>>>>> >> > cause failover to occur as expected.<br>
>>>>>>>> >> ><br>
>>>>>>>> >> > Maybe it would be enough just to modify degenerate_backend_set,<br>
>>>>>>>> to take<br>
>>>>>>>> >> > fail_over_on_backend_error into account just like it already<br>
>>>>>>>> takes<br>
>>>>>>>> >> > DISALLOW_TO_FAILOVER into account.<br>
>>>>>>>> >> ><br>
>>>>>>>> >> > Kind regards,<br>
>>>>>>>> >> > Stevo.<br>
>>>>>>>> >> ><br>
>>>>>>>> >> > 2012/1/15 Stevo Slaviĉ <<a href="mailto:sslavic@gmail.com">sslavic@gmail.com</a>><br>
>>>>>>>> >> ><br>
>>>>>>>> >> >> Yes and that behaviour which you describe as expected, is not<br>
>>>>>>>> what we<br>
>>>>>>>> >> >> want. We want pgpool to degrade backend0 and failover when<br>
>>>>>>>> configured<br>
>>>>>>>> >> max<br>
>>>>>>>> >> >> health check retries have failed, and to failover only in that<br>
>>>>>>>> case, so<br>
>>>>>>>> >> not<br>
>>>>>>>> >> >> sooner e.g. connection/child error condition, but as soon as<br>
>>>>>>>> max health<br>
>>>>>>>> >> >> check retries have been attempted.<br>
>>>>>>>> >> >><br>
>>>>>>>> >> >> Maybe examples will be more clear.<br>
>>>>>>>> >> >><br>
>>>>>>>> >> >> Imagine two nodes (node 1 and node 2). On each node a single<br>
>>>>>>>> pgpool and<br>
>>>>>>>> >> a<br>
>>>>>>>> >> >> single backend. Apps/clients access db through pgpool on their<br>
>>>>>>>> own node.<br>
>>>>>>>> >> >> Two backends are configured in postgres native streaming<br>
>>>>>>>> replication.<br>
>>>>>>>> >> >> pgpools are used in raw mode. Both pgpools have same backend as<br>
>>>>>>>> >> backend0,<br>
>>>>>>>> >> >> and same backend as backend1.<br>
>>>>>>>> >> >> initial state: both backends are up and pgpool can access<br>
>>>>>>>> them, clients<br>
>>>>>>>> >> >> connect to their pgpool and do their work on master backend,<br>
>>>>>>>> backend0.<br>
>>>>>>>> >> >><br>
>>>>>>>> >> >> 1st case: unmodified/non-patched pgpool 3.1.1 is used,<br>
>>>>>>>> backends are<br>
>>>>>>>> >> >> configured with ALLOW_TO_FAILOVER flag<br>
>>>>>>>> >> >> - temporary network outage happens between pgpool on node 2<br>
>>>>>>>> and backend0<br>
>>>>>>>> >> >> - error condition is reported by child process, and since<br>
>>>>>>>> >> >> ALLOW_TO_FAILOVER is set, pgpool performs failover without<br>
>>>>>>>> giving<br>
>>>>>>>> >> chance to<br>
>>>>>>>> >> >> pgpool health check retries to control whether backend is just<br>
>>>>>>>> >> temporarily<br>
>>>>>>>> >> >> inaccessible<br>
>>>>>>>> >> >> - failover command on node 2 promotes standby backend to a new<br>
>>>>>>>> master -<br>
>>>>>>>> >> >> split brain occurs, with two masters<br>
>>>>>>>> >> >><br>
>>>>>>>> >> >><br>
>>>>>>>> >> >> 2nd case: unmodified/non-patched pgpool 3.1.1 is used,<br>
>>>>>>>> backends are<br>
>>>>>>>> >> >> configured with DISALLOW_TO_FAILOVER<br>
>>>>>>>> >> >> - temporary network outage happens between pgpool on node 2<br>
>>>>>>>> and backend0<br>
>>>>>>>> >> >> - error condition is reported by child process, and since<br>
>>>>>>>> >> >> DISALLOW_TO_FAILOVER is set, pgpool does not perform failover<br>
>>>>>>>> >> >> - health check gets a chance to check backend0 condition,<br>
>>>>>>>> determines<br>
>>>>>>>> >> that<br>
>>>>>>>> >> >> it's not accessible, there will be no health check retries<br>
>>>>>>>> because<br>
>>>>>>>> >> >> DISALLOW_TO_FAILOVER is set, no failover occurs ever<br>
>>>>>>>> >> >><br>
>>>>>>>> >> >><br>
>>>>>>>> >> >> 3rd case, pgpool 3.1.1 + patch you've sent applied, and<br>
>>>>>>>> backends<br>
>>>>>>>> >> >> configured with DISALLOW_TO_FAILOVER<br>
>>>>>>>> >> >> - temporary network outage happens between pgpool on node 2<br>
>>>>>>>> and backend0<br>
>>>>>>>> >> >> - error condition is reported by child process, and since<br>
>>>>>>>> >> >> DISALLOW_TO_FAILOVER is set, pgpool does not perform failover<br>
>>>>>>>> >> >> - health check gets a chance to check backend0 condition,<br>
>>>>>>>> determines<br>
>>>>>>>> >> that<br>
>>>>>>>> >> >> it's not accessible, health check retries happen, and even<br>
>>>>>>>> after max<br>
>>>>>>>> >> >> retries, no failover happens since failover is disallowed<br>
>>>>>>>> >> >><br>
>>>>>>>> >> >><br>
>>>>>>>> >> >> 4th expected behaviour, pgpool 3.1.1 + patch we sent, and<br>
>>>>>>>> backends<br>
>>>>>>>> >> >> configured with DISALLOW_TO_FAILOVER<br>
>>>>>>>> >> >> - temporary network outage happens between pgpool on node 2<br>
>>>>>>>> and backend0<br>
>>>>>>>> >> >> - error condition is reported by child process, and since<br>
>>>>>>>> >> >> DISALLOW_TO_FAILOVER is set, pgpool does not perform failover<br>
>>>>>>>> >> >> - health check gets a chance to check backend0 condition,<br>
>>>>>>>> determines<br>
>>>>>>>> >> that<br>
>>>>>>>> >> >> it's not accessible, health check retries happen, before a max<br>
>>>>>>>> retry<br>
>>>>>>>> >> >> network condition is cleared, retry happens, and backend0<br>
>>>>>>>> remains to be<br>
>>>>>>>> >> >> master, no failover occurs, temporary network issue did not<br>
>>>>>>>> cause split<br>
>>>>>>>> >> >> brain<br>
>>>>>>>> >> >> - after some time, temporary network outage happens again<br>
>>>>>>>> between pgpool<br>
>>>>>>>> >> >> on node 2 and backend0<br>
>>>>>>>> >> >> - error condition is reported by child process, and since<br>
>>>>>>>> >> >> DISALLOW_TO_FAILOVER is set, pgpool does not perform failover<br>
>>>>>>>> >> >> - health check gets a chance to check backend0 condition,<br>
>>>>>>>> determines<br>
>>>>>>>> >> that<br>
>>>>>>>> >> >> it's not accessible, health check retries happen, after max<br>
>>>>>>>> retries<br>
>>>>>>>> >> >> backend0 is still not accessible, failover happens, standby is<br>
>>>>>>>> new<br>
>>>>>>>> >> master<br>
>>>>>>>> >> >> and backend0 is degraded<br>
>>>>>>>> >> >><br>
>>>>>>>> >> >> Kind regards,<br>
>>>>>>>> >> >> Stevo.<br>
>>>>>>>> >> >><br>
>>>>>>>> >> >><br>
>>>>>>>> >> >> 2012/1/15 Tatsuo Ishii <<a href="mailto:ishii@postgresql.org">ishii@postgresql.org</a>><br>
>>>>>>>> >> >><br>
>>>>>>>> >> >>> In my test evironment, the patch works as expected. I have two<br>
>>>>>>>> >> >>> backends. Health check retry conf is as follows:<br>
>>>>>>>> >> >>><br>
>>>>>>>> >> >>> health_check_max_retries = 3<br>
>>>>>>>> >> >>> health_check_retry_delay = 1<br>
>>>>>>>> >> >>><br>
>>>>>>>> >> >>> 5 09:17:20 LOG: pid 21411: Backend status file<br>
>>>>>>>> /home/t-ishii/work/<br>
>>>>>>>> >> >>> <a href="http://git.postgresql.org/test/log/pgpool_status" target="_blank">git.postgresql.org/test/log/pgpool_status</a> discarded<br>
>>>>>>>> >> >>> 2012-01-15 09:17:20 LOG: pid 21411: pgpool-II successfully<br>
>>>>>>>> started.<br>
>>>>>>>> >> >>> version 3.2alpha1 (hatsuiboshi)<br>
>>>>>>>> >> >>> 2012-01-15 09:17:20 LOG: pid 21411: find_primary_node:<br>
>>>>>>>> primary node<br>
>>>>>>>> >> id<br>
>>>>>>>> >> >>> is 0<br>
>>>>>>>> >> >>> -- backend1 was shutdown<br>
>>>>>>>> >> >>><br>
>>>>>>>> >> >>> 2012-01-15 09:17:50 ERROR: pid 21445:<br>
>>>>>>>> >> connect_unix_domain_socket_by_port:<br>
>>>>>>>> >> >>> connect() failed to /tmp/.s.PGSQL.11001: No such file or<br>
>>>>>>>> directory<br>
>>>>>>>> >> >>> 2012-01-15 09:17:50 ERROR: pid 21445:<br>
>>>>>>>> make_persistent_db_connection:<br>
>>>>>>>> >> >>> connection to /tmp(11001) failed<br>
>>>>>>>> >> >>> 2012-01-15 09:17:50 ERROR: pid 21445:<br>
>>>>>>>> check_replication_time_lag: could<br>
>>>>>>>> >> >>> not connect to DB node 1, check sr_check_user and<br>
>>>>>>>> sr_check_password<br>
>>>>>>>> >> >>> 2012-01-15 09:17:50 ERROR: pid 21411:<br>
>>>>>>>> >> connect_unix_domain_socket_by_port:<br>
>>>>>>>> >> >>> connect() failed to /tmp/.s.PGSQL.11001: No such file or<br>
>>>>>>>> directory<br>
>>>>>>>> >> >>> 2012-01-15 09:17:50 ERROR: pid 21411:<br>
>>>>>>>> make_persistent_db_connection:<br>
>>>>>>>> >> >>> connection to /tmp(11001) failed<br>
>>>>>>>> >> >>> 2012-01-15 09:17:50 ERROR: pid 21411:<br>
>>>>>>>> >> connect_unix_domain_socket_by_port:<br>
>>>>>>>> >> >>> connect() failed to /tmp/.s.PGSQL.11001: No such file or<br>
>>>>>>>> directory<br>
>>>>>>>> >> >>> 2012-01-15 09:17:50 ERROR: pid 21411:<br>
>>>>>>>> make_persistent_db_connection:<br>
>>>>>>>> >> >>> connection to /tmp(11001) failed<br>
>>>>>>>> >> >>> -- health check failed<br>
>>>>>>>> >> >>><br>
>>>>>>>> >> >>> 2012-01-15 09:17:50 ERROR: pid 21411: health check failed. 1<br>
>>>>>>>> th host<br>
>>>>>>>> >> /tmp<br>
>>>>>>>> >> >>> at port 11001 is down<br>
>>>>>>>> >> >>> -- start retrying<br>
>>>>>>>> >> >>> 2012-01-15 09:17:50 LOG: pid 21411: health check retry<br>
>>>>>>>> sleep time: 1<br>
>>>>>>>> >> >>> second(s)<br>
>>>>>>>> >> >>> 2012-01-15 09:17:51 ERROR: pid 21411:<br>
>>>>>>>> >> connect_unix_domain_socket_by_port:<br>
>>>>>>>> >> >>> connect() failed to /tmp/.s.PGSQL.11001: No such file or<br>
>>>>>>>> directory<br>
>>>>>>>> >> >>> 2012-01-15 09:17:51 ERROR: pid 21411:<br>
>>>>>>>> make_persistent_db_connection:<br>
>>>>>>>> >> >>> connection to /tmp(11001) failed<br>
>>>>>>>> >> >>> 2012-01-15 09:17:51 ERROR: pid 21411: health check failed. 1<br>
>>>>>>>> th host<br>
>>>>>>>> >> /tmp<br>
>>>>>>>> >> >>> at port 11001 is down<br>
>>>>>>>> >> >>> 2012-01-15 09:17:51 LOG: pid 21411: health check retry<br>
>>>>>>>> sleep time: 1<br>
>>>>>>>> >> >>> second(s)<br>
>>>>>>>> >> >>> 2012-01-15 09:17:52 ERROR: pid 21411:<br>
>>>>>>>> >> connect_unix_domain_socket_by_port:<br>
>>>>>>>> >> >>> connect() failed to /tmp/.s.PGSQL.11001: No such file or<br>
>>>>>>>> directory<br>
>>>>>>>> >> >>> 2012-01-15 09:17:52 ERROR: pid 21411:<br>
>>>>>>>> make_persistent_db_connection:<br>
>>>>>>>> >> >>> connection to /tmp(11001) failed<br>
>>>>>>>> >> >>> 2012-01-15 09:17:52 ERROR: pid 21411: health check failed. 1<br>
>>>>>>>> th host<br>
>>>>>>>> >> /tmp<br>
>>>>>>>> >> >>> at port 11001 is down<br>
>>>>>>>> >> >>> 2012-01-15 09:17:52 LOG: pid 21411: health check retry<br>
>>>>>>>> sleep time: 1<br>
>>>>>>>> >> >>> second(s)<br>
>>>>>>>> >> >>> 2012-01-15 09:17:53 ERROR: pid 21411:<br>
>>>>>>>> >> connect_unix_domain_socket_by_port:<br>
>>>>>>>> >> >>> connect() failed to /tmp/.s.PGSQL.11001: No such file or<br>
>>>>>>>> directory<br>
>>>>>>>> >> >>> 2012-01-15 09:17:53 ERROR: pid 21411:<br>
>>>>>>>> make_persistent_db_connection:<br>
>>>>>>>> >> >>> connection to /tmp(11001) failed<br>
>>>>>>>> >> >>> 2012-01-15 09:17:53 ERROR: pid 21411: health check failed. 1<br>
>>>>>>>> th host<br>
>>>>>>>> >> /tmp<br>
>>>>>>>> >> >>> at port 11001 is down<br>
>>>>>>>> >> >>> 2012-01-15 09:17:53 LOG: pid 21411: health_check: 1<br>
>>>>>>>> failover is<br>
>>>>>>>> >> canceld<br>
>>>>>>>> >> >>> because failover is disallowed<br>
>>>>>>>> >> >>> -- after 3 retries, pgpool wanted to failover, but gave up<br>
>>>>>>>> because<br>
>>>>>>>> >> >>> DISALLOW_TO_FAILOVER is set for backend1<br>
>>>>>>>> >> >>><br>
>>>>>>>> >> >>> 2012-01-15 09:18:00 ERROR: pid 21445:<br>
>>>>>>>> >> connect_unix_domain_socket_by_port:<br>
>>>>>>>> >> >>> connect() failed to /tmp/.s.PGSQL.11001: No such file or<br>
>>>>>>>> directory<br>
>>>>>>>> >> >>> 2012-01-15 09:18:00 ERROR: pid 21445:<br>
>>>>>>>> make_persistent_db_connection:<br>
>>>>>>>> >> >>> connection to /tmp(11001) failed<br>
>>>>>>>> >> >>> 2012-01-15 09:18:00 ERROR: pid 21445:<br>
>>>>>>>> check_replication_time_lag: could<br>
>>>>>>>> >> >>> not connect to DB node 1, check sr_check_user and<br>
>>>>>>>> sr_check_password<br>
>>>>>>>> >> >>> 2012-01-15 09:18:03 ERROR: pid 21411:<br>
>>>>>>>> >> connect_unix_domain_socket_by_port:<br>
>>>>>>>> >> >>> connect() failed to /tmp/.s.PGSQL.11001: No such file or<br>
>>>>>>>> directory<br>
>>>>>>>> >> >>> 2012-01-15 09:18:03 ERROR: pid 21411:<br>
>>>>>>>> make_persistent_db_connection:<br>
>>>>>>>> >> >>> connection to /tmp(11001) failed<br>
>>>>>>>> >> >>> 2012-01-15 09:18:03 ERROR: pid 21411: health check failed. 1<br>
>>>>>>>> th host<br>
>>>>>>>> >> /tmp<br>
>>>>>>>> >> >>> at port 11001 is down<br>
>>>>>>>> >> >>> 2012-01-15 09:18:03 LOG: pid 21411: health check retry<br>
>>>>>>>> sleep time: 1<br>
>>>>>>>> >> >>> second(s)<br>
>>>>>>>> >> >>> 2012-01-15 09:18:04 ERROR: pid 21411:<br>
>>>>>>>> >> connect_unix_domain_socket_by_port:<br>
>>>>>>>> >> >>> connect() failed to /tmp/.s.PGSQL.11001: No such file or<br>
>>>>>>>> directory<br>
>>>>>>>> >> >>> 2012-01-15 09:18:04 ERROR: pid 21411:<br>
>>>>>>>> make_persistent_db_connection:<br>
>>>>>>>> >> >>> connection to /tmp(11001) failed<br>
>>>>>>>> >> >>> 2012-01-15 09:18:04 ERROR: pid 21411: health check failed. 1<br>
>>>>>>>> th host<br>
>>>>>>>> >> /tmp<br>
>>>>>>>> >> >>> at port 11001 is down<br>
>>>>>>>> >> >>> 2012-01-15 09:18:04 LOG: pid 21411: health check retry<br>
>>>>>>>> sleep time: 1<br>
>>>>>>>> >> >>> second(s)<br>
>>>>>>>> >> >>> 2012-01-15 09:18:05 LOG: pid 21411: after some retrying<br>
>>>>>>>> backend<br>
>>>>>>>> >> >>> returned to healthy state<br>
>>>>>>>> >> >>> -- started backend1 and pgpool succeeded in health checking.<br>
>>>>>>>> Resumed<br>
>>>>>>>> >> >>> using backend1<br>
>>>>>>>> >> >>> --<br>
>>>>>>>> >> >>> Tatsuo Ishii<br>
>>>>>>>> >> >>> SRA OSS, Inc. Japan<br>
>>>>>>>> >> >>> English: <a href="http://www.sraoss.co.jp/index_en.php" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
>>>>>>>> >> >>> Japanese: <a href="http://www.sraoss.co.jp" target="_blank">http://www.sraoss.co.jp</a><br>
>>>>>>>> >> >>><br>
>>>>>>>> >> >>> > Hello Tatsuo,<br>
>>>>>>>> >> >>> ><br>
>>>>>>>> >> >>> > Thank you for the patch and effort, but unfortunately this<br>
>>>>>>>> change<br>
>>>>>>>> >> won't<br>
>>>>>>>> >> >>> > work for us. We need to set disallow failover to prevent<br>
>>>>>>>> failover on<br>
>>>>>>>> >> >>> child<br>
>>>>>>>> >> >>> > reported connection errors (it's ok if few clients lose<br>
>>>>>>>> their<br>
>>>>>>>> >> >>> connection or<br>
>>>>>>>> >> >>> > can not connect), and still have pgpool perform failover<br>
>>>>>>>> but only on<br>
>>>>>>>> >> >>> failed<br>
>>>>>>>> >> >>> > health check (if configured, after max retries threshold<br>
>>>>>>>> has been<br>
>>>>>>>> >> >>> reached).<br>
>>>>>>>> >> >>> ><br>
>>>>>>>> >> >>> > Maybe it would be best to add an extra value for<br>
>>>>>>>> backend_flag -<br>
>>>>>>>> >> >>> > ALLOW_TO_FAILOVER_ON_HEALTH_CHECK or<br>
>>>>>>>> >> >>> DISALLOW_TO_FAILOVER_ON_CHILD_ERROR.<br>
>>>>>>>> >> >>> > It should behave same as DISALLOW_TO_FAILOVER is set, with<br>
>>>>>>>> only<br>
>>>>>>>> >> >>> difference<br>
>>>>>>>> >> >>> > in behaviour when health check (if set, max retries) has<br>
>>>>>>>> failed -<br>
>>>>>>>> >> unlike<br>
>>>>>>>> >> >>> > DISALLOW_TO_FAILOVER, this new flag should allow failover<br>
>>>>>>>> in this<br>
>>>>>>>> >> case<br>
>>>>>>>> >> >>> only.<br>
>>>>>>>> >> >>> ><br>
>>>>>>>> >> >>> > Without this change health check (especially health check<br>
>>>>>>>> retries)<br>
>>>>>>>> >> >>> doesn't<br>
>>>>>>>> >> >>> > make much sense - child error is more likely to occur on<br>
>>>>>>>> (temporary)<br>
>>>>>>>> >> >>> > backend failure then health check and will or will not cause<br>
>>>>>>>> >> failover to<br>
>>>>>>>> >> >>> > occur depending on backend flag, without giving health<br>
>>>>>>>> check retries<br>
>>>>>>>> >> a<br>
>>>>>>>> >> >>> > chance to determine if failure was temporary or not,<br>
>>>>>>>> risking split<br>
>>>>>>>> >> brain<br>
>>>>>>>> >> >>> > situation with two masters just because of temporary<br>
>>>>>>>> network link<br>
>>>>>>>> >> >>> hiccup.<br>
>>>>>>>> >> >>> ><br>
>>>>>>>> >> >>> > Our main problem remains though with the health check<br>
>>>>>>>> timeout not<br>
>>>>>>>> >> being<br>
>>>>>>>> >> >>> > respected in these special conditions we have. Maybe Nenad<br>
>>>>>>>> can help<br>
>>>>>>>> >> you<br>
>>>>>>>> >> >>> > more to reproduce the issue on your environment.<br>
>>>>>>>> >> >>> ><br>
>>>>>>>> >> >>> > Kind regards,<br>
>>>>>>>> >> >>> > Stevo.<br>
>>>>>>>> >> >>> ><br>
>>>>>>>> >> >>> > 2012/1/13 Tatsuo Ishii <<a href="mailto:ishii@postgresql.org">ishii@postgresql.org</a>><br>
>>>>>>>> >> >>> ><br>
>>>>>>>> >> >>> >> Thanks for pointing it out.<br>
>>>>>>>> >> >>> >> Yes, checking DISALLOW_TO_FAILOVER before retrying is<br>
>>>>>>>> wrong.<br>
>>>>>>>> >> >>> >> However, after retry count over, we should check<br>
>>>>>>>> >> DISALLOW_TO_FAILOVER I<br>
>>>>>>>> >> >>> >> think.<br>
>>>>>>>> >> >>> >> Attached is the patch attempt to fix it. Please try.<br>
>>>>>>>> >> >>> >> --<br>
>>>>>>>> >> >>> >> Tatsuo Ishii<br>
>>>>>>>> >> >>> >> SRA OSS, Inc. Japan<br>
>>>>>>>> >> >>> >> English: <a href="http://www.sraoss.co.jp/index_en.php" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
>>>>>>>> >> >>> >> Japanese: <a href="http://www.sraoss.co.jp" target="_blank">http://www.sraoss.co.jp</a><br>
>>>>>>>> >> >>> >><br>
>>>>>>>> >> >>> >> > pgpool is being used in raw mode - just for (health<br>
>>>>>>>> check based)<br>
>>>>>>>> >> >>> failover<br>
>>>>>>>> >> >>> >> > part, so applications are not required to restart when<br>
>>>>>>>> standby<br>
>>>>>>>> >> gets<br>
>>>>>>>> >> >>> >> > promoted to new master. Here is pgpool.conf file and a<br>
>>>>>>>> very small<br>
>>>>>>>> >> >>> patch<br>
>>>>>>>> >> >>> >> > we're using applied to pgpool 3.1.1 release.<br>
>>>>>>>> >> >>> >> ><br>
>>>>>>>> >> >>> >> > We have to have DISALLOW_TO_FAILOVER set for the backend<br>
>>>>>>>> since any<br>
>>>>>>>> >> >>> child<br>
>>>>>>>> >> >>> >> > process that detects condition that master/backend0 is<br>
>>>>>>>> not<br>
>>>>>>>> >> >>> available, if<br>
>>>>>>>> >> >>> >> > DISALLOW_TO_FAILOVER was not set, will degenerate<br>
>>>>>>>> backend without<br>
>>>>>>>> >> >>> giving<br>
>>>>>>>> >> >>> >> > health check a chance to retry. We need health check<br>
>>>>>>>> with retries<br>
>>>>>>>> >> >>> because<br>
>>>>>>>> >> >>> >> > condition that backend0 is not available could be<br>
>>>>>>>> temporary<br>
>>>>>>>> >> (network<br>
>>>>>>>> >> >>> >> > glitches to the remote site where master is, or<br>
>>>>>>>> deliberate<br>
>>>>>>>> >> failover<br>
>>>>>>>> >> >>> of<br>
>>>>>>>> >> >>> >> > master postgres service from one node to the other on<br>
>>>>>>>> remote site<br>
>>>>>>>> >> -<br>
>>>>>>>> >> >>> in<br>
>>>>>>>> >> >>> >> both<br>
>>>>>>>> >> >>> >> > cases remote means remote to the pgpool that is going to<br>
>>>>>>>> perform<br>
>>>>>>>> >> >>> health<br>
>>>>>>>> >> >>> >> > checks and ultimately the failover) and we don't want<br>
>>>>>>>> standby to<br>
>>>>>>>> >> be<br>
>>>>>>>> >> >>> >> > promoted as easily to a new master, to prevent temporary<br>
>>>>>>>> network<br>
>>>>>>>> >> >>> >> conditions<br>
>>>>>>>> >> >>> >> > which could occur frequently to frequently cause split<br>
>>>>>>>> brain with<br>
>>>>>>>> >> two<br>
>>>>>>>> >> >>> >> > masters.<br>
>>>>>>>> >> >>> >> ><br>
>>>>>>>> >> >>> >> > But then, with DISALLOW_TO_FAILOVER set, without the<br>
>>>>>>>> patch health<br>
>>>>>>>> >> >>> check<br>
>>>>>>>> >> >>> >> > will not retry and will thus give only one chance to<br>
>>>>>>>> backend (if<br>
>>>>>>>> >> >>> health<br>
>>>>>>>> >> >>> >> > check ever occurs before child process failure to<br>
>>>>>>>> connect to the<br>
>>>>>>>> >> >>> >> backend),<br>
>>>>>>>> >> >>> >> > rendering retry settings effectively to be ignored.<br>
>>>>>>>> That's where<br>
>>>>>>>> >> this<br>
>>>>>>>> >> >>> >> patch<br>
>>>>>>>> >> >>> >> > comes into action - enables health check retries while<br>
>>>>>>>> child<br>
>>>>>>>> >> >>> processes<br>
>>>>>>>> >> >>> >> are<br>
>>>>>>>> >> >>> >> > prevented to degenerate backend.<br>
>>>>>>>> >> >>> >> ><br>
>>>>>>>> >> >>> >> > I don't think, but I could be wrong, that this patch<br>
>>>>>>>> influences<br>
>>>>>>>> >> the<br>
>>>>>>>> >> >>> >> > behavior we're seeing with unwanted health check attempt<br>
>>>>>>>> delays.<br>
>>>>>>>> >> >>> Also,<br>
>>>>>>>> >> >>> >> > knowing this, maybe pgpool could be patched or some<br>
>>>>>>>> other support<br>
>>>>>>>> >> be<br>
>>>>>>>> >> >>> >> built<br>
>>>>>>>> >> >>> >> > into it to cover this use case.<br>
>>>>>>>> >> >>> >> ><br>
>>>>>>>> >> >>> >> > Regards,<br>
>>>>>>>> >> >>> >> > Stevo.<br>
>>>>>>>> >> >>> >> ><br>
>>>>>>>> >> >>> >> ><br>
>>>>>>>> >> >>> >> > 2012/1/12 Tatsuo Ishii <<a href="mailto:ishii@postgresql.org">ishii@postgresql.org</a>><br>
>>>>>>>> >> >>> >> ><br>
>>>>>>>> >> >>> >> >> I have accepted the moderation request. Your post<br>
>>>>>>>> should be sent<br>
>>>>>>>> >> >>> >> shortly.<br>
>>>>>>>> >> >>> >> >> Also I have raised the post size limit to 1MB.<br>
>>>>>>>> >> >>> >> >> I will look into this...<br>
>>>>>>>> >> >>> >> >> --<br>
>>>>>>>> >> >>> >> >> Tatsuo Ishii<br>
>>>>>>>> >> >>> >> >> SRA OSS, Inc. Japan<br>
>>>>>>>> >> >>> >> >> English: <a href="http://www.sraoss.co.jp/index_en.php" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
>>>>>>>> >> >>> >> >> Japanese: <a href="http://www.sraoss.co.jp" target="_blank">http://www.sraoss.co.jp</a><br>
>>>>>>>> >> >>> >> >><br>
>>>>>>>> >> >>> >> >> > Here is the log file and strace output file (this<br>
>>>>>>>> time in an<br>
>>>>>>>> >> >>> archive,<br>
>>>>>>>> >> >>> >> >> > didn't know about 200KB constraint on post size which<br>
>>>>>>>> requires<br>
>>>>>>>> >> >>> >> moderator<br>
>>>>>>>> >> >>> >> >> > approval). Timings configured are 30sec health check<br>
>>>>>>>> interval,<br>
>>>>>>>> >> >>> 5sec<br>
>>>>>>>> >> >>> >> >> > timeout, and 2 retries with 10sec retry delay.<br>
>>>>>>>> >> >>> >> >> ><br>
>>>>>>>> >> >>> >> >> > It takes a lot more than 5sec from started health<br>
>>>>>>>> check to<br>
>>>>>>>> >> >>> sleeping<br>
>>>>>>>> >> >>> >> 10sec<br>
>>>>>>>> >> >>> >> >> > for first retry.<br>
>>>>>>>> >> >>> >> >> ><br>
>>>>>>>> >> >>> >> >> > Seen in code (main.x, health_check() function),<br>
>>>>>>>> within (retry)<br>
>>>>>>>> >> >>> attempt<br>
>>>>>>>> >> >>> >> >> > there is inner retry (first with postgres database<br>
>>>>>>>> then with<br>
>>>>>>>> >> >>> >> template1)<br>
>>>>>>>> >> >>> >> >> and<br>
>>>>>>>> >> >>> >> >> > that part doesn't seem to be interrupted by alarm.<br>
>>>>>>>> >> >>> >> >> ><br>
>>>>>>>> >> >>> >> >> > Regards,<br>
>>>>>>>> >> >>> >> >> > Stevo.<br>
>>>>>>>> >> >>> >> >> ><br>
>>>>>>>> >> >>> >> >> > 2012/1/12 Stevo Slaviĉ <<a href="mailto:sslavic@gmail.com">sslavic@gmail.com</a>><br>
>>>>>>>> >> >>> >> >> ><br>
>>>>>>>> >> >>> >> >> >> Here is the log file and strace output file. Timings<br>
>>>>>>>> >> configured<br>
>>>>>>>> >> >>> are<br>
>>>>>>>> >> >>> >> >> 30sec<br>
>>>>>>>> >> >>> >> >> >> health check interval, 5sec timeout, and 2 retries<br>
>>>>>>>> with 10sec<br>
>>>>>>>> >> >>> retry<br>
>>>>>>>> >> >>> >> >> delay.<br>
>>>>>>>> >> >>> >> >> >><br>
>>>>>>>> >> >>> >> >> >> It takes a lot more than 5sec from started health<br>
>>>>>>>> check to<br>
>>>>>>>> >> >>> sleeping<br>
>>>>>>>> >> >>> >> >> 10sec<br>
>>>>>>>> >> >>> >> >> >> for first retry.<br>
>>>>>>>> >> >>> >> >> >><br>
>>>>>>>> >> >>> >> >> >> Seen in code (main.x, health_check() function),<br>
>>>>>>>> within (retry)<br>
>>>>>>>> >> >>> >> attempt<br>
>>>>>>>> >> >>> >> >> >> there is inner retry (first with postgres database<br>
>>>>>>>> then with<br>
>>>>>>>> >> >>> >> template1)<br>
>>>>>>>> >> >>> >> >> and<br>
>>>>>>>> >> >>> >> >> >> that part doesn't seem to be interrupted by alarm.<br>
>>>>>>>> >> >>> >> >> >><br>
>>>>>>>> >> >>> >> >> >> Regards,<br>
>>>>>>>> >> >>> >> >> >> Stevo.<br>
>>>>>>>> >> >>> >> >> >><br>
>>>>>>>> >> >>> >> >> >><br>
>>>>>>>> >> >>> >> >> >> 2012/1/11 Tatsuo Ishii <<a href="mailto:ishii@postgresql.org">ishii@postgresql.org</a>><br>
>>>>>>>> >> >>> >> >> >><br>
>>>>>>>> >> >>> >> >> >>> Ok, I will do it. In the mean time you could use<br>
>>>>>>>> "strace -tt<br>
>>>>>>>> >> -p<br>
>>>>>>>> >> >>> PID"<br>
>>>>>>>> >> >>> >> >> >>> to see which system call is blocked.<br>
>>>>>>>> >> >>> >> >> >>> --<br>
>>>>>>>> >> >>> >> >> >>> Tatsuo Ishii<br>
>>>>>>>> >> >>> >> >> >>> SRA OSS, Inc. Japan<br>
>>>>>>>> >> >>> >> >> >>> English: <a href="http://www.sraoss.co.jp/index_en.php" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
>>>>>>>> >> >>> >> >> >>> Japanese: <a href="http://www.sraoss.co.jp" target="_blank">http://www.sraoss.co.jp</a><br>
>>>>>>>> >> >>> >> >> >>><br>
>>>>>>>> >> >>> >> >> >>> > OK, got the info - key point is that ip<br>
>>>>>>>> forwarding is<br>
>>>>>>>> >> >>> disabled for<br>
>>>>>>>> >> >>> >> >> >>> security<br>
>>>>>>>> >> >>> >> >> >>> > reasons. Rules in iptables are not important,<br>
>>>>>>>> iptables can<br>
>>>>>>>> >> be<br>
>>>>>>>> >> >>> >> >> stopped,<br>
>>>>>>>> >> >>> >> >> >>> or<br>
>>>>>>>> >> >>> >> >> >>> > previously added rules removed.<br>
>>>>>>>> >> >>> >> >> >>> ><br>
>>>>>>>> >> >>> >> >> >>> > Here are the steps to reproduce (kudos to my<br>
>>>>>>>> colleague<br>
>>>>>>>> >> Nenad<br>
>>>>>>>> >> >>> >> >> Bulatovic<br>
>>>>>>>> >> >>> >> >> >>> for<br>
>>>>>>>> >> >>> >> >> >>> > providing this):<br>
>>>>>>>> >> >>> >> >> >>> ><br>
>>>>>>>> >> >>> >> >> >>> > 1.) make sure that ip forwarding is off:<br>
>>>>>>>> >> >>> >> >> >>> > echo 0 > /proc/sys/net/ipv4/ip_forward<br>
>>>>>>>> >> >>> >> >> >>> > 2.) create IP alias on some interface (and have<br>
>>>>>>>> postgres<br>
>>>>>>>> >> >>> listen on<br>
>>>>>>>> >> >>> >> >> it):<br>
>>>>>>>> >> >>> >> >> >>> > ip addr add x.x.x.x/yy dev ethz<br>
>>>>>>>> >> >>> >> >> >>> > 3.) set backend_hostname0 to aforementioned IP<br>
>>>>>>>> >> >>> >> >> >>> > 4.) start pgpool and monitor health checks<br>
>>>>>>>> >> >>> >> >> >>> > 5.) remove IP alias:<br>
>>>>>>>> >> >>> >> >> >>> > ip addr del x.x.x.x/yy dev ethz<br>
>>>>>>>> >> >>> >> >> >>> ><br>
>>>>>>>> >> >>> >> >> >>> ><br>
>>>>>>>> >> >>> >> >> >>> > Here is the interesting part in pgpool log after<br>
>>>>>>>> this:<br>
>>>>>>>> >> >>> >> >> >>> > 2012-01-11 17:38:04 DEBUG: pid 24358: starting<br>
>>>>>>>> health<br>
>>>>>>>> >> checking<br>
>>>>>>>> >> >>> >> >> >>> > 2012-01-11 17:38:04 DEBUG: pid 24358:<br>
>>>>>>>> health_check: 0 th DB<br>
>>>>>>>> >> >>> node<br>
>>>>>>>> >> >>> >> >> >>> status: 2<br>
>>>>>>>> >> >>> >> >> >>> > 2012-01-11 17:38:04 DEBUG: pid 24358:<br>
>>>>>>>> health_check: 1 th DB<br>
>>>>>>>> >> >>> node<br>
>>>>>>>> >> >>> >> >> >>> status: 1<br>
>>>>>>>> >> >>> >> >> >>> > 2012-01-11 17:38:34 DEBUG: pid 24358: starting<br>
>>>>>>>> health<br>
>>>>>>>> >> checking<br>
>>>>>>>> >> >>> >> >> >>> > 2012-01-11 17:38:34 DEBUG: pid 24358:<br>
>>>>>>>> health_check: 0 th DB<br>
>>>>>>>> >> >>> node<br>
>>>>>>>> >> >>> >> >> >>> status: 2<br>
>>>>>>>> >> >>> >> >> >>> > 2012-01-11 17:41:43 DEBUG: pid 24358:<br>
>>>>>>>> health_check: 0 th DB<br>
>>>>>>>> >> >>> node<br>
>>>>>>>> >> >>> >> >> >>> status: 2<br>
>>>>>>>> >> >>> >> >> >>> > 2012-01-11 17:41:46 ERROR: pid 24358: health<br>
>>>>>>>> check failed.<br>
>>>>>>>> >> 0<br>
>>>>>>>> >> >>> th<br>
>>>>>>>> >> >>> >> host<br>
>>>>>>>> >> >>> >> >> >>> > 192.168.2.27 at port 5432 is down<br>
>>>>>>>> >> >>> >> >> >>> > 2012-01-11 17:41:46 LOG: pid 24358: health<br>
>>>>>>>> check retry<br>
>>>>>>>> >> sleep<br>
>>>>>>>> >> >>> >> time:<br>
>>>>>>>> >> >>> >> >> 10<br>
>>>>>>>> >> >>> >> >> >>> > second(s)<br>
>>>>>>>> >> >>> >> >> >>> ><br>
>>>>>>>> >> >>> >> >> >>> > That pgpool was configured with health check<br>
>>>>>>>> interval of<br>
>>>>>>>> >> >>> 30sec,<br>
>>>>>>>> >> >>> >> 5sec<br>
>>>>>>>> >> >>> >> >> >>> > timeout, and 10sec retry delay with 2 max retries.<br>
>>>>>>>> >> >>> >> >> >>> ><br>
>>>>>>>> >> >>> >> >> >>> > Making use of libpq instead for connecting to db<br>
>>>>>>>> in health<br>
>>>>>>>> >> >>> checks<br>
>>>>>>>> >> >>> >> IMO<br>
>>>>>>>> >> >>> >> >> >>> > should resolve it, but you'll best determine<br>
>>>>>>>> which call<br>
>>>>>>>> >> >>> exactly<br>
>>>>>>>> >> >>> >> gets<br>
>>>>>>>> >> >>> >> >> >>> > blocked waiting. Btw, psql with PGCONNECT_TIMEOUT<br>
>>>>>>>> env var<br>
>>>>>>>> >> >>> >> configured<br>
>>>>>>>> >> >>> >> >> >>> > respects that env var timeout.<br>
>>>>>>>> >> >>> >> >> >>> ><br>
>>>>>>>> >> >>> >> >> >>> > Regards,<br>
>>>>>>>> >> >>> >> >> >>> > Stevo.<br>
>>>>>>>> >> >>> >> >> >>> ><br>
>>>>>>>> >> >>> >> >> >>> > On Wed, Jan 11, 2012 at 11:15 AM, Stevo Slaviĉ <<br>
>>>>>>>> >> >>> <a href="mailto:sslavic@gmail.com">sslavic@gmail.com</a><br>
>>>>>>>> >> >>> >> ><br>
>>>>>>>> >> >>> >> >> >>> wrote:<br>
>>>>>>>> >> >>> >> >> >>> ><br>
>>>>>>>> >> >>> >> >> >>> >> Tatsuo,<br>
>>>>>>>> >> >>> >> >> >>> >><br>
>>>>>>>> >> >>> >> >> >>> >> Did you restart iptables after adding rule?<br>
>>>>>>>> >> >>> >> >> >>> >><br>
>>>>>>>> >> >>> >> >> >>> >> Regards,<br>
>>>>>>>> >> >>> >> >> >>> >> Stevo.<br>
>>>>>>>> >> >>> >> >> >>> >><br>
>>>>>>>> >> >>> >> >> >>> >><br>
>>>>>>>> >> >>> >> >> >>> >> On Wed, Jan 11, 2012 at 11:12 AM, Stevo Slaviĉ <<br>
>>>>>>>> >> >>> >> <a href="mailto:sslavic@gmail.com">sslavic@gmail.com</a>><br>
>>>>>>>> >> >>> >> >> >>> wrote:<br>
>>>>>>>> >> >>> >> >> >>> >><br>
>>>>>>>> >> >>> >> >> >>> >>> Looking into this to verify if these are all<br>
>>>>>>>> necessary<br>
>>>>>>>> >> >>> changes<br>
>>>>>>>> >> >>> >> to<br>
>>>>>>>> >> >>> >> >> have<br>
>>>>>>>> >> >>> >> >> >>> >>> port unreachable message silently rejected<br>
>>>>>>>> (suspecting<br>
>>>>>>>> >> some<br>
>>>>>>>> >> >>> >> kernel<br>
>>>>>>>> >> >>> >> >> >>> >>> parameter tuning is needed).<br>
>>>>>>>> >> >>> >> >> >>> >>><br>
>>>>>>>> >> >>> >> >> >>> >>> Just to clarify it's not a problem that host is<br>
>>>>>>>> being<br>
>>>>>>>> >> >>> detected<br>
>>>>>>>> >> >>> >> by<br>
>>>>>>>> >> >>> >> >> >>> pgpool<br>
>>>>>>>> >> >>> >> >> >>> >>> to be down, but the timing when that happens. On<br>
>>>>>>>> >> environment<br>
>>>>>>>> >> >>> >> where<br>
>>>>>>>> >> >>> >> >> >>> issue is<br>
>>>>>>>> >> >>> >> >> >>> >>> reproduced pgpool as part of health check<br>
>>>>>>>> attempt tries<br>
>>>>>>>> >> to<br>
>>>>>>>> >> >>> >> connect<br>
>>>>>>>> >> >>> >> >> to<br>
>>>>>>>> >> >>> >> >> >>> >>> backend and hangs for tcp timeout instead of<br>
>>>>>>>> being<br>
>>>>>>>> >> >>> interrupted<br>
>>>>>>>> >> >>> >> by<br>
>>>>>>>> >> >>> >> >> >>> timeout<br>
>>>>>>>> >> >>> >> >> >>> >>> alarm. Can you verify/confirm please the health<br>
>>>>>>>> check<br>
>>>>>>>> >> retry<br>
>>>>>>>> >> >>> >> timings<br>
>>>>>>>> >> >>> >> >> >>> are not<br>
>>>>>>>> >> >>> >> >> >>> >>> delayed?<br>
>>>>>>>> >> >>> >> >> >>> >>><br>
>>>>>>>> >> >>> >> >> >>> >>> Regards,<br>
>>>>>>>> >> >>> >> >> >>> >>> Stevo.<br>
>>>>>>>> >> >>> >> >> >>> >>><br>
>>>>>>>> >> >>> >> >> >>> >>><br>
>>>>>>>> >> >>> >> >> >>> >>> On Wed, Jan 11, 2012 at 10:50 AM, Tatsuo Ishii <<br>
>>>>>>>> >> >>> >> >> <a href="mailto:ishii@postgresql.org">ishii@postgresql.org</a><br>
>>>>>>>> >> >>> >> >> >>> >wrote:<br>
>>>>>>>> >> >>> >> >> >>> >>><br>
>>>>>>>> >> >>> >> >> >>> >>>> Ok, I did:<br>
>>>>>>>> >> >>> >> >> >>> >>>><br>
>>>>>>>> >> >>> >> >> >>> >>>> # iptables -A FORWARD -j REJECT --reject-with<br>
>>>>>>>> >> >>> >> >> icmp-port-unreachable<br>
>>>>>>>> >> >>> >> >> >>> >>>><br>
>>>>>>>> >> >>> >> >> >>> >>>> on the host where pgpoo is running. And pull<br>
>>>>>>>> network<br>
>>>>>>>> >> cable<br>
>>>>>>>> >> >>> from<br>
>>>>>>>> >> >>> >> >> >>> >>>> backend0 host network interface. Pgpool<br>
>>>>>>>> detected the<br>
>>>>>>>> >> host<br>
>>>>>>>> >> >>> being<br>
>>>>>>>> >> >>> >> >> down<br>
>>>>>>>> >> >>> >> >> >>> >>>> as expected...<br>
>>>>>>>> >> >>> >> >> >>> >>>> --<br>
>>>>>>>> >> >>> >> >> >>> >>>> Tatsuo Ishii<br>
>>>>>>>> >> >>> >> >> >>> >>>> SRA OSS, Inc. Japan<br>
>>>>>>>> >> >>> >> >> >>> >>>> English: <a href="http://www.sraoss.co.jp/index_en.php" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
>>>>>>>> >> >>> >> >> >>> >>>> Japanese: <a href="http://www.sraoss.co.jp" target="_blank">http://www.sraoss.co.jp</a><br>
>>>>>>>> >> >>> >> >> >>> >>>><br>
>>>>>>>> >> >>> >> >> >>> >>>> > Backend is not destination of this message,<br>
>>>>>>>> pgpool<br>
>>>>>>>> >> host<br>
>>>>>>>> >> >>> is,<br>
>>>>>>>> >> >>> >> and<br>
>>>>>>>> >> >>> >> >> we<br>
>>>>>>>> >> >>> >> >> >>> >>>> don't<br>
>>>>>>>> >> >>> >> >> >>> >>>> > want it to ever get it. With command I've<br>
>>>>>>>> sent you<br>
>>>>>>>> >> rule<br>
>>>>>>>> >> >>> will<br>
>>>>>>>> >> >>> >> be<br>
>>>>>>>> >> >>> >> >> >>> >>>> created for<br>
>>>>>>>> >> >>> >> >> >>> >>>> > any source and destination.<br>
>>>>>>>> >> >>> >> >> >>> >>>> ><br>
>>>>>>>> >> >>> >> >> >>> >>>> > Regards,<br>
>>>>>>>> >> >>> >> >> >>> >>>> > Stevo.<br>
>>>>>>>> >> >>> >> >> >>> >>>> ><br>
>>>>>>>> >> >>> >> >> >>> >>>> > On Wed, Jan 11, 2012 at 10:38 AM, Tatsuo<br>
>>>>>>>> Ishii <<br>
>>>>>>>> >> >>> >> >> >>> <a href="mailto:ishii@postgresql.org">ishii@postgresql.org</a>><br>
>>>>>>>> >> >>> >> >> >>> >>>> wrote:<br>
>>>>>>>> >> >>> >> >> >>> >>>> ><br>
>>>>>>>> >> >>> >> >> >>> >>>> >> I did following:<br>
>>>>>>>> >> >>> >> >> >>> >>>> >><br>
>>>>>>>> >> >>> >> >> >>> >>>> >> Do following on the host where pgpool is<br>
>>>>>>>> running on:<br>
>>>>>>>> >> >>> >> >> >>> >>>> >><br>
>>>>>>>> >> >>> >> >> >>> >>>> >> # iptables -A FORWARD -j REJECT<br>
>>>>>>>> --reject-with<br>
>>>>>>>> >> >>> >> >> >>> icmp-port-unreachable -d<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> 133.137.177.124<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> (133.137.177.124 is the host where backend<br>
>>>>>>>> is running<br>
>>>>>>>> >> >>> on)<br>
>>>>>>>> >> >>> >> >> >>> >>>> >><br>
>>>>>>>> >> >>> >> >> >>> >>>> >> Pull network cable from backend0 host<br>
>>>>>>>> network<br>
>>>>>>>> >> interface.<br>
>>>>>>>> >> >>> >> Pgpool<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> detected the host being down as expected.<br>
>>>>>>>> Am I<br>
>>>>>>>> >> missing<br>
>>>>>>>> >> >>> >> >> something?<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> --<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> Tatsuo Ishii<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> SRA OSS, Inc. Japan<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> English:<br>
>>>>>>>> <a href="http://www.sraoss.co.jp/index_en.php" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
>>>>>>>> >> >>> >> >> >>> >>>> >> Japanese: <a href="http://www.sraoss.co.jp" target="_blank">http://www.sraoss.co.jp</a><br>
>>>>>>>> >> >>> >> >> >>> >>>> >><br>
>>>>>>>> >> >>> >> >> >>> >>>> >> > Hello Tatsuo,<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> ><br>
>>>>>>>> >> >>> >> >> >>> >>>> >> > With backend0 on one host just configure<br>
>>>>>>>> following<br>
>>>>>>>> >> >>> rule on<br>
>>>>>>>> >> >>> >> >> other<br>
>>>>>>>> >> >>> >> >> >>> >>>> host<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> where<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> > pgpool is:<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> ><br>
>>>>>>>> >> >>> >> >> >>> >>>> >> > iptables -A FORWARD -j REJECT<br>
>>>>>>>> --reject-with<br>
>>>>>>>> >> >>> >> >> >>> icmp-port-unreachable<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> ><br>
>>>>>>>> >> >>> >> >> >>> >>>> >> > and then have pgpool startup with health<br>
>>>>>>>> checking<br>
>>>>>>>> >> and<br>
>>>>>>>> >> >>> >> >> retrying<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> configured,<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> > and then pull network cable from backend0<br>
>>>>>>>> host<br>
>>>>>>>> >> network<br>
>>>>>>>> >> >>> >> >> >>> interface.<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> ><br>
>>>>>>>> >> >>> >> >> >>> >>>> >> > Regards,<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> > Stevo.<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> ><br>
>>>>>>>> >> >>> >> >> >>> >>>> >> > On Wed, Jan 11, 2012 at 6:27 AM, Tatsuo<br>
>>>>>>>> Ishii <<br>
>>>>>>>> >> >>> >> >> >>> <a href="mailto:ishii@postgresql.org">ishii@postgresql.org</a><br>
>>>>>>>> >> >>> >> >> >>> >>>> ><br>
>>>>>>>> >> >>> >> >> >>> >>>> >> wrote:<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> ><br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> I want to try to test the situation you<br>
>>>>>>>> descrived:<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >><br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> >> > When system is configured for<br>
>>>>>>>> security<br>
>>>>>>>> >> reasons<br>
>>>>>>>> >> >>> not<br>
>>>>>>>> >> >>> >> to<br>
>>>>>>>> >> >>> >> >> >>> return<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> destination<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> >> > host unreachable messages, even<br>
>>>>>>>> though<br>
>>>>>>>> >> >>> >> >> >>> health_check_timeout is<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >><br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> But I don't know how to do it. I pulled<br>
>>>>>>>> out the<br>
>>>>>>>> >> >>> network<br>
>>>>>>>> >> >>> >> >> cable<br>
>>>>>>>> >> >>> >> >> >>> and<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> pgpool detected it as expected. Also I<br>
>>>>>>>> configured<br>
>>>>>>>> >> the<br>
>>>>>>>> >> >>> >> server<br>
>>>>>>>> >> >>> >> >> >>> which<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> PostgreSQL is running on to disable the<br>
>>>>>>>> 5432<br>
>>>>>>>> >> port. In<br>
>>>>>>>> >> >>> >> this<br>
>>>>>>>> >> >>> >> >> case<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> connect(2) returned EHOSTUNREACH (No<br>
>>>>>>>> route to<br>
>>>>>>>> >> host)<br>
>>>>>>>> >> >>> so<br>
>>>>>>>> >> >>> >> >> pgpool<br>
>>>>>>>> >> >>> >> >> >>> >>>> detected<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> the error as expected.<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >><br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> Could you please instruct me?<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> --<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> Tatsuo Ishii<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> SRA OSS, Inc. Japan<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> English:<br>
>>>>>>>> <a href="http://www.sraoss.co.jp/index_en.php" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> Japanese: <a href="http://www.sraoss.co.jp" target="_blank">http://www.sraoss.co.jp</a><br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >><br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> > Hello Tatsuo,<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> ><br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> > Thank you for replying!<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> ><br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> > I'm not sure what exactly is blocking,<br>
>>>>>>>> just by<br>
>>>>>>>> >> >>> pgpool<br>
>>>>>>>> >> >>> >> code<br>
>>>>>>>> >> >>> >> >> >>> >>>> analysis I<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> > suspect it is the part where a<br>
>>>>>>>> connection is<br>
>>>>>>>> >> made<br>
>>>>>>>> >> >>> to<br>
>>>>>>>> >> >>> >> the<br>
>>>>>>>> >> >>> >> >> db<br>
>>>>>>>> >> >>> >> >> >>> and<br>
>>>>>>>> >> >>> >> >> >>> >>>> it<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> doesn't<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> > seem to get interrupted by alarm.<br>
>>>>>>>> Tested<br>
>>>>>>>> >> thoroughly<br>
>>>>>>>> >> >>> >> health<br>
>>>>>>>> >> >>> >> >> >>> check<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> behaviour,<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> > it works really well when host/ip is<br>
>>>>>>>> there and<br>
>>>>>>>> >> just<br>
>>>>>>>> >> >>> >> >> >>> >>>> backend/postgres<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> is<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> > down, but not when backend host/ip is<br>
>>>>>>>> down. I<br>
>>>>>>>> >> could<br>
>>>>>>>> >> >>> >> see in<br>
>>>>>>>> >> >>> >> >> >>> log<br>
>>>>>>>> >> >>> >> >> >>> >>>> that<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> initial<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> > health check and each retry got<br>
>>>>>>>> delayed when<br>
>>>>>>>> >> >>> host/ip is<br>
>>>>>>>> >> >>> >> >> not<br>
>>>>>>>> >> >>> >> >> >>> >>>> reachable,<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> > while when just backend is not<br>
>>>>>>>> listening (is<br>
>>>>>>>> >> down)<br>
>>>>>>>> >> >>> on<br>
>>>>>>>> >> >>> >> the<br>
>>>>>>>> >> >>> >> >> >>> >>>> reachable<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> host/ip<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> > then initial health check and all<br>
>>>>>>>> retries are<br>
>>>>>>>> >> >>> exact to<br>
>>>>>>>> >> >>> >> the<br>
>>>>>>>> >> >>> >> >> >>> >>>> settings in<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> > pgpool.conf.<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> ><br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> > PGCONNECT_TIMEOUT is listed as one of<br>
>>>>>>>> the libpq<br>
>>>>>>>> >> >>> >> >> environment<br>
>>>>>>>> >> >>> >> >> >>> >>>> variables<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> in<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> > the docs (see<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >><br>
>>>>>>>> >> >>> >><br>
>>>>>>>> <a href="http://www.postgresql.org/docs/9.1/static/libpq-envars.html" target="_blank">http://www.postgresql.org/docs/9.1/static/libpq-envars.html</a>)<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> > There is equivalent parameter in libpq<br>
>>>>>>>> >> >>> >> PGconnectdbParams (<br>
>>>>>>>> >> >>> >> >> >>> see<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> ><br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >><br>
>>>>>>>> >> >>> >> >> >>> >>>> >><br>
>>>>>>>> >> >>> >> >> >>> >>>><br>
>>>>>>>> >> >>> >> >> >>><br>
>>>>>>>> >> >>> >> >><br>
>>>>>>>> >> >>> >><br>
>>>>>>>> >> >>><br>
>>>>>>>> >><br>
>>>>>>>> <a href="http://www.postgresql.org/docs/9.1/static/libpq-connect.html#LIBPQ-CONNECT-CONNECT-TIMEOUT" target="_blank">http://www.postgresql.org/docs/9.1/static/libpq-connect.html#LIBPQ-CONNECT-CONNECT-TIMEOUT</a><br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> )<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> > At the beginning of that same page<br>
>>>>>>>> there are<br>
>>>>>>>> >> some<br>
>>>>>>>> >> >>> >> >> important<br>
>>>>>>>> >> >>> >> >> >>> >>>> infos on<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> using<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> > these functions.<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> ><br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> > psql respects PGCONNECT_TIMEOUT.<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> ><br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> > Regards,<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> > Stevo.<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> ><br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> > On Wed, Jan 11, 2012 at 12:13 AM,<br>
>>>>>>>> Tatsuo Ishii <<br>
>>>>>>>> >> >>> >> >> >>> >>>> <a href="mailto:ishii@postgresql.org">ishii@postgresql.org</a>><br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> wrote:<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> ><br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> >> > Hello pgpool community,<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> >> ><br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> >> > When system is configured for<br>
>>>>>>>> security<br>
>>>>>>>> >> reasons<br>
>>>>>>>> >> >>> not<br>
>>>>>>>> >> >>> >> to<br>
>>>>>>>> >> >>> >> >> >>> return<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> destination<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> >> > host unreachable messages, even<br>
>>>>>>>> though<br>
>>>>>>>> >> >>> >> >> >>> health_check_timeout is<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> >> configured,<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> >> > socket call will block and alarm<br>
>>>>>>>> will not get<br>
>>>>>>>> >> >>> raised<br>
>>>>>>>> >> >>> >> >> >>> until TCP<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> timeout<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> >> > occurs.<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> >><br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> >> Interesting. So are you saying that<br>
>>>>>>>> read(2)<br>
>>>>>>>> >> >>> cannot be<br>
>>>>>>>> >> >>> >> >> >>> >>>> interrupted by<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> >> alarm signal if the system is<br>
>>>>>>>> configured not to<br>
>>>>>>>> >> >>> return<br>
>>>>>>>> >> >>> >> >> >>> >>>> destination<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> >> host unreachable message? Could you<br>
>>>>>>>> please<br>
>>>>>>>> >> guide<br>
>>>>>>>> >> >>> me<br>
>>>>>>>> >> >>> >> >> where I<br>
>>>>>>>> >> >>> >> >> >>> can<br>
>>>>>>>> >> >>> >> >> >>> >>>> get<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> >> such that info? (I'm not a network<br>
>>>>>>>> expert).<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> >><br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> >> > Not a C programmer, found some info<br>
>>>>>>>> that<br>
>>>>>>>> >> select<br>
>>>>>>>> >> >>> call<br>
>>>>>>>> >> >>> >> >> >>> could be<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> replace<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> >> with<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> >> > select/pselect calls. Maybe it<br>
>>>>>>>> would be best<br>
>>>>>>>> >> if<br>
>>>>>>>> >> >>> >> >> >>> >>>> PGCONNECT_TIMEOUT<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> value<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> >> > could be used here for connection<br>
>>>>>>>> timeout.<br>
>>>>>>>> >> >>> pgpool<br>
>>>>>>>> >> >>> >> has<br>
>>>>>>>> >> >>> >> >> >>> libpq as<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> >> dependency,<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> >> > why isn't it using libpq for the<br>
>>>>>>>> healthcheck<br>
>>>>>>>> >> db<br>
>>>>>>>> >> >>> >> connect<br>
>>>>>>>> >> >>> >> >> >>> >>>> calls, then<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> >> > PGCONNECT_TIMEOUT would be applied?<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> >><br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> >> I don't think libpq uses<br>
>>>>>>>> select/pselect for<br>
>>>>>>>> >> >>> >> establishing<br>
>>>>>>>> >> >>> >> >> >>> >>>> connection,<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> >> but using libpq instead of homebrew<br>
>>>>>>>> code seems<br>
>>>>>>>> >> to<br>
>>>>>>>> >> >>> be<br>
>>>>>>>> >> >>> >> an<br>
>>>>>>>> >> >>> >> >> >>> idea.<br>
>>>>>>>> >> >>> >> >> >>> >>>> Let me<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> >> think about it.<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> >><br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> >> One question. Are you sure that libpq<br>
>>>>>>>> can deal<br>
>>>>>>>> >> >>> with<br>
>>>>>>>> >> >>> >> the<br>
>>>>>>>> >> >>> >> >> case<br>
>>>>>>>> >> >>> >> >> >>> >>>> (not to<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> >> return destination host unreachable<br>
>>>>>>>> messages)<br>
>>>>>>>> >> by<br>
>>>>>>>> >> >>> using<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> >> PGCONNECT_TIMEOUT?<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> >> --<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> >> Tatsuo Ishii<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> >> SRA OSS, Inc. Japan<br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> >> English:<br>
>>>>>>>> <a href="http://www.sraoss.co.jp/index_en.php" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> >> Japanese: <a href="http://www.sraoss.co.jp" target="_blank">http://www.sraoss.co.jp</a><br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >> >><br>
>>>>>>>> >> >>> >> >> >>> >>>> >> >><br>
>>>>>>>> >> >>> >> >> >>> >>>> >><br>
>>>>>>>> >> >>> >> >> >>> >>>><br>
>>>>>>>> >> >>> >> >> >>> >>><br>
>>>>>>>> >> >>> >> >> >>> >>><br>
>>>>>>>> >> >>> >> >> >>> >><br>
>>>>>>>> >> >>> >> >> >>><br>
>>>>>>>> >> >>> >> >> >><br>
>>>>>>>> >> >>> >> >> >><br>
>>>>>>>> >> >>> >> >><br>
>>>>>>>> >> >>> >><br>
>>>>>>>> >> >>><br>
>>>>>>>> >> >><br>
>>>>>>>> >> >><br>
>>>>>>>> >><br>
>>>>>>>><br>
>>>>>>><br>
>>>>>>><br>
>>>>>><br>
>>>>><br>
>>>><br>
>>><br>
>><br>
</div></div></blockquote></div><br>