0Pricing
SQL Academy · Урок

Как избежать бесконечных циклов

Ограничивайте глубину и обнаруживайте циклы

«Как избежать бесконечных циклов» — бесплатный урок SQL Academy на CoddyKit. Это урок 4 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Academy, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Academy содержит 4 уроков всего.

Проблема бесконечного цикла

Рекурсивные CTE обладают большой мощью, но несут серьёзный риск: если запрос никогда не достигает базового случая, он будет выполняться бесконечно, израсходует всю доступную память и приведёт к сбою сеанса базы данных.

Понимание причин бесконечных циклов — первый шаг к их предотвращению.

Когда цикл не завершается

Рекурсивный CTE выполняется бесконечно, когда рекурсивный элемент продолжает создавать новые строки и никогда не достигает состояния, в котором новые строки больше не создаются.

Обычно это происходит в двух случаях: отсутствует условие завершения или оно задано неверно; либо данные содержат цикл, в котором узел A указывает на B, а B — обратно на 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;

Добавление ограничения глубины

Самая простая мера защиты — счётчик глубины. Добавьте столбец, который увеличивается на 1 на каждом рекурсивном шаге, и остановите выполнение, когда глубина превысит максимальное значение.

Это гарантирует завершение независимо от данных, а выбранный предел обеспечивает безопасный верхний порог.

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;

Ограничение глубины в запросе иерархии

При обходе иерархии сотрудников можно отслеживать глубину вместе с путём. Предложение WHERE depth < 5 не позволяет обходить уровни глубже пятого, даже если в данных есть более глубокие или циклические связи.

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;

Что такое обнаружение циклов

Цикл возникает в данных графа, когда последовательное прохождение по рёбрам в итоге приводит к уже посещённому узлу. Например: A → B → C → A.

Ограничение глубины всё равно завершит запрос при наличии цикла в данных, но не покажет, где находится цикл. Явное обнаружение циклов это покажет.

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;

Отслеживание посещённых узлов с помощью массива

Надёжный способ обнаруживать циклы — передавать через рекурсию массив идентификаторов посещённых узлов. Перед посещением следующего узла проверьте, нет ли его уже в массиве. Если есть, пропустите этот узел.

PostgreSQL упрощает эту задачу с помощью оператора ANY(array) и оператора добавления элемента в массив ||.

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;

Предложение CYCLE (PostgreSQL 14+)

В PostgreSQL 14 появилось встроенное предложение CYCLE для рекурсивных CTE. Оно автоматически добавляет два столбца: логический флаг, равный true при обнаружении цикла, и массив, в котором записан пройденный путь.

Это удобнее, чем поддерживать массив вручную.

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;

Объединение ограничения глубины и обнаружения циклов

Одновременное использование ограничения глубины и обнаружения циклов обеспечивает наиболее надёжную защиту:

  • Ограничение глубины задаёт жёсткий предел независимо от качества данных.
  • Обнаружение циклов останавливает выполнение сразу после обнаружения цикла, экономя ненужные итерации.

В рабочих запросах всегда применяйте хотя бы одну из этих мер защиты.

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;

Построение полного пути в виде строки

Вместе с обнаружением циклов полезно записывать полный путь обхода в удобной для чтения строке. Объединение идентификаторов узлов с разделителем -> упрощает отображение или отладку маршрута, пройденного в графе.

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;

Настройка максимального числа рекурсивных итераций

Некоторые базы данных (MariaDB, старые версии MySQL) используют переменную сеанса для ограничения рекурсии. В PostgreSQL эквивалентный подход заключается в использовании счётчика глубины, который Вы создаёте самостоятельно, или ограничений времени выполнения на уровне оператора.

Настройка statement_timeout — это крайняя мера защиты, которая завершает любой неконтролируемо выполняющийся запрос по истечении заданного времени.

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

Выбор подходящего ограничения глубины

Универсального ограничения глубины не существует. Выбирайте его с учётом максимальной реалистичной глубины данных:

  • Организационная схема редко превышает 10–15 уровней — используйте depth < 20 как комфортный запас.
  • Дерево файловой системы может иметь глубину 50–100 уровней.
  • Обход графа социальной сети часто ограничивают 3–6 переходами.

Установите предел достаточно высоким, чтобы охватить корректные данные, но достаточно низким, чтобы рано обнаруживать неконтролируемо выполняющиеся запросы.

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

Ограничение глубины или обнаружение циклов

Какой способ следует использовать?

Итоги: безопасность рекурсивных запросов

Подведём итоги изученного о предотвращении бесконечных циклов в рекурсивных CTE:

  • Ограничение глубины — добавьте столбец-счётчик и остановите выполнение с помощью WHERE depth < N. Всегда эффективно и просто реализуется.
  • Обнаружение циклов на основе массива — передавайте идентификаторы посещённых узлов в массиве и пропускайте узлы, которые уже в нём находятся. Останавливает выполнение сразу при обнаружении первого цикла.
  • Предложение CYCLE (PostgreSQL 14+) — встроенный синтаксис, автоматизирующий отслеживание циклов с помощью столбцов is_cycle и path.
  • Ограничение времени выполнения оператора — мера защиты на уровне базы данных от неконтролируемо выполняющихся запросов, но не замена правильной логике.
  • Объединяйте оба подхода: ограничение глубины и обнаружение циклов в рабочих системах обеспечивают наиболее надёжную защиту.

С помощью этих методов Вы сможете уверенно обходить иерархии и графы, не подвергая базу данных риску сбоя.

Часто задаваемые вопросы

Урок «Как избежать бесконечных циклов» бесплатный?

Да — полный текст урока «Как избежать бесконечных циклов» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Academy, подпишись на CoddyKit PRO. Курс SQL Academy содержит 4 уроков всего.

Чему я научусь в уроке «Как избежать бесконечных циклов»?

Ограничивайте глубину и обнаруживайте циклы Ты практикуешь SQL Academy с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать SQL Academy?

Предыдущий опыт не требуется. SQL Academy на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 4 из 4.

Сколько времени занимает урок «Как избежать бесконечных циклов»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке SQL Academy?

Да. Каждый урок SQL Academy включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

  1. Как работают рекурсивные CTE
  2. Обход дерева категорий
  3. Генерация рядов и последовательностей
  4. Как избежать бесконечных циклов
← Назад к SQL Academy