0Pricing
SQL Academy · Leçon

Éviter les boucles infinies

Limitez la profondeur et détectez les cycles.

Éviter les boucles infinies est une leçon SQL Academy gratuite sur CoddyKit. Ceci est la leçon 4 sur 4. Tu peux lire la leçon complète ci-dessous gratuitement — puis la pratiquer en direct dans le navigateur avec un éditeur de code intégré et un tuteur IA 24/7. Elle fait partie du parcours d'apprentissage SQL Academy, et ta progression se synchronise sur le web et l'application CoddyKit. Le cours SQL Academy comprend 4 leçons au total.

Le problème de la boucle infinie

Les CTE récursifs sont puissants, mais ils comportent un risque sérieux : si votre requête n’atteint jamais un cas de base, elle s’exécutera indéfiniment, consommera toute la mémoire disponible et fera planter la session de base de données.

Comprendre pourquoi les boucles infinies se produisent est la première étape pour les prévenir.

Quand une boucle ne se termine-t-elle jamais ?

Un CTE récursif boucle indéfiniment lorsque le terme récursif continue de produire de nouvelles lignes sans jamais atteindre un état où aucune nouvelle ligne n’est générée.

Cela se produit généralement dans deux cas : une condition d’arrêt manquante ou incorrecte, ou des données cycliques dans lesquelles le nœud A pointe vers B et B pointe à nouveau vers 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;

Ajouter une limite de profondeur

La protection la plus simple est un compteur de profondeur. Ajoutez une colonne qui s’incrémente de 1 à chaque étape récursive, puis arrêtez-vous lorsqu’elle dépasse une profondeur maximale.

Cela garantit la terminaison quelles que soient les données, et la limite choisie vous fournit un plafond de sécurité.

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;

Limite de profondeur dans une requête hiérarchique

Lorsque vous parcourez une hiérarchie d’employés, vous pouvez suivre la profondeur en même temps que le chemin. La clause WHERE depth < 5 empêche le parcours de dépasser 5 niveaux, même si les données contiennent des liens plus profonds ou circulaires.

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;

Qu’est-ce que la détection des cycles ?

Un cycle se produit dans les données d’un graphe lorsque le parcours des arêtes finit par ramener à un nœud déjà visité. Par exemple : A → B → C → A.

Une limite de profondeur termine tout de même la requête lorsque les données sont cycliques, mais elle ne vous indique pas où se trouve le cycle. La détection explicite des cycles le permet.

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;

Suivre les nœuds visités avec un tableau

Une technique robuste de détection des cycles consiste à transporter un tableau d’identifiants de nœuds visités pendant la récursion. Avant de visiter le nœud suivant, vérifiez s’il figure déjà dans le tableau. Si c’est le cas, ignorez-le.

PostgreSQL facilite cette tâche avec l’opérateur ANY(array) et l’opérateur d’ajout au tableau ||.

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;

La clause CYCLE (PostgreSQL 14+)

PostgreSQL 14 a introduit une clause CYCLE intégrée pour les CTE récursifs. Elle ajoute automatiquement deux colonnes : un indicateur booléen qui vaut true lorsqu’un cycle est détecté et un tableau qui enregistre le chemin parcouru.

C’est plus clair que de gérer le tableau manuellement.

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;

Associer limite de profondeur et détection des cycles

Utiliser simultanément une limite de profondeur et la détection des cycles vous offre la meilleure garantie de sécurité :

  • La limite de profondeur agit comme un plafond strict, quelle que soit la qualité des données.
  • La détection des cycles s’arrête dès qu’une boucle est trouvée, ce qui évite les itérations inutiles.

Dans les requêtes de production, appliquez toujours au moins l’une de ces protections.

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;

Construire le chemin complet sous forme de chaîne

En plus de la détection des cycles, il est utile d’enregistrer le chemin complet du parcours sous forme de chaîne lisible. La concaténation des identifiants de nœuds séparés par -> facilite l’affichage ou le débogage de l’itinéraire suivi dans le graphe.

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;

Définir max_recursive_iterations

Certaines bases de données (MariaDB, anciennes versions de MySQL) utilisent une variable de session pour plafonner la récursion. Dans PostgreSQL, l’approche équivalente consiste à s’appuyer sur le compteur de profondeur que vous écrivez vous-même ou à utiliser des délais d’expiration au niveau de l’instruction.

Définir un statement_timeout constitue un filet de sécurité de dernier recours qui interrompt toute requête qui s’emballe après un délai défini.

-- 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';

Choisir la bonne limite de profondeur

Il n’existe pas de limite de profondeur universelle. Choisissez la vôtre en fonction de la profondeur maximale réaliste de vos données :

  • Un organigramme dépasse rarement 10 à 15 niveaux — utilisez depth < 20 comme marge confortable.
  • Une arborescence de système de fichiers peut atteindre 50 à 100 niveaux de profondeur.
  • Le parcours d’un graphe de réseau social est souvent limité à 3 à 6 bonds.

Définissez une limite suffisamment élevée pour inclure les données valides, mais suffisamment basse pour détecter rapidement les requêtes qui s’emballent.

-- 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;

Limites de profondeur ou détection des cycles

Quelle technique devriez-vous utiliser ?

Récapitulatif : sécuriser les requêtes récursives

Voici un résumé de ce que vous avez appris pour éviter les boucles infinies dans les CTE récursifs :

  • Limite de profondeur — ajoutez une colonne compteur et arrêtez-vous avec WHERE depth < N. Toujours efficace et facile à mettre en œuvre.
  • Détection des cycles avec un tableau — transportez les identifiants des nœuds visités dans un tableau et ignorez tout nœud qui y figure déjà. La détection s’arrête dès le premier cycle.
  • Clause CYCLE (PostgreSQL 14+) — syntaxe intégrée qui automatise le suivi des cycles avec les colonnes is_cycle et path.
  • statement_timeout — filet de sécurité au niveau de la base de données pour les requêtes qui s’emballent, et non un remplacement d’une logique correcte.
  • Combinez les deux — utilisez à la fois une limite de profondeur et la détection des cycles en production pour obtenir la meilleure garantie.

Grâce à ces techniques, vous pouvez parcourir des hiérarchies et des graphes en toute confiance, sans risquer de faire planter la base de données.

Questions Fréquemment Posées

La leçon « Éviter les boucles infinies » est-elle gratuite ?

Oui — le texte complet de « Éviter les boucles infinies » est gratuit à lire ici sur le web. Pour la pratiquer de manière interactive (un éditeur de code intégré et un tuteur IA 24/7) et déverrouiller le reste du cours SQL Academy, passe à CoddyKit PRO. Le cours SQL Academy comprend 4 leçons au total.

Qu'est-ce que j'apprendrai dans « Éviter les boucles infinies » ?

Limitez la profondeur et détectez les cycles. Tu pratiques SQL Academy avec du code pratique que tu exécutes directement dans le navigateur, et un tuteur IA 24/7 répond à tes questions au fur et à mesure que tu avances dans la leçon.

Dois-je avoir de l'expérience pour commencer SQL Academy ?

Aucune expérience préalable n'est requise. SQL Academy sur CoddyKit est structuré pour les débutants jusqu'aux apprenants avancés, donc tu peux commencer ici ou depuis le début et avancer à ton rythme. Ceci est la leçon 4 sur 4.

Combien de temps prend la leçon « Éviter les boucles infinies » ?

La plupart des leçons CoddyKit prennent environ 5–10 minutes. Chacune est courte et interactive, tu progresses régulièrement et tu repiques exactement où tu t'es arrêté sur le web et l'app.

Peux-tu écrire et exécuter du code dans cette leçon SQL Academy ?

Oui. Chaque leçon SQL Academy inclut un éditeur de code intégré, tu écris et exécutes du vrai code directement dans ton navigateur et tu reçois des retours IA instantanés — aucune configuration locale requise.

Toutes les leçons de ce cours

  1. Fonctionnement des CTE récursives
  2. Parcourir un arbre de catégories
  3. Générer des séries et des séquences
  4. Éviter les boucles infinies
← Retour à SQL Academy