UAT环境PostgreSQLONCONFLICTDOUPDATE报错问题及解决

背景

为了应对最终的上线测试，我们在UAT环境进行了一次总量达3000万的数据集成任务。这批数据来自四种不同的数据源。

免费影视、动漫、音乐、游戏、小说资源长期稳定更新！ 👉 点此立即查看 👈

然而，当任务执行到第三种数据源时，集成过程意外失败了。系统随即发来了告警邮件，其中包含的关键报错信息如下。在深入解读这个报错之前，有必要先了解一下我们当前采用的数据集成方案。

### Cause: PSQLException: ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time

建议：Ensure that no rows proposed for insertion within the same command ha ve duplicate constrained values.

; ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time

建议：Ensure that no rows proposed for insertion within the same command ha ve duplicate constrained values.; nested exception is org.postgresql.util.PSQLException: ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time

建议：Ensure that no rows proposed for insertion within the same command ha ve duplicate constrained values., 2025-06-18 14:31:12.729, 2025-06-18 15:50:03.329, 4213015, 20250618143057241, 1750227637384308, 2, 2025-06-18 17:10:06.947774, 2025-06-19 09:25:29.999212, 0, 2025-06-19 10:20:39.607914, 2025-06-19 10:36:15.499851

<== Total: 2

Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4f49b57a]​

当前集成方案 (演示)

INSERT INTO master_order (order_id, order_info, product_info, geo_info)
SELECT A.order_id,  A.order_id,  P.product_info,  G.geo_info
FROM  transaction_table  A 
LEFT JOIN product_table P ON A.product_no = P.product_no
  LEFT JOIN geo_table G ON A.country_code = G.country_code
WHERE  version_number = '001'
ON CONFLICT (order_id) DO
    UPDATE
    SET order_info = excluded.order_info,
    product_info = excluded.product_info,
    geo_info = geo_info.geo_info

这里使用的核心是 ON CONFLICT DO UPDATE 语法，也就是常说的 UPSERT 操作。它的逻辑很清晰：根据主键进行判断，如果目标表中不存在该记录则执行插入，如果已存在则执行更新。

这里有个通用建议：

• 在处理海量数据时，应优先考虑UPSERT方式。
• 尽量避免采用“先删除再插入”的策略。
• 因为后者在大数据量场景下性能堪忧，会引发大量的索引重建、数据页分裂以及存储空间碎片化等问题。

UPSERT 使用限制

1. CONFLICT(字段1，字段2) 必须为唯一主键

UPSERT语句中CONFLICT子句指定的字段，必须是目标表的主键（Primary Key）。这一点没有商量余地，即便是唯一索引（二级索引）也不行。

2. 更新的数据源主键不允许重复

这又是什么意思呢？

以本例来说，master_order表中的order_id是唯一主键。那么，这就要求我们用于插入或更新的数据源SQL（即INSERT ... SELECT中的SELECT部分），其查询结果里order_id字段的值必须是唯一的，不能出现重复。否则，PostgreSQL就会直接抛出错误，它不会主动去重或合并数据，这个保障责任落在了用户自己身上。

简单来说，这是用户必须确保的前提条件：

// 这个查询结果必须保证唯一，一个order_id只能对应一条记录
SELECT A.order_id,  A.order_id,  P.product_info,  G.geo_info
FROM  transaction_table  A 
LEFT JOIN product_table P ON A.product_no = P.product_no
  LEFT JOIN geo_table G ON A.country_code = G.country_code
WHERE  version_number = '001'

问题分析

回到我们的案例。首先检查第一个限制条件，没问题，CONFLICT指定的确实是主键。

那问题很可能出在第二个条件上。但仔细一想，系统开发文档白纸黑字写着：transaction_table表主键是order_id，product_table表主键是product_no，geo_table表主键是country_code。理论上，关联查询的结果集order_id也应该是唯一的。那为什么还会报错？当时第一反应甚至是：难道遇到了PostgreSQL的Bug？差点就去提交issue了。

最终，经过层层排查，真相浮出水面。原来，某位“勇猛”的同事移除了product_table表中product_no字段的主键约束，并且随后插入了两条具有相同产品编号的数据。正是这个改动，导致关联查询时，一个order_id关联到了多条产品记录，从而在结果集中产生了重复的order_id，触发了UPSERT的报错条件。

解决办法很直接：立即清理冗余数据，为product_table表重新建立product_no的主键索引，然后手动重启数据集成任务。危机就此解除。

话说回来，这次幸好发生在UAT环境，算是一次有价值的预警。要是生产环境，后果可就不止是“改咯”那么简单了。

总结

以上便是这次UAT环境数据集成故障的完整复盘与解析。希望这个案例能为大家提供一个具体的参考，在设计和执行类似大数据量UPSERT操作时，务必时刻牢记那两个关键限制条件，尤其是数据源唯一性的保障，往往就藏在细节之中。

• PostgreSQL中ON CONFLICT的使用及一些扩展用法
• PostgreSQL的upsert实例操作(insert on conflict do)
• Postgresql使用update语句的方法示例
• postgresql兼容MySQL on update current_timestamp问题
• 实操MySQL+PostgreSQL批量插入更新insertOrUpdate