用 PostgreSQL 递归 CTE 检测部门父子关系循环引用

举报
yd_217513249 发表于 2026/11/12 17:30:18 2026/11/12
【摘要】 部门表用 parent_id 表示上下级时,插入或更新可能形成环。本文给出最小表结构、基于递归 CTE 的向上追溯检测 SQL,以及 BEFORE INSERT/UPDATE 触发器实现,并说明自引用、脏数据和并发写入等边界。

问题场景

部门表常用 parent_id 表示上下级关系。如果插入或更新时把某个节点的父节点指向它自己的后代,就会形成循环引用,例如 A -> B -> C -> A。这种数据会让树形查询、权限继承和展示逻辑出现异常。

更稳妥的做法是在写入前检测:从目标父节点开始沿 parent_id 向上追溯,如果遇到当前节点,说明目标父节点位于当前节点的子树中,应拒绝写入。下面给出最小表结构、检测函数和触发器示例。示例中的 ID、部门名称均为占位值。

最小表结构

CREATE TABLE department (
    id        bigint PRIMARY KEY,
    name      text NOT NULL,
    parent_id bigint,
    CONSTRAINT fk_department_parent
        FOREIGN KEY (parent_id) REFERENCES department(id)
);

parent_id IS NULL 表示根节点。这里假设 id 在业务上不可变;如果允许修改 id,触发器需要同步扩展检查逻辑。

检测思路

准备写入的边是“当前节点 current_id 指向目标父节点 parent_id”。检测时不要从当前节点向下查子树,而是从目标父节点开始向上追溯:

  1. 以 parent_id 作为递归 CTE 的锚点;
  2. 每层通过 p.id = a.parent_id 找到上一级;
  3. 用 path 数组记录本次递归访问过的节点,防止已有脏数据形成环时无限递归;
  4. 如果追溯结果中包含 current_id,说明会形成环。

检测函数

CREATE OR REPLACE FUNCTION department_would_cycle(
    p_current_id bigint,
    p_parent_id  bigint
)
RETURNS boolean
LANGUAGE sql
STABLE
AS $$
    WITH RECURSIVE ancestors(id, parent_id, path) AS (
        SELECT d.id, d.parent_id, ARRAY[d.id]
        FROM department d
        WHERE d.id = p_parent_id

        UNION ALL

        SELECT p.id, p.parent_id, a.path || p.id
        FROM department p
        JOIN ancestors a ON p.id = a.parent_id
        WHERE NOT (p.id = ANY(a.path))
    )
    SELECT EXISTS (
        SELECT 1
        FROM ancestors
        WHERE id = p_current_id
    );
$$;

函数返回 true 表示当前节点会出现在目标父节点的祖先链中,即写入后会形成环。

触发器示例

CREATE OR REPLACE FUNCTION fn_department_check_cycle()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    IF NEW.parent_id IS NULL THEN
        RETURN NEW;
    END IF;

    IF TG_OP = 'UPDATE' THEN
        IF NEW.parent_id IS NOT DISTINCT FROM OLD.parent_id THEN
            RETURN NEW;
        END IF;
    END IF;

    IF NEW.parent_id = NEW.id THEN
        RAISE EXCEPTION '部门 % 不能以自身为父部门', NEW.id
            USING ERRCODE = '23514';
    END IF;

    IF department_would_cycle(NEW.id, NEW.parent_id) THEN
        RAISE EXCEPTION '部门父子关系将形成循环:当前节点 %,目标父节点 %',
            NEW.id, NEW.parent_id
            USING ERRCODE = '23514';
    END IF;

    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_department_check_cycle
BEFORE INSERT OR UPDATE OF parent_id ON department
FOR EACH ROW
EXECUTE FUNCTION fn_department_check_cycle();

触发器只在新插入行或 parent_id 被更新时执行。parent_id 为 NULL 的根节点直接放行;自引用会在进入递归 CTE 前被拒绝。

运行与验证

先依次执行表结构、检测函数和触发器,再插入测试数据:

INSERT INTO department (id, name, parent_id) VALUES
    (1, '总部', NULL),
    (2, '研发部', 1),
    (3, '测试组', 2),
    (4, '前端组', 3);

-- 合法:挂到测试组下
INSERT INTO department (id, name, parent_id) VALUES (5, '自动化组', 3);

-- 非法:自引用
UPDATE department SET parent_id = 1 WHERE id = 1;

-- 非法:总部挂到前端组下,会形成 1 -> 4 -> 3 -> 2 -> 1
UPDATE department SET parent_id = 4 WHERE id = 1;

也可以直接调用检测函数:

SELECT department_would_cycle(1, 4); -- true
SELECT department_would_cycle(4, 1); -- false

每条非法语句会抛出异常并中止当前事务。如果在显式事务中连续演示,需要先 ROLLBACK 或使用 SAVEPOINT。

边界与限制

  • parent_id IS NULL 表示根节点,不参与循环检测。
  • 自引用 id = parent_id 由触发器单独拒绝,避免递归 CTE 处理特殊情况。
  • 目标父节点不存在时,检测函数不会发现环,但外键约束会在写入阶段拒绝该行。
  • 如果表中已经存在历史脏环,path 数组可以让递归终止,避免无限循环;但该函数只判断“本次写入是否形成环”,不会自动修复历史数据。
  • 触发器检查基于当前事务可见的数据快照。两个并发事务可能同时通过检查后提交,从而形成环。若业务要求强一致,需要串行化父子关系写入,例如使用事务级 advisory lock、表级锁,或采用 SERIALIZABLE 隔离级别并处理重试。
  • 示例触发器只覆盖 parent_id 变更。如果允许修改 id,还需要在触发器中检查新旧 ID 及引用关系。
  • 层级很深的树会增加递归深度和 path 数组开销,建议结合业务限制最大层级,并定期审计历史数据。
【版权声明】本文为华为云社区用户原创内容,未经允许不得转载,如需转载请自行联系原作者进行授权。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0)

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。