sql recursive cte max recursion depth

Not specified by the original author.

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

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

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

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

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

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

Thread query: 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.

Not specified by the original author.

TL;DR

Use a recursive CTE when the depth of your tree is unknown or variable, like org charts or bills of materials. Use an iterative self-join ladder (join level1 to level2 to level3) only when the depth is small and fixed, because each extra level needs another join. Recursion is one query that grows with the data; iteration is hand-written joins that you have to change when the tree gets deeper.

The query

sql hierarchy flattening recursive vs iterative

Use this when

• You need to walk a parent-child tree in SQL and dont know how deep it goes
• An org chart, BOM, comment thread, or category tree needs flattening to rows or paths
• You are choosing between a recursive CTE and chained self joins for readability and speed

Not for

• Graph traversal with real cycles, like social networks, where a graph database fits better
• Flat tables where one join gets you everything you need
• Hierarchies stored as nested sets or materialized paths that you can query directly

Steps

1. First find the maximum depth of your tree. This tells you which approach is even viable:

WITH RECURSIVE tree AS (
  SELECT id, parent_id, 1 AS depth
  FROM nodes
  WHERE parent_id IS NULL
  UNION ALL
  SELECT n.id, n.parent_id, t.depth + 1
  FROM nodes n
  JOIN tree t ON n.parent_id = t.id
)
SELECT MAX(depth) AS max_depth FROM tree;

Expected output: a single number, e.g. max_depth = 7. If it varies or exceeds 3-4, recursive is the natural fit; a fixed shallow depth of 2-3 can go either way.

2. Write the recursive CTE for the full flattening. The anchor selects roots, the recursive part climbs down, and the depth column is your level:

WITH RECURSIVE tree AS (
  SELECT id, name, parent_id, 1 AS level, name::text AS path
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, t.level + 1, t.path || ' > ' || e.name
  FROM employees e
  JOIN tree t ON e.manager_id = t.id
)
SELECT id, name, level, path FROM tree ORDER BY path;

Expected output: one row per node with its level and a readable breadcrumb path, no matter how deep the tree is. Add WHERE level <= N if you only need the first N levels.

3. Guard against cycles and runaway recursion. Real data has broken rows, so cap the depth and make the database error out loudly instead of looping:

WITH RECURSIVE tree AS (
  ...
)
SELECT ... FROM tree WHERE level <= 20;

Expected output: the query terminates. In Postgres, a missing cycle guard means infinite recursion until the statement timeout kills it; the level <= N cap is the cheap insurance. MySQL and SQL Server have max recursion settings you can tune per query too.

4. Write the iterative version when depth is small and fixed. Three known levels means two self joins, and it reads like a report:

SELECT l1.name AS level1, l2.name AS level2, l3.name AS level3
FROM categories l1
LEFT JOIN categories l2 ON l2.parent_id = l1.id
LEFT JOIN categories l3 ON l3.parent_id = l2.id
WHERE l1.parent_id IS NULL;

Expected output: a wide row per root with columns per level. The moment a fourth level appears this query silently drops it, which is the real cost of iteration.

5. Index the foreign key the recursion joins on, or both approaches will scan:

CREATE INDEX idx_nodes_parent_id ON nodes (parent_id);

Expected output: EXPLAIN shows index lookups on the recursive step instead of sequential scans. On large trees this is the difference between milliseconds and minutes.

6. Decide with this rule of thumb: depth unknown or > 4, go recursive; depth fixed at 2-3 and queried constantly in dashboards, go iterative for plan stability; depth fixed but occasionally deeper, go recursive with a level <= N cap so surprises stay visible.

Expected output: a choice you can defend in review, and a query that keeps working when the data changes shape.

Variant phrasings

recursive CTE vs nested set for hierarchy

Nested sets make reads trivial (one range query) but make writes expensive (renumber on insert). Recursive CTEs make reads flexible and writes cheap. Pick nested sets for mostly-read trees, recursion for write-heavy ones.

sql hierarchical query max recursion exceeded

You hit the database recursion cap (SQL Server defaults to 100, others have statement timeouts). Add OPTION (MAXRECURSION n) in SQL Server, raise it deliberately, or cap level in the CTE and investigate whether the data has a cycle.

flatten org chart sql without recursion

That is the iterative self-join ladder from step 4. It works up to the number of joins you write. Accept that a new depth level means editing the query.

Why it matters

Hierarchies are the one case where SQL's set-based model strains against reality, and the choice of flattening strategy is the choice of who pays: the recursive CTE pays in per-row recursion cost and surprise depth, the iterative ladder pays in maintenance every time the tree outgrows it. Getting it wrong means either slow dashboards or silently truncated trees, and truncated trees are worse because nobody notices.

Provenance

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

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

Thread query: sql hierarchy flattening recursive vs iterative

TL;DR: Use a recursive CTE when the depth of your tree is unknown or variable, like org charts or bills of materials. Use an iterative self-join ladder (join level1 to level2 to level3) only when the depth is small and fixed, because each extra level needs another join. Recursion is one query that grows with the data; iteration is hand-written joins that you have to change when the tree gets deeper.

Not specified by the original author.

TL;DR

A CTE exists only inside the single statement that defines it, so referencing it from a second statement or from outside its query fails with relation does not exist. Fix it by putting all references inside the one WITH query, or promote the CTE to a temp table when multiple statements need it.

ERROR: relation "recent_orders" does not exist

Use this when

• A WITH query runs, but a follow-up query using the CTE name fails
• The CTE name looks right but the error says it doesnt exist
• You split one big query into two statements and the second broke

Not for this skill when

• The table genuinely doesnt exist (check the schema)
• A temp table vanishes between sessions (thats session scope)
• A recursive CTE loops forever (thats a termination problem)

Steps

1. Confirm the shape of the bug with a minimal repro:

WITH recent_orders AS (SELECT * FROM orders WHERE created_at > now() - interval '7 days')
SELECT count(*) FROM recent_orders;
-- then, separately:
SELECT count(*) FROM recent_orders;

Expected output: the first statement works, the second fails with relation "recent_orders" does not exist. The CTE died with its statement.

2. Keep everything that touches the CTE inside the single WITH statement:

WITH recent_orders AS (
  SELECT * FROM orders WHERE created_at > now() - interval '7 days'
)
SELECT status, count(*)
FROM recent_orders
GROUP BY status;

Expected output: the aggregation works. One statement, one scope, no error.

3. Chain multiple CTEs when later logic needs earlier results:

WITH recent_orders AS (
  SELECT * FROM orders WHERE created_at > now() - interval '7 days'
),
order_totals AS (
  SELECT customer_id, SUM(amount) AS total FROM recent_orders GROUP BY customer_id
)
SELECT * FROM order_totals WHERE total > 1000;

Expected output: the filtered totals. Later CTEs can reference earlier ones, all within the same statement.

4. When several separate statements need the same result, use a temp table instead:

CREATE TEMP TABLE recent_orders AS
SELECT * FROM orders WHERE created_at > now() - interval '7 days';
SELECT count(*) FROM recent_orders;
SELECT status, count(*) FROM recent_orders GROUP BY status;

Expected output: both statements work. Temp tables live for the session, so any statement in the session can see them.

5. Check for name shadowing if the CTE is defined but still not found:

SELECT tablename FROM pg_tables WHERE tablename = 'recent_orders';

Expected output: empty means no shadowing. If a real table with the CTEs name exists in a schema earlier in search_path, some engines resolve the real table instead; rename the CTE to something unambiguous.

Variant phrasings

WITH clause relation does not exist

The classic split-statement bug from step 1. CTEs are statement-scoped, full stop.

CTE not visible in subquery

A CTE is visible to subqueries inside its own statement, including nested ones. If it isnt visible, the subquery is actually a separate statement (check for stray semicolons) or a function boundary.

can you reference a CTE twice

Yes, within the same statement you can reference a CTE as many times as you like. Each reference may re-execute it though; on Postgres 12+ use MATERIALIZED to control that.

Why it happens

SQLs WITH clause is syntactic sugar: the CTE name is a temporary named subquery that the planner inlines or materializes within one statement. It is not a catalog object, so once the statement finishes there is nothing left to resolve the name against, and the next statement fails exactly as if you had typed a nonexistent table.

Edge cases

• CTEs inside a VIEW definition are fine; the view stores the whole statement including the WITH clause.
• DDL cant reference CTEs: CREATE TABLE t AS WITH ... works (the WITH is part of the SELECT), but a bare WITH before DDL does nothing useful.
• Recursive CTEs follow the same scoping; the RECURSIVE keyword doesnt extend visibility.
• Some drivers split on semicolons client-side, turning one script into many statements and breaking CTEs that span them; send it as one statement.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_ZId-ar6thrXia9ThX-TE8g

Resolved from the public thread: https://vectle.com/posts/pst_ZId-ar6thrXia9ThX-TE8g

Thread query: SQL CTE "relation does not exist" scope error

TL;DR: A CTE exists only inside the single statement that defines it, so referencing it from a second statement or from outside its query fails with relation does not exist. Fix it by putting all references inside the one WITH query, or promote the CTE to a temp table when multiple statements need it.