AFTER STATEMENT 트리거에서 제한된 재귀 쿼리를 사용해 허용된 한계보다 깊은 범주를 거부합니다. 직렬화 가능한 쓰기 트랜잭션을 요구하고 직렬화 실패를 재시도하여, 동시 변경으로 인해 유효하지 않은 계층 구조가 함께 만들어지지 않도록 합니다.

이 튜토리얼은 로컬 PostgreSQL 데이터베이스를 사용하는 Mac의 Terminal 환경에서 작업하는 개발자를 위한 것입니다. 구체적인 목표는 기존 하위 트리를 이동하는 경우에도 범주 수준을 최대 3단계로 제한하는 것입니다. 모든 예제에서는 가상의 기사 범주를 사용합니다.

깊이 제한의 의미를 정확히 정의하기

여기서 루트 범주의 깊이는 1입니다. 그 자식의 깊이는 2, 손자의 깊이는 3입니다. 네 번째 수준은 유효하지 않습니다. 애플리케이션에서 루트의 깊이를 0으로 계산한다면, 제한을 정하기 전에 해당 기준에 맞게 변환하세요.

이 테이블은 인접 목록을 사용합니다. 각 범주는 다른 범주를 가리키는 선택적 parent_id을 저장합니다. 외래 키는 참조된 부모가 존재하도록 보장하지만, 깊이 규칙을 적용하려면 여러 행을 살펴봐야 합니다. PostgreSQL은 다른 행에 의존하는 CHECK 제약 조건을 지속적으로 유지되는 보장으로 지원하지 않는다고 명시합니다. CHECK에서 호출하는 함수 안에 탐색 로직을 넣어도 이 제한은 사라지지 않습니다. 제약 조건.

아래 구현은 이 글을 위해 조회한 현재 안정 버전의 문서를 기준으로 PostgreSQL 18을 대상으로 합니다. 설치된 PostgreSQL 18 서버와 클라이언트, 그리고 JavaScript 예제용 Node.js 24와 npm을 사용하세요. 이전 PostgreSQL 버전은 이 튜토리얼의 호환성 범위에 포함되지 않습니다. node-postgres 문서에는 호환 버전으로 Node.js 24가 나와 있습니다. PostgreSQL 18 문서, node-postgres 호환성.

문장 실행 후 전체 계층 구조를 검증하는 이유

하위 트리를 이동할 때 새 부모의 조상만 확인해서는 충분하지 않습니다. Articles → Guides → Editing이 이미 세 수준을 차지한다고 가정해 보세요. Articles를 다른 루트 아래로 이동하면 이동된 행 자체는 깊이 2에 도달하지만 Editing은 네 번째 수준의 범주가 됩니다.

이동을 위한 판정 규칙은 다음과 같습니다. 하위 트리 루트의 새 깊이 + 하위 트리 높이는 최대 깊이를 넘지 않아야 합니다. 높이는 하위 트리 루트에서 가장 깊은 자손까지의 간선 수입니다. 예를 들어 새 루트 깊이가 2이고 하위 트리 높이가 2라면 가장 깊은 수준은 4가 됩니다.

조상과 자손을 별도로 계산하는 대신, 이 구현은 변경을 수행하는 각 문장이 끝난 후 모든 범주를 검사합니다. PostgreSQL 문서에 따르면 AFTER 트리거는 작업으로 변경된 내용을 볼 수 있고, 문장 트리거는 문장마다 한 번 실행됩니다. 따라서 행 처리 순서에 의존하지 않고 여러 행을 삽입하거나 업데이트하는 작업도 지원합니다. CREATE TRIGGER.

깊이가 제한된 탐색은 모든 범주에서 시작해 부모 링크를 따라갑니다. 위쪽으로 탐색하는 경로에서 노드 네 개를 찾으면 3단계 규칙을 위반한다는 뜻입니다. 루프에서도 루트에 도달하지 못하므로 네 개의 노드가 있는 경로가 만들어집니다. 따라서 같은 제한을 사용해 무제한 재귀 탐색 없이 지나치게 깊은 경로와 순환을 모두 거부합니다. 이 설계는 PostgreSQL의 재귀 CTE 평가 문서에서 도출한 것입니다. WITH 쿼리.

테이블과 깊이 트리거 만들기

Mac 텍스트 편집기에서 아래 설정 전체를 category-depth.sql 파일로 저장하세요. 새 스키마를 만들기 때문에 해당 스키마가 아직 없는 로컬 개발 데이터베이스에서 한 번 실행하면 됩니다. 기존 테이블을 삭제하거나 바꾸지 않습니다.

트리거 함수는 PL/pgSQL 데이터 변경 트리거에 필요한 trigger을 반환합니다. RAISE EXCEPTION과 명시적 오류 코드를 사용해 유효하지 않은 계층 구조와 잘못된 트랜잭션 설정을 구분합니다. 트리거 함수, 오류와 메시지.

BEGIN ISOLATION LEVEL SERIALIZABLE;

CREATE SCHEMA category_depth_demo;

CREATE TABLE category_depth_demo.category (
    id integer PRIMARY KEY,
    name text NOT NULL,
    parent_id integer REFERENCES category_depth_demo.category(id),
    CONSTRAINT category_not_own_parent
        CHECK (parent_id IS NULL OR parent_id <> id)
);

CREATE INDEX category_parent_idx
    ON category_depth_demo.category(parent_id);

CREATE FUNCTION category_depth_demo.enforce_depth()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
    max_levels CONSTANT integer := 3;
    bad_id integer;
BEGIN
    IF current_setting('transaction_isolation') <> 'serializable' THEN
        RAISE EXCEPTION
            'Category writes require a SERIALIZABLE transaction'
            USING ERRCODE = '25000';
    END IF;

    WITH RECURSIVE walk(origin_id, parent_id, levels) AS (
        SELECT id, parent_id, 1
        FROM category_depth_demo.category

        UNION ALL

        SELECT w.origin_id, p.parent_id, w.levels + 1
        FROM walk AS w
        JOIN category_depth_demo.category AS p
          ON p.id = w.parent_id
        WHERE w.levels <= max_levels
    )
    SELECT origin_id INTO bad_id
    FROM walk
    WHERE levels = max_levels + 1
    LIMIT 1;

    IF FOUND THEN
        RAISE EXCEPTION
            'Category % exceeds % levels or belongs to a cycle',
            bad_id, max_levels
            USING ERRCODE = '23514';
    END IF;

    RETURN NULL;
END;
$$;

CREATE TRIGGER category_depth_guard
AFTER INSERT OR UPDATE OR DELETE
ON category_depth_demo.category
FOR EACH STATEMENT
EXECUTE FUNCTION category_depth_demo.enforce_depth();

INSERT INTO category_depth_demo.category(id, name, parent_id)
VALUES
    (1, 'Articles', NULL),
    (2, 'Guides', 1),
    (3, 'Editing', 2),
    (4, 'Reference', NULL),
    (10, 'Notes', NULL),
    (11, 'Examples', NULL);

COMMIT;

이 함수는 PostgreSQL 문서에 나온 current_setting 함수를 통해 트랜잭션 격리 설정을 읽습니다. max_levels 상수 하나로 재귀 한도와 거부 기준을 모두 제어합니다. 설정 조회 함수.

Terminal을 열고 파일이 있는 디렉터리로 이동한 다음 아래 명령을 실행하세요. 이 명령은 기존 로컬 연결 설정으로 localhost의 postgres 데이터베이스에 연결할 수 있다고 가정합니다. -X 옵션은 개인 psql 시작 파일을 건너뛰고, ON_ERROR_STOP은 오류가 나면 파일 처리를 중단합니다. psql.

psql -X -h localhost -d postgres -v ON_ERROR_STOP=1 -f category-depth.sql

외래 키는 기본 삭제 동작을 유지합니다. 자식이 남아 있는 범주를 삭제하면 하위 트리를 조용히 삭제하는 대신 작업이 거부됩니다. 부모 인덱스는 parent_id을 기준으로 한 조회를 지원하지만, 트리거의 전체 테이블 검증을 없애지는 않습니다. PostgreSQL은 외래 키의 참조 측에 인덱스를 자동으로 만들지 않습니다. 외래 키.

삽입, 하위 트리 이동, 순환 확인하기

애플리케이션을 연결하기 전에 시드 데이터로 경계 사례를 검증하세요. 아래 각 작업은 원래 설정이 그대로인 상태를 가정합니다.

작업 예상 결과 이유
범주 5를 범주 3 아래에 삽입 거부 새 범주의 수준이 4가 됩니다.
범주 1을 범주 4 아래로 이동 거부 범주 3이 수준 4로 이동합니다.
범주 1을 범주 3 아래로 이동 거부 이동으로 순환이 만들어집니다.
범주 3을 범주 4 아래로 이동 허용 범주 3이 수준 2가 됩니다.
기본 Read Committed 트랜잭션에서 이름 업데이트 거부 이 구현은 모든 범주 쓰기에 직렬화 가능한 트랜잭션을 요구합니다.

구체적인 하위 트리 테스트를 하려면 psql -X -h localhost -d postgres로 대화형 세션을 시작한 뒤 다음 블록을 실행하세요. 업데이트는 깊이 예외를 발생시켜야 합니다. ROLLBACK는 실패한 트랜잭션을 종료해 원래 계층 구조를 유지합니다.

BEGIN ISOLATION LEVEL SERIALIZABLE;

UPDATE category_depth_demo.category
SET parent_id = 4
WHERE id = 1;

ROLLBACK;

SELECT id, name, parent_id
FROM category_depth_demo.category
ORDER BY id;

이 탐색은 지나친 깊이와 순환을 의도적으로 함께 보고합니다. 목적은 불변 조건을 적용하는 것이며, 순환이 시작되는 정확한 지점을 찾는 것이 아닙니다. 진단 쿼리에서 방문한 ID를 별도로 추적할 수 있지만, 이처럼 제한된 허용 또는 거부 판정에는 필요하지 않습니다.

동시 쓰기에도 같은 규칙 적용하기

재귀 트리거만으로는 동시성 문제가 해결되지 않습니다. 두 트랜잭션이 각자 계층 구조를 검증할 때 상대 트랜잭션의 커밋되지 않은 변경 내용을 볼 수 없을 수 있습니다. PostgreSQL의 일관성 문서는 이 문제를 설명하고, 여러 행에 걸친 규칙을 적용하는 한 가지 방법으로 직렬화 가능한 트랜잭션을 권장합니다. 애플리케이션 수준의 데이터 일관성 검사.

따라서 이 설계는 모든 범주 쓰기에 직렬화 가능한 격리 수준을 요구합니다. 성공한 직렬화 가능 트랜잭션은 하나씩 실행된 것처럼 동작합니다. 동시 작업으로 인해 그런 순서를 보장할 수 없으면 PostgreSQL이 트랜잭션을 거부할 수 있으므로, 업데이트가 성공하더라도 트랜잭션을 커밋하기 전까지는 결과가 확정되지 않습니다. 트랜잭션 격리.

가상의 루트 두 개, 10과 11을 사용해 문제를 확인할 수 있습니다. 대화형 psql 세션 두 개에서 각 세션의 업데이트를 실행하되, 어느 쪽도 아직 커밋하지 마세요.

-- Session A
BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE category_depth_demo.category
SET parent_id = 11
WHERE id = 10;

-- Session B, in a separate psql session
BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE category_depth_demo.category
SET parent_id = 10
WHERE id = 11;

그런 다음 각 세션에서 COMMIT를 실행하세요. 반대 방향의 부모 변경이 함께 순환을 만들므로 두 변경이 모두 커밋되어서는 안 됩니다. 문장 실행 중이나 커밋 시점에 직렬화 실패가 발생할 수 있습니다. 한 세션이 커밋한 뒤 다른 세션이 업데이트를 실행하면, 트리거가 이제 보이는 순환을 직접 거부할 수도 있습니다. 이는 검증 쿼리와 직렬화 가능한 실행을 결합했을 때 예상되는 결과입니다. 트랜잭션 격리.

node-postgres로 범주 이동 실행하기

프로젝트 디렉터리에서 패키지 파일이 없다면 npm init -y를 실행한 다음 npm install pg로 드라이버를 설치하세요. 아래 프로그램 전체를 move-category.mjs으로 저장합니다. 문서에 나온 설치 패키지는 pg입니다. node-postgres.

프로그램은 psql 명령과 같은 로컬 데이터베이스에 연결합니다. 나머지 연결 항목은 드라이버의 환경 변수 기반 기본값을 사용합니다. node-postgres의 요구 사항에 따라 모든 트랜잭션 문장은 하나의 대여된 클라이언트에서 실행합니다. 트랜잭션 문장을 별도의 pool.query 호출에 분산해서는 안 됩니다. 연결, 트랜잭션.

import pg from 'pg';
import { setTimeout as delay } from 'node:timers/promises';

const { Pool } = pg;
const pool = new Pool({
  host: 'localhost',
  database: 'postgres',
  connectionTimeoutMillis: 5000
});

pool.on('error', (error) => {
  console.error('Idle database client error:', error.message);
  process.exitCode = 1;
});

async function moveCategory(id, parentId) {
  const maxAttempts = 5;

  for (let attempt = 1; attempt <= maxAttempts; attempt++) {
    const client = await pool.connect();
    let discard = false;
    let retry = false;

    try {
      await client.query('BEGIN ISOLATION LEVEL SERIALIZABLE');

      const result = await client.query(
        `UPDATE category_depth_demo.category
         SET parent_id = $1
         WHERE id = $2
         RETURNING id, name, parent_id`,
        [parentId, id]
      );

      if (result.rowCount !== 1) {
        throw new Error(`Category ${id} does not exist`);
      }

      await client.query('COMMIT');
      return result.rows[0];
    } catch (error) {
      try {
        await client.query('ROLLBACK');
      } catch {
        discard = true;
      }

      retry = !discard && error.code === '40001'
        && attempt < maxAttempts;

      if (!retry) throw error;
    } finally {
      client.release(discard);
    }

    if (retry) await delay(25 * attempt);
  }
}

try {
  const moved = await moveCategory(3, 4);
  console.log(moved);
} catch (error) {
  console.error(error.code ?? 'APP_ERROR', error.message);
  process.exitCode = 1;
} finally {
  await pool.end();
}

node move-category.mjs로 실행하세요. 원래 시드 데이터에서는 범주 3이 범주 4 아래로 이동하며, 반환된 행에는 parent_id: 4가 포함됩니다. 쿼리는 드라이버의 문서화된 매개변수 인터페이스에 따라 $1과 $2로 값을 전달합니다. 쿼리.

프로그램은 finally에서 클라이언트를 반환하고, 롤백이 실패하면 해당 클라이언트를 폐기하며, 스크립트가 끝나면 풀을 닫습니다. 이 생명 주기 작업은 문서화된 풀 API를 따릅니다. pg.Pool.

SQLSTATE 40001는 이동에 필요한 읽기를 포함해 전체 트랜잭션을 반복하여 재시도하세요. 트리거의 23514 깊이 오류를 자동으로 재시도하지 마세요. 제안된 변경을 수정해야 합니다. 5회 시도 제한과 짧은 점증 지연은 예시용 애플리케이션 선택이며, PostgreSQL의 요구 사항이나 완료 보장이 아닙니다. 직렬화 실패 처리.

예제를 수정하기 전에 한계 이해하기

이 구현은 이름만 바꾸는 업데이트를 포함해 모든 문장 뒤에 계층 구조를 검사합니다. 한 문장에서 일시적으로 깊이를 초과한 뒤 다음 문장에서 복구하는 트랜잭션은 허용되지 않습니다. 각 문장이 끝날 때 계층 구조가 유효하도록 계획하세요. PostgreSQL은 지연 가능한 제약 트리거를 지원하지만 AFTER ROW 트리거여야 합니다. 이 문장 트리거를 지연 가능으로 표시할 수는 없습니다. CREATE TRIGGER.

이 방식은 예측 가능한 검증 작업량과 맞바꿉니다. 제한이 3이면 재귀 관계에 범주당 최대 4개의 탐색 행이 포함되지만, 실제 실행 비용은 쿼리 계획과 데이터베이스 내용에 따라 달라집니다. 테이블이 크거나 작은 쓰기가 잦다면 전체 테이블을 훑는 방식을 채택하기 전에 측정하세요. 영향을 받는 하위 트리만 검사하도록 바꾸려면 자손 검증, 순환 거부, 동시성 관련 근거를 모두 유지해야 합니다.

기존 테이블에 트리거를 설치해도 저장된 행을 자동으로 검사하지는 않습니다. 일반 쓰기를 일시 중지한 상태에서 함수의 재귀 쿼리를 단독 실행해 감사하세요. max_levels을 3으로 바꾸고, 일치하는 모든 origin_id를 선택합니다. 쓰기 경로를 활성화하기 전에 보고된 경로를 해결하고 부모 참조도 별도로 확인하세요.

유지해야 할 불변 조건은 간단합니다. 모든 범주는 세 노드 이내에 루트에 도달해야 합니다. 제한된 탐색은 하위 트리 이동과 순환을 확인하고, 직렬화 가능한 트랜잭션과 전체 트랜잭션 재시도는 동시 업데이트에서도 이 조건을 유지합니다. 범주 테이블에 맞게 예제를 수정할 때도 이 요소들을 함께 유지하세요.

출처