Postgres中引用CTE内UPDATE结果时的异常查询行为

在GCP CloudSQL的Postgres 14版本、READ COMMITTED隔离级别下，执行包含UPDATE和DELETE的CTE查询时，会出现DELETE语句无法识别CTE内UPDATE对表数据修改的问题。

问题场景 #

原业务SQL：

WITH deleted_files AS (
  SELECT name
  FROM files
  WHERE id = $1
), updated_links AS (
  UPDATE links l
    SET object = JSONB_SET(object, 
                           '{files}', 
                           (SELECT jsonb_agg(elem)
                            FROM files nested,
                                 jsonb_array_elements(object -> 'files') elem
                            WHERE nested.id = l.id
                            AND elem ->> 'name' != (SELECT name FROM deleted_files)),
                            true
                  ) 
   RETURNING id, object
)
DELETE FROM links 
WHERE id IN (SELECT id 
             FROM updated_links 
             WHERE object IS NULL);

简化测试SQL同样存在问题：

WITH deleted_files AS (
SELECT name FROM files
WHERE id = $1
), updated_links AS (
UPDATE links SET object = NULL RETURNING *
)
DELETE FROM links WHERE object IS NULL;

现象：执行后仍存在object为NULL的行，但将DELETE替换为SELECT id FROM updated_links WHERE object is NULL能得到正确的待删除ID列表。

原因分析 #

这是Postgres在READ COMMITTED隔离级别下的快照机制导致的：

• 整个CTE查询作为单个语句，其快照在语句启动时生成。主DELETE语句扫描links表时，使用的是这个初始快照，无法看到CTE内UPDATE操作后续修改的数据。
• 而SELECT id FROM updated_links能得到正确结果，是因为RETURNING子句直接返回UPDATE操作产生的实时修改结果，不受初始快照限制。

解决方案 #

不要直接通过表中的字段（如object IS NULL）过滤待删除行，而是利用CTE中UPDATE返回的RETURNING结果集里的ID来定位待删除行。

针对简化测试SQL，修改后的正确写法：

WITH deleted_files AS (
SELECT name FROM files WHERE id = $1
), updated_links AS (
UPDATE links SET object = NULL RETURNING id, object
)
DELETE FROM links 
WHERE id IN (SELECT id FROM updated_links WHERE object IS NULL);

针对原业务SQL，你的写法逻辑上是正确的，如果仍有问题，建议检查：

• links表的id是否为主键或唯一键，确保ID能唯一定位行
• JSONB_SET的结果是否确实在预期场景下返回NULL，可单独测试该函数的输出

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