PostgreSQL 54000 Error: Causes and Solutions Complete Guide

PostgreSQL Error 54000: program_limit_exceeded — Causes, Fixes & Prevention

PostgreSQL error code 54000 program_limit_exceeded is raised when a query or database operation surpasses a hard structural limit enforced by the PostgreSQL engine. Unlike resource exhaustion errors (53xxx class), this error signals that the logical or architectural boundaries of PostgreSQL have been breached. The most common trigger is a stack depth overflow caused by deep recursive function calls, but overly complex query structures can also be responsible.

---

Top 3 Causes

1. Stack Depth Limit Exceeded by Recursive Functions

This is by far the most frequent cause. When a PL/pgSQL or SQL function calls itself too many times, PostgreSQL's internal stack overflows beyond the max_stack_depth parameter (default: ~2MB).

-- Dangerous: unbounded recursive function
CREATE OR REPLACE FUNCTION infinite_recurse(n INTEGER)
RETURNS INTEGER AS $$
BEGIN
    RETURN infinite_recurse(n + 1);  -- No base case!
END;
$$ LANGUAGE plpgsql;

-- This will immediately throw ERROR 54000:
SELECT infinite_recurse(1);

-- Safe version with depth guard
CREATE OR REPLACE FUNCTION safe_recurse(n INTEGER, depth INTEGER DEFAULT 0)
RETURNS INTEGER AS $$
BEGIN
    IF depth > 100 THEN
        RAISE EXCEPTION 'Max recursion depth reached at depth %', depth;
    END IF;
    IF n <= 0 THEN RETURN 0; END IF;
    RETURN n + safe_recurse(n - 1, depth + 1);
END;
$$ LANGUAGE plpgsql;

2. Uncontrolled WITH RECURSIVE Queries (Cycles or Excessive Depth)

Recursive CTEs without proper termination conditions or cycle detection can spiral into infinite loops on graph or tree-structured data, triggering error 54000.

-- Risky: no cycle detection, no depth limit
WITH RECURSIVE tree AS (
    SELECT id, parent_id FROM nodes WHERE parent_id IS NULL
    UNION ALL
    SELECT n.id, n.parent_id
    FROM nodes n
    JOIN tree t ON n.parent_id = t.id
    -- Will loop forever if a cycle exists in data!
)
SELECT * FROM tree;

-- Safe: depth limit + cycle prevention
WITH RECURSIVE tree AS (
    SELECT id, parent_id, 1 AS depth, ARRAY[id] AS visited
    FROM nodes WHERE parent_id IS NULL
    UNION ALL
    SELECT n.id, n.parent_id, t.depth + 1, t.visited || n.id
    FROM nodes n
    JOIN tree t ON n.parent_id = t.id
    WHERE t.depth < 50
      AND NOT (n.id = ANY(t.visited))  -- Cycle guard
)
SELECT id, depth FROM tree ORDER BY depth;

-- PostgreSQL 14+: use CYCLE clause
WITH RECURSIVE tree AS (
    SELECT id, parent_id FROM nodes WHERE parent_id IS NULL
    UNION ALL
    SELECT n.id, n.parent_id FROM nodes n
    JOIN tree t ON n.parent_id = t.id
)
CYCLE id SET is_cycle USING path
SELECT * FROM tree WHERE NOT is_cycle;

3. Excessively Nested Subqueries or Joins

Auto-generated ORM queries or complex BI report queries sometimes produce dozens of nested subqueries, pushing the planner beyond its complexity threshold.

-- Problematic: deeply nested subqueries
SELECT * FROM (
    SELECT * FROM (
        SELECT o.id, c.name, SUM(oi.price) AS total
        FROM orders o
        JOIN customers c ON o.customer_id = c.id
        JOIN order_items oi ON oi.order_id = o.id
        GROUP BY o.id, c.name
    ) sub1 WHERE total > 100
) sub2 WHERE name LIKE 'A%';

-- Better: use temp tables to break it apart
CREATE TEMP TABLE t_order_totals AS
SELECT o.id, c.name, SUM(oi.price) AS total
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id, c.name;

SELECT * FROM t_order_totals
WHERE total > 100 AND name LIKE 'A%';

DROP TABLE t_order_totals;

---

Quick Fix Solutions

-- Check and adjust max_stack_depth (within OS limits)
SHOW max_stack_depth;
SET max_stack_depth = '4MB';  -- Session level

-- Apply permanently in postgresql.conf, then:
SELECT pg_reload_conf();

-- Monitor long-running queries that may be looping
SELECT pid, now() - query_start AS runtime, query, state
FROM pg_stat_activity
WHERE state != 'idle'
  AND now() - query_start > INTERVAL '2 minutes'
ORDER BY runtime DESC;

-- Kill a runaway query if needed
SELECT pg_terminate_backend(<pid>);

---

Prevention Tips

1. Enforce recursion depth limits in all recursive code.
Make it a team standard: every recursive function and WITH RECURSIVE query must include a maximum depth parameter and cycle detection. Validate maximum depth against real data before deploying to production using EXPLAIN ANALYZE.

2. Set statement timeouts as a safety net.
Configure statement_timeout at the role or session level to automatically cancel runaway queries before they exhaust stack or system resources.

-- Set a 30-second timeout for a specific role
ALTER ROLE report_user SET statement_timeout = '30s';

-- Or at session level
SET statement_timeout = '30000';  -- 30 seconds in ms

---

Related Error Codes

Code	Name	Brief Description
53000	insufficient_resources	Physical resource exhaustion (memory, disk)
53200	out_of_memory	Memory allocation failure during query execution
57014	query_canceled	Query cancelled by timeout or manual pg_cancel_backend()
42P17	invalid_object_definition	Malformed recursive view or function definition

Error 54000 is a structural signal — it means your query logic needs to be redesigned, not just that you need more hardware resources. Always address the root cause in the query or function design rather than simply bumping configuration limits.

---

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