[pgpool-hackers: 1723] Re: WIP handling pg_terminate_backend function in pgpool-II

[Tatsuo Ishii]

What if pg_terminate_backend() is called in an extended query?

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

> Hi
> 
> 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.
> 
> Problems needed to be addressed for pg_terminated_backend()
> ======
> 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.
> 
> 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.
> 
> 
> How is this patch trying to solve this?
> ===========
> 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
> 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.
> 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.
> 
> 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.
> 
> 
> Issues with this approach
> ====
> 1- What to do if somehow two or more connections to different backend
> PostgreSQL servers have the same PID.
> 
> 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
>  e.g
> pgpool=# select pg_terminate_backend((select 1));
> pgpool=# select pg_terminate_backend( 2 +1);
> 
> 
> 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.
> 
> TODOs
> =====
> 
> 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.
> 
> 
> What are your thoughts and suggestions on this approach?
> 
> Many thanks in anticipation.
> Best regards
> Muhammad Usama