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

Tatsuo Ishii ishii at postgresql.org
Sat Aug 15 11:51:15 JST 2015


> Hi everyone,
> 
> This is somewhat of a long email message because there appears to be
> some conflicting information on the mailing list on how to
> terminate/cancel queries.  Most of the messages say do not
> terminate/cancel queries via PGPool however Yugo had helped me (see
> 'pgpool-general: 3627' below) to get terminate to work.
> 
> What I want to know is the proper way to have an end-user terminate
> their connection through PGPool, without it causing PGPool to
> degenerate (fail):
> 
>    o pg_cancel_backend()
>    o pg_terminate_backend()
> 
> and a follow up question:  if one of the above calls causes PGPool to
> degenerate, is there a way to tell PGPool to ignore the call?
> 
> Thanks a lot!
> 
> ::: Background :::
> 
> Earlier in the week one of our users issued a pg_terminate_backend()
> followed by a pg_cancel_backend().  This caused PGPool
> (V3_4_2-1-g129ae15) to degenerate.  :(
> 
> I'm attaching both the PGPool and PG log files.  The command was
> issued at '2015-08-12 22:52:49'
> 
> In pgpool.conf, we have set 'fail_over_on_backend_error = off'
> 
> Our current version of PGPool is `V3_4_2-1-g129ae15'

Yes, that's an expected behavior of pg_terminate_backend();

> ::: PGPool-General History on cancelling/terminating Connections :::
> 
> Scanning this year's email, there have been several threads which
> discuss cancelling/terminating connections via PGPool.  One of them
> was initiated by me.  :)
> 
> It's possible over time between 3.4.2 and 3.4.3 cancelling /
> terminating has changed.
> 
>>>> pgpool-general: 3627 <<<
> 
> http://www.sraoss.jp/pipermail/pgpool-general/2015-April/003675.html
> 
> Yugo helped me figure out 'pg_terminate_backend' was causing an issue
> with degeneration I had back in April.  Once I set
> 'fail_over_on_backend_error = off' though, the problem was resolved.
> 
>>>> pgpool-general: 3679 <<<
> 
> http://www.sraoss.jp/pipermail/pgpool-general/2015-May/003732.html
> 
> The summary of this thread is to not use PGPool to issue
> cancel/terminate.
> 
>>>> pgpool-general: 3486 <<<
> 
> http://www.sraoss.jp/pipermail/pgpool-general/2015-February/003530.html
> 
> The question asked whether there's an alternative to using
> 'pg_terminate_backend'  Tatsuo says no because 'pg_cancel_backend' has
> the same effect ... it sends SIGTERM to the backend process.
> 
> According to the 9.3 PG doc(*) states:
> 
>    pg_cancel_backend and pg_terminate_backend send signals (SIGINT or
>    SIGTERM respectively) ...
> 
> In other words:
> 
>    pg_cancel_backend()    = SIGINT
>    pg_terminate_backend() = SIGTERM
> 
> * - http://www.postgresql.org/docs/9.3/static/functions-admin.html

You are right. Sorry I was wrong. You can safely use
pg_cancel_backend() with pgpool-II.

>>>> PGPool Manual <<<
> 
> 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.

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


More information about the pgpool-general mailing list