SQL Academy · Lesson

Avoiding Infinite Loops

Depth limits and cycle detection.

The Infinite Loop Problem

Recursive CTEs are powerful, but they carry a serious risk: if your query never reaches a base case, it will loop forever, consuming all available memory and crashing the database session.

Understanding why infinite loops happen is the first step toward preventing them.

When Does a Loop Never End?

A recursive CTE loops indefinitely when the recursive term keeps producing new rows without ever arriving at a state where no new rows are generated.

This usually happens in two scenarios: a missing or wrong termination condition, or cyclic data where node A points to B and B points back to A.

-- Simple recursive CTE that WOULD loop forever
-- (do NOT run this as-is; illustration only)
WITH RECURSIVE counter AS (
  SELECT 1 AS n          -- base case
  UNION ALL
  SELECT n + 1           -- recursive term
  FROM counter
  -- no WHERE clause to stop it!
)
SELECT n FROM counter;

All lessons in this course

← Back to SQL Academy