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

[Muhammad Usama]

On Thu, Feb 9, 2017 at 1:32 PM, Avi Weinberg <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]
> 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.
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20170209/c994bca8/attachment.html>