PostgreSQL statement timeout: locate the waiting phase

The symptoms

• •Application receives SQLSTATE 57014 query_canceled with a message naming statement_timeout, while the same query plan finishes when run interactively or with timeout disabled.
• •psql sessions show ERROR: canceling statement due to statement timeout after roughly the configured millisecond budget, even when the workload is reportedly light.
• •Application log lines record a successful prepare/plan but a later execute step that returns 57014, suggesting the timeout fires after planning rather than at parse.
• •Statement_timeout GUC reports a non-zero value at session, user, database, or server level, but the application layer insists it never sets the parameter.
• •Failures correlate with lock waits, autovacuum, or extension maintenance work; cancelling happens while the backend is reported as waiting, not active.
• •Intermittent cancellations only on specific connection pool members or pooled sessions, while direct sessions from the same user succeed.

Likely causes

• •Server-side statement_timeout inherited from postgresql.conf, ALTER ROLE/DATABASE, or a connection-pooler "session" GUC, applied even when the application does not request it.
• •Per-session override via SET statement_timeout or a SET LOCAL inside a transaction that outlives the intended scope and leaks through a pooled connection.
• •Lock contention on a relation, extension, or advisory lock where the backend spends most of its budget in wait state, triggering the timeout before the executor runs.
• •I/O-bound work: sequential scan, vacuum, ANALYZE, or extension maintenance that exceeds the budget during executor execution rather than during a wait.
• •Client-side or proxy timeout (PgBouncer, JDBC socketTimeout, application HTTP timeout) that surfaces as 57014 because the driver or proxy closed the socket and the backend reported cancellation.
• •idle_in_transaction_session_timeout or transaction_read_only interactions causing the backend to abort a session that the application interpreted as a query timeout.

First ten minutes

1. 01Capture the exact SQLSTATE (57014), the ERROR message text, the client timestamp, and the connection identifier (pid, application_name, usename, datname) from the application log or psql terminal.
2. 02From a superuser session, query pg_stat_activity for the affected backend: record state, wait_event_type, wait_event, query_start, state_change, xact_start, and the literal statement text.
3. 03Resolve the effective statement_timeout for the cancelling backend by checking current_setting('statement_timeout') and inspecting SET search_path; rule out SET LOCAL leakage from a prior transaction in a pool.
4. 04Cross-reference pg_locks for the same pid to determine whether the backend was holding locks, waiting on a lock (granted = false), or blocked on an advisory or extension lock at the moment of cancellation.
5. 05Inspect the wait_event taxonomy: Lock, LWLock, IO, Activity, Extension, Client, IPC, Timeout categories from pg_stat_activity map the cancellation phase to waits versus active execution.
6. 06Differentiate server enforcement from client cancellation by recording backend start timestamp, client connection endpoint if logged, and matching the SQLSTATE origin against server logs versus application/proxy logs.
7. 07Note the isolation level, transaction status (idle, idle in transaction, active), and any SET commands that appear in pg_stat_activity backend xact log to scope the timeout origin.

Evidence to collect

• •pg_stat_activity row: pid, datname, usename, application_name, client_addr, state, wait_event_type, wait_event, query_start, state_change, xact_start, backend_xmin, backend_xid.
• •Effective GUC snapshot: current_setting('statement_timeout'), current_setting('idle_in_transaction_session_timeout'), current_setting('lock_timeout'), current_setting('log_min_duration_statement').
• •Lock map: pg_locks rows filtered to the pid, showing locktype, relation, mode, granted, and any blocker pid referenced via blocking_pids.
• •Server log entries containing SQLSTATE 57014 with the associated session line, including log_line_prefix fields such as application_name, session_id, and command tag if log_lock_waits is on.
• •Wait-event distribution over a sample window using pg_stat_activity or cumulative counters to confirm whether the cancellation occurred during Lock/IO/Extension waits versus active execution.
• •Client-side log line showing the same cancellation, including driver-level socket timeout setting (for example socketTimeout) and the timestamp delta versus the server's query_start.

Where to look

• •Server boundary: PostgreSQL configuration files (postgresql.conf, conf.d), ALTER ROLE/DATABASE defaults, and pg_db_role_setting for non-default statement_timeout values.
• •Session boundary: pg_stat_activity, pg_locks, pg_settings, and the SET chain captured in the backend's xact command log (pg_stat_statements if enabled) for per-session overrides.
• •Client boundary: connection pooler (PgBouncer, pgpool) "server_lifetime", "query_wait_timeout" and pool-mode-related behavior, JDBC socketTimeout, ODBC connect_timeout, application HTTP client timeouts.
• •Network boundary: load balancer or proxy idle/connection timeouts that can sever the socket and be reported by the backend as a cancelled statement upon next server interaction.
• •Workload boundary: autovacuum, ANALYZE, maintenance operations, and extension workers (for example pg_stat_statements reset) that contend for the same relations or locks as the cancelled statement.
• •Logical replication boundary: subscriptions and apply workers whose transaction time can be bounded by a session-level statement_timeout that surfaces on the publisher-facing sessions.

Diagnostic steps

1. 01Read the SQLSTATE and message: 57014 with the statement_timeout phrase confirms server enforcement; 57014 without it, or a different SQLSTATE with the same wording, signals client-side or proxy cancellation.
2. 02Resolve effective statement_timeout with current_setting('statement_timeout') on the cancelling pid and trace it to postgresql.conf, ALTER ROLE/DATABASE, ALTER SYSTEM, or SET; record each layer.
3. 03Sample pg_stat_activity for the pid over a short window to classify the wait_event_type at the cancellation point: Lock/LWLock points to blocking, IO to disk-bound execution, Extension to extension work, Client to network.
4. 04Correlate pg_locks with pg_stat_activity: if granted = false for a blocking locktype, the cancel happened while waiting; if granted = true and the wait_event is IO or Activity, it fired during active execution.
5. 05Inspect pg_settings for reset_val and source columns to identify whether the timeout is a default, a session override, a database/role override, or a file-level setting; this distinguishes transient leaks from persistent misconfiguration.
6. 06Compare server log timestamps against application log timestamps for the same session_id; a delta suggests client or proxy cancellation preceded the server's reported cancel.
7. 07Reproduce with a controlled timeout using SET statement_timeout to a known value and EXPLAIN (ANALYZE, BUFFERS) on the failing query to observe the exact phase that consumes the budget.

Common mistakes

• •Assuming statement_timeout fires only during active execution; it fires during any backend state including lock and I/O waits, which is the most common source of surprise.
• •Blame the application without checking postgresql.conf, ALTER ROLE, or ALTER DATABASE; server-level defaults silently apply to every connection.
• •Confusing log_min_duration_statement with statement_timeout; a slow log entry is independent of a 57014 cancel and does not prove the timeout was the cause.
• •Reading SQLSTATE 57014 as always server-side; PgBouncer query_wait_timeout, JDBC socketTimeout, and HTTP client timeouts can produce the same client-visible error string.
• •Changing statement_timeout to a very large value as a first response, which masks lock contention and I/O regressions without identifying the waiting phase.
• •Ignoring pool-mode semantics: in transaction or statement pooling, per-session SET LOCAL can leak or be ignored, producing non-deterministic cancellation behavior.

Safe fixes

• •If the wait_event_type is Lock and pg_locks shows granted = false on a long-held relation lock, target the holder's transaction (terminate the blocking pid or shorten its work) rather than raising statement_timeout; use pg_terminate_backend(<pid>) only after confirming the holder's identity and impact.
• •If the cancellation is server-enforced and inherited from a global setting, scope the override to the affected role/database with ALTER ROLE <role> SET statement_timeout TO <ms> or ALTER DATABASE <db> SET statement_timeout TO <ms>, and document the rationale.
• •If the timeout fires during active executor work (wait_event_type IO or Activity with granted locks), narrow the budget using EXPLAIN (ANALYZE, BUFFERS) findings: add an index, raise work_mem for a hash join, or split a batch into smaller statements.
• •If the timeout is client- or proxy-driven, raise the driver socketTimeout or the pooler query_wait_timeout, and align it above the server's statement_timeout so the server is the source of truth for cancellation.
• •If SET LOCAL is leaking through a pooled connection, move the override to a transaction-scoped SET LOCAL inside a single statement or to a session-scoped SET applied by the pooler only on the appropriate pool-mode transactions.
• •If the failure is workload-specific, gate the override on a per-application connection that sets statement_timeout = 0 for known long-running migrations and reports, and keep the global default tight for general traffic.

Prove the fix

1. 01Repeat the original workload with the diagnostic queries attached; observe no SQLSTATE 57014 in server logs over a representative window that includes peak contention.
2. 02Capture pg_stat_activity snapshots before and after the change: wait_event_type shifts away from Lock, or the cancelling backend no longer appears with a non-zero wait time at the moment of completion.
3. 03Record current_setting('statement_timeout') for the previously affected pid and confirm the value matches the intended layer (server, role, database, or session) using pg_settings.source.
4. 04Run EXPLAIN (ANALYZE, BUFFERS) on the previously failing query and confirm the total execution time sits comfortably below the configured timeout with realistic concurrency.
5. 05Run a regression check that intentionally injects a smaller statement_timeout and verifies the application receives a clean 57014 with the correct cancel phase reported; this proves the boundary remains observable.
6. 06Monitor pg_locks for the workload's pids over the next maintenance cycle (autovacuum, ANALYZE) to confirm cancellations do not reappear during routine contention.

Prevention and next steps

• •Document statement_timeout as a layered setting: default, role-level, database-level, and per-application session, with the source of truth recorded in version control alongside postgresql.conf.
• •Standardize connection pooler behavior: disallow SET LOCAL for statement_timeout in transaction-pooled modes, and set the pooler's query_wait_timeout above the server's statement_timeout.
• •Adopt a release checklist that requires EXPLAIN (ANALYZE, BUFFERS) review for any query whose expected runtime is within a factor of the configured statement_timeout.
• •Alert on SQLSTATE 57014 rate per minute per application_name so a regression in the waiting phase is detected before user-facing incidents.
• •Periodically audit pg_db_role_setting and pg_settings for unexpected statement_timeout overrides that may have been introduced during incident remediation.

Safe commands and checks

psql -h <host> -p <port> -U <user> -d <db> -c "SELECT pid, datname, usename, application_name, state, wait_event_type, wait_event, query_start, state_change, xact_start, left(query, 200) AS query FROM pg_stat_activity WHERE state <> 'idle';"
psql -h <host> -p <port> -U <user> -d <db> -c "SELECT pid, current_setting('statement_timeout') AS statement_timeout, current_setting('idle_in_transaction_session_timeout') AS its_timeout, current_setting('lock_timeout') AS lock_timeout FROM pg_stat_activity WHERE pid = <pid>;"
psql -h <host> -p <port> -U <user> -d <db> -c "SELECT locktype, mode, granted, relation::regclass AS relation, pid, pg_blocking_pids(pid) AS blocking_pids FROM pg_locks WHERE pid = <pid> OR pid = ANY(pg_blocking_pids(<pid>::int)) ORDER BY granted DESC, pid;"
psql -h <host> -p <port> -U <user> -d <db> -c "SELECT name, setting, unit, source, category FROM pg_settings WHERE name IN ('statement_timeout','idle_in_transaction_session_timeout','lock_timeout','log_min_duration_statement');"
psql -h <host> -p <port> -U <user> -d <db> -c "SELECT setdatabase, setrole, setconfig FROM pg_db_role_setting WHERE setconfig::text ILIKE '%statement_timeout%';"
psql -h <host> -p <port> -U <user> -d <db> -c "SET statement_timeout = '5s'; EXPLAIN (ANALYZE, BUFFERS) <query>;"
psql -h <host> -p <port> -U <user> -d <db> -c "SELECT count(*) AS cancelled_last_min FROM pg_stat_activity WHERE state = 'idle' AND query LIKE '%canceling statement%';"