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

[Muhammad Usama]

On Tue, Jul 26, 2016 at 5:07 AM, Tatsuo Ishii <ishii at postgresql.org> wrote:

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

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.

Best regards
Muhammad Usama


> 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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20160727/74b9d719/attachment-0001.html>