在递归CTE中检测并去除重复依赖项的实现方案

我明白你的问题——当前的递归CTE只在单条遍历路径里跳过已访问对象，但不同分支走到同一个对象时还是会重复处理，既浪费资源又可能导致重复结果，尤其是广度优先遍历的时候问题更突出。咱们可以从根源上解决这个问题，要么用纯SQL优化递归逻辑，要么用PL/pgSQL借助临时表跟踪全局已处理对象，两种方案都能避免重复分支的遍历：

方案1：纯SQL递归CTE（简洁高效） #

核心思路是用UNION替代UNION ALL自动去重，同时在递归步骤中只处理还未被加入结果集的对象，从根源上终止重复分支：

WITH RECURSIVE __dependents AS (
    -- 初始步骤：获取当前对象的所有直接依赖
    SELECT object.* 
    FROM immediate_object_dependents(_objectid) object
    UNION
    -- 递归步骤：仅处理未被发现过的间接依赖
    SELECT object.*
    FROM __dependents d
    JOIN immediate_object_dependents((d.object).id) object 
        ON object.id NOT IN (SELECT (obj).id FROM __dependents)
)
SELECT * FROM __dependents;

工作原理：

• UNION会自动合并并去重结果，确保同一对象不会被多次加入结果集；
• 递归步骤的WHERE条件过滤掉已经在结果集中的对象，这样即使多分支走到同一个对象，也不会重复处理它及其后续依赖；
• 循环依赖会被自然阻断：当递归回到已处理过的对象时，会被条件过滤，不会无限循环。

用你的示例表测试时，查询_objectid=1会返回2、4、3三个对象，无重复且不会触发循环。

方案2：PL/pgSQL函数（大数据场景更优） #

如果你的数据集很大，纯SQL里的子查询可能会有性能瓶颈，这时候可以用临时表全局跟踪已处理对象，彻底避免重复查询和遍历：

CREATE OR REPLACE FUNCTION get_all_dependents(_objectid int)
RETURNS SETOF objectdependencies AS $$
DECLARE
    _rec record;
BEGIN
    -- 创建临时表存储已处理的对象ID，主键约束确保唯一性
    CREATE TEMP TABLE IF NOT EXISTS processed_ids (id int PRIMARY KEY);
    TRUNCATE processed_ids; -- 清空之前的残留数据

    -- 先处理直接依赖，插入临时表并返回结果
    FOR _rec IN SELECT * FROM immediate_object_dependents(_objectid) LOOP
        INSERT INTO processed_ids VALUES (_rec.id) ON CONFLICT DO NOTHING;
        RETURN NEXT _rec;
    END LOOP;

    -- 循环处理间接依赖，直到没有新对象可加入
    LOOP
        -- 找出所有已处理对象的直接依赖，且未被处理过的新对象
        FOR _rec IN 
            SELECT od.*
            FROM processed_ids pid
            JOIN objectdependencies od ON od.dependson = pid.id
            LEFT JOIN processed_ids pid2 ON od.id = pid2.id
            WHERE pid2.id IS NULL
        LOOP
            INSERT INTO processed_ids VALUES (_rec.id) ON CONFLICT DO NOTHING;
            RETURN NEXT _rec;
        END LOOP;

        -- 没有新对象时退出循环
        IF NOT FOUND THEN
            EXIT;
        END IF;
    END LOOP;

    RETURN;
END;
$$ LANGUAGE plpgsql;

工作原理：

• 临时表processed_ids记录所有已经处理过的对象ID，主键确保不会重复插入；
• 每次循环只处理未被记录的新依赖对象，避免重复遍历同一分支；
• ON CONFLICT DO NOTHING处理可能的并发插入（或重复分支），彻底杜绝重复处理。

总结 #

两种方案都能在处理阶段就终止重复分支，无需外层DISTINCT浪费资源：

• 纯SQL方案更简洁，适合中小数据集；
• PL/pgSQL方案性能更优，适合大数据量或复杂依赖场景。

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