DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

PostgreSQL 57014 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 57014: Query Canceled — What It Means and How to Fix It

PostgreSQL error code 57014 (query_canceled) occurs when a running query is forcibly interrupted by an external factor before it completes. The most common triggers are a statement_timeout expiration, an explicit cancellation via pg_cancel_backend(), or a lock_timeout being exceeded while waiting to acquire a table or row lock. Understanding the root cause is essential 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 cancels it automatically. This is common with complex aggregations, full table scans, or poorly indexed queries.

-- Check current statement_timeout
SHOW statement_timeout;

-- Temporarily extend timeout for a heavy query (current session only)
SET statement_timeout = '5min';

SELECT
    customer_id,
    SUM(amount) AS total_sales
FROM orders
WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY customer_id
ORDER BY total_sales DESC;

-- Reset after the query
SET statement_timeout = '30s';

-- Always analyze slow queries before raising the timeout
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE status = 'PENDING';

-- Add missing indexes to fix the root cause
CREATE INDEX CONCURRENTLY idx_orders_status ON orders(status);
Enter fullscreen mode Exit fullscreen mode

2. Lock Timeout or Lock Contention

When multiple transactions compete for the same row or table lock, a query waiting beyond lock_timeout will be canceled. DDL operations like ALTER TABLE are especially notorious for causing lock queues.

-- Find which sessions are blocking others
SELECT
    blocked_locks.pid     AS blocked_pid,
    blocked_activity.query AS blocked_query,
    blocking_locks.pid    AS blocking_pid,
    blocking_activity.query AS blocking_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity
    ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
    ON blocking_locks.relation = blocked_locks.relation
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity
    ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

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

-- Set a lock_timeout to avoid indefinite waiting
SET lock_timeout = '5s';
Enter fullscreen mode Exit fullscreen mode

3. Explicit Cancellation by Admin or Monitoring System

Automated slow-query killers, connection poolers (e.g., pgBouncer), or cloud providers (e.g., AWS RDS) may cancel queries that exceed their own thresholds. A DBA manually calling pg_cancel_backend() also falls into this category.

-- Identify long-running active queries
SELECT
    pid,
    usename,
    now() - query_start AS running_time,
    state,
    left(query, 100) AS query_snippet
FROM pg_stat_activity
WHERE state = 'active'
  AND query_start < now() - interval '30 seconds'
ORDER BY running_time DESC;

-- Cancel a specific backend by PID
SELECT pg_cancel_backend(12345);

-- Enable logging to track cancellations
ALTER SYSTEM SET log_min_duration_statement = '3000'; -- log queries > 3s
ALTER SYSTEM SET log_cancels = 'on';
SELECT pg_reload_conf();
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

  • Raise timeout only for the specific session or transaction using SET LOCAL statement_timeout inside a BEGIN...COMMIT block — never change it globally unless necessary.
  • Optimize the query first using EXPLAIN ANALYZE before simply increasing timeout values.
  • Use CREATE INDEX CONCURRENTLY to add indexes without locking the table and causing cascading cancellations.
  • Separate timeout policies per role: short timeouts for app users, longer for analytics/batch users. 
-- Per-role timeout configuration (Best Practice)
ALTER ROLE app_user      SET statement_timeout = '10s';
ALTER ROLE analytics_user SET statement_timeout = '30min';
ALTER ROLE batch_user    SET statement_timeout = '2h';
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  1. Enable pg_stat_statements and regularly review the top 10 slowest queries. Proactively optimizing them before they hit timeouts is far better than reacting to errors in production. 
SELECT
    left(query, 80) AS query,
    calls,
    round(mean_exec_time::numeric, 2) AS avg_ms
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;
Enter fullscreen mode Exit fullscreen mode
  1. Set sensible timeout defaults at the database level and override them per role as needed. Never rely solely on application-level timeout handling — always have a database-level safety net.

Related Errors

Code Name Description
57P01 admin_shutdown Query canceled due to server shutdown
55P03 lock_not_available Lock not acquired with NOWAIT
40P01 deadlock_detected Deadlock between two or more transactions

📖 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.

Top comments (0)