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