Near Realtime (every 10 seconds)
Detects transactions that have been open with no activity (the
idle in transaction state) for longer than the specified threshold of
1800 seconds and creates an issue with severity "warning". Escalates to "critical" if the transaction is still idle after
3600 seconds. Resolves automatically once the transaction is committed, rolled back, or shows activity again.
This check is enabled by default. These parameters can be tuned in the Configure section of the Alerts & Check-Up page.
Transactions left open can hold locks, blocking other queries, and can prevent VACUUM (including autovacuum) from cleaning up dead rows, leading to index or table bloat.
If there is an error (either in the application or in the database) while processing a query, the application must handle that correctly and roll back the transaction (and possibly close the connection). If it fails to do that, the idle transaction may be left open as long as the application keeps running.
If your application sets
application_nameon its connections or uses a distinct role, you can find more details by querying pg_stat_activity. Review the code path that triggered the idle transaction (you can also check the last query on the connection in pg_stat_activity), and make sure that any errors are handled appropriately (e.g., with
catch) and roll back the transaction.
If someone is accessing the database directly in an interactive session and they manually open a transaction with BEGIN or START TRANSACTION, they must close it with COMMIT or ROLLBACK, or disconnect entirely (which will roll back the transaction).
For an immediate solution, you can kill a connection with an idle transaction with SELECT pg_terminate_backend("<session_pid>");. Note that this only treats the symptom: unless this is due to a one-off manual session, you may run into this problem again.
You can also change the
idle_in_transaction_session_timeout setting to automatically roll back idle transactions older than a specified threshold.
Couldn't find what you were looking for or want to talk about something specific?
Start a conversation with us →