<div dir="ltr"><br><div class="gmail_extra"><br><div class="gmail_quote">On Thu, Feb 9, 2017 at 1:32 PM, Avi Weinberg <span dir="ltr">&lt;<a href="mailto:AviW@gilat.com" target="_blank">AviW@gilat.com</a>&gt;</span> wrote:<br><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Thanks for the reply.  We are using <span id="gmail-1e5b6799-659f-4111-b5e6-a4be647e4aa3" class="gmail-GINGER_SOFTWARE_mark"><span id="gmail-66ac7f2a-f7cc-441d-8391-29604de7b82d" class="gmail-GINGER_SOFTWARE_mark">pgpool</span></span> 3.6.1.  From the manual I understood that the  pg_terminate_backend<span id="gmail-20a89130-ef90-4aa9-8752-c0e183a6384c" class="gmail-GINGER_SOFTWARE_mark"><span id="gmail-db247d1d-ad65-4e04-9672-158914847afd" class="gmail-GINGER_SOFTWARE_mark">(</span></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 id="gmail-1c59c0c0-a53b-4e2a-a93b-b518a14c069d" class="gmail-GINGER_SOFTWARE_mark"><span id="gmail-23a02cdd-2c2f-4187-9909-b7ffbbfdd8f2" class="gmail-GINGER_SOFTWARE_mark">argument</span></span> is a constant/<br>
I execute it from <span id="gmail-97c4f3f4-f2e5-41f9-a8a7-4a42e71b5608" class="gmail-GINGER_SOFTWARE_mark"><span id="gmail-9f2c39b4-131a-4bec-a996-673f34d99b54" class="gmail-GINGER_SOFTWARE_mark">psql</span></span> as follows - select pg_terminate_backend<span id="gmail-65f38e75-5e6f-4048-9c06-b2449f5d2a08" class="gmail-GINGER_SOFTWARE_mark"><span id="gmail-2776f714-637d-404e-be97-f771cb6c116c" class="gmail-GINGER_SOFTWARE_mark">(</span></span>3568);</blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<br>
>From the manual:<br>
&quot; If you use pg_terminate_backend<span id="gmail-814b5fe8-5f4c-43dc-9930-412a092c30e4" class="gmail-GINGER_SOFTWARE_mark"><span id="gmail-3271e730-6fe1-4410-8aa4-b4b58b35a31c" class="gmail-GINGER_SOFTWARE_mark">(</span></span>) to stop a <span id="gmail-4c477038-f154-4f79-bcfa-a8b3ce5604e1" class="gmail-GINGER_SOFTWARE_mark"><span id="gmail-f4d2ecc8-5cfd-40e1-9bf7-c9f7c06e1119" class="gmail-GINGER_SOFTWARE_mark">backend</span></span>, this will trigger a failover. The reason why this happens is that PostgreSQL sends exactly the same message for a terminated <span id="gmail-95fdd206-0d2e-4b86-a9a6-bd30d155342c" class="gmail-GINGER_SOFTWARE_mark"><span id="gmail-d1976690-37b8-4f43-af03-4352d3c6c9c3" class="gmail-GINGER_SOFTWARE_mark">backend</span></span> as for a full postmaster <span id="gmail-3c279c43-8cca-4d5b-9d70-af3ddf20de75" class="gmail-GINGER_SOFTWARE_mark"><span id="gmail-b5cf608d-0e88-44c6-bc07-3b427f4c0e25" class="gmail-GINGER_SOFTWARE_mark">shutdown</span></span>. There is no workaround prior <span id="gmail-a7b6a24e-da7e-467b-8ea7-5f4a36ca59fa" class="gmail-GINGER_SOFTWARE_mark"><span id="gmail-074bab4b-95c9-434b-b2d5-e28e6ce92572" class="gmail-GINGER_SOFTWARE_mark">of</span></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 id="gmail-a73b4b5d-fb46-4260-9721-713a27f329b6" class="gmail-GINGER_SOFTWARE_mark"><span id="gmail-852800e3-d29b-409c-bbef-905a70dd0caf" class="gmail-GINGER_SOFTWARE_mark">function though</span></span>.&quot;<br>
<br>
How should I execute it so it will not trigger a failover?<br></blockquote><div><br></div><div>The query you mentioned you are executing &quot;select pg_terminate_backend<span id="gmail-0a16ab4c-3f07-435c-8ab1-63a7938d9646" class="gmail-GINGER_SOFTWARE_mark"><span id="gmail-0a28df19-e31f-440c-885b-4b19765c097f" class="gmail-GINGER_SOFTWARE_mark">(</span></span>3568);&quot; should work fine with Pgpool-II 3.6. Since it is passing the constant integer value to the pg_terminate_backend argument.</div><div>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.<br></div><div><br></div><div>For example: The below <span style="font-size:12.8px">pg_terminate_backend<span id="gmail-5a3d1000-8521-49e6-8d11-b1dc7c04252c" class="gmail-GINGER_SOFTWARE_mark">(</span>)</span> queries will not be handled by Pgpool-II</div><div><span style="font-size:12.8px"><br></span></div><div><span style="font-size:12.8px">pgpool=# select pg_terminate_backend<span id="gmail-56be72f9-7b16-4e5e-9ee3-55a1543b1e9e" class="gmail-GINGER_SOFTWARE_mark"><span id="gmail-56be72f9-7b16-4e5e-9ee3-55a1543b1e9e" class="gmail-GINGER_SOFTWARE_mark"><span id="gmail-80e310bd-ebdd-463c-addf-e1a11eee2d92" class="gmail-GINGER_SOFTWARE_mark">(</span></span></span>(select </span>3568<span style="font-size:12.8px">));</span></div><div><span style="font-size:12.8px">pgpool=# select pg_terminate_backend<span id="gmail-1ff53905-5242-4a1e-b2c5-538deb7a2dbf" class="gmail-GINGER_SOFTWARE_mark"><span id="gmail-1ff53905-5242-4a1e-b2c5-538deb7a2dbf" class="gmail-GINGER_SOFTWARE_mark"><span id="gmail-f814b5fe-e834-4195-b41e-5ceaddcecc20" class="gmail-GINGER_SOFTWARE_mark"><span id="gmail-9678f8dc-8661-450a-9182-3fcc3ae9c986" class="gmail-GINGER_SOFTWARE_mark">( </span></span></span></span></span>3567<span style="font-size:12.8px"> +1);</span><br></div><div><br></div><div> </div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<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 id="gmail-63da5fd6-7891-4632-9e74-8eb538e46798" class="gmail-GINGER_SOFTWARE_mark"><span id="gmail-2725bf2d-8c77-426f-8082-87e39d4c6a30" class="gmail-GINGER_SOFTWARE_mark">server</span></span> the query is stuck.<br></blockquote><div><br></div><div>It is mandatory to issue the pg_terminate_backend query through <span id="gmail-d8e1d462-4410-4fc6-8719-eb930fe25e70" class="gmail-GINGER_SOFTWARE_mark">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.</div><div><br></div><div>Thanks</div><div>Best regards</div><div>Muhammad Usama</div><div><br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<br>
Thanks<br>
<span class="gmail-HOEnZb"><font color="#888888"><span id="gmail-bbd356fb-c05a-4d13-a1e0-55652a12e603" class="gmail-GINGER_SOFTWARE_mark"><span id="gmail-3cc7480d-cf36-42df-a58d-328365fb4317" class="gmail-GINGER_SOFTWARE_mark">Avi</span></span><br>
</font></span><div class="gmail-HOEnZb"><div class="gmail-h5"><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 &lt;<a href="mailto:AviW@gilat.com">AviW@gilat.com</a>&gt;<br>
Cc: <a href="mailto:pgpool-general@pgpool.net">pgpool-general@pgpool.net</a><br>
Subject: Re: [<span id="gmail-f02ac33b-66a4-49df-a7c8-a3a82e33f5a3" class="gmail-GINGER_SOFTWARE_mark"><span id="gmail-36abc92c-6d8d-4891-a408-e544f60692a7" class="gmail-GINGER_SOFTWARE_mark">pgpool</span></span>-general: 5305] execute pg_terminate_backend<span id="gmail-15e1d245-09f7-4987-8cdc-7cb6a70a867e" class="gmail-GINGER_SOFTWARE_mark"><span id="gmail-31c7d0cc-1369-4bfa-bd27-0bedda7c5759" class="gmail-GINGER_SOFTWARE_mark">(</span></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" rel="noreferrer" target="_blank">http://pgpool.net/mediawiki/<wbr>index.php/FAQ#Can_I_use_pg_<wbr>cancel_backend.28.29_or_pg_<wbr>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" rel="noreferrer" target="_blank">http://www.sraoss.co.jp/index_<wbr>en.php</a><br>
Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.<wbr>jp</a><br>
<br>
&gt; Hi all,<br>
&gt;<br>
&gt;<br>
&gt; On some setups when we execute pg_terminate_backend<span id="gmail-c1b03f31-310a-4911-ae95-ac1d25afa835" class="gmail-GINGER_SOFTWARE_mark"><span id="gmail-f8f83f38-dad7-42c4-bb24-baa3a48b5228" class="gmail-GINGER_SOFTWARE_mark">(</span></span>) on connection that is using the VIP and port 9999 it causes a failover.<br>
&gt;<br>
&gt;<br>
&gt;<br>
&gt; How can we terminate a query without having a failover?<br>
&gt;<br>
&gt;<br>
&gt;<br>
&gt; Is it safe to kill <span id="gmail-570690cb-c88d-4156-9d88-49925437648a" class="gmail-GINGER_SOFTWARE_mark"><span id="gmail-54282059-ef3a-42f7-98a5-e140351cb3cf" class="gmail-GINGER_SOFTWARE_mark">pgpool process</span></span> for that query.  Will it close <span id="gmail-b319176a-8b4f-4eef-91f7-2686224abedb" class="gmail-GINGER_SOFTWARE_mark"><span id="e6fd56e1-394e-4c5d-9b67-d9b50bf3b810" class="gmail-GINGER_SOFTWARE_mark gmail-GINGER_SOFTWARE_mark gmail-GINGER_SOFTWARE_mark">pgpool</span></span> connection and <span id="gmail-aeb27fab-8da9-4943-a59b-48bc15fbf06b" class="gmail-GINGER_SOFTWARE_mark"><span id="gmail-61d633fc-7531-4737-8f0a-34a8260dd4d8" class="gmail-GINGER_SOFTWARE_mark">postgres</span></span> connection properly?<br>
&gt;<br>
&gt;<br>
&gt;<br>
&gt; If it <span id="gmail-b6168af5-5534-46d7-8d13-530c9a40dcdb" class="gmail-GINGER_SOFTWARE_mark"><span id="gmail-0d095b23-ce8e-415d-80de-b64b00bd3672" class="gmail-GINGER_SOFTWARE_mark">is not closing</span></span> it <span id="gmail-60efa2cb-2331-47ab-8f8d-0d988ef5de03" class="gmail-GINGER_SOFTWARE_mark"><span id="gmail-6951599b-e7ef-4d81-bb1e-572282650519" class="gmail-GINGER_SOFTWARE_mark">properly what</span></span> can we do to prevent reaching the connection limit?<br>
&gt;<br>
&gt;<br>
&gt;<br>
&gt; Does it make a difference if we use connection_cache or not?<br>
&gt;<br>
&gt;<br>
&gt;<br>
&gt; What is the reason that on some setups this causes a restart and on some not.<br>
&gt;<br>
&gt;<br>
&gt;<br>
&gt; Thanks<br>
&gt;<br>
&gt; <span id="gmail-893415c3-b853-4e78-acda-dde93189737a" class="gmail-GINGER_SOFTWARE_mark"><span id="gmail-8409edfc-2f77-413f-842f-5c5b72549b2a" class="gmail-GINGER_SOFTWARE_mark">Avi</span></span><br>
&gt;<br>
&gt; 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 id="gmail-f7117261-9a82-43fc-9b99-77f9423118a4" class="gmail-GINGER_SOFTWARE_mark"><span id="gmail-ab10b242-14d9-416a-866f-159c68510b52" class="gmail-GINGER_SOFTWARE_mark">e-mail</span></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 id="gmail-8b557967-6ad6-40a4-8829-666541ec4460" class="gmail-GINGER_SOFTWARE_mark"><span id="gmail-721dca9f-0e62-4af8-8fd9-937e4dce52ec" class="gmail-GINGER_SOFTWARE_mark">e-mail</span></span> for any purpose nor disclose its contents to any person.<br>
______________________________<wbr>_________________<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" rel="noreferrer" target="_blank">http://www.pgpool.net/mailman/<wbr>listinfo/pgpool-general</a><br>
</div></div></blockquote></div><br></div></div>