How to fix Idle in transaction session timeout in PostgreSQL

SHOW idle_in_transaction_session_timeout;

SET idle_in_transaction_session_timeout = '10min';

ALTER ROLE your_app_user SET idle_in_transaction_session_timeout = '10min';

ALTER DATABASE your_database SET idle_in_transaction_session_timeout = '5min';

idle_in_transaction_session_timeout = '5min'

SELECT pg_reload_conf();

// BAD: Opens transaction, then waits
const result = await db.transaction(async (trx) => {
  await trx.insert({...});
  // Application waits for user input here - transaction is idle
  const userInput = await waitForUserInput();
  return trx.update({...});
});

// GOOD: Minimize transaction scope
const userInput = await waitForUserInput();
const result = await db.transaction(async (trx) => {
  await trx.insert({...});
  return trx.update({...});
});

// Ensure connections are returned promptly
await client.query('BEGIN');
try {
  await doWork();
  await client.query('COMMIT');
} catch (e) {
  await client.query('ROLLBACK');
} finally {
  client.release(); // Important: release connection
}

# Increase timeout for pg_dump
pg_dump --command="SET idle_in_transaction_session_timeout = 0" your_database > backup.sql

SET idle_in_transaction_session_timeout = 0; -- Disable
CREATE INDEX CONCURRENTLY idx_large ON huge_table(column);
RESET idle_in_transaction_session_timeout; -- Re-enable

SELECT pid, usename, application_name, state, state_change
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY state_change ASC;

SELECT 
  l.locktype, l.database, l.relation, l.page, l.tuple,
  a.usename, a.application_name, a.pid
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted;