[pgpool-general: 3976] Re: How does one terminate/cancel queries via PGPool safely?

[Tatsuo Ishii]

> [ Comments below, in-line ]
> 
> 
> On 08/14/2015 10:51 PM, Tatsuo Ishii wrote:
>>> The PGPool manual has a dire warning about not using
>>> >pg_terminate_backend().
>>> >
>>> >http://www.pgpool.net/docs/latest/pgpool-en.html#restriction
>>> >
>>> >Under "Functionality of PostgreSQL" it states:
>>> >
>>> >    If you use pg_terminate_backend() to stop a backend, this will
>>> >    trigger a failover.
>> This is still correct. PostgreSQL returns the same error code when
>> PostgreSQL is shutting down.
> 
> Hi Tatsuo,
> 
> This morning we had an issue this morning where a query needed to be
> killed.
> 
> I tried to _cancel() it via PGPool and the connection did not die.

How did you send pg_terminate_backend() exactly?

> Next, I went to the DB Server and issued a _terminate().  This caused
> PGPool to degenerate.  Is this expected?

Yes, as stated in the docs.

> I think the answer is yes.
> 
> That is, for any connection which is initiated via PGPool, you cannot
> issue a pg_terminate_backend() to kill a connection.  Either from
> PGPool or psql.
> 
> If the above is true, I suggest we update the documentation to not the
> above.

It's already documented.

------------------------------------------------------------------------
If you use pg_terminate_backend() to stop a backend, this will trigger
a failover. The reason why this happens is that PostgreSQL sends
exactly the same message for a terminated backend as for a full
postmaster shutdown. There is no workaround as of today. Please do not
use this function.
------------------------------------------------------------------------

> My next test, either in Production (yikes!) or in my sandbox is to try
> and issue a signal from the DB server to emulate the _cancel.
> 
> Cheers,
> --
> Pablo Sanchez - Blueoak Database Engineering, Inc
> Ph:    819.459.1926         Blog:  http://pablo-blog.blueoakdb.com
> iNum:  883.5100.0990.1054
>