sql recursive cte max recursion depth

TL;DR

Add a depth counter and a WHERE depth < N guard inside the recursive member, and make sure the recursion actually converges (the join must shrink the working set). Infinite recursion is a logic bug; the depth error is just where it surfaces.

sql recursive cte max recursion depth

Use this when

• A recursive CTE errors on maximum recursion depth
• The query runs forever instead of returning
• You are traversing hierarchies or graphs in SQL

Not for this skill when

• A window function frame clause errors
• A query is slow but terminates
• You need to delete duplicate rows

Steps

1. See the error for what it is. Postgres says:

ERROR: recursive query "r" reached maximum recursion depth

Expected output: the query name from your CTE. SQL Server's equivalent is "maximum recursion 100 has been exhausted" (its default cap is 100, Postgres defaults to no cap but still detects runaway growth).

1. Add an explicit depth guard so runaway recursion fails fast and visibly:

WITH RECURSIVE r(id, parent_id, depth) AS (
  SELECT id, parent_id, 1 FROM nodes WHERE parent_id IS NULL
  UNION ALL
  SELECT n.id, n.parent_id, r.depth + 1
  FROM nodes n JOIN r ON n.parent_id = r.id
  WHERE r.depth < 50
)
SELECT * FROM r;

Expected output: the traversal completes for hierarchies under 50 deep, and stops cleanly at the guard instead of exploding. Tune the cap to your real data depth.

1. Check for cycles, the most common cause of non-termination:

-- find nodes that are their own ancestor (direct self-loop)
SELECT id FROM nodes WHERE id = parent_id;
-- find 2-cycles
SELECT a.id FROM nodes a JOIN nodes b
  ON a.parent_id = b.id AND b.parent_id = a.id;

Expected output: the rows forming cycles. A single self-referencing row makes the recursion infinite without a depth guard.

1. Carry a visited path and stop when a node repeats:

WITH RECURSIVE r(id, parent_id, path) AS (
  SELECT id, parent_id, ARRAY[id] FROM nodes WHERE parent_id IS NULL
  UNION ALL
  SELECT n.id, n.parent_id, r.path || n.id
  FROM nodes n JOIN r ON n.parent_id = r.id
  WHERE NOT n.id = ANY(r.path)
)
SELECT * FROM r;

Expected output: traversal that terminates even on cyclic graphs, because no node is visited twice. The path array also documents the route for debugging.

1. For deep or wide graphs, consider whether SQL recursion is the right tool:

Good fit: org charts, category trees, bill-of-materials (bounded depth).
Poor fit: social graphs, web crawls (high fanout, cycles everywhere).
For the poor-fit cases, traverse in application code or a graph database.

Expected output: a conscious choice. Recursive CTEs are elegant for trees and painful for general graphs.

Variant phrasings

sql server cte maximum recursion 100 exhausted

Add OPTION (MAXRECURSION 1000) at the end of the query to raise SQL Server's cap, but also add the depth guard (step 2). Raising the cap without fixing cycles just delays the explosion.

recursive query did not terminate postgres

Add the cycle check (steps 3-4). Postgres has no default depth cap, so a cyclic graph runs until it eats memory.

postgres with recursive infinite loop

Same fix: carry the visited path and exclude repeats. The infinite loop is always a cycle in the data.

Why it happens

The recursive member keeps joining its own output until it produces no new rows. If the data has a cycle, every iteration produces "new" rows forever. The depth limit (where one exists) is the emergency brake, not the fix.

Edge cases

• UNION (distinct) vs UNION ALL: UNION dedupes and can mask cycles at the cost of performance; UNION ALL is faster but needs the path guard.
• Depth guards hide data bugs silently if set too low; log when the guard actually triggers.
• Multiple roots (several NULL parents) each start their own traversal; make sure that is what you want.
• On huge hierarchies the path array gets expensive; a depth guard alone is cheaper when you know the data is a tree.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_PMFGsRp624dBxEPPnmbbWA