WITH子句中的数据修改语句疑似执行两次的问题咨询

为什么会出现这个问题？ #

这不是数据库Bug，是PostgreSQL（从语法特征判断你使用的是该数据库）对可写CTE的执行策略导致的：默认情况下，可写CTE不会被物化，当主查询（这里的UPDATE）用IN (SELECT rowid FROM i)引用它时，数据库可能会把CTE内联到主查询中，导致INSERT语句被执行两次。第一次执行时插入了所有符合条件的发票，第二次执行时noninsertedmydatainvoices已经没有可插入的记录，CTE返回空结果，自然UPDATE就不会修改任何行的ref字段。

正确的实现方式 #

有几种可靠的写法可以解决这个问题：

方法1：用FROM子句关联CTE结果

将UPDATE的条件从IN子查询改为直接关联CTE返回的行，这样数据库会正确复用INSERT的结果，避免重复执行：

WITH i AS (
    INSERT INTO llx_facture_fourn (ref_supplier)
    SELECT seriesaa FROM noninsertedmydatainvoices
    RETURNING rowid
)
UPDATE llx_facture_fourn f
SET ref = concat('(MYDATA ', f.rowid, ')')
FROM i
WHERE f.rowid = i.rowid;

方法2：强制物化CTE（PostgreSQL 12+支持）

在CTE定义后加上MATERIALIZED关键字，强制数据库只执行一次INSERT并保存结果，供后续UPDATE使用：

WITH i AS MATERIALIZED (
    INSERT INTO llx_facture_fourn (ref_supplier)
    SELECT seriesaa FROM noninsertedmydatainvoices
    RETURNING rowid
)
UPDATE llx_facture_fourn f
SET ref = concat('(MYDATA ', f.rowid, ')')
WHERE f.rowid IN (SELECT rowid FROM i);

方法3：使用临时表存储插入结果

如果你的PostgreSQL版本低于12，或者更倾向于直观的写法，可以用临时表保存INSERT返回的rowid，再执行UPDATE：

-- 保存插入的rowid到临时表
CREATE TEMP TABLE temp_inserted AS
INSERT INTO llx_facture_fourn (ref_supplier)
SELECT seriesaa FROM noninsertedmydatainvoices
RETURNING rowid;

-- 更新刚插入的行
UPDATE llx_facture_fourn
SET ref = concat('(MYDATA ', rowid, ')')
WHERE rowid IN (SELECT rowid FROM temp_inserted);

-- 临时表会在会话结束后自动删除，也可以手动删除
DROP TABLE temp_inserted;

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