用 PostgreSQL 递归 CTE 查询组织架构并检测 manager_id 环

问题场景

很多系统用单表保存组织架构：

CREATE TABLE employees (
  id         integer PRIMARY KEY,
  name       text NOT NULL,
  manager_id integer REFERENCES employees(id)
);

manager_id 指向同表的 id，NULL 表示顶层。正常数据是一棵树，但历史导入、人工修改或程序缺陷可能让 manager_id 互相指向，形成 A -> B -> A 这样的环。外键只能保证被引用的行存在，不能阻止环。

用 WITH RECURSIVE 向上追溯汇报链时，如果链中有环，数据库会不断产生新行。没有终止条件时，迭代不会自行停止，会一直占用连接和内存，直到语句被取消或达到保护限制。

准备最小环境

下面创建一张占位员工表，并插入一批占位数据：

DROP TABLE IF EXISTS employees;

CREATE TABLE employees (
  id         integer PRIMARY KEY,
  name       text NOT NULL,
  manager_id integer REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
  (1, 'ceo',      NULL),
  (2, 'vp_a',     1),
  (3, 'vp_b',     1),
  (4, 'mgr_a1',   2),
  (5, 'mgr_a2',   2),
  (6, 'dev_a1',   4),
  (7, 'dev_a2',   4),
  (8, 'dev_b1',   3),
  (9, 'dev_b2',   3),
  (10, 'intern',  8);

-- 制造一个环：8 和 9 互相把对方设为上级
UPDATE employees SET manager_id = 9 WHERE id = 8;
UPDATE employees SET manager_id = 8 WHERE id = 9;

此时 8 的上级是 9，9 的上级是 8，形成环。员工 10 的上级是 8，因此从 10 往上追溯会进入这个环。

不检测环时会怎样

先看一个没有环检测的向上追溯查询：

WITH RECURSIVE chain AS (
  SELECT e.id, e.name, e.manager_id, 1 AS level
  FROM employees e
  WHERE e.id = 10

  UNION ALL

  SELECT m.id, m.name, m.manager_id, c.level + 1
  FROM employees m
  JOIN chain c ON m.id = c.manager_id
)
SELECT * FROM chain;

从 10 出发，依次得到 8、9、8、9……level 不断增加。这个查询不会自行停止。实际执行时通常需要手动取消，或者在数据库保护机制下报错。不要在生产库随意运行这类语句。

用路径数组检测环

在递归 CTE 中增加两个列：

• path：从起点到当前节点经过的 id 数组。
• is_cycle：当前节点是否已经在父行的 path 中出现过。

完整查询如下：

WITH RECURSIVE chain AS (
  SELECT
    e.id,
    e.name,
    e.manager_id,
    1 AS level,
    ARRAY[e.id] AS path,
    false AS is_cycle
  FROM employees e
  WHERE e.id = 10

  UNION ALL

  SELECT
    m.id,
    m.name,
    m.manager_id,
    c.level + 1,
    c.path || m.id,
    m.id = ANY(c.path)
  FROM employees m
  JOIN chain c ON m.id = c.manager_id
  WHERE NOT c.is_cycle
)
SELECT id, name, manager_id, level, is_cycle, path
FROM chain
ORDER BY level;

逻辑说明：

• 起点 10 的 path 是 {10}，is_cycle 为 false。
• 递归项通过 m.id = c.manager_id 找到当前节点的上级 m。
• c.path || m.id 把上级 id 追加到路径末尾。
• m.id = ANY(c.path) 判断上级 id 是否已经出现过：出现过就是环，is_cycle 为 true。
• WHERE NOT c.is_cycle 保证当上一行已经标记为环闭合点时，不再继续向下展开。

查询结果大致如下（占位数据）：

第 4 行的 is_cycle 为 true，表示再次遇到 8，形成环，递归在这里终止。如果只想要干净的向上链，可以在外层加 WHERE NOT is_cycle，把最后这条闭合记录过滤掉。

运行方法

把建表和查询保存为 org.sql，用 psql 执行：

psql -h 127.0.0.1 -p 5432 -U placeholder_user -d placeholder_db -f org.sql

也可以在 psql 交互界面中依次粘贴。先建表、插入数据、执行两条 UPDATE 制造环，再运行带环检测的查询，观察 is_cycle 列。

向下查下属树的变体

如果要从某个负责人向下查所有下属，把递归项中的连接方向改为：

FROM employees e
JOIN org o ON e.manager_id = o.id
WHERE NOT o.is_cycle

同时用 e.id = ANY(o.path) 计算 is_cycle，数组累积和环检测逻辑完全相同。需要注意：在 manager_id 单值的结构里，如果环上的节点已经与原来的上级断开，从环外的根向下遍历不一定能进入环；但把环内节点作为起点时，就会遇到环。

边界与限制

• 数组随深度增长：path 保存从起点到当前节点的完整 id 列表，深度为 d 的行会携带长度为 d 的数组。深树或宽树会增加内存和传输开销。如果业务只需要防止无限递归，可以只保留最近若干个祖先，但这样可能漏掉跨度很大的环。
• 索引：递归项使用 m.id = c.manager_id 或 e.manager_id = o.id 做连接，建议在 manager_id 上建立索引，否则每次迭代可能需要扫描较多行。主键 id 上的索引通常已经存在。
• 检测范围：= ANY 只判断当前路径上是否出现过某个 id，不能自动修复数据。找到 is_cycle 为 true 的行后，需要人工或程序修正 manager_id。
• 与 UNION 去重的区别：把 UNION ALL 改为 UNION 也能抑制重复行，但 UNION 需要对整行去重，开销更高，而且会去掉合法的重复路径。数组方式可以区分“同一节点出现在不同分支下”和“环导致的重复”。
• CYCLE 子句：PostgreSQL 14 起支持 SQL 标准的 CYCLE 子句，可以让数据库自动生成循环标记和路径列。如果所用版本较旧，数组方式兼容性更好；如果版本支持，也可以作为替代写法。
• 起点选择：环检测只保证从给定起点出发的遍历会终止。要发现表中所有环，需要其他方式，例如从每个节点分别出发或使用图算法，不能只靠一次递归查询。

小结

在自引用表上做组织架构遍历时，环是需要处理的数据问题。在递归项中维护祖先 id 数组，用 = ANY 判断下一节点是否已经出现，可以精确标记环并在环闭合处终止递归。这个模式对向上追溯和向下展开都适用，代价是数组会随深度增长。配合 manager_id 索引和脏数据修复流程，可以让组织架构查询保持可控。