在 PostgreSQL 分类表中强制执行最大深度

在 AFTER STATEMENT 触发器中使用有界递归查询，拒绝深度超过允许限制的分类。要求使用可序列化写入事务并重试序列化失败，以便并发更改无法共同创建无效层级。

本教程面向在 Mac 终端中使用本地 PostgreSQL 数据库的开发者。具体任务是强制执行最多三级分类，包括现有子树移动的情况。所有示例均使用合成文章分类。

明确深度限制的含义

此处，根分类深度为 1。其子分类深度为 2，孙分类深度为 3。第四级无效。如果您的应用将根计为深度零，请在选择限制前转换该约定。

该表使用邻接列表：每个分类存储一个指向另一个分类的可选 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 移至另一根分类下会使 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。在启用写入路径前，解决任何报告的路径并单独验证父引用。

有用的不变量很简单：每个分类必须在三个节点内到达根。有界遍历跨子树移动和环检查该条件；可序列化事务和完整重试使其在并发更新中得以维持。将这些部分结合在一起，以将示例适配到您的分类表。

来源

• 约束 — PostgreSQL 全球开发组。
• WITH 查询（公用表表达式） — PostgreSQL 全球开发组。
• CREATE TRIGGER — PostgreSQL 全球开发组。
• 触发器函数 — PostgreSQL 全球开发组。
• 错误和消息 — PostgreSQL 全球开发组。
• 系统管理函数 — PostgreSQL 全球开发组。
• psql — PostgreSQL 全球开发组。
• 应用层面的数据一致性检查 — PostgreSQL 全球开发组。
• 事务隔离 — PostgreSQL 全球开发组。
• 序列化失败处理 — PostgreSQL 全球开发组。
• node-postgres — node-postgres。
• 连接 — node-postgres。
• 事务 — node-postgres。
• 查询 — node-postgres。
• pg.Pool — node-postgres。