如何使用 PostgreSQL 18 RETURNING 的 OLD 和 NEW？

题干与适用场景

PostgreSQL 18 允许 RETURNING 为 INSERT、UPDATE、DELETE 和 MERGE 显式返回旧行与新行。结果来自同一个变更语句，可以避免后续查询与其他写入者竞争。对于不存在的一侧，旧值或新值可能为 NULL。

假设审计事件必须准确反映一个语句改变的值，重试需要幂等，应用不能承担变更与审计之间的第二次读取。

面试官考察点

面试官关注语句级原子性、不同操作的语义，以及事务边界和重试方案。强回答会区分 INSERT、UPDATE、DELETE、ON CONFLICT 和 MERGE，并说明审计投递与提交的关系。

普通回答写入后再查询一次。强回答把 RETURNING 当作变更输出，保留操作身份，避免事务回滚后仍发布事件。

回答前需要澄清的问题

• 审计事件必须提交后发送，还是同事务写 outbox 即可？
• 一个语句是否会修改多行？事件 ID 如何生成？
• upsert 冲突和每个 MERGE 分支中的“旧值”如何定义？
• 如何让重试不产生重复审计？
• 哪些列必须在离开数据库前脱敏？

如果下游投递必须跟随提交，应在同一事务写入 outbox，再异步发布。如果审计只用于数据库内部报表，调用方可以直接消费 RETURNING。

30 秒回答框架

“我会让变更语句显式返回操作类型、旧值、新值和稳定事件键。多行写入时，把这些结果在同一事务写入 outbox，提交后再发布。插入和删除要测试空值一侧，明确 upsert 与 MERGE 语义，脱敏敏感字段，并用事件键保证重试幂等。”

分步骤深入解答

1. 定义事件契约。 包含表标识、主键、操作、旧投影、新投影、事务或请求 ID 和幂等键。
2. 使用显式别名。 使用文档支持的 RETURNING WITH (OLD AS oldrow, NEW AS newrow) 语法，不依赖 RETURNING *。
3. 处理操作语义。 插入通常没有旧行，删除通常没有新行；upsert 和 MERGE 需要按分支给出操作类型。
4. 原子持久化。 在同一事务把返回记录写入 outbox。提交前的独立查询或外部发布可能看到最终回滚的数据。
5. 保护数据与重试。 脱敏字段，必要时哈希敏感值，并用唯一事件键避免重试重复投递。
6. 验证并发。 测试并发写入、冲突、回滚、多行语句和部分 MERGE 分支，把审计行与已提交表状态比较。

替代方案包括集中执行的触发器、全库变更捕获的逻辑解码，或表达领域语义的应用事件。当变更语句已经拥有精确行级结果时，RETURNING 最合适。

高质量示范回答

“应用更新返回明确操作、主键、旧投影、新投影和事件键。upsert 冲突标记为 update；MERGE 每个分支提供自己的操作。事务把所有返回行写入 outbox 并一次提交，worker 提交后发布，用唯一事件键安全重试。删除的新投影为空，插入的旧投影为空，敏感列在事件离开数据库前移除。”

常见错误

• 错误表现： 变更后重新查询 → 失败原因： 其他写入者可能已经改变行 → 修正方法： 消费同一语句的 RETURNING。
• 错误表现： 提交前发布 → 失败原因： 审计事件可能描述回滚数据 → 修正方法： 使用事务 outbox。
• 错误表现： 把每次 upsert 都当作插入 → 失败原因： 冲突更新有不同语义 → 修正方法： 显式返回操作类型。
• 错误表现： 无差别返回所有列 → 失败原因： 密钥或个人数据泄漏 → 修正方法： 使用允许列表投影和脱敏。

追问及应对

INSERT 的 OLD 包含什么？

通常没有旧行，因此旧侧为 NULL。事件契约应表达缺失，不应伪造默认行。

如何捕获多行 MERGE？

每个受影响行消费一条 RETURNING 结果，包含分支操作，并在同一 outbox 事务写入事件。

outbox 写入失败怎么办？

事务应失败并回滚变更。不能确认业务写入成功，却静默丢弃审计记录。

什么时候优先逻辑解码？

需要全库变更捕获，或无法修改语句时使用逻辑解码。需要领域投影和精确语句语义时优先 RETURNING。