如何查询PostgreSQL中incoming_records表的循环依赖故障记录

表结构与测试数据 #

我有一个PostgreSQL表incoming_records，包含supervisor_id和emp_id两列，建表语句及测试数据如下：

create table incoming_records(supervisor_id,emp_id)as values
 (null,01) -- 顶级资源，上级ID为空
,(01,02)
   ,(02,03)
      ,(03,04)
         ,(04,05)
            ,(05,06)
               ,(06,07)
                  ,(07,08)
,(null,10) -- 直接管理3名员工的上级
     ,(10,11)
     ,(10,12)
     ,(10,13)
,(14,14) -- 长度为1的循环（自循环）
,(15,16)
   ,(16,15) -- 长度为2的循环
,(17,18)
   ,(18,19)
      ,(19,17) -- 长度为3的循环
,(20,21)
   ,(21,22)
      ,(22,23)
         ,(23,20) -- 长度为4的循环
,(null,24) -- 无下属的上级
,(null,25) -- 分支后，一名员工有两名上级
     ,(25,26),(26,28)
     ,(25,27),(27,28)
,(null,29) -- 上级对应空员工
     ,(29,null)
,(null,null) -- 空上级对应空员工
,(99,30) -- 员工对应不存在的上级
,(99,null); -- 空员工对应不存在的上级

问题背景 #

该表每日从外部应用全量更新，约有60000条记录。我需要基于这些记录构建资源层级，编写的递归查询在正常数据下可运行，但当数据存在循环依赖（如A的上级是B，B的上级是A这类场景）时，递归查询会陷入无限循环。我尝试了如下查询来定位这类故障记录，但未成功。

失败的尝试查询 #

WITH recursive tmp AS (
   SELECT supervisor_id,
          emp_id
   FROM incoming_records
   UNION ALL
   SELECT tmp.supervisor_id,
          t.emp_id
   FROM tmp
       INNER JOIN incoming_records AS t
           ON t.supervisor_id = tmp.emp_id
   WHERE t.supervisor_id = tmp.emp_id) 
select * from tmp

解决方案 #

要定位循环依赖，核心思路是在递归过程中跟踪已访问的节点路径，当新节点出现在已有路径中时，即可判定存在循环。以下是可行的查询语句：

WITH RECURSIVE hierarchy AS (
    SELECT 
        supervisor_id,
        emp_id,
        ARRAY[emp_id] AS path,
        FALSE AS is_cycle
    FROM incoming_records
    WHERE emp_id IS NOT NULL -- 排除空员工的无效记录
    UNION ALL
    SELECT 
        h.supervisor_id,
        ir.emp_id,
        h.path || ir.emp_id,
        ir.emp_id = ANY(h.path) -- 检查当前员工是否已在路径中，判断是否循环
    FROM hierarchy h
    JOIN incoming_records ir ON h.emp_id = ir.supervisor_id
    WHERE NOT h.is_cycle -- 已发现循环的分支不再递归
      AND ir.emp_id IS NOT NULL
)
-- 去重后提取所有循环记录及对应的循环路径
SELECT DISTINCT ON(supervisor_id, emp_id)
    supervisor_id,
    emp_id,
    path AS cycle_path
FROM hierarchy
WHERE is_cycle
OR (supervisor_id = emp_id AND supervisor_id IS NOT NULL) -- 单独处理自循环场景
ORDER BY supervisor_id, emp_id;

逻辑说明 #

1. 初始查询：获取所有非空员工的记录，初始化路径为当前员工ID，标记is_cycle为false。
2. 递归步骤：将当前员工作为上级关联下一级员工，更新路径为原路径追加新员工ID；同时检查新员工ID是否已存在于路径中，若是则标记is_cycle为true。
3. 结果筛选：提取所有标记为循环的记录，同时单独处理自循环（上级ID等于员工ID）的场景，最后去重输出循环记录及对应的路径。

这个查询可以有效定位所有类型的循环依赖，同时避免无限递归，适配6万条数据的规模。

内容的提问来源于stack exchange，提问作者GD_Java