<div dir="ltr">It will be great to get this fix committed.<div><br></div><div>Are you planning to back port this fix?</div></div><div class="gmail_extra"><br><div class="gmail_quote">On Wed, May 4, 2016 at 12:27 PM, Muhammad Usama <span dir="ltr"><<a href="mailto:m.usama@gmail.com" target="_blank">m.usama@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><br><div class="gmail_extra"><br><div class="gmail_quote"><span class="">On Wed, May 4, 2016 at 5:53 AM, Tatsuo Ishii <span dir="ltr"><<a href="mailto:ishii@postgresql.org" target="_blank">ishii@postgresql.org</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-style:solid;border-left-color:rgb(204,204,204);padding-left:1ex">Usama,<br>
<br>
Thank you for testing the patch. Shall I commit/push it after you fix<br>
the regression test issue? Or shall I commit/push it now? Either is<br>
fine for me.<br></blockquote><div><br></div></span><div style="font-size:12.8px"><br>I have already pushed the fix for regression failure, and was waiting for the buildfarm results for the confirmation. Todays results verified the fix, and you can go ahead with committing the patch.</div><div style="font-size:12.8px"><br></div><div><span style="font-size:12.8px">Kind regards</span></div><span class="HOEnZb"><font color="#888888"><div>Muhammad Usama </div></font></span><div><div class="h5"><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-style:solid;border-left-color:rgb(204,204,204);padding-left:1ex">
<br>
Best regards,<br>
<div><div>--<br>
Tatsuo Ishii<br>
SRA OSS, Inc. Japan<br>
English: <a href="http://www.sraoss.co.jp/index_en.php" rel="noreferrer" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.jp</a><br>
<br>
> Hi Ishii-San<br>
><br>
> I have tested the patch, It successfully takes care of the very annoying<br>
> problem and it is working as expected.<br>
><br>
> Best regards<br>
> Muhammad Usama<br>
><br>
><br>
> On Tue, May 3, 2016 at 5:25 PM, Tatsuo Ishii <<a href="mailto:ishii@postgresql.org" target="_blank">ishii@postgresql.org</a>> wrote:<br>
><br>
>> Currently any attempt to connect to pgpool fails if pgpool is doing<br>
>> health check against failed node even if fail_over_on_backend_error is<br>
>> off because pgpool child first tries to connect to all backend<br>
>> including the failed one and exits if it fails to connect to a backend<br>
>> (of course it fails). This is a temporary situation and will be<br>
>> resolved before pgpool executes failover. However if the health check<br>
>> is retrying, the temporary situation keeps longer depending on the<br>
>> setting of health_check_max_retries and health_check_retry_delay. This<br>
>> is not good. Attached patch tries to mitigate the problem:<br>
>><br>
>> - When an attempt to connect to backend fails, give up connecting to<br>
>> the failed node and skip to other node, rather than exiting the<br>
>> process if operating in streaming replication mode and the node is<br>
>> not primary node.<br>
>><br>
>> - Mark the local status of the failed node to "down".<br>
>><br>
>> - This will let the primary node be selected as a load balance node<br>
>> and every queries will be sent to the primary node. If there's other<br>
>> healthy standby nodes, one of them will be chosen as the load<br>
>> balance node.<br>
>><br>
>> - After the session is over, the child process will suicide to not<br>
>> retain the local status.<br>
>><br>
>> Comments?<br>
>> --<br>
>> Tatsuo Ishii<br>
>> SRA OSS, Inc. Japan<br>
>> English: <a href="http://www.sraoss.co.jp/index_en.php" rel="noreferrer" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
>> Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.jp</a><br>
>><br>
>> diff --git a/src/include/pool.h b/src/include/pool.h<br>
>> index 4c6e82f..1f43efd 100644<br>
>> --- a/src/include/pool.h<br>
>> +++ b/src/include/pool.h<br>
>> @@ -323,6 +323,7 @@ extern int my_master_node_id;<br>
>> */<br>
>> #define PRIMARY_NODE_ID (Req_info->primary_node_id >=0?\<br>
>><br>
>> Req_info->primary_node_id:REAL_MASTER_NODE_ID)<br>
>> +#define IS_PRIMARY_NODE_ID(node_id) (node_id == PRIMARY_NODE_ID)<br>
>><br>
>> /*<br>
>> * Real primary node id. If not in the mode or there's no primary<br>
>> diff --git a/src/protocol/pool_connection_pool.c<br>
>> b/src/protocol/pool_connection_pool.c<br>
>> index b7cc946..7c33366 100644<br>
>> --- a/src/protocol/pool_connection_pool.c<br>
>> +++ b/src/protocol/pool_connection_pool.c<br>
>> @@ -812,8 +812,8 @@ static POOL_CONNECTION_POOL_SLOT<br>
>> *create_cp(POOL_CONNECTION_POOL_SLOT *cp, int s<br>
>> }<br>
>><br>
>> /*<br>
>> - * create actual connections to backends<br>
>> - * new connection resides in TopMemoryContext<br>
>> + * Create actual connections to backends.<br>
>> + * New connection resides in TopMemoryContext.<br>
>> */<br>
>> static POOL_CONNECTION_POOL *new_connection(POOL_CONNECTION_POOL *p)<br>
>> {<br>
>> @@ -851,12 +851,34 @@ static POOL_CONNECTION_POOL<br>
>> *new_connection(POOL_CONNECTION_POOL *p)<br>
>> ereport(FATAL,<br>
>> (errmsg("failed to create a<br>
>> backend connection"),<br>
>> errdetail("executing<br>
>> failover on backend")));<br>
>> - }<br>
>> + }<br>
>> else<br>
>> {<br>
>> - ereport(FATAL,<br>
>> - (errmsg("failed to create a<br>
>> backend connection"),<br>
>> - errdetail("not executing<br>
>> failover because fail_over_on_backend_error is off")));<br>
>> + /*<br>
>> + * If we are in streaming replication mode<br>
>> and the node is a<br>
>> + * standby node, then we skip this node to<br>
>> avoid fail over.<br>
>> + */<br>
>> + if (STREAM && !IS_PRIMARY_NODE_ID(i))<br>
>> + {<br>
>> + ereport(LOG,<br>
>> + (errmsg("failed to<br>
>> create a backend %d connection", i),<br>
>> + errdetail("skip<br>
>> this backend because because fail_over_on_backend_error is off and we are<br>
>> in streaming replication mode and node is standby node")));<br>
>> +<br>
>> + /* set down status to local status<br>
>> area */<br>
>> + *(my_backend_status[i]) = CON_DOWN;<br>
>> +<br>
>> + /* make sure that we need to<br>
>> restart the process after<br>
>> + * finishing this session<br>
>> + */<br>
>> +<br>
>> pool_get_my_process_info()->need_to_restart = 1;<br>
>> + continue;<br>
>> + }<br>
>> + else<br>
>> + {<br>
>> + ereport(FATAL,<br>
>> + (errmsg("failed to<br>
>> create a backend %d connection", i),<br>
>> + errdetail("not<br>
>> executing failover because fail_over_on_backend_error is off")));<br>
>> + }<br>
>> }<br>
>> child_exit(POOL_EXIT_AND_RESTART);<br>
>> }<br>
>> diff --git a/src/utils/pool_process_reporting.c<br>
>> b/src/utils/pool_process_reporting.c<br>
>> index 9b190c7..6cfd860 100644<br>
>> --- a/src/utils/pool_process_reporting.c<br>
>> +++ b/src/utils/pool_process_reporting.c<br>
>> @@ -5,7 +5,7 @@<br>
>> * pgpool: a language independent connection pool server for PostgreSQL<br>
>> * written by Tatsuo Ishii<br>
>> *<br>
>> - * Copyright (c) 2003-2015 PgPool Global Development Group<br>
>> + * Copyright (c) 2003-2016 PgPool Global Development Group<br>
>> *<br>
>> * Permission to use, copy, modify, and distribute this software and<br>
>> * its documentation for any purpose and without fee is hereby<br>
>><br>
>> _______________________________________________<br>
>> pgpool-hackers mailing list<br>
>> <a href="mailto:pgpool-hackers@pgpool.net" target="_blank">pgpool-hackers@pgpool.net</a><br>
>> <a href="http://www.pgpool.net/mailman/listinfo/pgpool-hackers" rel="noreferrer" target="_blank">http://www.pgpool.net/mailman/listinfo/pgpool-hackers</a><br>
>><br>
>><br>
</div></div></blockquote></div></div></div><br></div></div>
<br>_______________________________________________<br>
pgpool-hackers mailing list<br>
<a href="mailto:pgpool-hackers@pgpool.net">pgpool-hackers@pgpool.net</a><br>
<a href="http://www.pgpool.net/mailman/listinfo/pgpool-hackers" rel="noreferrer" target="_blank">http://www.pgpool.net/mailman/listinfo/pgpool-hackers</a><br>
<br></blockquote></div><br><br clear="all"><div><br></div>-- <br><div class="gmail_signature">Ahsan Hadi<br>Snr Director Product Development<br>EnterpriseDB Corporation<br>The Enterprise Postgres Company<br><br>Phone: +92-51-8358874 <br>Mobile: +92-333-5162114<br><br>Website: <a href="http://www.enterprisedb.com" target="_blank">www.enterprisedb.com</a><br>EnterpriseDB Blog: <a href="http://blogs.enterprisedb.com/" target="_blank">http://blogs.enterprisedb.com/</a><br>Follow us on Twitter: <a href="http://www.twitter.com/enterprisedb" target="_blank">http://www.twitter.com/enterprisedb</a><br><br>This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message.</div>
</div>