PostgreSQL 57014 Error: Causes and Solutions Complete Guide

PostgreSQL Error 57014: query canceled

PostgreSQL error code 57014 (query_canceled) occurs when a running query is forcibly stopped by an external event before it completes. This is most commonly triggered by a statement_timeout being exceeded, a lock_timeout expiring while waiting for a lock, or an explicit cancellation via pg_cancel_backend(). Understanding the root cause is critical because the fix differs significantly depending on what triggered the cancellation.

---

Top 3 Causes

1. statement_timeout Exceeded

The most frequent cause. When a query runs longer than the configured statement_timeout, PostgreSQL immediately cancels it. This often affects large table scans, complex joins, or poorly optimized queries in production environments.

-- Check current statement_timeout
SHOW statement_timeout;

-- Temporarily increase timeout for the current session
SET statement_timeout = '10min';

-- Apply a different timeout per role
ALTER ROLE app_user SET statement_timeout = '30s';
ALTER ROLE batch_user SET statement_timeout = '1h';

-- Check slow queries using pg_stat_statements
SELECT
    LEFT(query, 80) AS query_snippet,
    calls,
    ROUND((total_exec_time / calls)::numeric, 2) AS avg_ms
FROM pg_stat_statements
ORDER BY avg_ms DESC
LIMIT 10;

2. lock_timeout or Deadlock Detection

If a query waits too long to acquire a lock held by another transaction, and lock_timeout is set, the waiting query gets canceled. PostgreSQL's deadlock detector can also choose a query as a victim to resolve circular lock dependencies.

-- Check lock_timeout
SHOW lock_timeout;

-- Set lock timeout to avoid long waits
SET lock_timeout = '5s';

-- Find blocking and blocked queries
SELECT
    blocked.pid        AS blocked_pid,
    blocked.query      AS blocked_query,
    blocking.pid       AS blocking_pid,
    blocking.query     AS blocking_query
FROM pg_stat_activity AS blocked
JOIN pg_stat_activity AS blocking
    ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;

-- Cancel the blocking backend
SELECT pg_cancel_backend(<blocking_pid>);

3. Explicit Cancellation via pg_cancel_backend()

DBAs or automated slow-query-killer scripts can explicitly cancel a running query using pg_cancel_backend(). This is intentional but may surprise application developers if not communicated clearly.

-- Identify long-running queries
SELECT
    pid,
    usename,
    state,
    NOW() - query_start AS duration,
    LEFT(query, 100) AS query_snippet
FROM pg_stat_activity
WHERE state != 'idle'
  AND NOW() - query_start > INTERVAL '5 minutes'
ORDER BY duration DESC;

-- Cancel a specific query gracefully
SELECT pg_cancel_backend(<pid>);

-- Force terminate if cancel doesn't work
SELECT pg_terminate_backend(<pid>);

---

Quick Fix Solutions

1. Raise the timeout for long-running but legitimate queries using SET statement_timeout at the session level or ALTER ROLE for persistent settings.
2. Optimize the query by running EXPLAIN (ANALYZE, BUFFERS) and adding missing indexes with CREATE INDEX CONCURRENTLY.
3. Resolve lock contention by identifying and canceling long-running blocking transactions using pg_blocking_pids().

-- Add a missing index without locking the table
CREATE INDEX CONCURRENTLY idx_orders_created_at
ON orders (created_at);

-- Check for missing indexes via sequential scans
SELECT
    relname AS table_name,
    seq_scan,
    idx_scan,
    seq_scan - idx_scan AS difference
FROM pg_stat_user_tables
WHERE seq_scan > idx_scan
ORDER BY difference DESC;

---

Prevention Tips

1. Set layered timeouts by role: Apply short statement_timeout and lock_timeout values for OLTP users and longer values for batch/analytics roles. Also configure idle_in_transaction_session_timeout to clean up stale transactions automatically.

ALTER ROLE app_user SET statement_timeout = '30s';
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '60s';
ALTER ROLE batch_user SET statement_timeout = '3600s';

1. Enable pg_stat_statements and monitor regularly: Proactively detect queries that are trending slower over time before they start hitting timeouts in production.

-- In postgresql.conf:
-- shared_preload_libraries = 'pg_stat_statements'

SELECT LEFT(query, 80), calls,
       ROUND((total_exec_time/calls)::numeric, 2) AS avg_ms
FROM pg_stat_statements
ORDER BY avg_ms DESC LIMIT 10;

---

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.