<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p
{mso-style-priority:99;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
span.gmail-gingersoftwaremark
{mso-style-name:gmail-ginger_software_mark;}
span.gmail-hoenzb
{mso-style-name:gmail-hoenzb;}
span.EmailStyle19
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 90.0pt 72.0pt 90.0pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-US" link="blue" vlink="purple">
<div class="WordSection1">
<p>Hi<o:p></o:p></p>
<p>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.<o:p></o:p></p>
<p>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);<o:p></o:p></p>
<p>I use the VIP and port 9999 to execute pg_terminate_backend<o:p></o:p></p>
<p>What can be done to avoid a failover?<o:p></o:p></p>
<p><o:p> </o:p></p>
<p>Thanks Avi<o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><b><span style="font-size:11.0pt;font-family:"Calibri",sans-serif">From:</span></b><span style="font-size:11.0pt;font-family:"Calibri",sans-serif"> Muhammad Usama [mailto:m.usama@gmail.com]
<br>
<b>Sent:</b> Thursday, February 09, 2017 1:14 PM<br>
<b>To:</b> Avi Weinberg <AviW@gilat.com><br>
<b>Cc:</b> Tatsuo Ishii <ishii@sraoss.co.jp>; pgpool-general@pgpool.net<br>
<b>Subject:</b> Re: [pgpool-general: 5314] Re: execute pg_terminate_backend() without failover<o:p></o:p></span></p>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<p class="MsoNormal">On Thu, Feb 9, 2017 at 1:32 PM, Avi Weinberg <<a href="mailto:AviW@gilat.com" target="_blank">AviW@gilat.com</a>> wrote:<o:p></o:p></p>
<blockquote style="border:none;border-left:solid #CCCCCC 1.0pt;padding:0cm 0cm 0cm 6.0pt;margin-left:4.8pt;margin-right:0cm">
<p class="MsoNormal">Thanks for the reply. We are using <span class="gmail-gingersoftwaremark">
pgpool</span> 3.6.1. From the manual I understood that the pg_terminate_backend<span class="gmail-gingersoftwaremark">(</span>) 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
<span class="gmail-gingersoftwaremark">argument</span> is a constant/<br>
I execute it from <span class="gmail-gingersoftwaremark">psql</span> as follows - select pg_terminate_backend<span class="gmail-gingersoftwaremark">(</span>3568);<o:p></o:p></p>
</blockquote>
<blockquote style="border:none;border-left:solid #CCCCCC 1.0pt;padding:0cm 0cm 0cm 6.0pt;margin-left:4.8pt;margin-right:0cm">
<p class="MsoNormal"><br>
From the manual:<br>
" If you use pg_terminate_backend<span class="gmail-gingersoftwaremark">(</span>) to stop a
<span class="gmail-gingersoftwaremark">backend</span>, this will trigger a failover. The reason why this happens is that PostgreSQL sends exactly the same message for a terminated
<span class="gmail-gingersoftwaremark">backend</span> as for a full postmaster <span class="gmail-gingersoftwaremark">
shutdown</span>. There is no workaround prior <span class="gmail-gingersoftwaremark">
of</span> 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
<span class="gmail-gingersoftwaremark">function though</span>."<br>
<br>
How should I execute it so it will not trigger a failover?<o:p></o:p></p>
</blockquote>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">The query you mentioned you are executing "select pg_terminate_backend<span class="gmail-gingersoftwaremark">(</span>3568);" should work fine with Pgpool-II 3.6. Since it is passing the constant integer value to the pg_terminate_backend
argument.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">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.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">For example: The below <span style="font-size:9.5pt">pg_terminate_backend<span class="gmail-gingersoftwaremark">(</span>)</span> queries will not be handled by Pgpool-II<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:9.5pt">pgpool=# select pg_terminate_backend<span class="gmail-gingersoftwaremark">(</span>(select </span>3568<span style="font-size:9.5pt">));</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:9.5pt">pgpool=# select pg_terminate_backend<span class="gmail-gingersoftwaremark">( </span></span>3567<span style="font-size:9.5pt"> +1);</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal"> <o:p></o:p></p>
</div>
<blockquote style="border:none;border-left:solid #CCCCCC 1.0pt;padding:0cm 0cm 0cm 6.0pt;margin-left:4.8pt;margin-right:0cm">
<p class="MsoNormal"><br>
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
<span class="gmail-gingersoftwaremark">server</span> the query is stuck.<o:p></o:p></p>
</blockquote>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">It is mandatory to issue the pg_terminate_backend query through
<span class="gmail-gingersoftwaremark">pgpool-II</span> (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.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Thanks<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">Best regards<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">Muhammad Usama<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<blockquote style="border:none;border-left:solid #CCCCCC 1.0pt;padding:0cm 0cm 0cm 6.0pt;margin-left:4.8pt;margin-right:0cm">
<p class="MsoNormal"><br>
Thanks<br>
<span class="gmail-gingersoftwaremark"><span style="color:#888888">Avi</span></span><o:p></o:p></p>
<div>
<div>
<p class="MsoNormal"><br>
<br>
<br>
<br>
<br>
<br>
-----Original Message-----<br>
From: Tatsuo Ishii [mailto:<a href="mailto:ishii@sraoss.co.jp">ishii@sraoss.co.jp</a>]<br>
Sent: Wednesday, February 08, 2017 2:27 AM<br>
To: Avi Weinberg <<a href="mailto:AviW@gilat.com">AviW@gilat.com</a>><br>
Cc: <a href="mailto:pgpool-general@pgpool.net">pgpool-general@pgpool.net</a><br>
Subject: Re: [<span class="gmail-gingersoftwaremark">pgpool</span>-general: 5305] execute pg_terminate_backend<span class="gmail-gingersoftwaremark">(</span>) without failover<br>
<br>
Please take a look at the FAQ.<br>
<br>
<a href="http://pgpool.net/mediawiki/index.php/FAQ#Can_I_use_pg_cancel_backend.28.29_or_pg_terminate_backend.28.29.3F" target="_blank">http://pgpool.net/mediawiki/index.php/FAQ#Can_I_use_pg_cancel_backend.28.29_or_pg_terminate_backend.28.29.3F</a><br>
<br>
Best regards,<br>
--<br>
Tatsuo Ishii<br>
SRA OSS, Inc. Japan<br>
English: <a href="http://www.sraoss.co.jp/index_en.php" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
Japanese:<a href="http://www.sraoss.co.jp" target="_blank">http://www.sraoss.co.jp</a><br>
<br>
> Hi all,<br>
><br>
><br>
> On some setups when we execute pg_terminate_backend<span class="gmail-gingersoftwaremark">(</span>) on connection that is using the VIP and port 9999 it causes a failover.<br>
><br>
><br>
><br>
> How can we terminate a query without having a failover?<br>
><br>
><br>
><br>
> Is it safe to kill <span class="gmail-gingersoftwaremark">pgpool process</span> for that query. Will it close
<span class="gmail-gingersoftwaremark">pgpool</span> connection and <span class="gmail-gingersoftwaremark">
postgres</span> connection properly?<br>
><br>
><br>
><br>
> If it <span class="gmail-gingersoftwaremark">is not closing</span> it <span class="gmail-gingersoftwaremark">
properly what</span> can we do to prevent reaching the connection limit?<br>
><br>
><br>
><br>
> Does it make a difference if we use connection_cache or not?<br>
><br>
><br>
><br>
> What is the reason that on some setups this causes a restart and on some not.<br>
><br>
><br>
><br>
> Thanks<br>
><br>
> <span class="gmail-gingersoftwaremark">Avi</span><br>
><br>
> 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 <span class="gmail-gingersoftwaremark">e-mail</span> for any purpose nor disclose its contents to any person.<br>
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 <span class="gmail-gingersoftwaremark">e-mail</span> for any purpose nor disclose its contents to any person.<br>
_______________________________________________<br>
pgpool-general mailing list<br>
<a href="mailto:pgpool-general@pgpool.net">pgpool-general@pgpool.net</a><br>
<a href="http://www.pgpool.net/mailman/listinfo/pgpool-general" target="_blank">http://www.pgpool.net/mailman/listinfo/pgpool-general</a><o:p></o:p></p>
</div>
</div>
</blockquote>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
</div>
</div>
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.
</body>
</html>