Hunt Down idle in transaction Sessions in PostgreSQL

1. List idle-in-transaction sessions sorted by age.

psql -d app -c "SELECT pid, now() - xact_start AS xact_age, state, left(query, 60) AS last_query FROM pg_stat_activity WHERE state = 'idle in transaction' ORDER BY xact_start LIMIT 10;"

Expected output: Sessions print with xact_age such as 03:21:07, meaning a transaction has been open for hours while the connection sits idle.

2. Check what locks the oldest session holds.

psql -d app -c "SELECT l.pid, l.mode, c.relname FROM pg_locks l JOIN pg_class c ON l.relation = c.oid WHERE l.pid = 48213 ORDER BY c.relname;"

Expected output: Row and relation locks held by the session are listed, typically including a RowExclusiveLock on the hottest table that other writers now queue behind.

3. Identify which application owns the connection.

psql -d app -c "SELECT pid, usename, application_name, client_addr, backend_start FROM pg_stat_activity WHERE pid = 48213;"

Expected output: The application_name and client_addr point at the specific service instance, narrowing the search to one deploy.

4. Find the leak in code: a transaction opened without commit or rollback.

// leak: early return leaves the transaction open
async function transfer(from, to, amount) {
  const tx = await pool.connect();
  try {
    await tx.query("BEGIN");
    const bal = await tx.query("SELECT balance FROM accounts WHERE id = $1", [from]);
    if (bal.rows[0].balance < amount) return { ok: false };   // <-- leak
    await tx.query("UPDATE accounts SET balance = balance - $1 WHERE id = $2", [amount, from]);
    await tx.query("UPDATE accounts SET balance = balance + $1 WHERE id = $2", [amount, to]);
    await tx.query("COMMIT");
    return { ok: true };
  } finally {
    tx.release(); // releases the connection but NOT the open transaction
  }
}

// fix: wrap in a helper that always finishes the transaction
async function withTx(pool, fn) {
  const tx = await pool.connect();
  try {
    await tx.query("BEGIN");
    const out = await fn(tx);
    await tx.query("COMMIT");
    return out;
  } catch (err) {
    await tx.query("ROLLBACK");
    throw err;
  } finally {
    tx.release();
  }
}

Expected output: After adopting withTx, every code path ends with COMMIT or ROLLBACK, and idle-in-transaction counts drop to zero within the pool lifetime.

5. Add a server-side safety net that reaps forgotten transactions.

psql -d app -c "SHOW idle_in_transaction_session_timeout;" && psql -d app -c "ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';" && psql -d app -c "SELECT pg_reload_conf();"

Expected output: The timeout changes from 0 (disabled) to 5min; leaked sessions are now terminated by the server, converting a silent leak into a logged error.

Problem

Sessions disappear before you can inspect them

Solution

Poll pg_stat_activity on a loop writing to a table, or enable log_statements tracking, so the next occurrence is captured with full context.

Problem

pool.release does not close the transaction

Solution

Releasing a connection back to the pool keeps its transaction state; always issue COMMIT or ROLLBACK before release, or use the helper pattern that guarantees it.

Problem

ORM hides the transaction boundaries

Solution

Search for the callback or decorator that opens a unit of work; early returns inside it are the classic leak, and stack traces from the timeout errors will name the exact function.