Idle in transaction in Postgres: why pg_cancel_backend returns true and changes nothing

I build HeapDeck, an iPhone and iPad app for Postgres on call. You won't need it here: everything below runs in psql.

The API slows down, then a migration hangs, and pg_stat_activity shows a session in idle in transaction with a transaction that started forty minutes ago. It runs nothing and uses no CPU, and the rest of the table's traffic waits on it. You call pg_cancel_backend, Postgres answers t, and the session stays where it was.

Reproduce it

Open three psql sessions on a scratch database. I ran this on PostgreSQL 18.6.

Session A opens a transaction and goes quiet:

CREATE TABLE t (id int); INSERT INTO t VALUES (0); BEGIN; UPDATE t SET id = 1; 

Session B needs the table, and it hangs:

ALTER TABLE t ADD COLUMN note text; 

Session C shows you both:

SELECT pid, state, left(query, 30) AS last_statement FROM pg_stat_activity WHERE state LIKE 'idle in transaction%'; 

 pid | state | last_statement -----+---------------------+---------------------- 103 | idle in transaction | UPDATE t SET id = 1; 

SELECT pid, wait_event_type, pg_blocking_pids(pid) AS blocked_by, left(query, 40) AS query FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0; 

 pid | wait_event_type | blocked_by | query -----+-----------------+------------+------------------------------------- 102 | Lock | {103} | ALTER TABLE t ADD COLUMN note text; 

Now cancel the session at the root:

SELECT pg_cancel_backend(103); 

 pg_cancel_backend ------------------- t 

Run the first query again. You get the same pid and the same state, and the ALTER TABLE keeps waiting. t means Postgres delivered the signal. pg_cancel_backend interrupts a running statement, and this session runs none.

Terminate it:

SELECT pg_terminate_backend(103); 

Session B finishes and prints ALTER TABLE. Session A learns about it on its next command:

FATAL: terminating connection due to administrator command server closed the connection unexpectedly 

SELECT id FROM t returns 0, because the UPDATE rolled back.

What the state means

The state column in pg_stat_activity tells you what a backend is doing:

The session did some work, the statement finished, and the client has not sent COMMIT or ROLLBACK. PostgreSQL waits for it.

Why it hurts

It holds its locks. A transaction keeps each lock until it ends. An UPDATE that finished twenty minutes ago still holds its row locks, and a plain SELECT still holds ACCESS SHARE on the table, which is enough to stop an ALTER TABLE and everything queued behind it: the lock queue, step by step.

It can hold back vacuum. If the transaction wrote something, it has a transaction ID (backend_xid). If it runs at REPEATABLE READ or keeps a cursor open, it holds a snapshot (backend_xmin). Either one stops vacuum from removing rows that died after that point, so dead tuples pile up across the database, and over a long enough time wraparound gets closer. A read-only transaction at the default READ COMMITTED holds no snapshot between statements, so check both columns before you blame it for bloat.

It takes a connection slot. With an application pool, a connection stuck in a transaction is also one the pool can't hand out. Enough of them and you get too many clients already.

Find them in production

SELECT pid, usename, application_name, client_addr, now() - xact_start AS xact_age, now() - state_change AS idle_for, backend_xid, backend_xmin, pg_blocking_pids(pid) AS blocked_by, left(query, 80) AS last_statement FROM pg_stat_activity WHERE state LIKE 'idle in transaction%' ORDER BY xact_start; 

In this state, query shows the last statement the session ran, which tells you where in the code the transaction stopped. application_name and client_addr tell you who owns it. To see whether anyone waits on it, look for its pid in the blocked_by column of the sessions that wait: finding blocking queries.

Who left it open

• A slow call inside a transaction. The code opens a transaction, then calls a payment API or sends an email. You see many short ones until that API slows down.
• An error path that skips the rollback. An exception escapes, and the connection goes back to the pool or stays open with the transaction still running.
• An ORM session that lives too long, such as one held for a whole web request or background job.
• A person. Someone ran BEGIN in psql, or a GUI client opened a transaction for them, and went to lunch.

Before you terminate

PostgreSQL rolls back the transaction, releases the locks and closes the session. Whatever it wrote is gone, and the client sees a dropped connection. You can do this for your own role's sessions, or for other non-superuser roles' sessions with membership in pg_signal_backend.

Check last_statement first. If the session belongs to a person or a job you can reach, a message may cost less than a rollback.

Keep it from coming back

Set a limit per role. The default of 0 turns the limit off:

ALTER ROLE app SET idle_in_transaction_session_timeout = '2min'; 

The setting applies to new sessions, so existing pooled connections keep the old value until they reconnect. PostgreSQL 17 also has transaction_timeout, which caps the whole transaction.

The lasting fix sits in the code: finish calls to other services before you open the transaction, or after you commit it.

On the phone

HeapDeck's Sessions tile on Health reads 12 active · 9 idle in tx and turns amber at 5 such sessions. Tap it to open Activity, then filter by Idle in tx. Each row counts its time from the start of the transaction, amber at 10 seconds and red at 4 minutes, and marks the session if it blocks others.

Session details show Transaction open for, the client address and the SQL under the label LAST STATEMENT. For these sessions HeapDeck offers Terminate session only and explains that the transaction rolls back. If the session sits at the root of a lock chain, Locks offers Terminate root (unblocks N) and asks you to type its pid.

Seeing all of this is free for one saved server, and terminating is part of HeapDeck Pro. The site has the rest.

What left your last idle in transaction open? Tell me in the comments.

Postgres, PostgreSQL and the Slonik Logo are trademarks or registered trademarks of the PostgreSQL Community Association of Canada, and used with their permission. HeapDeck is an independent client and is not affiliated with, endorsed by, or sponsored by the PostgreSQL Community Association of Canada, PostgreSQL Global Development Group, PostgreSQL Project, PostgreSQL Core Team, or PostgreSQL Community.