재귀 CTE 무한 루프, 순환 데이터를 막는 DB별 방법

한서윤 · 2026년 10월 29일 · 쿼리와 SQL

재작년 가을, 쇼핑몰 관리자 화면의 카테고리 메뉴가 열리지 않는다는 연락을 받았다. 화면은 로딩 표시만 돌았고 DB 서버의 CPU 그래프는 한쪽 코어만 꼭대기에 붙어 있었다. 실행 중인 쿼리 목록을 열어 보니 카테고리 트리를 펼치는 재귀 쿼리가 몇 분째 같은 자리에 머물러 있었다. 몇 달 동안 아무 문제 없이 돌던 쿼리였다.

원인은 데이터에 있었다. 운영자가 드래그로 카테고리를 옮기다가 상위 카테고리를 자기 손자 밑으로 넣어 버렸다. 화면은 이 이동을 막지 않았고, 테이블에는 1의 자식이 2, 2의 자식이 3, 3의 자식이 다시 1인 고리가 생겼다. 쿼리는 그 고리를 끝없이 돌고 있었다. 쿼리를 끊고 parent_id 하나를 되돌리자 화면은 바로 열렸다. 허탈했다.

그 뒤로 재귀 CTE를 쓸 때는 데이터에 순환이 없다고 가정하지 않는다. 아래는 그때부터 PostgreSQL, MySQL, SQLite에서 순환을 막으려고 쓰는 방법들이다.

기본형: 부모를 따라 내려가는 재귀

카테고리나 조직도처럼 행마다 부모 id를 하나씩 들고 있는 구조는 WITH RECURSIVE로 펼친다. 앵커 부분에서 시작 노드를 고르고, 재귀 부분에서 지금까지 찾은 행의 자식을 붙인다. 재귀 부분이 새 행을 하나도 만들지 못할 때 반복이 끝난다.

CREATE TABLE category (
  id        INTEGER PRIMARY KEY,
  parent_id INTEGER,
  name      VARCHAR(100) NOT NULL
);

WITH RECURSIVE tree AS (
  SELECT id, parent_id, name, 1 AS depth
  FROM category
  WHERE id = 1
  UNION ALL
  SELECT c.id, c.parent_id, c.name, t.depth + 1
  FROM category c
  JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree;

이 쿼리는 세 DB에서 모두 그대로 돈다. 문제는 종료 조건이 데이터에만 달려 있다는 점이다. 자식이 없는 노드에 닿아야 끝나는데, 고리 안에는 그런 노드가 없다. 1에서 출발해 2, 3을 지나면 3의 자식으로 1이 다시 나오고, depth만 하나씩 늘어난 채 같은 세 줄이 계속 쌓인다.

UNION으로 바꾸면 끝날까

처음 떠올린 해법은 UNION ALL을 UNION으로 바꾸는 것이었다. SQLite 문서도 그래프에 순환이 있을 때 무한 루프를 피하려고 UNION을 쓴다고 설명한다. 이미 나온 행과 똑같은 행은 버리니 고리를 한 바퀴 돌면 더 붙을 행이 없어진다는 원리다.

그런데 사내 도구용 SQLite 파일에서 이걸 믿고 바꿨다가 그대로 멈춰 버린 적이 있다. 이유는 depth 컬럼이었다. 같은 카테고리 1이라도 첫 바퀴에서는 depth가 1이고 두 번째 바퀴에서는 4다. 행 전체가 다르니 UNION은 중복으로 보지 않는다. depth나 경로처럼 반복마다 바뀌는 컬럼이 하나라도 있으면 UNION은 순환을 막지 못한다. PostgreSQL 문서도 UNION이 순환을 막지 못하는 경우가 있다고 짚는다. UNION이 통하는 건 id 같은 노드 정보만 돌려받을 때뿐이다.

경로를 들고 다니며 지나온 노드를 확인하기

확실한 방법은 지금까지 거쳐 온 id를 행마다 들고 다니다가, 새로 붙일 자식이 그 안에 있으면 버리는 것이다.

PostgreSQL에는 배열 타입이 있어서 이 방식이 깔끔하다. 앵커에서 ARRAY[id]로 시작하고 재귀마다 ||로 붙인다.

WITH RECURSIVE tree AS (
  SELECT id, parent_id, name, 1 AS depth, ARRAY[id] AS path
  FROM category
  WHERE id = 1
  UNION ALL
  SELECT c.id, c.parent_id, c.name, t.depth + 1, t.path || c.id
  FROM category c
  JOIN tree t ON c.parent_id = t.id
  WHERE c.id <> ALL(t.path)
)
SELECT * FROM tree;

MySQL과 SQLite에는 배열이 없으니 문자열로 흉내 낸다. 앞뒤에 쉼표를 붙여 ,1,2, 꼴로 저장해야 12를 찾을 때 1이나 2가 걸리지 않는다. MySQL에서는 컬럼 타입이 앵커 부분만 보고 정해지므로 CAST로 넉넉한 길이를 잡아 둔다. 이걸 빼먹으면 경로가 길어질 때 값이 잘리거나 에러가 난다. 실제로 경로가 잘려 순환 검사가 틀어진 걸 보고 한참 헤맨 적이 있다.

-- MySQL 8.0
WITH RECURSIVE tree AS (
  SELECT id, parent_id, name, 1 AS depth,
         CAST(CONCAT(',', id, ',') AS CHAR(1000)) AS path
  FROM category
  WHERE id = 1
  UNION ALL
  SELECT c.id, c.parent_id, c.name, t.depth + 1,
         CONCAT(t.path, c.id, ',')
  FROM category c
  JOIN tree t ON c.parent_id = t.id
  WHERE LOCATE(CONCAT(',', c.id, ','), t.path) = 0
)
SELECT * FROM tree;

-- SQLite
WITH RECURSIVE tree AS (
  SELECT id, parent_id, name, 1 AS depth, ',' || id || ',' AS path
  FROM category
  WHERE id = 1
  UNION ALL
  SELECT c.id, c.parent_id, c.name, t.depth + 1, t.path || c.id || ','
  FROM category c
  JOIN tree t ON c.parent_id = t.id
  WHERE instr(t.path, ',' || c.id || ',') = 0
)
SELECT * FROM tree;

PostgreSQL 14의 CYCLE 절

PostgreSQL 14부터는 SQL 표준의 SEARCH와 CYCLE 절을 쓸 수 있다. CYCLE 절은 위의 경로 배열 방식을 문법으로 감싼 것이다. 어느 컬럼으로 순환을 판단할지, 표시 컬럼과 경로 컬럼의 이름을 무엇으로 할지만 적으면 된다.

WITH RECURSIVE tree AS (
  SELECT id, parent_id, name, 1 AS depth
  FROM category
  WHERE id = 1
  UNION ALL
  SELECT c.id, c.parent_id, c.name, t.depth + 1
  FROM category c
  JOIN tree t ON c.parent_id = t.id
) CYCLE id SET is_cycle USING path
SELECT * FROM tree WHERE NOT is_cycle;

결과에는 is_cycle과 path 두 컬럼이 자동으로 붙는다. 직접 짠 방식과 다른 점이 하나 있다. 순환을 일으킨 행이 버려지지 않고 is_cycle이 참인 채로 한 번 나온 다음 거기서 멈춘다. 그래서 바깥에서 NOT is_cycle로 거르거나, 반대로 WHERE is_cycle로 어디서 고리가 생겼는지 찾을 수 있다. 나는 두 번째 쓰임새가 더 반갑다. 카테고리 사건 때 이 기능이 있었다면 문제의 parent_id를 찾는 데 몇 분이면 충분했을 것이다.

깊이 제한과 DB별 안전장치

경로 검사는 순환을 막는다. 하지만 데이터가 순환 없이 비정상적으로 깊어지는 경우까지 막지는 못한다. 그래서 재귀 부분에 AND t.depth < 20 같은 깊이 조건을 함께 건다. 카테고리가 20단계를 넘을 일은 없으니, 넘었다면 데이터가 이상하다는 신호로 본다.

DB가 기본으로 막아 주는 정도는 서로 다르다.

MySQL은 기본값 1000을 넘게 재귀하면 쿼리를 중단한다. 인사 시스템에서 받은 조직도 파일을 MySQL에 넣고 펼쳤을 때 이 에러를 처음 봤다. 두 사람이 서로를 상사로 적어 둔 행이 있었다. 에러 덕분에 서버는 무사했지만, 그날 나는 한도를 10000으로 올려 보는 엉뚱한 짓부터 했다. 한도는 순환을 고쳐 주지 않는다. 더 늦게 실패하게 할 뿐이다.

PostgreSQL 문서는 테스트할 때 바깥 쿼리에 LIMIT을 붙여 무한 재귀를 끊는 요령을 소개한다. 다만 이 동작은 DB마다 다르니 운영 쿼리에서는 권장하지 않는다고 덧붙인다. 나도 그 요령은 콘솔에서 데이터를 확인할 때만 쓴다.

지금 내가 쓰는 방식

요즘은 재귀 CTE를 새로 짤 때 순환 검사부터 넣고 시작한다. PostgreSQL 14 이상이면 CYCLE 절을, 아니면 경로 배열이나 문자열을 쓰고, 어느 쪽이든 깊이 조건을 하나 더 건다. 여기에 DB 쪽 시간 제한을 걸어 두면 최악의 경우에도 쿼리 하나가 서버를 붙잡고 있지는 못한다.

그래도 쿼리는 마지막 방어선이라고 생각한다. 그 가을 이후 카테고리를 옮기는 API에는 새 부모의 조상 중에 자기 자신이 있는지 확인하는 검사를 넣었다. 같은 재귀 쿼리를 거꾸로 올라가며 돌리면 된다. 순환은 생긴 뒤에 피해 가는 것보다 처음부터 생기지 않게 하는 편이 훨씬 싸다. 쿼리가 고리를 건너뛰는 동안에도 잘못된 데이터는 테이블에 그대로 남아 있다. 그래서 WHERE is_cycle로 걸리는 행이 생기면 알림을 받도록 해 두었다.