Postgres 14中UPSERT返回旧行值及并发写入安全问题

表结构与需求 #

首先定义的表结构如下：

CREATE TABLE mytable (
  id            text PRIMARY KEY
, top           bigint  NOT NULL
, top_timestamp bigint  NOT NULL
);

核心需求：

• 执行upsert操作时，仅当新的top值大于旧值才更新现有行
• 操作完成后需返回旧的top和top_timestamp值（插入场景无旧值则返回空）
• 方案必须支持安全的并发写入

方案一：WITH子句+SELECT FOR UPDATE的实现 #

你写出的完整实现语句：

WITH old_values AS (
   SELECT top, top_timestamp FROM mytable
   WHERE id = 'some-id' FOR UPDATE
   )
, upd AS (
INSERT INTO mytable
AS mt (id, top, top_timestamp)
VALUES ('some-id', 123, 999)
ON CONFLICT (id)
DO UPDATE SET top=123, top_timestamp=999
WHERE mt.top < 123
)
SELECT top AS old_top, top_timestamp AS old_top_timestamp
FROM old_values;

并发安全性验证 #

这个方案可以安全应对并发写入，关键原因：

• SELECT ... FOR UPDATE会在查询执行时立即锁定目标行，锁会持有到整个事务提交或回滚。并发请求会被阻塞直到当前事务完成，彻底避免了并发更新导致的竞态条件。
• 整个操作是原子性的：先锁定行获取旧值快照，再执行upsert，最后返回锁定时的旧值，不会出现中间状态被其他事务篡改的情况。

方案二：RETURNING子句嵌套查询的实现 #

你提到的另一种写法：

INSERT INTO mytable
AS mt (id, top, top_timestamp)
VALUES ('some-id', 123, 999)
ON CONFLICT (id)
DO UPDATE SET top=123, top_timestamp=999
WHERE mt.top < 123
RETURNING (SELECT top FROM mytable WHERE id = 'some-id') AS last_top, 
          (SELECT top_timestamp FROM mytable WHERE id = 'some-id') AS last_top_timestamp

与方案一的核心区别 #

1. 并发安全性缺失：RETURNING子句中的嵌套查询是在UPDATE完成后执行的，虽然Postgres在upsert过程中会持有行锁，但如果其他事务在当前事务提交前修改该行（低概率但存在风险），嵌套查询会返回最新值而非本次操作的旧值。
2. 返回值不符合需求：如果是插入操作（无旧行），嵌套查询会返回新插入的值，而非预期的空值；如果是更新操作，返回的是更新后的值，不是你需要的旧值。
3. 原子性不足：嵌套查询与upsert操作并非严格绑定的原子逻辑，无法保证返回的是本次操作触发前的旧值。

方案一通过SELECT FOR UPDATE提前锁定行，确保后续upsert基于锁定的旧值执行，返回的old_values是锁定时的快照，完全匹配需求。

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