Long COMMIT requests with idle state in pg_stat_activity

Question

If I ask:

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

I get a lot of lines that match idle , and the request is COMMIT . They are durable and do not disappear. After some time, my application reaches hibernate.c3p0.max_size (the maximum number of JDBC connections in the pool) and stops working with the database.

Some application implementation details are described in another SO thread: Guice DAO provider in a thread pool - requests become inactive in a transaction

Why is this happening? How to solve this problem?

Answer 1

If the session is idle, the last statement that was executed is displayed in the query column. This is not a "current" request, so the connection does not wait for the commit to complete.

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

An “idle” connection is not a problem and is essentially the reason the connection pool is used so that it can be reused. However, sessions that remain very long in standby mode in a transaction are a problem . But you stated that your connections are in a state of "inaction".

If your connection pool reaches the limit, this most likely means that your application incorrectly returns connections to the pool. This is what you need to fix in your application.