Detecting cycles in a graph: the CYCLE clause
A loop in the data (A is B's parent, B is A's parent) makes a recursive CTE run forever. The CYCLE clause (PostgreSQL 14+) stops the recursion and flags the offending rows.
Prerequisites
PostgreSQL 14+ (avant : accumuler un ARRAY des ids visités)
SQL
WITH RECURSIVE walk AS (
SELECT id, parent_id
FROM nodes
WHERE parent_id IS NULL
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 USING path -- PostgreSQL 14+
SELECT id, path
FROM walk
WHERE is_cycle; -- lignes impliquées dans une boucleResult
id | path
-----+-------------------------
88 | {(17),(88),(204),(88)}
204 | {(17),(88),(204),(88),(204)}
(2 rows) -- 88 -> 204 -> 88 : boucle à corriger avant migrationSQLRécursifCYCLEIntégrité