<div dir="ltr"><br><div class="gmail_extra"><br><div class="gmail_quote">On Tue, Jul 26, 2016 at 5:07 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:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">What if pg_terminate_backend() is called in an extended query?<br></blockquote><div><br></div><div>My bad, I totally missed out the extended query part. I will implement that on the same footing if you feel comfortable with the design.</div><div><br></div><div>Best regards</div><div>Muhammad Usama</div><div><br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<br>
Best regards,<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>
<div class="HOEnZb"><div class="h5"><br>
> Hi<br>
><br>
> Please find the attached work in progress patch for the handling of<br>
> pg_terminate_backend() in pgpool-II. There is still some work need to be<br>
> done on the patch, but I am sharing it to get the thoughts of a wider group<br>
> of audience on the idea and the design.<br>
><br>
> Problems needed to be addressed for pg_terminated_backend()<br>
> ======<br>
> 1- Since the pg_terminate_backend just go on and kills the child connection<br>
> it is treated as the backend failure by pgpool-II and depending on the<br>
> pgpool-II configuration pgpool-II failover that backend node.<br>
><br>
> 2- The current logic in pgpool-II have no idea of which backend node the<br>
> pg_terminate_backend call is intended to, and it is routed on the basis of<br>
> logic in pool_where_to_send() function that can send the query to the node<br>
> that does not have the connection with the pid.<br>
><br>
><br>
> How is this patch trying to solve this?<br>
> ===========<br>
> After parsing the query in SimpleQuery() function the patch runs the<br>
> function walker on the parsed query tree to find the<br>
> pg_terminate_backend("const integer") call. And if the function is found in<br>
> the query the second step is to locate the pgpool-II child process that<br>
> contains the backend connection with PID specified in<br>
> pg_terminate_backend() function call. For that<br>
> the patch loops on all pgpool-II child process and try to locate<br>
> the backend connection with the specific PID by comparing the PID stored in<br>
> the shared memory ConnectionInfos. If the search is successful and the<br>
> child process with the backend connection having the same PID is found, the<br>
> shared memory "ConnectionInfo->backend_terminated"(added by this patch)<br>
> flag is set to true for that connection.<br>
> Once, after the backend node is identified and the flag is set, the next<br>
> step is to rout the query to the exact backend node that contains the<br>
> connection. For that the patch refrains from calling the<br>
> pool_where_to_send() for this query and explicitly sets the query<br>
> destination to the backend node that has the connection so that the query<br>
> should lands on the proper backend node.<br>
><br>
> Now when the query is successfully executed on the backend server and<br>
> consequently the backend node kills the said connection to the pgpool, It<br>
> results in the communication error in pgpool-II child process. So to make<br>
> sure that the child process does not failover that backend node and the<br>
> child process checks the "ConnectionInfo->backend_terminated" flag before<br>
> performing the failover, and if the flag is set, it just does not proceed<br>
> with failover and bails out by FATAL error that kills that<br>
> particular pgpool-II child process which eventually gets re-spawned by<br>
> pgpool main.<br>
><br>
><br>
> Issues with this approach<br>
> ====<br>
> 1- What to do if somehow two or more connections to different backend<br>
> PostgreSQL servers have the same PID.<br>
><br>
> 2- We can only support pg_terminate_backend(constant number) function<br>
> calls. If the expression or sub query is used in the argument of<br>
> pg_terminate_backend then it would not be handled<br>
> e.g<br>
> pgpool=# select pg_terminate_backend((select 1));<br>
> pgpool=# select pg_terminate_backend( 2 +1);<br>
><br>
><br>
> 3- One very remote scenario where after setting the flag<br>
> ConnectionInfo->backend_terminated to true and before the execution of<br>
> pg_terminate_backend the backend actually gets fail, will still be treated<br>
> as pg_terminate_backend case. But I believe it would be harmless since that<br>
> backend node failure will be caught by the child process sending the<br>
> pg_terminate_backend to that same backend.<br>
><br>
> TODOs<br>
> =====<br>
><br>
> If the pg_terminate_backend() fails on the backend because of permission or<br>
> any other issue ,we need to reset the ConnectionInfo->backend_terminated<br>
> flag.<br>
><br>
><br>
> What are your thoughts and suggestions on this approach?<br>
><br>
> Many thanks in anticipation.<br>
> Best regards<br>
> Muhammad Usama<br>
</div></div></blockquote></div><br></div></div>