[Solved] Why does this recursive CTE cause an infinite loop?WITH RECURSIVE loop_cte AS ( SELECT id FROM items WHERE id = 1 UNION ALL SELECT i.id FROM items i JOIN loop_cte l ON i.parent_id = l.... | PostgreSQL

PostgreSQL - Common Table Expressions

Why does this recursive CTE cause an infinite loop?

WITH RECURSIVE loop_cte AS ( SELECT id FROM items WHERE id = 1 UNION ALL SELECT i.id FROM items i JOIN loop_cte l ON i.parent_id = l.id ) SELECT * FROM loop_cte;

Assuming the table has a cycle in parent-child links.

ABecause the anchor member is empty

BBecause the recursive CTE lacks a termination condition to stop cycles

CBecause UNION ALL is used instead of UNION

DBecause the JOIN condition is incorrect

Step-by-Step Solution

Solution:

Step 1: Understand recursion with cycles
If the data has cycles, recursion can revisit same rows endlessly without a stop condition.
Step 2: Identify missing termination logic
The query does not filter or limit recursion to prevent revisiting nodes, causing infinite loops.
Final Answer:
Because the recursive CTE lacks a termination condition to stop cycles -> Option B
Quick Check:
Cycles need termination conditions [OK]

Quick Trick: Add cycle checks or limits to prevent infinite recursion [OK]

Common Mistakes:

Blaming UNION ALL for infinite loops
Ignoring data cycles in hierarchy
Assuming anchor member causes loops

Master "Common Table Expressions" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

Why does this recursive CTE cause an infinite loop?

Step 1: Understand recursion with cycles

Step 2: Identify missing termination logic

Final Answer:

Quick Check:

Want More Practice?