<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 'idle in transaction' 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 = 'ABORT; DISCARD ALL'<br>                                                                                                                             <br>                #reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT'<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 "idle in transaction" connections in backend DB'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 "idle in transaction" 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 "idle in transaction" 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>