用 PostgreSQL 递归 CTE 检测部门父子关系循环引用
【摘要】 部门表用 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”。检测时不要从当前节点向下查子树,而是从目标父节点开始向上追溯:
- 以
parent_id作为递归 CTE 的锚点; - 每层通过
p.id = a.parent_id找到上一级; - 用
path数组记录本次递归访问过的节点,防止已有脏数据形成环时无限递归; - 如果追溯结果中包含
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)