Before you start
A session is idle in transaction when it ran BEGIN, did some work and is now doing nothing, with neither COMMIT nor ROLLBACK sent. It still holds every lock the transaction took. Other sessions queue behind those locks, and the open transaction also keeps VACUUM from removing rows that were deleted since it started. For slow statements rather than idle sessions, see Find long-running queries in PostgreSQL. Names and values below are made up.
Find them
SELECT pid, application_name, usename,
now() - xact_start AS transaction_open_for,
now() - state_change AS idle_for,
left(query, 60) AS last_query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
ORDER BY xact_start;The query column of an idle session holds the last statement it ran, not a running one. The oldest xact_start is the first one to look at. The state idle in transaction (aborted) means a statement failed and the session is waiting for a ROLLBACK.
Why applications leave them behind
- A code path returns or throws between BEGIN and COMMIT without a rollback.
- The transaction stays open while the code calls another service, waits on a user, or does slow work that needs no database.
- A client library opens a transaction on the first statement and the application never closes it.
End one, and stop it recurring
-- Ends the session and rolls back its open transaction
SELECT pg_terminate_backend(4398);
-- Ends any new session of this role that sits idle in a transaction over 60 seconds
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '60s';Ending a session loses what its transaction wrote, and the application may see a dropped connection. The timeout does the same automatically, so choose a value longer than your slowest legitimate pause, and fix the code that leaves the transaction open: that is the real repair.
Next step
dbtempo can record long open transactions (paid plan; see Pricing). The setup page lists exactly what is kept: see Turn on lock and transaction recording. dbtempo does not end sessions; the statements above are yours to run.