Long & idle transactions
An open transaction can retain locks or an old cleanup horizon, and it occupies its connection until it ends. Those are separate diagnostics: not every idle transaction retains a snapshot, and connection-pool exhaustion is not measured here. This PostgreSQL 18.6 Scenario finds one idle READ ONLY REPEATABLE READ report.
Finding them
A starts a READ ONLY report at REPEATABLE READ and pauses before committing.
A> BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY;
BEGIN
A> SELECT count(*)::int AS orders FROM orders;
orders
--------
2
(1 row)Detector 1 — the oldest open transaction, and how long it's been open:
M> SELECT application_name, state, now() - xact_start > interval '1 second' AS older_than_1s
FROM pg_stat_activity
WHERE xact_start IS NOT NULL AND pid <> pg_backend_pid()
ORDER BY xact_start LIMIT 1;
application_name | state | older_than_1s
------------------+---------------------+---------------
A | idle in transaction | t
(1 row)Detector 2 — sessions holding a transaction open while doing nothing:
M> SELECT application_name
FROM pg_stat_activity
WHERE state = 'idle in transaction' AND now() - state_change > interval '1 second'
ORDER BY application_name;
application_name
------------------
A
(1 row)Detector 3: A retains a snapshot horizon (backend_xmin), without an assigned top-level xid. An xid is not a count of writes; this query does not execute VACUUM or measure storage reclamation.
M> SELECT application_name, backend_xid IS NOT NULL AS has_xid,
backend_xmin IS NOT NULL AS retains_snapshot
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL AND pid <> pg_backend_pid()
ORDER BY application_name;
application_name | has_xid | retains_snapshot
------------------+---------+------------------
A | f | t
(1 row)
A> COMMIT;
COMMITVerified against PostgreSQL 18.6 · Run it yourself · Scenario source
The report reads two orders, then pauses. All three queries identify A: its transaction is older than one second, its state is idle in transaction, and it retains backend_xmin without an assigned backend_xid. The manual calls backend_xmin the "current backend's xmin horizon" in pg_stat_activity. A missing top-level xid is not a general proof that a session never wrote: xid assignment and write counts are different things.
This Scenario observes the horizon but executes no VACUUM. The REPEATABLE READ reclamation case demonstrates affected tuple slots; the READ COMMITTED queue case asserts an assigned xid rather than a transaction-wide snapshot. Sort by age to find candidates, then inspect state, locks, backend_xmin, and backend_xid. The oldest xact_start alone does not identify every cause of blocked cleanup.
Guardrails: make the database enforce it
statement_timeout: the per-statement seatbelt.
A> SET statement_timeout = '100ms';
SET
A> SELECT pg_sleep(2); -- query_canceled
ERROR: 57014: canceling statement due to statement timeoutThe canceled standalone statement's implicit transaction ended; the session remains usable.
A> SELECT 'still here' AS session;
session
------------
still here
(1 row)In an explicit transaction, statement cancellation leaves the transaction failed.
A> BEGIN;
BEGIN
A> UPDATE accounts SET balance = 999 WHERE id = 1;
UPDATE 1
A> SELECT pg_sleep(2);
ERROR: 57014: canceling statement due to statement timeout
A> SELECT balance FROM accounts WHERE id = 1;
ERROR: 25P02: current transaction is aborted, commands ignored until end of transaction block
A> ROLLBACK;
ROLLBACK
M> SELECT balance FROM accounts WHERE id = 1;
balance
---------
100
(1 row)transaction_timeout (PostgreSQL 17+) terminates the session when its transaction exceeds the limit. This run exercises the idle case; prepared transactions are exempt by the manual contract.
A> RESET statement_timeout;
RESET
A> SET transaction_timeout = '500ms';
SET
A> BEGIN;
BEGIN
A> UPDATE accounts SET balance = 999 WHERE id = 1;
UPDATE 1After the transaction timeout interval, A's backend is absent:
M> SELECT count(*)::int AS backends FROM pg_stat_activity WHERE application_name = 'A';
backends
----------
0
(1 row)
A> COMMIT; -- psycopg can receive 25P04; Bun reports the closed connection
ERROR: ERR_POSTGRES_CONNECTION_CLOSED: Connection closedThe update from the terminated transaction is absent:
M> SELECT balance FROM accounts WHERE id = 1;
balance
---------
100
(1 row)Verified against PostgreSQL 18.6 · Run it yourself · Scenario source
statement_timeoutemits57014. The standalone statement leaves the session usable. The explicit-transaction case emits25P02on the next query until ROLLBACK, after which M reads the original balance 100. Session survival does not mean transaction survival.idle_in_transaction_session_timeoutterminates an idle session with an open transaction. The separate ORM-pitfalls Scenario demonstrates termination; no ORM runs there.transaction_timeout, available since PostgreSQL 17, terminates a session whose transaction exceeds its limit. Here A is idle after an update: M observes no A backend and balance 100. Bun reports a closed connection; psycopg can receive25P04. This run does not exercise a continuously busy transaction or prepared work.
Documented scope: PostgreSQL 18's timeout contracts apply to explicit and implicit transactions as specified. A shorter or equal transaction_timeout supersedes a longer statement or idle-in-transaction timeout. The manual states: "Prepared transactions are not subject to this timeout". Default zero disables each of these timeouts.
Operational advice: size role-specific limits for the application's work and recovery policy. Termination loses the session and rolls back its open database work; it does not undo external effects or resolve prepared transactions. Check connection-pool handling of server-closed connections before rollout. These settings bound work or waits; they do not guarantee that incidents cannot occur.