[pgpool-general: 6854] 'idle in transaction' connections in pgpool server and not able see the same in postgres servers

[postgann2020 s]

Hi Team,

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 ).

Environment Details : Two pgpool servers( 3.7.11 ) with [ one master and
three slave Postgres servers (9.5) ]
                      Application side : Java with struts framework and
deploying in Wildfly servers(9).

1.Can not reproduce the issue every time.



2. Pgpool.conf params :


                connection_cache = on
                reset_query_list = 'ABORT; DISCARD ALL'


                #reset_query_list = 'ABORT; RESET ALL; SET SESSION
AUTHORIZATION DEFAULT'
                num_init_children = 1000

                max_pool = 1
                # - Life time -
                child_life_time = 300
                child_max_connections = 0
                connection_life_time = 0
                client_idle_limit = 0
                connect_timeout = 100000
                client_idle_limit_in_recovery = 0



3. pids which got struck in pgpool servers and disappear in postgres servers



                1). From the pgpool servers:



                (i) In Pgpool :

                root     30659  6239  0 13:17 ?        00:00:00 pgpool:
postgres  postgres  10.19.61.122     idle in transaction

                root     32688  6239  0 13:29 ?        00:00:00 pgpool:
postgres  postgres  10.19.61.122     idle in transaction



                (ii) show pool_pools;

                30659    | 2020-02-08 13:17:26 | 0       | 0          |
postgres | postgres     | 2020-02-08 13:47:53 | 3            | 0
| 1            | 378276          | 1

                30659    | 2020-02-08 13:17:26 | 0       | 1          |
postgres | postgres     | 2020-02-08 13:47:53 | 3            | 0
| 1            | 446378          | 1

                30659    | 2020-02-08 13:17:26 | 0       | 2          |
postgres | postgres     | 2020-02-08 13:47:53 | 3            | 0
| 1            | 26020           | 1

                30659    | 2020-02-08 13:17:26 | 0       | 3          |
postgres | postgres     | 2020-02-08 13:47:53 | 3            | 0
| 1            | 346430          | 1



                32688    | 2020-02-08 13:29:14 | 0       | 0          |
postgres | postgres     | 2020-02-08 13:52:05 | 3            | 0
| 1            | 378814          | 1

                32688    | 2020-02-08 13:29:14 | 0       | 1          |
postgres | postgres     | 2020-02-08 13:52:05 | 3            | 0
| 1            | 447031          | 1

                32688    | 2020-02-08 13:29:14 | 0       | 2          |
postgres | postgres     | 2020-02-08 13:52:05 | 3            | 0
| 1            | 26544           | 1

                32688    | 2020-02-08 13:29:14 | 0       | 3          |
postgres | postgres     | 2020-02-08 13:52:05 | 3            | 0
| 1            | 346873          | 1



                (iii)          In Postgres :



                ps -ef| grep 378814

                397874 393866  0 16:26 pts/0    00:00:00 grep --color=auto
378814

                ps -ef| grep 378276

                397995 393866  0 16:27 pts/0    00:00:00 grep --color=auto
378276



                iv: Not able to see in pg_stat_activity  in backend as well.



Please help us to find and resolve the issues:



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).

2. how to check what exactly the "idle in transaction" connections are
doing in backends?. and how to see those connections?.

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?.



Your support will be much appreciate.

Thanks for your support.



Regards,

Bingo.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20200210/dfad65c3/attachment.htm>