0Pricing
SQL Academy · Lesson

Avoiding Infinite Loops

Depth limits and cycle detection.

Avoiding Infinite Loops is a free SQL Academy lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

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;

Adding a Depth Limit

The simplest safeguard is a depth counter. Add a column that increments by 1 on every recursive step, then stop when it exceeds a maximum depth.

This guarantees termination regardless of the data, and the chosen limit gives you a safety ceiling.

WITH RECURSIVE counter AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1
  FROM counter
  WHERE n < 10       -- stop at depth 10
)
SELECT n FROM counter;

Depth Limit in a Hierarchy Query

When traversing an employee hierarchy, you can track depth alongside the path. The WHERE depth < 5 clause prevents traversal beyond 5 levels even if the data has deeper or circular links.

CREATE TEMP TABLE employees (
  id   INT PRIMARY KEY,
  name TEXT,
  manager_id INT
);

INSERT INTO employees VALUES
  (1, 'Alice', NULL),
  (2, 'Bob',   1),
  (3, 'Carol', 2),
  (4, 'Dave',  3);

WITH RECURSIVE hierarchy AS (
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL          -- root

  UNION ALL

  SELECT e.id, e.name, e.manager_id, h.depth + 1
  FROM employees e
  JOIN hierarchy h ON e.manager_id = h.id
  WHERE h.depth < 5                 -- depth limit
)
SELECT id, name, depth FROM hierarchy ORDER BY depth, id;

What Is Cycle Detection?

A cycle occurs in graph data when following edges eventually leads back to a node you already visited. For example: A → B → C → A.

A depth limit still terminates the query in cyclic data, but it does not tell you where the cycle is. Explicit cycle detection does.

CREATE TEMP TABLE edges (
  from_node INT,
  to_node   INT
);

-- Introduce a cycle: 1->2->3->1
INSERT INTO edges VALUES
  (1, 2),
  (2, 3),
  (3, 1),   -- cycle back to 1
  (1, 4);   -- also a non-cyclic branch

SELECT * FROM edges;

Tracking Visited Nodes with an Array

A robust cycle-detection technique is to carry an array of visited node IDs through the recursion. Before visiting the next node, check whether it is already in the array. If it is, skip it.

PostgreSQL makes this easy with the ANY(array) operator and the || array-append operator.

WITH RECURSIVE traverse AS (
  -- Start from node 1
  SELECT from_node,
         to_node,
         ARRAY[from_node] AS visited
  FROM edges
  WHERE from_node = 1

  UNION ALL

  SELECT e.from_node,
         e.to_node,
         t.visited || e.from_node
  FROM edges e
  JOIN traverse t ON e.from_node = t.to_node
  WHERE NOT (e.from_node = ANY(t.visited))   -- skip visited nodes
)
SELECT from_node, to_node, visited
FROM traverse;

The CYCLE Clause (PostgreSQL 14+)

PostgreSQL 14 introduced a built-in CYCLE clause for recursive CTEs. It automatically adds two columns: a boolean flag that is true when a cycle is detected, and an array recording the path taken.

This is cleaner than maintaining the array manually.

WITH RECURSIVE traverse AS (
  SELECT from_node, to_node
  FROM edges
  WHERE from_node = 1

  UNION ALL

  SELECT e.from_node, e.to_node
  FROM edges e
  JOIN traverse t ON e.from_node = t.to_node
)
CYCLE from_node SET is_cycle USING path
SELECT from_node, to_node, is_cycle, path
FROM traverse;

Combining Depth Limit and Cycle Detection

Using both a depth limit and cycle detection together gives you the strongest safety guarantee:

  • The depth limit acts as a hard ceiling regardless of data quality.
  • Cycle detection stops early the moment a loop is found, saving unnecessary iterations.

In production queries, always apply at least one of these safeguards.

WITH RECURSIVE traverse AS (
  SELECT from_node,
         to_node,
         1 AS depth,
         ARRAY[from_node] AS visited
  FROM edges
  WHERE from_node = 1

  UNION ALL

  SELECT e.from_node,
         e.to_node,
         t.depth + 1,
         t.visited || e.from_node
  FROM edges e
  JOIN traverse t ON e.from_node = t.to_node
  WHERE t.depth < 10                           -- depth limit
    AND NOT (e.from_node = ANY(t.visited))     -- cycle guard
)
SELECT from_node, to_node, depth, visited
FROM traverse;

Building the Full Path as a String

Alongside cycle detection it is useful to record the full traversal path as a human-readable string. Concatenating node IDs separated by -> makes it easy to display or debug the route taken through the graph.

WITH RECURSIVE traverse AS (
  SELECT from_node,
         to_node,
         1 AS depth,
         ARRAY[from_node] AS visited,
         from_node::TEXT AS path_str
  FROM edges
  WHERE from_node = 1

  UNION ALL

  SELECT e.from_node,
         e.to_node,
         t.depth + 1,
         t.visited || e.from_node,
         t.path_str || ' -> ' || e.from_node::TEXT
  FROM edges e
  JOIN traverse t ON e.from_node = t.to_node
  WHERE t.depth < 10
    AND NOT (e.from_node = ANY(t.visited))
)
SELECT from_node, to_node, path_str, depth
FROM traverse
ORDER BY depth;

Setting max_recursive_iterations

Some databases (MariaDB, older MySQL) use a session variable to cap recursion. In PostgreSQL the equivalent approach is relying on the depth counter you write yourself, or using statement-level timeouts.

Setting a statement_timeout is a last-resort safety net that terminates any runaway query after a set time.

-- PostgreSQL: set a statement timeout as a safety net
SET statement_timeout = '5s';

-- Now any query that runs longer than 5 seconds is cancelled
WITH RECURSIVE counter AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM counter WHERE n < 1000000
)
SELECT MAX(n) FROM counter;

-- Reset to default when done
SET statement_timeout = '0';

Choosing the Right Depth Limit

There is no universal depth limit. Choose yours based on the maximum realistic depth in your data:

  • An org chart rarely exceeds 10-15 levels — use depth < 20 as a comfortable buffer.
  • A file-system tree might go 50-100 levels deep.
  • A social-network graph traversal is often capped at 3-6 hops.

Set the limit high enough to capture valid data, but low enough to catch runaway queries early.

-- Example: org chart with a generous but safe depth cap
WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.id, e.name, e.manager_id, o.depth + 1
  FROM employees e
  JOIN org o ON e.manager_id = o.id
  WHERE o.depth < 20    -- realistic upper bound for an org chart
)
SELECT id, name, depth
FROM org
ORDER BY depth, name;

Depth Limits vs Cycle Detection

Which technique should you use?

Recap: Keeping Recursive Queries Safe

Here is a summary of what you learned about avoiding infinite loops in recursive CTEs:

  • Depth limit — add a counter column and stop with WHERE depth < N. Always effective, easy to implement.
  • Array-based cycle detection — carry visited node IDs in an array and skip any node already in it. Stops early at the first cycle.
  • CYCLE clause (PostgreSQL 14+) — built-in syntax that automates cycle tracking with is_cycle and path columns.
  • statement_timeout — a database-level safety net for runaway queries, not a replacement for proper logic.
  • Combine both depth limit and cycle detection in production for the strongest guarantee.

With these techniques you can confidently traverse hierarchies and graphs without risking database crashes.

Frequently asked questions

Is the “Avoiding Infinite Loops” lesson free?

Yes — the full text of “Avoiding Infinite Loops” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Avoiding Infinite Loops”?

Depth limits and cycle detection. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Avoiding Infinite Loops” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. How Recursive CTEs Work
  2. Walking a Category Tree
  3. Generating Series and Sequences
  4. Avoiding Infinite Loops
← Back to SQL Academy