Postgres Errors Statement Timeouts

postgres-errors-statement-timeouts

Quick Reference :

PostgreSQL has five timeout GUCs. Each one is a safety bound, and each one is 0 (disabled) by default. A production database with all five at 0 has no defense against a runaway query, a leaked transaction, or a migration that queues behind a slow SELECT.

ERROR:  canceling statement due to statement timeout      -- SQLSTATE 57014
ERROR:  canceling statement due to lock timeout           -- SQLSTATE 55P03
FATAL:  terminating connection due to idle-in-transaction timeout  -- 25P03

The five timeouts, what they bound, and what they do when hit :

ALWAYS set idle_in_transaction_session_timeout in production : a forgotten open transaction holds locks and blocks VACUUM from cleaning dead tuples. ALWAYS set a lock_timeout before any migration. NEVER blindly retry a 57014 from statement_timeout : a genuinely slow query needs an index, not a retry.

When To Use This Skill :

ALWAYS use this skill when :

• A query fails with "canceling statement due to statement timeout" (57014)
• A statement fails with "canceling statement due to lock timeout" (55P03)
• A session is dropped with "idle-in-transaction timeout" (25P03)
• You are choosing timeout values for a web role, a batch role, or a migration
• A migration ALTER TABLE stalled all traffic on the table
• VACUUM cannot clean a table and old idle in transaction sessions are suspected

NEVER use this skill for :

• 40P01 deadlock_detected (use postgres-errors-deadlocks)
• 40001 serialization_failure retry logic (use postgres-errors-serialization)
• Connection / authentication failures unrelated to idle timeouts (use postgres-errors-connection-auth)
• Designing the migration DDL itself (use postgres-impl-zero-downtime-migrations)

Decision Trees :

Which timeout fired, and do I retry :

Statement or session ended unexpectedly. Read the message and SQLSTATE :
├── "canceling statement due to statement timeout"  (57014)
│      -> the statement ran longer than statement_timeout.
│         Retry will NOT help: the query is genuinely slow.
│         Fix the query or add an index. Retry only if a transient
│         load spike is the proven cause.
├── "canceling statement due to lock timeout"  (55P03)
│      -> the statement waited too long to ACQUIRE a lock.
│         Often transient. Retry with backoff is reasonable.
├── "canceling statement due to user request"  (57014)
│      -> NOT a timeout: pg_cancel_backend() or a client cancel.
│         Investigate who canceled it; do not auto-retry.
├── "terminating connection due to idle-in-transaction timeout"  (25P03)
│      -> the app left a transaction open and idle. The session is gone.
│         Fix the app: commit/rollback promptly, shorten transactions.
└── connection dropped, no statement running (class 08)
       -> idle_session_timeout or transaction_timeout terminated the
          session. Reconnect; review whether the cap is too tight.

Which timeout to set for which workload :

What kind of database role is this?
├── Web / API request role
│      -> statement_timeout short (e.g. '5s' to '15s')
│         idle_in_transaction_session_timeout '10s'
│         transaction_timeout (v17+) as a hard cap, e.g. '30s'
├── Batch / ETL / reporting role
│      -> statement_timeout long or 0, set deliberately per job
│         idle_in_transaction_session_timeout still set ('60s'+)
├── Migration / DDL role
│      -> lock_timeout REQUIRED (e.g. '3s') so DDL fails fast
│         instead of queueing behind a long query and blocking all traffic
│      -> statement_timeout sized to the operation, not the web default
└── Every role, always
       -> idle_in_transaction_session_timeout > 0 to catch leaked transactions

statement_timeout vs lock_timeout interaction :

Setting both on the same role?
├── lock_timeout < statement_timeout
│      -> CORRECT. A lock wait fails fast (55P03) before the
│         statement budget (57014) is spent.
└── lock_timeout >= statement_timeout (both nonzero)
       -> POINTLESS. statement_timeout always triggers first;
          lock_timeout can never fire. Lower lock_timeout.

Patterns :

Pattern 1 : Always set idle_in_transaction_session_timeout

ALWAYS set idle_in_transaction_session_timeout to a nonzero value in production. NEVER leave it at the 0 default.

-- global floor in postgresql.conf, or per role :
ALTER ROLE app_web SET idle_in_transaction_session_timeout = '10s';

WHY : the docs state an open transaction "prevents vacuuming away recently-dead tuples that may be visible only to this transaction; so remaining idle for a long time can contribute to table bloat". A connection-pool client that opens a transaction and then stalls (slow app code, a crashed worker) holds locks and pins the xmin horizon indefinitely. This timeout terminates that session with 25P03, releasing the locks and unblocking VACUUM.

Pattern 2 : lock_timeout before every migration

ALWAYS set a small lock_timeout for the duration of a migration. NEVER run an ALTER TABLE against a busy table without one.

BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN note text;   -- needs ACCESS EXCLUSIVE
COMMIT;

WHY : ALTER TABLE needs an ACCESS EXCLUSIVE lock. If a long-running query holds even a weak lock, the ALTER waits in the lock queue, and every NEW query queues behind the ALTER. The whole table freezes. With lock_timeout the ALTER fails fast with 55P03 instead, leaving traffic untouched. Retry the migration when the table is quiet.

Pattern 3 : Scope timeouts per role, not globally

ALWAYS scope timeouts to the role or database that needs them with ALTER ROLE ... SET / ALTER DATABASE ... SET. NEVER force one global statement_timeout on every workload.

ALTER ROLE app_web   SET statement_timeout = '10s';
ALTER ROLE app_batch SET statement_timeout = '30min';
ALTER DATABASE analytics SET statement_timeout = '5min';

WHY : a web request that runs 10s is a bug ; a batch job that runs 10s is normal. A single global value is either too tight for batch or too loose for web. Role-scoped settings apply at connection time and need no application change. SET LOCAL inside a transaction overrides them for one unit of work.

Pattern 4 : transaction_timeout as a hard cap (v17+)

ALWAYS add transaction_timeout on v17+ for web roles as an absolute ceiling on transaction duration. NEVER rely on it as a substitute for the other timeouts.

-- v17+ : caps total transaction time regardless of activity
ALTER ROLE app_web SET transaction_timeout = '30s';

WHY : statement_timeout bounds one statement and idle_in_transaction_session_timeout bounds idle gaps, but a transaction can run for hours by chaining many short, active statements. transaction_timeout "terminates any session that spans longer than the specified amount of time in a transaction". The docs note : if transaction_timeout is shorter than or equal to idle_in_transaction_session_timeout or statement_timeout, the longer timeout is ignored. Prepared transactions are not subject to it.

Pattern 5 : Fail fast for web requests, never disable globally

ALWAYS give web-facing roles a short statement_timeout. NEVER set statement_timeout = 0 globally in production.

ALTER ROLE app_web SET statement_timeout = '8s';

WHY : with statement_timeout = 0 a single pathological query runs until it finishes or the connection dies, holding locks and a buffer-pool footprint the whole time. A short timeout converts an unbounded hang into a fast, catchable 57014 the application can turn into an error response. Disable the timeout only deliberately and narrowly (a known-long batch statement via SET LOCAL).

Pattern 6 : Distinguish a timeout cancel from a manual cancel

ALWAYS read the error message text, not just the SQLSTATE, to tell timeout cancels apart. NEVER assume every 57014 is statement_timeout.

57014 query_canceled has several causes, distinguished by message :
  "... due to statement timeout"        -> statement_timeout fired
  "... due to user request"             -> pg_cancel_backend() or client cancel
  "... due to conflict with recovery"   -> a hot-standby replay conflict
55P03 lock_not_available with message :
  "... due to lock timeout"             -> lock_timeout fired
  (also raised by NOWAIT clauses)

WHY : a 57014 from statement_timeout means "the query is too slow, fix it" ; a 57014 from "user request" means a human or a watchdog killed it. Branching retry logic on SQLSTATE alone treats these identical, and auto-retries a manual cancel or a genuinely slow query. The message text carries the actual cause.

Anti-Patterns :

(Full cause + symptom + SQLSTATE + fix in references/anti-patterns.md)

• No idle_in_transaction_session_timeout : leaked transactions block VACUUM forever
• statement_timeout = 0 globally in production : one runaway query has no bound
• Blindly retrying a 57014 from statement_timeout : a missing index is not fixed by retrying
• Migration ALTER with no lock_timeout : the DDL queues and freezes all table traffic
• lock_timeout >= statement_timeout (both nonzero) : lock_timeout can never fire
• One global statement_timeout for web and batch : too tight for one, too loose for the other

Reference Links :

• references/methods.md : All five timeout GUCs, exact semantics, scoping commands, SQLSTATE table
• references/examples.md : Role and database scoping, migration wrapper, retry logic, diagnostic queries
• references/anti-patterns.md : Anti-patterns with cause, symptom, SQLSTATE, and fix

See Also :

• postgres-errors-deadlocks : NOWAIT / SKIP LOCKED also raise 55P03, lock-wait diagnosis
• postgres-errors-serialization : 40001 retry logic, contrasted with non-retryable 57014
• postgres-impl-zero-downtime-migrations : lock_timeout + retry as a migration safety pattern
• postgres-errors-connection-auth : class 08 connection errors from terminated sessions
• Vooronderzoek section : §21 (timeout GUCs), §16 (migration safety)
• Official docs : https://www.postgresql.org/docs/17/runtime-config-client.html
• Official docs : https://www.postgresql.org/docs/17/errcodes-appendix.html