[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