[pgpool-general: 5319] Multiple pids for one transaction and pg_terminate_backend

Avi Weinberg AviW at gilat.com
Fri Feb 10 01:20:07 JST 2017


Hi

We are using pgpool 3.6.1 with one master and one slave. When we execute a transaction with selects and updates some selects go to the slave and some to the master and all the updates/inserts go to the master. This way for one transaction I see several pids with "idle in transaction". If I want to execute pg_terminate_backend(pid). Which one should I use?  I have seen that if I just choose one pid in some cases it causes a failover even with version 3.6.1 that has a flag that handles such pg_terminate_backend commands.

For example psql -> begin; select * from a; select pg_sleep(1); select b from a; select pg_sleep(1);insert into a (b) values (1);

I use the VIP and port 9999 to execute pg_terminate_backend

What can be done to avoid a failover?



Thanks Avi


From: Muhammad Usama [mailto:m.usama at gmail.com]
Sent: Thursday, February 09, 2017 1:14 PM
To: Avi Weinberg <AviW at gilat.com>
Cc: Tatsuo Ishii <ishii at sraoss.co.jp>; pgpool-general at pgpool.net
Subject: Re: [pgpool-general: 5314] Re: execute pg_terminate_backend() without failover



On Thu, Feb 9, 2017 at 1:32 PM, Avi Weinberg <AviW at gilat.com<mailto:AviW at gilat.com>> wrote:
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?

The query you mentioned you are executing "select pg_terminate_backend(3568);" should work fine with Pgpool-II 3.6. Since it is passing the constant integer value to the pg_terminate_backend argument.
The unsupported cases for pg_terminate_backend with Pgpool-II are when an expression or a sub-query is used as an argument to the function.

For example: The below pg_terminate_backend() queries will not be handled by Pgpool-II

pgpool=# select pg_terminate_backend((select 3568));
pgpool=# select pg_terminate_backend( 3567 +1);



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.

It is mandatory to issue the pg_terminate_backend query through pgpool-II (VIP and port 9999 in your case). Since the Pgpool-II has to know when the pg_terminate_backnd() query is issued so that it can prepare itself for the backend connection termination.

Thanks
Best regards
Muhammad Usama


Thanks
Avi






-----Original Message-----
From: Tatsuo Ishii [mailto:ishii at sraoss.co.jp<mailto:ishii at sraoss.co.jp>]
Sent: Wednesday, February 08, 2017 2:27 AM
To: Avi Weinberg <AviW at gilat.com<mailto:AviW at gilat.com>>
Cc: pgpool-general at pgpool.net<mailto: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.
_______________________________________________
pgpool-general mailing list
pgpool-general at pgpool.net<mailto:pgpool-general at pgpool.net>
http://www.pgpool.net/mailman/listinfo/pgpool-general

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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20170209/740abbf9/attachment-0001.html>


More information about the pgpool-general mailing list