PostgreSQL forcibly closes connections that remain idle within an active transaction for too long. Adjust the timeout setting, optimize application code to minimize transaction scope, or disable it for maintenance operations.
When a PostgreSQL connection stays idle within an open transaction longer than the idle_in_transaction_session_timeout parameter allows, PostgreSQL automatically terminates the connection and rolls back the transaction. This is a safety mechanism to prevent long-lived idle transactions from holding locks on tables and rows, blocking other operations and preventing VACUUM from cleaning dead rows. Idle transactions can cause severe table bloat and impact database performance.
Check PostgreSQL logs to confirm the error. Look for messages like:
FATAL: terminating connection due to idle_in_transaction_session_timeout
CONTEXT: while handling message type "Execute"Confirm the current timeout setting:
SHOW idle_in_transaction_session_timeout;Default is 0 (disabled). Common values are in milliseconds or interval format (e.g., "5min", "300000").
Adjust the timeout at the appropriate level for your application:
Session level (temporary, current session only):
SET idle_in_transaction_session_timeout = '15min';For a specific database:
ALTER DATABASE myapp_database SET idle_in_transaction_session_timeout = '10min';For a specific application user/role:
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '10min';System-wide in postgresql.conf:
idle_in_transaction_session_timeout = '5min'Then reload:
SELECT pg_reload_conf();The best solution is to prevent idle transactions by restructuring application logic:
Problem pattern:
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()});
});Solution: Move waits outside transaction:
// 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()});
});If using a connection pool (PgBouncer, Pgpool-II, node-postgres pool), ensure connections are properly released:
// 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
}For frameworks like Sequelize or TypeORM, ensure transaction blocks are properly scoped:
// Sequelize
await sequelize.transaction(async (trx) => {
// All DB operations here
// Auto-commits or rolls back on error
});For long-running operations like pg_dump, backups, or rebuilds, temporarily disable the timeout:
# 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.sqlFor index rebuilds within a transaction:
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;Identify connections that are idle in transactions:
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;Terminate long-running idle transactions if needed:
-- 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';The idle_in_transaction_session_timeout parameter was introduced in PostgreSQL 9.6. It is disabled by default (value = 0). Related parameters: idle_session_timeout (kills idle sessions not in a transaction) and statement_timeout (kills long-running individual queries). Long-running statements themselves are not affected by this timeout—only idle time within an open transaction triggers it. This parameter is particularly important in cloud-hosted PostgreSQL (AWS RDS, Google Cloud SQL, Azure Database, Heroku) where it may be enabled by default. Connection pooling tools (PgBouncer, Pgpool-II) should have their own timeout configurations that do not conflict with the server setting. For applications with legitimate long transactions (backups, heavy analytics), consider using separate connection pools or database roles with different timeout settings.
ERROR: syntax error at end of input
Syntax error at end of input in PostgreSQL
Bind message supplies N parameters but prepared statement requires M
Bind message supplies N parameters but prepared statement requires M in PostgreSQL
Multidimensional arrays must have sub-arrays with matching dimensions
Multidimensional arrays must have sub-arrays with matching dimensions
ERROR: value too long for type character varying
Value too long for type character varying
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL