DBAtlas · Database operations atlas

Terminate long 'idle in transaction' sessions in bulk, in a controlled way

PostgreSQLSessions

Command

-- 1) Dry run first: who will be affected?
SELECT pid, usename, application_name, client_addr,
       now() - state_change AS idle_for, left(query, 80) AS last_query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
  AND now() - state_change > interval '15 minutes'
  AND backend_type = 'client backend'
  AND pid <> pg_backend_pid();

-- 2) Terminate; wait up to 5 s for each process to exit
SELECT pid, usename, now() - state_change AS idle_for,
       pg_terminate_backend(pid, 5000) AS terminated
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
  AND now() - state_change > interval '15 minutes'
  AND backend_type = 'client backend'
  AND pid <> pg_backend_pid();

What it does

First lists sessions that have left a transaction open and idle for a given time, then terminates them; the timeout parameter waits until the process has actually exited (or the timeout expires).

When to use it

For emergency intervention when forgotten sessions holding locks cause blocking, or hold back the xmin horizon and stall VACUUM.

Caution

pg_cancel_backend does nothing for an 'idle in transaction' session (there is no running query to cancel); pg_terminate_backend is required and all uncommitted changes are rolled back. Narrow the filter by usename/application_name; exclude replication, backup and monitoring sessions. Connection pools will reopen terminated connections. If the timeout expires, the function emits a warning and returns false. As a permanent fix, set idle_in_transaction_session_timeout per role.

Open in DBAtlas →

Related