PostgreSQL 11.1更新诊断表时如何删除冲突记录避免唯一索引冲突

PostgreSQL 11 中，同一个顶层语句的WITH子句内定义的所有可写CTE（含DELETE、UPDATE操作），不存在固定的先后执行顺序

• 官方实现逻辑：所有可写CTE并行启动执行，每个操作都基于语句启动瞬间的全表快照运行，互相看不到对方的写入修改
• 这种设计从根本上不支持“先执行DELETE释放唯一约束空位、再执行UPDATE写入新值”的串行依赖逻辑
• 哪怕把DELETE代码写在UPDATE前面，最终执行顺序、元组可见性都是不可预测的，大概率依然会触发唯一约束冲突

1. 执行顺序依赖错误：如上述规则，DELETE和UPDATE并行执行时，DELETE删除的冲突记录对UPDATE操作不可见，UPDATE依然会基于初始快照把旧疾病记录更新为新疾病编码，触发重复键冲突
2. UPDATE条件逻辑偏移：现有代码最后UPDATE的WHERE条件写为d.disease_recid = n.new_disease_recid，相当于匹配已经是新疾病编码的记录做重复修改，完全没有定位待更新的旧疾病编码记录，和业务目标偏离

要实现“冲突先删除、无冲突再更新”的类ON CONFLICT DO DELETE逻辑，必须依赖事务内的严格串行执行特性，将操作拆为顺序执行的独立语句，不能在单个顶层SQL的WITH中嵌套多个可写CTE。
操作步骤完全匹配业务规则：

• 第一步：递归查询目标患者关联的所有正向、反向关联的历史recid集合
• 第二步：先删除所有会触发唯一冲突的旧疾病记录——即同患者、同服务日期下，已经存在新疾病编码记录的旧疾病诊断
• 第三步：对剩下无冲突的旧疾病记录，批量更新为新的疾病编码
• 全程保留原唯一索引的防重复能力，不破坏数据一致性

示例实现代码：

BEGIN;
-- 定义通用入参与关联患者recid查询逻辑
WITH RECURSIVE input_param (tservice, patient_recid, old_disease, new_disease) AS (
    VALUES 
        ('2021-04-18'::timestamp, 15478::integer, 17242::integer, 17287::integer)
),
-- 顺着newrecid前向遍历所有链路关联的正确账号recid
read_forward AS (
    SELECT ip.patient_recid, p.recid, p.newrecid
    FROM input_param ip
    JOIN patients p ON p.recid = ip.patient_recid
    UNION ALL
    SELECT rf.patient_recid, p.recid, p.newrecid
    FROM patients p
    JOIN read_forward rf ON rf.newrecid = p.recid
),
-- 反向遍历所有指向当前recid的历史账号
read_backward AS (
    SELECT ip.patient_recid, p.recid, p.newrecid
    FROM input_param ip
    JOIN patients p ON p.newrecid = ip.patient_recid
    UNION ALL
    SELECT rb.patient_recid, p.recid, p.newrecid
    FROM patients p
    JOIN read_backward rb ON p.recid = rb.patient_recid
),
all_related_patients AS (
    SELECT DISTINCT patient_recid, recid 
    FROM read_forward
    UNION
    SELECT DISTINCT patient_recid, recid 
    FROM read_backward
)
-- 顺序执行第一步：删除冲突源记录
DELETE FROM dx d
USING input_param ip, all_related_patients arp
WHERE 
    d.patient_recid = arp.recid
    AND d.disease_recid = ip.old_disease
    AND d.tservice::date <= ip.tservice::date
    -- 匹配规则：同患者、同服务日期已经存在新疾病编码的旧疾病记录，直接删除
    AND EXISTS (
        SELECT 1
        FROM dx d2
        WHERE 
            d2.patient_recid = d.patient_recid
            AND d2.tservice::date = d.tservice::date
            AND d2.disease_recid = ip.new_disease
    );

-- 顺序执行第二步：更新无冲突的旧疾病记录
WITH RECURSIVE input_param (tservice, patient_recid, old_disease, new_disease) AS (
    VALUES 
        ('2021-04-18'::timestamp, 15478::integer, 17242::integer, 17287::integer)
),
read_forward AS (
    SELECT ip.patient_recid, p.recid, p.newrecid
    FROM input_param ip
    JOIN patients p ON p.recid = ip.patient_recid
    UNION ALL
    SELECT rf.patient_recid, p.recid, p.newrecid
    FROM patients p
    JOIN read_forward rf ON rf.newrecid = p.recid
),
read_backward AS (
    SELECT ip.patient_recid, p.recid, p.newrecid
    FROM input_param ip
    JOIN patients p ON p.newrecid = ip.patient_recid
    UNION ALL
    SELECT rb.patient_recid, p.recid, p.newrecid
    FROM patients p
    JOIN read_backward rb ON p.recid = rb.patient_recid
),
all_related_patients AS (
    SELECT DISTINCT patient_recid, recid 
    FROM read_forward
    UNION
    SELECT DISTINCT patient_recid, recid 
    FROM read_backward
)
UPDATE dx d
SET disease_recid = ip.new_disease
FROM input_param ip, all_related_patients arp
WHERE 
    d.patient_recid = arp.recid
    AND d.disease_recid = ip.old_disease
    AND d.tservice::date <= ip.tservice::date;

COMMIT;

• PostgreSQL 11 原生ON CONFLICT语法不支持DO DELETE动作配置，仅支持冲突时跳过或者更新冲突行，不要依赖语法技巧在ON CONFLICT子句内实现删除操作，容易出现元组可见性引发的不可预期问题
• 上述事务方案可以通过临时表缓存关联患者recid集合，避免两步重复递归查询，提升执行效率
• 原有唯一索引结构完全可以支撑冲突判断逻辑，不需要调整索引

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