canceling statement due to statement timeout

What it means

statement_timeout is a limit on how long one statement may run. This statement went past it, so the server cancelled it. Whatever it had done is undone. If it was inside a transaction block, the whole transaction is now aborted, and you need to roll back before anything else works.

The clock runs from the moment the statement reaches the server until it finishes, so time spent waiting for a lock counts as well as time spent working. A statement that would take a millisecond can still time out if another session is blocking it.

The setting is off (0) by default, so if you see this error, something set it: your session, your role, the database, the server’s configuration, a connection option, or a hosted provider or pooler.

The SQLSTATE is 57014 (query_canceled). The same code comes with canceling statement due to user request, which means someone cancelled the statement: a Cancel button, Ctrl+C in psql, or pg_cancel_backend().

Common causes

1. The query is slow: a sequential scan of a big table, a missing index, a poor join plan, or a very large result.
2. It was waiting for a lock. An ALTER TABLE waiting behind a long transaction, or an ordinary query queued behind that ALTER TABLE.
3. The limit suits the app, not this job. Migrations, reports and backfills run as the app’s role inherit its short timeout.
4. The limit was set somewhere you didn’t expect: ALTER ROLE … SET, ALTER DATABASE … SET, PGOPTIONS, or a provider default.

How to fix it

Find where the limit comes from

SHOW statement_timeout;
SELECT setting, unit, source FROM pg_settings WHERE name = 'statement_timeout';

source says where it came from: user for a role setting, database for a database setting, session for a SET in this session, configuration file for postgresql.conf. To list every role and database setting:

SELECT coalesce(r.rolname, 'all roles') AS role,
       coalesce(d.datname, 'all databases') AS database,
       s.setconfig
FROM pg_db_role_setting s
LEFT JOIN pg_roles r ON r.oid = s.setrole
LEFT JOIN pg_database d ON d.oid = s.setdatabase;

Raise it for one job, not for everyone

For this session:

SET statement_timeout = '10min';

For one transaction only:

BEGIN;
SET LOCAL statement_timeout = '10min';
-- the long statement
COMMIT;

For a role that runs reports or migrations:

ALTER ROLE reporting SET statement_timeout = '15min';

For a command-line tool built on libpq, pass it as a connection option (0 turns it off):

PGOPTIONS='-c statement_timeout=15min' psql -h <host> -U <user> -d <database> -f migrate.sql

The PostgreSQL documentation advises against setting it in postgresql.conf, because it then applies to every session.

Make the query faster

Run EXPLAIN (without ANALYZE, which would run the query and time out again) to see the plan. A sequential scan over a large table, where you expected an index, is the usual culprit; an index on the filtered columns often fixes it.

If it was waiting for a lock

While it’s stuck, see what’s blocking it:

SELECT pid, pg_blocking_pids(pid) AS blocked_by, state, wait_event_type, left(query, 60)
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

Finish or end the blocking session. For migrations, set a short lock_timeout below statement_timeout, so a statement that can’t get its lock gives up quickly instead of blocking everyone behind it.

Reproduce it

On PostgreSQL 18.6:

SET statement_timeout = '1s';
SELECT pg_sleep(3);

ERROR:  canceling statement due to statement timeout

Real work is cancelled the same way: SELECT count(*) FROM generate_series(1, 100000000) failed after a second. With \set VERBOSITY verbose, psql shows the code: ERROR: 57014: canceling statement due to statement timeout. PostgreSQL 14, 15, 16 and 17 give the same message.

A role setting, after ALTER ROLE seo_pgconn_login SET statement_timeout = '2s', in a new session as that role:

 setting | source 
---------+--------
 2000    | user
(1 row)

ERROR:  canceling statement due to statement timeout

A lock wait counts. With one session holding an open transaction on seo_pgconn.accounts and an ALTER TABLE seo_pgconn.accounts ADD COLUMN note text waiting behind it, a plain read from a third session queued behind the ALTER TABLE and timed out:

SET statement_timeout = '2s';
SET
SELECT count(*) FROM seo_pgconn.accounts;
ERROR:  canceling statement due to statement timeout

For comparison, pg_cancel_backend() on a running pg_sleep(5) gave:

ERROR:  canceling statement due to user request

In Inlet

Inlet’s query editor can cancel a running statement, and its Activity monitor shows which session blocks which, so you can tell a slow query from one that’s waiting for a lock. EXPLAIN and EXPLAIN ANALYZE are drawn as a tree with the slowest step highlighted.