如何在PostgreSQL中检测资产的间接自包含关系？

可以通过PostgreSQL的**递归CTE（Common Table Expression）**实现这个需求，通过跟踪资产的包含路径并限制最大递归层级（比如10层）来避免无限循环。以下是具体的查询语句：

WITH RECURSIVE asset_chain AS (
    -- 初始步骤：将每个资产作为起始节点，记录当前路径和层级
    SELECT
        a.id AS start_asset_id,
        a.id AS current_asset_id,
        ARRAY[a.id] AS visited_assets,
        1 AS depth
    FROM assets a

    UNION ALL

    -- 递归步骤：沿着包含关系向下遍历，排除已访问过的节点（避免重复），限制最大层级
    SELECT
        ac.start_asset_id,
        ai.included_asset_id AS current_asset_id,
        ac.visited_assets || ai.included_asset_id AS visited_assets,
        ac.depth + 1 AS depth
    FROM asset_chain ac
    JOIN asset_inclusions ai ON ac.current_asset_id = ai.including_asset_id
    -- 确保下一个节点不在已访问路径中，防止重复遍历
    WHERE ai.included_asset_id <> ALL(ac.visited_assets)
    -- 限制最大递归层级为10，避免无限运行
    AND ac.depth < 10
)
-- 筛选出存在循环的起始资产：当前节点回到了起始节点，且层级大于1（排除直接自包含，已有约束禁止）
SELECT DISTINCT start_asset_id, a.name
FROM asset_chain ac
JOIN assets a ON ac.start_asset_id = a.id
WHERE ac.current_asset_id = ac.start_asset_id
AND ac.depth > 1
ORDER BY start_asset_id;

语句说明： #

• 初始CTE部分：把每个资产作为循环检测的起始点，初始化访问路径为仅包含自身，层级设为1。
• 递归部分：通过asset_inclusions表遍历包含关系，每次将新的被包含资产加入访问路径，层级加1；通过ai.included_asset_id <> ALL(ac.visited_assets)确保不会重复访问同一节点，ac.depth < 10限制最大递归深度。
• 最终筛选：当current_asset_id等于start_asset_id时，说明形成了循环（间接自包含）；depth > 1是因为已有约束禁止直接自包含（including_asset_id = included_asset_id），所以只保留层级大于1的循环。
• DISTINCT：避免同一个资产因为多个循环路径被重复返回。

示例验证： #

• 对于asset1包含asset2，asset2包含asset1的场景：递归到第2层时，current_asset_id回到asset1，会被筛选出来。
• 对于asset1包含asset2，asset2包含asset3，asset3包含asset1的场景：递归到第3层时，current_asset_id回到asset1，会被筛选出来。

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