PostgreSQL UPDATE RETURNING返回旧值的原因、解决及性能疑问

这是PostgreSQL的语句级快照隔离规则导致的：
整个UPDATE语句从启动到执行完成，会统一使用同一个数据快照，所有在语句内部嵌套的子查询、聚合统计，都只能看到语句开始前的旧数据，完全感知不到当前UPDATE操作自身做的修改。
当前写法里，RETURNING内部嵌套的统计子查询，就是基于这个旧快照运行的，所以哪怕刚把p_id='c'的行改成了completed=true，统计的时候还是用的更新前的旧结果。

可以通过CTE（公共表表达式）把本次更新的结果先暂存下来，再结合原表的旧快照数据做计算，就能得到正确的实时统计值，示例写法：

WITH updated_rows AS (
  -- 先执行更新，拿到本次改动的所有行的p_id、j_id、新completed值
  UPDATE progress_status 
  SET completed = true  
  WHERE p_id = 'c'
  RETURNING p_id, j_id, completed
)
SELECT to_json(stat) AS status
FROM (
  SELECT
    -- 总条数不受本次update影响（没有增删行），直接用旧快照统计即可
    (SELECT count(*) FROM progress_status WHERE j_id = 'b') AS total,
    -- 已完成数 = 原表中除本次更新行之外的已完成数 + 本次更新行的已完成数
    (
      SELECT count(*) 
      FROM progress_status 
      WHERE j_id = 'b' 
        AND p_id NOT IN (SELECT p_id FROM updated_rows) -- 排除本次被更新的行，用旧快照的旧值统计
        AND completed = true
    ) + (
      SELECT count(*) 
      FROM updated_rows 
      WHERE j_id = 'b' AND completed = true -- 用本次更新的新值统计
    ) AS completed
) stat

如果更新操作涉及批量修改、或者伴随增删行，只需要对应调整total的计算逻辑即可，核心思路是把本次更新产生的新数据和旧快照中未被改动的数据分开统计后合并，绕开语句快照看不到自身修改的限制。

将更新与统计查询合并在同一个语句中执行，通常性能优于拆成两条单独查询，原因如下：

• 减少了应用和数据库之间的两次网络往返开销
• 单语句运行天然处于同一个事务中，能避免两次查询之间被其他会话插入/修改数据、导致统计结果和更新后的实际状态不一致的问题
  如果统计表数据量极大、关联逻辑非常复杂，可能会有极微小的额外开销，但绝大多数业务场景下，这种合并写法的收益远大于成本，还能保证统计一致性。

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