使用 AFTER STATEMENT 觸發器中的有界遞迴查詢，拒絕深度超過允許上限的類別。要求寫入交易採用可序列化隔離，並重試序列化失敗，避免並行變更共同建立無效的階層。

本教學適用於使用 Mac 終端機和本機 PostgreSQL 資料庫的開發者。具體任務是限制類別最多只能有三層，包括移動既有子樹時也要遵守此限制。所有範例都使用合成的文章類別。

明確定義深度限制的意義

在此，根類別的深度為 1。其子類別的深度為 2，孫類別的深度為 3。第四層無效。如果你的應用程式將根類別的深度計為零，請先轉換此慣例，再選擇限制值。

資料表使用鄰接串列：每個類別都會儲存一個選填的 parent_id，指向另一個類別。外部索引鍵可確保參照的父類別存在，但深度規則需要檢查多筆資料列。PostgreSQL 明確不支援依賴其他資料列、並能持續維護保證的 CHECK 限制條件。在由 CHECK 呼叫的函式中進行遍歷，也無法消除這項限制。限制條件。

以下實作以 PostgreSQL 18 為目標，這是本文查閱時目前穩定版的文件。請使用已安裝的 PostgreSQL 18 伺服器和用戶端，以及 Node.js 24 和 npm 執行 JavaScript 範例。本教學不保證與更早版本的 PostgreSQL 相容。node-postgres 文件將 Node.js 24 列為相容版本之一。PostgreSQL 18 文件、node-postgres 相容性。

為什麼要在陳述式之後驗證整個階層？

只檢查新父類別的祖先，不足以處理子樹移動。假設 Articles → Guides → Editing 已經佔據三層。將 Articles 移到另一個根類別之下，會使 Editing 成為第四層類別，儘管被移動的資料列本身只到第二層。

提議移動時的判斷規則是：子樹根的新深度 + 子樹高度不得超過最大值，其中高度以子樹根到最深後代的邊數計算。例如，新根深度為 2，加上子樹高度 2，最深深度就是 4。

此實作不分別計算祖先和後代，而是在每個修改陳述式之後檢查所有類別。PostgreSQL 文件指出，AFTER 觸發器會看到該操作所做的變更，而陳述式觸發器每個陳述式只會執行一次。這也能支援多資料列插入和更新，無須依賴資料列處理順序。CREATE TRIGGER。

有界遍歷會從每個類別開始，沿著父項連結向上追溯。任何向上路徑中出現四個節點，就表示不符合三層規則。循環也會產生四節點路徑，因為它永遠無法抵達根類別。因此，同一個深度上限就能在不進行無界遞迴搜尋的情況下，拒絕過深的階層和循環。這項設計是根據 PostgreSQL 文件中遞迴 CTE 的評估方式推導而來。WITH 查詢。

建立資料表和深度觸發器

在 Mac 文字編輯器中，將以下完整設定存成 category-depth.sql。它會建立新的結構描述，因此請在尚未有該結構描述的本機開發資料庫中執行一次。它不會刪除或取代既有資料表。

觸發器函式會傳回 trigger，這是 PL/pgSQL 資料變更觸發器的必要回傳值。它使用 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 不會自動在外部索引鍵的參照端建立索引。外部索引鍵。

檢查插入、子樹移動和循環

連接應用程式之前，請使用植入的資料檢查邊界案例。以下每個操作都假設原始設定未被變更。

操作 預期結果 原因
在類別 3 之下插入類別 5 拒絕 新類別會位於第 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 深度錯誤：提議的變更需要修正。最多重試五次和逐漸增加的短暫延遲只是範例應用程式選擇，並非 PostgreSQL 要求，也不保證最終一定成功。序列化失敗處理。

修改範例前，先了解其限制

此實作會在每個陳述式之後檢查階層，包括只更新名稱的情況。交易不能在一個陳述式中暫時建立超過深度限制的階層，並在後續陳述式中再修正。請規劃每個陳述式，使其執行後都維持有效的階層。PostgreSQL 支援延後檢查的限制觸發器，但它們必須是 AFTER ROW 觸發器；無法只將此陳述式觸發器標記為可延後。CREATE TRIGGER。

此方法的取捨是驗證工作量可預期：若限制為三層，每個類別的遞迴關聯最多包含四筆路徑資料列，不過實際執行成本取決於查詢計畫和資料庫內容。大型資料表或頻繁的小型寫入，在採用全表掃描之前應先進行測量。若改用受影響子樹檢查，就必須保留對後代的驗證、循環拒絕和並行處理的論證。

對於既有資料表，安裝觸發器本身不會稽核已儲存的資料列。暫停一般寫入者後，將函式中的遞迴查詢用作獨立稽核：把 max_levels 替換為 3，並選取每個符合條件的 origin_id。解決回報的路徑，並在啟用寫入路徑之前另外驗證父項參照。

需要維持的不變條件很簡單：每個類別都必須在三個節點內抵達根類別。有界遍歷會在子樹移動和循環的情況下檢查此條件；可序列化交易和完整重試則確保並行更新也遵守此條件。修改範例以套用到你的類別資料表時，請保留這些部分。

資料來源