WITH RECURSIVE tree AS (

SELECT id, parent_id FROM nodes WHERE id = 1

UNION ALL

SELECT n.id, n.parent_id FROM nodes n JOIN tree t ON n.parent_id = t.id

-- Missing termination!

)

SELECT * FROM tree;

WITH RECURSIVE tree AS (

SELECT id, parent_id, 0 AS level FROM nodes WHERE id = 1

UNION ALL

SELECT n.id, n.parent_id, t.level + 1 FROM nodes n JOIN tree t ON n.parent_id = t.id

WHERE t.level < 10 -- Termination condition

)

SELECT * FROM tree;

Without termination condition, recursive CTE continues forever if data has cycles or is very deep. Always add WHERE level < N or similar limit. Database will eventually hit MAXRECURSION limit and error.

Always track level/depth and add WHERE level < reasonable_max

Critical

Infinite recursion - query never stops

WITH RECURSIVE paths AS (

SELECT node_id, ARRAY[node_id] AS path FROM graph WHERE node_id = 1

UNION ALL

SELECT g.target_id, p.path || g.target_id FROM graph g JOIN paths p ON g.source_id = p.node_id

-- No cycle detection!

)

SELECT * FROM paths;

WITH RECURSIVE paths AS (

SELECT node_id, ARRAY[node_id] AS path FROM graph WHERE node_id = 1

UNION ALL

SELECT g.target_id, p.path || g.target_id FROM graph g JOIN paths p ON g.source_id = p.node_id

WHERE NOT g.target_id = ANY(p.path) -- Prevent cycles

)

SELECT * FROM paths;

Graph data (friendships, links) can have cycles: A→B→C→A. Without cycle detection, recursion loops forever. Track visited nodes in path array and check before adding.

For graphs, always track path and check: WHERE NOT new_node = ANY(path)

Critical

Infinite loop in cyclic graphs

WITH RECURSIVE org AS (

SELECT * FROM employees WHERE manager_id IS NULL

UNION -- Wrong! Should be UNION ALL

SELECT e.* FROM employees e JOIN org o ON e.manager_id = o.id