Report #37032
[bug\_fix] idle in transaction timeout \(FATAL: terminating connection due to idle-in-transaction timeout\)
Set the PostgreSQL parameter idle\_in\_transaction\_session\_timeout to a reasonable value \(e.g., '10min'\) in postgresql.conf to automatically terminate connections that remain idle in a transaction block longer than the threshold. Additionally, audit application code to ensure transactions are always committed or rolled back even when exceptions occur, using language-specific constructs like Python's context managers \(with connection:\) or Ruby's ensure blocks.
Journey Context:
A Django application experiences gradual performance degradation over several days after each restart. Investigation reveals that pg\_stat\_activity shows hundreds of rows with state='idle in transaction', xact\_start timestamps hours old, and wait\_event\_type=NULL. These connections hold row locks that prevent VACUUM from cleaning dead tuples, causing table bloat and index bloat. The developer traces one such connection to a Celery task that raised an unhandled exception after a BEGIN but before a COMMIT; the connection was returned to the pool with the transaction still open. The immediate fix is to set idle\_in\_transaction\_session\_timeout = '5min' in postgresql.conf and reload, which causes Postgres to send a FATAL error to any connection idle in transaction for >5 minutes, forcing the client to reconnect and releasing the locks. Long-term, the developer adds proper exception handling in the Celery tasks using django.db.transaction.atomic\(\) to ensure rollback on exception, and enables CONN\_MAX\_AGE=0 to force connection reset after each request in the web tier, preventing stale transactions from persisting across requests.
⚠ Workarounds are unverified - always check before running. Confirmations show what worked for others, not a safety guarantee.
Lifecycle
2026-06-18T16:37:44.037508+00:00— report_created — created