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

Pablo Sanchez pablo at blueoakdb.com
Tue Aug 18 22:14:28 JST 2015


[ Comments below, in-line ]

On 08/17/2015 09:42 PM, Tatsuo Ishii wrote:
>>
>> 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?

On the DB server I used psql:

    select pg_terminate_backend(<<pid>>);

Did you mean to ask how did I issue the _cancel?  If so, I did it
similarly to the _terminate:

    select pg_cancel_backend(<<pid>>);

FWIW, I've upgraded to the latest version of PGPool via git.

> It's already documented.

Might I suggest the following change to make it crystal clear?

From:

    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.

To:

    For any PGPool initiated connection, do not issue a
    pg_terminate_backend().  Do not issue the command from within
    PGPool or from the DB server.

    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.

    For any DB user which is using PGPool, it is highly recommended the
    function be revoked.

Cheers,
--
Pablo Sanchez - Blueoak Database Engineering, Inc
Ph:    819.459.1926         Blog:  http://pablo-blog.blueoakdb.com
iNum:  883.5100.0990.1054



More information about the pgpool-general mailing list