Long lasting 'COMMIT' queries with 'idle' state in pg_stat_activity

Question

If I query:

select * from pg_stat_activity where application_name ~ 'example-application';

I get many rows which state is idle and query is COMMIT. They are long lasting and do not disappear. After some time, my application reach hibernate.c3p0.max_size (maximum number of JDBC connections in the pool) limit and stops working with database.

Some application implementation details are described in other SO thread: Guice DAO Provider in thread pool - queries become 'idle in transation'

Why does it happen? How to solve this problem?

Answer 1

If the session is "idle" the query column shows the last statement that connection has executed. It is not the "current" query, so the connection is not waiting for the commit to finish.

The query column only shows the current statement if status shows active.

An "idle" connection is not a problem and essentially the reason why a connection pool is used, so that it can be re-used. However, sessions that stay very long in "idle in transaction" are a problem. But you stated your connections are in "idle" state.

If your connection pool reaches the limit, this most probably means your application is not returning the connections properly to the pool. It is something you need to fix in your application.