Menu
I have a Postgres 9.5 database for a web-based Java application. Sometimes, the number of sessions suddenly spikes up due to a long running query, and these sessions are not cleared immediately. When I check pg_stat_activity, query column shows COMMIT or ROLLBACK with a stage of idle. This is causing Postgres to hit high thresholds of max_connections, which can possibly cause Production outage.
query | ROLLBACK state | idle count | 167
query | COMMIT state | idle count | 280
Thanks
934
idle: This indicates that the connection is idle and we need to track these connections based on the time that they have been idle. idle in transaction: This indicates the backend is in a transaction, but it is currently not doing anything and could be waiting for an input from the end user.
The COMMIT command is the transactional command used to save changes invoked by a transaction to the database. The COMMIT command saves all transactions to the database since the last COMMIT or ROLLBACK command.
Kill an Idle Connection: >> SELECT pg_terminate_backend(7408); The process has been magnificently killed. Now check the remaining idle connections from the below-appended query.
The default value of commit is ON in PostgreSQL, which means we need not have to execute a commit statement to save the transaction; it will automatically save the transaction into the database. If we set auto-commit is off, then we need to write commit to save the transaction into the database.
These are connections waiting for another query. They aren't doing anything. That's why they're idle.
Your app uses a connection pooler to avoid having to disconnect and reconnect all the time. When it has more connections than active queries, some will be idle, and the query shown will be the last completed query.
This is all entirely normal, and nothing to worry about. There is nothing you need to change or fix.
91
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
