[pgpool-general: 5314] Re: execute pg_terminate_backend() without failover

[Avi Weinberg]

Thanks for the reply.  We are using pgpool 3.6.1.  From the manual I understood that the  pg_terminate_backend() in versions 3.6 and above as long as the argument is a constant.  Can someone please explain or give an example what does it mean argument is a constant/
I execute it from psql as follows - select pg_terminate_backend(3568);

>From the manual:
" 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 prior of version 3.6. From version 3.6, this limitation has been mitigated. If the argument to the function (that is a process id) is a constant, you can safely use the function. In extended protocol mode, you cannot use the function though."

How should I execute it so it will not trigger a failover?

In addition, does it make a difference if I connect directly to the physical IP and port 5432 or if I connect with the VIP and port 9999?  In this case I prefer the physical IP and port 5432 since I know on with server the query is stuck.

Thanks
Avi






-----Original Message-----
From: Tatsuo Ishii [mailto:ishii at sraoss.co.jp]
Sent: Wednesday, February 08, 2017 2:27 AM
To: Avi Weinberg <AviW at gilat.com>
Cc: pgpool-general at pgpool.net
Subject: Re: [pgpool-general: 5305] execute pg_terminate_backend() without failover

Please take a look at the FAQ.

http://pgpool.net/mediawiki/index.php/FAQ#Can_I_use_pg_cancel_backend.28.29_or_pg_terminate_backend.28.29.3F

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

> Hi all,
>
>
> On some setups when we execute pg_terminate_backend() on connection that is using the VIP and port 9999 it causes a failover.
>
>
>
> How can we terminate a query without having a failover?
>
>
>
> Is it safe to kill pgpool process for that query.  Will it close pgpool connection and postgres connection properly?
>
>
>
> If it is not closing it properly what can we do to prevent reaching the connection limit?
>
>
>
> Does it make a difference if we use connection_cache or not?
>
>
>
> What is the reason that on some setups this causes a restart and on some not.
>
>
>
> Thanks
>
> Avi
>
> IMPORTANT - This email and any attachments is intended for the above named addressee(s), and may contain information which is confidential or privileged. If you are not the intended recipient, please inform the sender immediately and delete this email: you should not copy or use this e-mail for any purpose nor disclose its contents to any person.
IMPORTANT - This email and any attachments is intended for the above named addressee(s), and may contain information which is confidential or privileged. If you are not the intended recipient, please inform the sender immediately and delete this email: you should not copy or use this e-mail for any purpose nor disclose its contents to any person.