<div dir="ltr"><span style="color:rgb(33,33,33);font-family:Calibri,sans-serif;font-size:14.6667px">Hi Team,</span> <div><br></div><div><span style="color:rgb(33,33,33);font-family:Calibri,sans-serif;font-size:14.6667px">We are getting too many &#39;idle in transaction&#39; connections in pgpool server and the related PIDs are missing in postgres servers i.e connections are getting closed in postgres servers( not sure what is happening ).</span></div><div><br></div><p style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(33,33,33)">Environment Details : Two pgpool servers( 3.7.11 ) with [ one master and three slave Postgres servers (9.5) ]</p><div><span style="color:rgb(33,33,33);font-family:Calibri,sans-serif;font-size:11pt">                      Application side : Java with struts framework and deploying in Wildfly servers(9).</span></div><div><br></div><p style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(33,33,33)">1.Can not reproduce the issue every time.</p><p style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(33,33,33)"> </p><p style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(33,33,33)">2. Pgpool.conf params :</p><p style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(33,33,33)"><br></p><p style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(33,33,33)">                connection_cache = on<br>                reset_query_list = &#39;ABORT; DISCARD ALL&#39;<br>                                                                                                                             <br>                #reset_query_list = &#39;ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT&#39;<br>                num_init_children = 1000<br><br>                max_pool = 1<br>                # - Life time -<br>                child_life_time = 300<br>                child_max_connections = 0<br>                connection_life_time = 0<br>                client_idle_limit = 0<br>                connect_timeout = 100000<br>                client_idle_limit_in_recovery = 0<br></p><p style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(33,33,33)">               </p><p style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(33,33,33)">3. pids which got struck in pgpool servers and disappear in postgres servers</p><p style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(33,33,33)"> </p><p style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(33,33,33)">                1). From the pgpool servers:</p><p style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(33,33,33)"> </p><p style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(33,33,33)">                (i) In Pgpool :</p><p style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(33,33,33)">                root     30659  6239  0 13:17 ?        00:00:00 pgpool: postgres  postgres  10.19.61.122     idle in transaction</p><p style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(33,33,33)">                root     32688  6239  0 13:29 ?        00:00:00 pgpool: postgres  postgres  10.19.61.122     idle in transaction</p><p style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(33,33,33)">               </p><p style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(33,33,33)">                (ii) show pool_pools;</p><p style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(33,33,33)">                30659    | 2020-02-08 13:17:26 | 0       | 0          | postgres | postgres     | 2020-02-08 13:47:53 | 3            | 0            | 1            | 378276          | 1</p><p style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(33,33,33)">                30659    | 2020-02-08 13:17:26 | 0       | 1          | postgres | postgres     | 2020-02-08 13:47:53 | 3            | 0            | 1            | 446378          | 1</p><p style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(33,33,33)">                30659    | 2020-02-08 13:17:26 | 0       | 2          | postgres | postgres     | 2020-02-08 13:47:53 | 3            | 0            | 1            | 26020           | 1</p><p style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(33,33,33)">                30659    | 2020-02-08 13:17:26 | 0       | 3          | postgres | postgres     | 2020-02-08 13:47:53 | 3            | 0            | 1            | 346430          | 1</p><p style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(33,33,33)"> </p><p style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(33,33,33)">                32688    | 2020-02-08 13:29:14 | 0       | 0          | postgres | postgres     | 2020-02-08 13:52:05 | 3            | 0            | 1            | 378814          | 1</p><p style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(33,33,33)">                32688    | 2020-02-08 13:29:14 | 0       | 1          | postgres | postgres     | 2020-02-08 13:52:05 | 3            | 0            | 1            | 447031          | 1</p><p style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(33,33,33)">                32688    | 2020-02-08 13:29:14 | 0       | 2          | postgres | postgres     | 2020-02-08 13:52:05 | 3            | 0            | 1            | 26544           | 1</p><p style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(33,33,33)">                32688    | 2020-02-08 13:29:14 | 0       | 3          | postgres | postgres     | 2020-02-08 13:52:05 | 3            | 0            | 1            | 346873          | 1</p><p style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(33,33,33)">               </p><p style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(33,33,33)">                (iii)          In Postgres :</p><p style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(33,33,33)"> </p><p style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(33,33,33)">                ps -ef| grep 378814</p><p style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(33,33,33)">                397874 393866  0 16:26 pts/0    00:00:00 grep --color=auto 378814</p><p style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(33,33,33)">                ps -ef| grep 378276</p><p style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(33,33,33)">                397995 393866  0 16:27 pts/0    00:00:00 grep --color=auto 378276</p><p style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(33,33,33)">               </p><p style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(33,33,33)">                iv: Not able to see in pg_stat_activity  in backend as well.</p><p style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(33,33,33)"> </p><p style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(33,33,33)">Please help us to find and resolve the issues:</p><p style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(33,33,33)"> </p><p style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(33,33,33)">1. Why we are not able to see the pgpool &quot;idle in transaction&quot; connections in backend DB&#39;s(Using pg_stat_activity and ps -eaf| grep pid).</p><p style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(33,33,33)">2. how to check what exactly the &quot;idle in transaction&quot; connections are doing in backends?. and how to see those connections?.</p><p style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(33,33,33)">3. How to avoid these kind of issues and what parameters at pgpool and db level will help us to release these &quot;idle in transaction&quot; connections?.</p><p style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(33,33,33)"> </p><p style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(33,33,33)">Your support will be much appreciate.</p><p style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(33,33,33)">Thanks for your support.</p><p style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(33,33,33)"> </p><p style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(33,33,33)">Regards,</p><p style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(33,33,33)">Bingo.</p></div>