How to fix Transaction timeout in PostgreSQL

Connect to your PostgreSQL database and view the active timeout parameters. Run this query as a superuser or privileged user:

SHOW transaction_timeout;
SHOW idle_in_transaction_session_timeout;
SHOW statement_timeout;
SHOW lock_timeout;

Note the values returned. A value of 0 means that timeout is disabled.

Check your application logs or PostgreSQL logs to determine which timeout parameter is causing the error. Look for error messages like:
- "FATAL: terminating connection due to transaction timeout" → transaction_timeout
- "ERROR: terminating connection due to idle-in-transaction timeout" → idle_in_transaction_session_timeout
- "ERROR: canceling statement due to statement timeout" → statement_timeout

PostgreSQL logs are typically in /var/lib/postgresql/data/log/ on Linux or can be queried from pg_log if enabled.

If the timeout is too short, increase it. Connect to your database and set the timeout at the session level before running the operation:

SET statement_timeout = '60s';
SET transaction_timeout = '600s';
SET idle_in_transaction_session_timeout = '300s';

-- Your long-running operation here
COPY table_name FROM 'large_file.csv';

For permanent changes, modify postgresql.conf (not recommended globally) or set user/database-level defaults:

ALTER DATABASE mydb SET statement_timeout = '60s';
ALTER USER myuser SET statement_timeout = '60s';

Instead of just increasing timeouts, optimize the operation to complete faster. For long queries:

-- Use EXPLAIN ANALYZE to identify bottlenecks
EXPLAIN ANALYZE SELECT * FROM large_table WHERE expensive_condition;

-- Add indexes if needed
CREATE INDEX idx_name ON table_name(column_name);

-- Use batching for bulk operations instead of single large transaction
BEGIN;
  DELETE FROM table_name WHERE condition LIMIT 1000;
COMMIT;
BEGIN;
  DELETE FROM table_name WHERE condition LIMIT 1000;
COMMIT;

For migrations, break them into smaller steps or run during maintenance windows.

If the error is "idle-in-transaction timeout", the problem is your application holding transactions open without activity. Ensure your application:

1. Commits or rolls back transactions promptly
2. Doesn't leave connections idle in a transaction
3. Uses connection pooling (PgBouncer, pgpool-II) if needed

Example of bad code (left open):

conn = psycopg2.connect(...)
cursor = conn.cursor()
cursor.execute("BEGIN")
cursor.execute("SELECT ...")
# ... code does something else for 5 minutes ...
# Connection still in transaction, not committed!

Fixed version:

conn = psycopg2.connect(...)
with conn:
    cursor = conn.cursor()
    cursor.execute("SELECT ...")
    # Automatically commits on success, rolls back on error

After fixing the immediate issue, set sensible timeout values:

-- For most applications
ALTER SYSTEM SET statement_timeout = '30s';
ALTER SYSTEM SET idle_in_transaction_session_timeout = '60s';

-- Reload configuration
SELECT pg_reload_conf();

Or in postgresql.conf:

statement_timeout = 30s
idle_in_transaction_session_timeout = 60s
transaction_timeout = 600s  # PostgreSQL 17+

Reload with pg_ctl reload or SELECT pg_reload_conf();