How to fix FATAL: terminating connection due to idle_in_transaction_session_timeout in PostgreSQL

FATAL:  terminating connection due to idle_in_transaction_session_timeout
CONTEXT:  while handling message type "Execute"

SHOW idle_in_transaction_session_timeout;

SET idle_in_transaction_session_timeout = '15min';

ALTER DATABASE myapp_database SET idle_in_transaction_session_timeout = '10min';

ALTER ROLE app_user SET idle_in_transaction_session_timeout = '10min';

idle_in_transaction_session_timeout = '5min'

SELECT pg_reload_conf();

await db.transaction(async (trx) => {
  // Long query
  const user = await trx.query('SELECT * FROM users WHERE id = ?', [userId]);
  
  // Application waits here—transaction is idle
  const apiResponse = await fetch('https://api.example.com/data');
  
  // Commit happens after delay
  await trx.update('users').set({lastUpdated: new Date()});
});

// Fetch external data first
const apiResponse = await fetch('https://api.example.com/data');

// Then open transaction only for database operations
await db.transaction(async (trx) => {
  const user = await trx.query('SELECT * FROM users WHERE id = ?', [userId]);
  await trx.update('users').set({lastUpdated: new Date()});
});

// Ensure transaction is committed/rolled back and connection is released
const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query('INSERT INTO table ...');
  await client.query('COMMIT');
} catch (error) {
  await client.query('ROLLBACK');
  console.error('Transaction failed', error);
} finally {
  client.release(); // Critical: return connection to pool
}

// Sequelize
await sequelize.transaction(async (trx) => {
  // All DB operations here
  // Auto-commits or rolls back on error
});

# Backup with timeout disabled
pg_dump --command="SET idle_in_transaction_session_timeout = 0" mydb > backup.sql

# Or increase substantially
pg_dump --command="SET idle_in_transaction_session_timeout = 1h" mydb > backup.sql

BEGIN;
SET idle_in_transaction_session_timeout = 0;
CREATE INDEX CONCURRENTLY idx_new ON large_table(column);
DROP INDEX CONCURRENTLY idx_old;
RESET idle_in_transaction_session_timeout;
COMMIT;

SELECT 
  pid,
  usename,
  application_name,
  state,
  state_change,
  NOW() - state_change AS idle_duration,
  xact_start,
  NOW() - xact_start AS transaction_duration
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY state_change ASC;

-- Be careful! Ensure you know what you're terminating
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND NOW() - state_change > INTERVAL '5 minutes';