为什么 `RETURNING` 子句在 **PostgreSQL** 中在 **UPSERT** 冲突解决期间可能返回意外的生成列值，什么机制可以正确引用拟议的插入值？

回答

问题的历史

这种模糊性出现在 PostgreSQL 9.5 中引入的本地 UPSERT 功能，通过 ON CONFLICT 子句。在此版本之前，开发人员使用复杂的 PL/pgSQL 循环或容易出错的应用程序逻辑实现幂等插入。RETURNING 子句一直是提取 UUID 或序列 ID 的重要工具，但它与 UPSERT 的双路径执行模型的相互作用——该语句可能导致 INSERT 或 UPDATE——造成了微妙的语义差距，甚至让高级工程师也对实际返回哪一行版本感到困惑。

问题

当 INSERT ... ON CONFLICT ... DO UPDATE 语句遇到唯一冲突时，它会转而更新现有行。RETURNING 子句随后引用该行的最终持久化状态。然而，如果你的应用逻辑依赖于被生成的插入值——如 created_at 时间戳、默认表达式或应用计算值——则该语句会返回预先存在的行的过时数据。这种静默替换会导致缓存失去同步、审计跟踪损坏以及微妙的竞争条件，导致下游系统接收时间上不一致的元数据。

解决方案

EXCLUDED 虚拟表提供了一个窗口，用于查看触发冲突的拟议插入值。通过在 RETURNING 子句或 UPDATE 设置列表中明确引用 EXCLUDED.column_name，确保可以访问到意图的新数据，无论采取了哪种执行路径。

INSERT INTO user_sessions 
    (user_id, login_count, last_seen, session_token)
VALUES 
    (1001, 1, NOW(), gen_random_uuid())
ON CONFLICT (user_id) 
DO UPDATE SET 
    login_count = user_sessions.login_count + 1,
    last_seen = EXCLUDED.last_seen,
    session_token = EXCLUDED.session_token
RETURNING 
    session_id,
    user_id,
    CASE WHEN xmax = 0 THEN 'inserted' ELSE 'updated' END AS operation_type,
    session_token,
    EXCLUDED.last_seen AS intended_timestamp;

在这个模式中，EXCLUDED.last_seen 和 EXCLUDED.session_token 确保应用程序接收到来自尝试插入的新值，即使数据库执行了更新。

生活中的情况

并发积分累积

一个处理高频微交易的金融科技平台遇到了幻影奖励计算。当两个并行请求试图同时向同一用户账户授予积分时，PostgreSQL 数据库正确地保持了原子性，但 Redis 缓存层从 RETURNING 子句接收了过时的 updated_at 时间戳。这导致缓存拒绝合法的积分增加，从而导致收入流失和客户对缺失奖励的投诉。

解决方案 A: Redis 中的分布式锁

工程团队最初建议在执行数据库事务之前先在 Redis 中获取分布式锁。这种方法会将冲突操作串行化，并确保顺序一致性。然而，它引入了单点故障，每个请求增加了 12-18 毫秒的网络延迟，并在事务在获取锁后中止时创建了复杂的死锁场景。锁管理的操作开销和潜在的级联故障使这一架构在规模上不可持续。

解决方案 B: 应用程序侧的读-修改-写

另一个建议涉及首先使用 SELECT 查询记录存在性，然后在应用程序代码中决定 INSERT 或 UPDATE。虽然从概念上讲简单，但在并发负载下，这种模式因 READ COMMITTED 隔离允许在检查和写入之间的不重读而崩溃。实现 SERIALIZABLE 隔离以防止竞争条件将导致过多的序列化失败和重试风暴，而显式表锁则会将吞吐量限制到不可接受的水平。

解决方案 C: 正确使用 EXCLUDED

选择的方案重构查询以利用 EXCLUDED 获取 RETURNING 子句中的所有可变值。通过引用 EXCLUDED.points 和 EXCLUDED.calculated_at，应用程序始终能够从插入尝试中获得意图元数据，无论操作是导致新行还是更新。

选择的解决方案和结果

团队在奖励微服务中实施了解决方案 C。这消除了缓存不一致问题，且不增加网络跳数或妥协隔离级别。积分累积的准确性提高到 99.99%，数据库 CPU 使用率因查询往返减少而下降了 35%，系统成功应对了黑色星期五期间的流量激增，无需人工干预。

候选人常常遗漏的内容

PostgreSQL 如何确定在一个表上存在多个索引时，使用哪个唯一索引进行冲突检测？

PostgreSQL 需要在 ON CONFLICT 子句中显式指定仲裁者。当你写 ON CONFLICT (column_list) 时，规划器选择其索引列恰好与提供的列表按顺序匹配的唯一索引。如果存在多个相同列的索引，则选择最早创建的一个。对于带 WHERE 子句的部分唯一索引或表达式索引，必须使用 ON CONFLICT ON CONSTRAINT constraint_name 语法；否则，数据库引擎会抛出错误，表示无法推断仲裁索引。候选人常常假设数据库会自动选择“最具选择性”索引，或忽视功能索引需要显式的约束命名。

为什么当多个事务在 READ COMMITTED 隔离下对相同键冲突时，UPSERT 语句可能静默丢失更新？

这是由于 UPDATE 子句的重新评估行为。当事务 A 插入一行并提交时，事务 B——等待行锁——对新可见的行重新执行其 UPDATE 谓词。如果 UPDATE 逻辑使用绝对赋值（例如，SET balance = 100）而不是相对算术引用 EXCLUDED（例如，SET balance = account.balance + EXCLUDED.amount），则事务 B 会完全覆盖事务 A 的更改。许多候选人错误地假设 UPSERT 意味着自动合并或累积，未能认识到 DO UPDATE 子句需要显式处理 EXCLUDED 值，以实现幂等累积语义。

在判断 UPSERT 是否执行插入时，检查 xmax = 0 与 xmax IS NULL 之间的精确区别是什么，为什么这一区别对 HOT 更新很重要？

在 PostgreSQL 中，xmax 存储删除或更新事务的事务 ID。对于新插入的行，xmax 初始化为 0，绝不会为 NULL。候选人常常错误检查 xmax IS NULL 以识别插入，这将始终返回 false。xmax = 0 检查可靠地识别插入与更新。这一区别在 HOT（仅堆元组）更新中变得至关重要，PostgreSQL 通过在同一页面上就地更新行而不修改索引来优化性能。虽然 xmax 正确地指示该行被触及，但理解 0 表示“没有上一个更新者”，而非零则表示版本化，可以防止在计算行生成数字或实现必须区分生命与变更的自定义变更数据捕获逻辑时发生逻辑错误。