PostgreSQL递归查询未递归执行问题排查求助

问题描述 #

当前PostgreSQL查询可正常运行无报错，但未执行预期的递归逻辑——预期父批次若存在子批次时，应将该父批次转为子批次循环查询上层父批次，但该功能未生效。

原查询代码 #

with recursive parentlots as(
    with
        child_lot as (
            select * from materiallot where id = 'H999230306001'
        )
    select 
    (select id from child_lot limit 1) as child_lot_id, 
    (select quantity from child_lot limit 1) as child_lot_quantity,
    ml.id as parent_lot_id
    from materiallot ml
    join materiallot_ap ap on ap.uid = ml.uid
    join materiallotlink l on ml.uid = l.parentmateriallotuid
    where l.childmateriallotuid = (select uid from child_lot limit 1)
    
    union
    
    select 
    (select id from child_lot limit 1) as child_lot_id, 
    (select quantity from child_lot limit 1) as child_lot_quantity,
    ml.id as parent_lot_id
    from materiallot ml
    inner join parentlots pl on child_lot_id = parent_lot_id
)
select * from parentlots
order by parent_lot_id

核心问题排查 #

1. 递归CTE结构错误：递归CTE内部嵌套的child_lot子查询仅在初始查询阶段生效，递归部分引用的child_lot_id始终是初始固定值，无法随着迭代更新为上一轮的父批次ID，直接导致递归链条断裂。
2. 递归关联逻辑错误：递归部分的关联条件child_lot_id = parent_lot_id是用初始子批次ID匹配上一轮的父批次ID，只有当父批次ID等于初始子批次ID时才会触发，完全不符合“用父批次作为新子批次继续查询”的递归逻辑。
3. 未形成迭代输入：递归CTE的核心是用上一轮的结果作为下一轮的输入，原查询递归部分未引用parentlots的结果去查找新的父批次，而是重复使用初始的child_lot数据，自然无法触发递归。

修正方案 #

调整递归CTE结构，让每一轮迭代都基于上一轮的父批次去追溯上层父批次，示例代码如下：

WITH RECURSIVE parentlots AS (
    -- 初始步骤：获取指定批次的直接父批次
    SELECT 
        cl.id AS current_lot_id,
        cl.quantity AS current_lot_quantity,
        ml.id AS parent_lot_id,
        ml.uid AS parent_lot_uid
    FROM materiallot cl
    JOIN materiallotlink l ON cl.uid = l.childmateriallotuid
    JOIN materiallot ml ON l.parentmateriallotuid = ml.uid
    WHERE cl.id = 'H999230306001'

    UNION ALL
    -- 递归步骤：用上一轮的父批次作为当前批次，继续查找其上层父批次
    SELECT 
        pl.parent_lot_id AS current_lot_id,
        ml.quantity AS current_lot_quantity,
        ml2.id AS parent_lot_id,
        ml2.uid AS parent_lot_uid
    FROM parentlots pl
    JOIN materiallotlink l ON pl.parent_lot_uid = l.childmateriallotuid
    JOIN materiallot ml2 ON l.parentmateriallotuid = ml2.uid
    JOIN materiallot ml ON pl.parent_lot_id = ml.id
)
SELECT 
    current_lot_id AS child_lot_id,
    current_lot_quantity AS child_lot_quantity,
    parent_lot_id
FROM parentlots
ORDER BY parent_lot_id;

修正说明 #

• 将初始子批次查询移到递归CTE的初始步骤中，直接关联其直接父批次，形成递归的起点。
• 递归步骤通过parentlots的上一轮结果（parent_lot_uid）作为新的子批次UID，关联materiallotlink查找上层父批次，形成完整的递归链条。
• 使用UNION ALL替代UNION，避免不必要的去重操作，提升递归查询效率（若数据存在循环引用，可添加循环检测逻辑）。

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