PostgreSQL 42P19 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 42P19: invalid recursion

PostgreSQL error code 42P19 (invalid_recursion) occurs when a recursive CTE (WITH RECURSIVE) violates the structural rules enforced by the PostgreSQL query planner. This error is caught at parse/plan time, meaning your query won't execute at all until the recursive structure is corrected. Understanding the strict rules around recursive CTEs is essential for any developer working with hierarchical or graph-style data in PostgreSQL.

Top 3 Causes

1. Using Aggregate Functions Inside the Recursive Term

PostgreSQL strictly forbids the use of GROUP BY, DISTINCT, HAVING, or aggregate functions (COUNT, SUM, etc.) within the recursive part of a WITH RECURSIVE query.

Broken:

-- ERROR 42P19: aggregate in recursive term
WITH RECURSIVE dept_tree AS (
    SELECT department_id, manager_id, 1 AS depth
    FROM departments
    WHERE manager_id IS NULL

    UNION ALL

    SELECT d.department_id, d.manager_id, COUNT(*) -- NOT allowed
    FROM departments d
    JOIN dept_tree dt ON d.manager_id = dt.department_id
    GROUP BY d.department_id, d.manager_id
)
SELECT * FROM dept_tree;

Fixed:

-- Move aggregation outside the recursive CTE
WITH RECURSIVE dept_tree AS (
    SELECT department_id, manager_id, 1 AS depth
    FROM departments
    WHERE manager_id IS NULL

    UNION ALL

    SELECT d.department_id, d.manager_id, dt.depth + 1
    FROM departments d
    JOIN dept_tree dt ON d.manager_id = dt.department_id
)
SELECT manager_id, COUNT(*) AS reports, MAX(depth) AS max_depth
FROM dept_tree
GROUP BY manager_id;

2. Nesting the Recursive Reference Inside a Subquery

The recursive CTE name must be referenced directly in the FROM clause of the recursive term. Placing it inside a subquery (IN, EXISTS, scalar subquery) triggers 42P19.

Broken:

-- ERROR 42P19: recursive reference inside subquery
WITH RECURSIVE emp_tree AS (
    SELECT employee_id, manager_id, name
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.employee_id, e.manager_id, e.name
    FROM employees e
    WHERE e.manager_id IN (
        SELECT employee_id FROM emp_tree  -- NOT allowed here
    )
)
SELECT * FROM emp_tree;

Fixed:

-- Use a direct JOIN instead
WITH RECURSIVE emp_tree AS (
    SELECT employee_id, manager_id, name, 0 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.employee_id, e.manager_id, e.name, et.level + 1
    FROM employees e
    JOIN emp_tree et ON e.manager_id = et.employee_id
)
SELECT employee_id, name, level
FROM emp_tree
ORDER BY level, name;

3. Missing Base Case or Wrong UNION Structure

A valid WITH RECURSIVE query must have a non-recursive base case and a recursive term joined by UNION or UNION ALL. Placing the recursive reference on the left side of UNION or omitting the base case entirely will trigger this error.

Broken:

-- ERROR 42P19: no base case, recursive reference on wrong side
WITH RECURSIVE category_path AS (
    SELECT cp.category_id, cp.name
    FROM category_path cp          -- recursive ref on LEFT side
    JOIN categories c ON c.parent_id = cp.category_id
)
SELECT * FROM category_path;

Fixed — with cycle detection for production safety:

WITH RECURSIVE category_path AS (
    -- Base case: start from root nodes
    SELECT category_id, parent_id, name,
           ARRAY[category_id] AS visited,
           0 AS depth
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- Recursive term: recursive ref on the RIGHT side of UNION ALL
    SELECT c.category_id, c.parent_id, c.name,
           cp.visited || c.category_id,
           cp.depth + 1
    FROM categories c
    JOIN category_path cp ON c.parent_id = cp.category_id
    WHERE NOT c.category_id = ANY(cp.visited)  -- cycle guard
      AND cp.depth < 50                         -- depth limit
)
SELECT category_id, name, depth
FROM category_path
ORDER BY depth, name;

Quick Fix Checklist

Symptom	Fix
Aggregate/GROUP BY in recursive term	Move aggregation to outer query
Recursive name inside subquery	Replace with direct `JOIN`
No base case	Add a non-recursive starting `SELECT`
Recursive ref on left of UNION	Swap — base case left, recursive right

Prevention Tips

1. Always follow the three-part recursive CTE template:
Every WITH RECURSIVE query should follow: ① non-recursive base case → ② UNION ALL → ③ recursive term with a direct JOIN to the CTE name. Keep aggregations, window functions, and subquery references out of the recursive term entirely.

2. Add depth limits and cycle detection on every recursive query in production:

-- Production-safe recursive query template
WITH RECURSIVE safe_tree AS (
    SELECT id, parent_id, name,
           ARRAY[id] AS path,
           0 AS depth
    FROM your_table
    WHERE parent_id IS NULL

    UNION ALL

    SELECT t.id, t.parent_id, t.name,
           st.path || t.id,
           st.depth + 1
    FROM your_table t
    JOIN safe_tree st ON t.parent_id = st.id
    WHERE NOT t.id = ANY(st.path)   -- prevents infinite loops
      AND st.depth < 100             -- hard depth cap
)
SELECT * FROM safe_tree;

Pair this with a statement_timeout setting at the session or role level to catch runaway queries before they impact production workloads.

Related Errors

42601 syntax_error — Malformed WITH RECURSIVE syntax before the planner even checks recursion rules.
42P20 windowing_error — Misuse of window functions, sometimes co-occurring when mixing window functions with CTEs.
54001 statement_too_complex — Can appear when deeply nested recursive CTEs exhaust planner resources.

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