A recursive CTE never finishes and has to be killed — how do you make it terminate?

## Why it runs forever A recursive CTE stops when a pass produces nothing. Everything else is a consequence. So a runaway query has exactly one cause in the abstract — every pass finds at least one row — and a handful of concrete forms: - **A missing or wrong stop predicate.** `SELECT n + 1 FROM t` with no `WHERE` is the textbook case: every pass has an input row and produces an output row, forever. - **A predicate that can never be false.** `WHERE n <> 0` on a counter that never reaches 0, or a bound compared against a column the recursive member never changes. - **A join condition that keeps re-matching.** If the recursive member joins on a relationship the data allows to revisit rows already produced, each pass keeps finding successors and the frontier never empties. - **Fan-out without repetition.** Even acyclic data can blow up: if each row expands into several, the frontier grows geometrically and the query is effectively unbounded in practice even though it would technically terminate. ## The one guard that always works Carry a depth counter and bound it inside the recursive member: ```sql WITH RECURSIVE walk(node, depth) AS ( SELECT 'a', 0 -- anchor seeds the counter UNION ALL SELECT e.dst, w.depth + 1 FROM walk w JOIN edges e ON e.src = w.node WHERE w.depth < 50 -- hard stop, independent of the data ) SELECT node, depth FROM walk; ``` Why this is the answer to give: it does not depend on any assumption about the shape of the data. Whatever the rows look like, after 50 passes the predicate is false for every row in the working set, the pass is empty, and the query ends. Choose the bound from the domain — a category tree that is five levels deep in production gets a bound of, say, 50, generous enough never to fire in normal operation and small enough to convert a data problem into a fast, finite answer instead of an outage. Selecting the depth column also makes the failure legible afterwards: if rows come back at the bound, you know you hit the guard rather than the natural end of the data. ## What does not work **An outer `LIMIT`.** The CTE is defined independently of the query reading it. Whether an engine can pipeline a `LIMIT` above a recursive CTE and stop the iteration early depends on the engine and on the plan it chose; some can, and the same query with a slightly different shape may not. It is not a stop condition — it is a coincidence you do not control. **An outer `WHERE`.** Unambiguously too late. It filters a result the engine must finish computing first. **Trusting the engine.** Some engines impose a default recursion-depth limit and raise an error when it is exceeded; others will keep going until they exhaust memory or spill to disk and fill the volume. Both are bad outcomes, and which one you get is a vendor detail. Write the bound yourself. **`ORDER BY` or `DISTINCT` in the outer query.** Neither participates in the iteration at all. ## What UNION buys, and its limit Replacing `UNION ALL` with `UNION` discards rows already produced before they are appended, so they never seed another pass; when the runaway is caused by revisiting the same rows, that alone converges. The limit is that duplicate elimination compares the entire row, so a depth counter, an accumulated path or any per-branch value makes each revisit a distinct row and the safety net vanishes. Since you usually want that depth column anyway, treat `UNION` as an optimisation and the depth bound as the guard. (Detecting an actual repetition in the data, as opposed to simply bounding the work, is a separate technique with its own trade-offs.) ## Diagnosing one in production 1. Reproduce with a small bound — set `WHERE depth < 5` and look at what comes back. If rows are still arriving at the bound in data you expected to be shallow, the data, not the query, is the problem. 2. Add the depth column to the output and group by it. A count per depth that stays flat or grows says the frontier is not shrinking; a count that grows by a constant factor per level says fan-out, not repetition. 3. Check the anchor. An anchor that returns far more rows than you intended (a missing filter on the seed) multiplies everything downstream. 4. Check the join predicate for a missing correlation — a recursive member that joins on a condition that is effectively always true re-expands the whole table every pass. ## The template to remember Every recursive CTE that touches data you do not fully control should ship with: a depth column, a `WHERE depth < n` in the recursive member, and the depth column exposed in the outer select. That is cheap, portable, and turns an unbounded query into a bounded one whose output tells you whether the bound fired.