Recursive CTEs and termination

The idea

A recursive common table expression starts with an anchor query and repeatedly applies a recursive term to produce additional rows. A simple sequence can begin at one and emit the next integer while its bound allows progress. Trace the rows contributed by the anchor separately from the rows produced later, because an off-by-one stopping condition can add or omit a boundary value.

Recursive graph traversal also needs a cycle policy. A hierarchy may be acyclic by contract, but arbitrary graph data can revisit the same nodes indefinitely. UNION can suppress duplicate complete rows in suitable formulations, while UNION ALL preserves them. Adding changing columns such as depth can make repeated nodes distinct complete rows, so deduplication alone is not a universal cycle detector. State a visited-node strategy or a justified bound.

WITH RECURSIVE n(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM n WHERE x<4)
SELECT x FROM n ORDER BY x;

x
1
2
3
4

The anchor contributes one; values below four generate their successor, and four generates no further row.