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

[Pablo Sanchez]

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'

::: 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

>>> 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.
-- 
Pablo Sanchez - Blueoak Database Engineering, Inc
Ph:    819.459.1926         Blog:  http://pablo-blog.blueoakdb.com
iNum:  883.5100.0990.1054
-------------- next part --------------
/*

log_line_prefix = '< %m - %a %u %d %r %p %e %s %i >'
                           |  |  |  |  |  |  |  |
                          %a = application name |
                              |  |  |  |  |  |  |
                             %u = user name  |  |
                                 |  |  |  |  |  |
                                %d = database name
                                    |  |  |  |  |
                                   %r = remote host and port
                                       |  |  |  |
                                       %p = process ID
                                          |  |  |
                                         %e = SQL state
                                             |  |
                                            %s = session start timestamp
                                                |
                                               %i = command tag

*/

< 2015-08-12 19:51:05.433 EDT - [unknown] postgres vote4db 10.201.3.1(2083) 11815 57014 2015-08-12 19:44:34 EDT SELECT >STATEMENT:  WITH z as
        (
        WITH marks_by_polls_crosstab as (

        SELECT * FROM crosstab(
        '

        with q as
        (
        with recent_marks as
        (
        SELECT cm.contact_id, max(cm.last_updated) as last_update
        from t_contact_marks cm
        left join t_etag e on e.campaign_id=cm.campaign_id and e.contact_id=cm.contact_id AND (etag_def_id=70 OR etag_def_id=67)

...
< 2015-08-12 22:52:49.513 EDT - [unknown] postgres vote4db 10.201.3.1(2083) 11815 57P01 2015-08-12 19:44:34 EDT SELECT >FATAL:  terminating connection due to administrator command
-------------- next part --------------
2015-08-12 22:52:49 - [unknown] (pid 23093): postgresvote4db: LOG:  reading and processing packets
2015-08-12 22:52:49 - [unknown] (pid 23093): postgresvote4db: DETAIL:  postmaster on DB node 0 was shutdown by administrative command
2015-08-12 22:52:49 - [unknown] (pid 23093): postgresvote4db: LOG:  received degenerate backend request for node_id: 0 from pid [23093]
2015-08-12 22:52:49 - [No Connection] (pid 419): [No Connection][No Connection]: LOG:  watchdog notifying to start interlocking
2015-08-12 22:52:49 - [No Connection] (pid 419): [No Connection][No Connection]: LOG:  watchdog became a new lock holder
2015-08-12 22:52:49 - [No Connection] (pid 486): [No Connection][No Connection]: LOG:  sending watchdog response
2015-08-12 22:52:49 - [No Connection] (pid 486): [No Connection][No Connection]: DETAIL:  WD_STAND_FOR_LOCK_HOLDER received but lock holder already exists
2015-08-12 22:52:50 - [No Connection] (pid 419): [No Connection][No Connection]: LOG:  starting degeneration. shutdown host 10.201.4.1(5432)
2015-08-12 22:52:50 - [No Connection] (pid 419): [No Connection][No Connection]: LOG:  Restart all children