PostgreSQL插入CTE后执行删除CTE，仍存在目标ID数据问题排查

现有product表结构及初始数据如下：

CREATE TABLE product
(
    id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    from_id bigint NOT NULL,
    to_id   bigint NOT NULL,
    comments text NOT NULL,
    data    jsonb  NOT NULL
);

CREATE UNIQUE INDEX product_unique_idx ON product(from_id, to_id, comments);

初始插入数据：

insert into product(from_id, to_id, comments, data) values
(1, 2, 'bla', '{}'),
(2, 3, 'bla', '{}'),
(1, 3, 'bla', '{}'),
(3, 2, 'bla', '{}'),
(2, 1, 'bla', '{}'),
(3, 1, 'bla', '{}');

需求：将所有from_id或to_id为1、2的记录替换为ID 3，同时删除from_id等于to_id的记录。为避免唯一索引冲突，采用先插入新记录再删除旧记录的CTE语句：

with
   insert_stmt_to_id AS (
      insert into product
         (from_id, to_id, comments, data)
         (select from_id,3,comments,data from product
           where to_id in (1,2))
         ON CONFLICT (from_id, to_id, comments) DO NOTHING),
   insert_stmt_from_id AS (
      insert into product
         (from_id, to_id, comments, data)
         (select 3,to_id,comments,data from product
           where from_id in (1,2))
         ON CONFLICT (from_id, to_id, comments) DO NOTHING),
   delete_stmt AS (DELETE from product where to_id in (1,2) or from_id in (1,2) RETURNING *)
select * from delete_stmt 

执行后查询product表，发现仍存在包含1、2的from_id或to_id记录，请问原因是什么？

1. PostgreSQL CTE的快照隔离特性 #

PostgreSQL中所有CTE子句共享同一个查询快照，也就是执行CTE时的表数据状态。insert_stmt_to_id和insert_stmt_from_id插入的新记录，不会被后续的delete_stmt看到——因为delete_stmt只能识别执行CTE时已存在的原始记录，无法感知到后续插入的新数据。

2. 插入逻辑不符合需求 #

你的插入逻辑只是单独替换to_id或from_id，没有同时处理两个字段：

• 比如原记录(1,2,'bla','{}')，会被拆分为(1,3,...)和(3,2,...)两条新记录，这两条记录的from_id或to_id依然包含1或2。
• 这些新插入的记录不在delete_stmt的删除范围内，最终会留在表中，导致查询结果仍有1、2的存在。

要实现需求，需要先生成同时替换from_id和to_id中1、2的新记录，过滤掉替换后from_id = to_id的情况，再插入新记录，最后删除所有原始的包含1、2的记录：

WITH new_records AS (
    -- 生成替换后的新记录，直接过滤掉from_id与to_id相等的情况
    SELECT
        CASE WHEN from_id IN (1,2) THEN 3 ELSE from_id END AS from_id,
        CASE WHEN to_id IN (1,2) THEN 3 ELSE to_id END AS to_id,
        comments,
        data
    FROM product
    WHERE NOT (
        CASE WHEN from_id IN (1,2) THEN 3 ELSE from_id END =
        CASE WHEN to_id IN (1,2) THEN 3 ELSE to_id END
    )
),
insert_new AS (
    -- 插入新记录，唯一索引冲突时忽略
    INSERT INTO product (from_id, to_id, comments, data)
    SELECT * FROM new_records
    ON CONFLICT (from_id, to_id, comments) DO NOTHING
),
delete_old AS (
    -- 删除所有原始的包含1、2的记录
    DELETE FROM product
    WHERE from_id IN (1,2) OR to_id IN (1,2)
    RETURNING *
)
SELECT * FROM delete_old;

针对你的初始数据，所有记录替换后都会变成(3,3,...)，会被过滤不插入，最终product表会被清空，符合需求。

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