How do you stop a recursive CTE from looping forever when the hierarchy contains a cycle?

## What a cycle is in an adjacency list A self-referencing table stores a hierarchy as parent pointers. Nothing in that design forces the result to be a tree. A foreign key guarantees the referenced row exists, not that following the pointers terminates. So `employees(id, manager_id)` can perfectly legally contain rows where 3 reports to 8, 8 reports to 12, and 12 reports to 3 — an import mistake, a bad re-parent, an admin fixing an org chart at midnight. Category trees acquire the same defect when a node is moved under one of its own descendants. Every recursive traversal of that data is exposed to it. This is not an exotic corner case; it is the failure mode that turns a well-tested hierarchy query into an incident. ## Why the query does not stop A recursive CTE iterates until an iteration produces no new rows. With a loop in the data, each pass around the cycle produces rows again, forever. If you are also carrying a depth counter or an accumulated path, the rows differ on every pass even by value, so nothing can ever eliminate them — the result grows without bound until the engine raises an error, exhausts its temporary space, or the client gives up. Some engines have a configurable recursion limit that turns this into an error rather than a hang; you cannot rely on that being on, and an error is still an outage. ## Guard 1: the visited-path check (portable) Carry the nodes already visited on the current branch and refuse to expand one twice: ```sql WITH RECURSIVE walk (id, parent_id, path) AS ( SELECT n.id, n.parent_id, CAST('/' || CAST(n.id AS VARCHAR(10)) || '/' AS VARCHAR(4000)) FROM nodes n WHERE n.id = 1 UNION ALL SELECT c.id, c.parent_id, w.path || CAST(c.id AS VARCHAR(10)) || '/' FROM nodes c JOIN walk w ON c.parent_id = w.id WHERE POSITION('/' || CAST(c.id AS VARCHAR(10)) || '/' IN w.path) = 0 ) SELECT id, path FROM walk; ``` Three points make this correct rather than approximately correct. The path is delimited **on both ends of every id**, so the containment test cannot match a substring of another id — without that, `/7/` matches inside `/17/` and you silently prune valid branches. The check is against the **whole accumulated path**, not just the immediate parent: comparing only to the parent catches a two-node ping-pong and misses every longer loop. And the anchor casts the path wide enough that deep branches do not truncate, which would corrupt the test itself. Where the engine offers array types, an array path with an element-membership test is cleaner and avoids the delimiter reasoning entirely — but that is dialect territory, so the string form is the portable default. Note the semantics: this prunes per *branch*. A node legitimately reachable by two different paths (common in a bill of materials, impossible in a strict tree) still appears once per path, which is usually what you want. ## Guard 2: the CYCLE clause The SQL standard defines a clause that does the bookkeeping for you: ```sql WITH RECURSIVE walk (id, parent_id) AS ( SELECT id, parent_id FROM nodes WHERE id = 1 UNION ALL SELECT n.id, n.parent_id FROM nodes n JOIN walk w ON n.parent_id = w.id ) CYCLE id SET is_cycle TO true DEFAULT false USING path SELECT id, parent_id, is_cycle FROM walk; ``` The engine maintains the `path` column of visited key values itself, adds an `is_cycle` marker column, and stops expanding a row once the key repeats — the offending row is returned with the marker set, so you can either filter it out or report it. Support is not universal: PostgreSQL implements `CYCLE` from version 14, while some widely used engines have no equivalent at all. Check your target before using it, and keep the manual path check as the portable form. ## Guard 3: a depth cap (a bound, not a detector) `WHERE w.depth < 50` in the recursive member stops the bleeding, and as a belt-and-braces limit alongside a real guard it is reasonable. On its own it is not cycle handling: it truncates legitimate deep branches at the same limit, and up to that limit it happily returns the same nodes over and over, so the *result* is wrong rather than merely incomplete. Choosing the cap is also guesswork — too low breaks real data, too high still burns significant work on bad data. ## Report the cycle, do not just survive it A cycle in an org chart or a category tree is corrupt data, and a query that quietly prunes it hides a bug that will resurface elsewhere. Prefer surfacing it: keep the row where the repeat was detected (the `is_cycle` marker, or the row your path predicate rejected, captured by a separate query) and route it to an alert or a data-quality report. Fixing the walk and fixing the data are two different jobs, and only one of them stays fixed. ## Common mistakes Believing the engine detects loops automatically. Believing `UNION` instead of `UNION ALL` fixes it — deduplication can save you only when the carried rows are literally identical, and the moment you add a depth or path column every pass produces distinct rows again. Comparing only against the immediate parent. Forgetting the delimiters. And shipping a depth cap as if it were a cycle guard.