Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

PostgreSQL: canceling statement due to statement timeout

The statement was canceled by statement_timeout. Find whether the role, the pooler or the client set it, then scope a longer budget to that job.

~5 min readSQL
#PostgreSQL#Performance#SQL#Troubleshooting
A postgres query block cut at a cyan timeout dial, amber canceled statement plate and a wider scoped window

Introduction & Quick Summary

text
ERROR: canceling statement due to statement timeout

The statement exceeded statement_timeout, and PostgreSQL canceled it. The error is a statement cancellation, not a connection failure: the client stays connected, the transaction is aborted, and everything the transaction did is rolled back.

Where the timeout comes from, in order of likelihood:

  • The role or database default. ALTER ROLE app SET statement_timeout = '5s' applies to every connection the role opens, including migrations and ad-hoc reports.
  • The pooler or the provider. Supabase, PgBouncer, and some managed services set a statement or idle timeout at the pooler level, so the value you set in psql is not the value applied.
  • A client library default. Several drivers send SET statement_timeout on connect, and a framework setting overrides the server's role default.
  • A transaction wrapper. SET LOCAL statement_timeout inside a transaction is reset at commit, so a long job started outside that transaction runs with the role value.
ContextWhere the timeout was setCheck
All statements on a connectionSHOW statement_timeoutRole, database, or client library
Only long reportsConnection string optionoptions=-c statement_timeout=...
Only behind a poolerPooler configurationProvider docs and pool mode
Only inside one functionSET LOCAL in the functionFunction body

Core Mechanics, Signatures & Reproducible Setup

statement_timeout is measured from the moment a statement is received until it completes, and it does not include time spent waiting for a lock unless that wait is part of the statement. When it fires, the backend cancels the statement, the transaction aborts with SQLSTATE 57014 (query_canceled), and the message reads canceling statement due to statement timeout. A separate lock_timeout covers lock waits.

A reproduction shows the error without touching a real table:

SHOW statement_timeout; BEGIN; SET LOCAL statement_timeout = '100ms'; SELECT pg_sleep(1); -- ERROR: canceling statement due to statement timeout ROLLBACK;
-- the same statement with the timeout raised for this session only SET statement_timeout = '5s'; SELECT pg_sleep(1); RESET statement_timeout;
text
statement_timeout ------------------- 100ms (1 row) ERROR: canceling statement due to statement timeout CONTEXT: SQL statement "SELECT pg_sleep(1)"

The CONTEXT line names the canceled statement, which matters when a timeout fires inside a function.

Key Comparison Table

Three settings look similar and are set in different places, so a fix in the wrong one appears to do nothing.

SettingMeasuresCancel messageTypical value
statement_timeoutWhole statement executioncanceling statement due to statement timeout5s to 60s
lock_timeoutTime waiting for a lockcanceling statement due to lock timeout1s to 5s
idle_in_transaction_session_timeoutTime idle inside an open transactionConnection terminated30s to 5m
idle_session_timeoutTime idle outside a transactionConnection terminated5m to 30m

Where a setting can be overridden decides what actually applies to a connection.

ScopeSet withApplies toSurvives reconnect
Serverpostgresql.confAll connections without an overrideYes
DatabaseALTER DATABASE ... SETAll connections to that databaseYes
RoleALTER ROLE ... SETConnections authenticated as that roleYes
SessionSET statement_timeoutCurrent connectionNo
TransactionSET LOCALUntil commit or rollbackNo

Practical Edge Cases, Error Fixes & Deep Dive Code Examples

Read the effective value before changing anything

SHOW statement_timeout shows the session value, which is the one in force. pg_settings shows whether it came from a config file, a role, a database, or a session SET.

SELECT name, setting, unit, source, sourcefile FROM pg_settings WHERE name IN ('statement_timeout', 'lock_timeout', 'idle_in_transaction_session_timeout');
text
name | setting | unit | source -------------------+---------+------+-------------------- statement_timeout | 5000 | ms | database lock_timeout | 0 | ms | default

A source of database or role explains why psql shows a value nobody typed.

Raise it for one job, not for everyone

A migration or a backfill needs a longer timeout; interactive queries do not. Raising the role default to accommodate the job makes every session slower to fail and hides a genuinely stuck query.

-- long-running maintenance, scoped to the session SET statement_timeout = '0'; -- no timeout at all SET statement_timeout = '30min'; -- or a generous ceiling SELECT maintenance_task(); RESET statement_timeout;
-- scoped to one statement inside a transaction BEGIN; SET LOCAL statement_timeout = '20min'; UPDATE large_table SET flag = true WHERE flag IS NULL; COMMIT;

SET LOCAL outside a transaction is a no-op with a warning, which is why the timeout appears not to change. Wrap the statement in BEGIN/COMMIT, or use plain SET for the session.

Cancel from another session on purpose

pg_cancel_backend sends the same cancel that a timeout sends, which is the manual equivalent and a useful way to confirm that a client handles 57014 correctly.

SELECT pid, now() - query_start AS running, left(query, 60) AS query FROM pg_stat_activity WHERE state = 'active' AND now() - query_start > interval '2 minutes' ORDER BY running DESC; SELECT pg_cancel_backend(48213); -- current statement SELECT pg_terminate_backend(48213); -- whole connection

Cancelling is cooperative: the statement stops at the next safe point, so a long COPY may take a moment to exit.

Handle the error in application code

The statement is aborted, so the transaction is unusable. Code that swallows 57014 and continues on the same transaction gets current transaction is aborted, commands ignored until end of transaction block on the next statement.

python
import psycopg try: with psycopg.connect(dsn) as conn: with conn.transaction(): conn.execute("SET LOCAL statement_timeout = '30s'") conn.execute("REFRESH MATERIALIZED VIEW CONCURRENTLY stats") except psycopg.errors.QueryCanceled: raise RuntimeError("refresh took longer than 30s") from None
js
try { await client.query("SET LOCAL statement_timeout = '30s'"); await client.query("REFRESH MATERIALIZED VIEW CONCURRENTLY stats"); } catch (error) { if (error.code === "57014") { throw new Error("refresh timed out; rerun with a longer budget"); } throw error; }

Rolling back and retrying with a larger budget is correct only for idempotent work. A retry on the same aborted transaction fails on the abort, not the timeout.

Poolers and provider-side timeouts

A transaction-mode pooler can hold a server-side setting that the client cannot see, and Supabase, Neon, and similar services document their own limits. A timeout with a suspiciously round value usually comes from there.

-- compare the client's view with the server's view SHOW statement_timeout; SELECT setting FROM pg_settings WHERE name = 'statement_timeout';
bash
# connection-string form, when the driver supports options psql "postgresql://app@db/app?options=-c%20statement_timeout%3D30s" -c "SHOW statement_timeout"

When the pooler sets the value, raising it from the client does not help unless the pooler allows an override, so batch the work into smaller statements or move long jobs to a direct connection.

Long queries that time out for the wrong reason

A timeout sometimes reports the statement you did not blame: a trigger, a view, or a generated column runs inside the outer statement and inherits its timeout.

EXPLAIN (ANALYZE, BUFFERS, SETTINGS) SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 50;

SETTINGS in EXPLAIN prints the planner-relevant settings, and BUFFERS distinguishes an I/O-bound plan from a CPU-bound one. A nested loop over a missing index is the usual finding, and the index removes the need for a longer timeout.

Common Compilation/Runtime Mistakes & Version Gotchas

Setting statement_timeout to 0. Zero means no timeout, which is valid but removes the guard that protects the connection pool from one runaway query. Use a large finite value for maintenance jobs.

Using SET LOCAL outside a transaction. It has no effect and emits a warning; the session value stays unchanged, so the statement still times out.

Retrying the same statement after a timeout. A statement that needs longer will time out again. Either raise the budget for that statement or change the plan.

Confusing statement and transaction timeouts. PostgreSQL has no transaction_timeout before 17. A long transaction made of short statements is not canceled by statement_timeout, but a pooler may enforce a transaction limit separately.

Ignoring 57014 in the driver. psycopg maps it to QueryCanceled, node-postgres exposes error.code, and SQLAlchemy wraps it in OperationalError. Catching too broadly hides a real connection loss.

Version notes. PostgreSQL 17 added transaction_timeout, which cancels a transaction that runs too long even when each statement is fast. idle_session_timeout arrived in 14, idle_in_transaction_session_timeout in 9.6. pg_stat_activity query text truncation is controlled by track_activity_query_size, so a long statement may appear cut in the monitoring query.

Order of investigation: SHOW statement_timeout and pg_settings.source, then the pooler's documentation, then EXPLAIN (ANALYZE, BUFFERS) on the statement itself. When the transaction is long and the statement short, pg_stat_activity with the timestamp converter makes elapsed time readable, and the SQL formatter keeps the queries copyable.

Official references: Client connection defaults and Server signaling functions.

Official references

primary documentation

The SQL documentation that defines the behaviour this guide explains.

Try matching browser tools

All tools

Small utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.

All SQL guides

More in SQL

All SQL guides

The guides filed next to this one in SQL, in library order.