如何保证 PostgreSQL UPSERT 操作与 RETURNING 的结果一致？

PostgreSQL UPSERT 操作和 RETURNING 子句：处理冲突

PostgreSQL 的 INSERT ... ON CONFLICT 提供 upsert 功能，将插入和更新结合起来。 但是，将 DO NOTHING 与 RETURNING 子句一起使用可能会导致并发场景中的结果不完整。

并发问题 1：缺少返回行

如果另一个事务在 INSERT ... ON CONFLICT 完成之前修改了目标行，则更新插入可能无法检测到冲突，从而导致 RETURNING 输出中丢失行。

解决方案：

有几种方法可以缓解这个问题：

• 行计数验证： 将输入行计数与 RETURNING 计数进行比较。 差异表示缺少行，提示重新执行语句。
• 强制覆盖：单独的CTE（公共表表达式）可以插入任何丢失的行。 虽然有效，但此方法存在因多个重叠事务而导致死锁的风险。

并发问题2：行锁

对于需要行锁的事务，请使用 ON CONFLICT DO UPDATE 和 WHERE FALSE。这会锁定行而不改变它们。 将此与 SELECT ... FOR UPDATE 结合使用以实现额外锁定。

数据类型处理和稳健性转换

现有的解决方案不足以应对所有并发场景。 更全面的方法包括：

低并发：

• 使用带有 INSERT 和 SELECT UNION 的 CTE 来区分插入的行和选定的行。
• 使用 CTE 定义输入数据类型，将它们与目标表架构对齐。

高并发：

• 使用低并发方法。
• 包含 CTE，以便在需要时强制插入缺失的行。
• 锁定INSERT ... ON CONFLICT CTE 中的相关行以管理并发。

避免死锁：

一致的插入顺序可以最大限度地减少死锁的发生。

数据类型管理：

• 利用现有关系自动确定输入数据类型。
• 省略列名称以进行自动列映射。

以上是如何保证 PostgreSQL UPSERT 操作与 RETURNING 的结果一致？的详细内容。更多信息请关注PHP中文网其他相关文章！