PostgreSQL sqlx执行upsert偶发违反唯一约束重复键报错问题

问题根因 #

这个偶发报错是两个原因共同导致的：

1. ON CONFLICT 未覆盖所有唯一约束
   你在Upsert语句里的ON CONFLICT仅指定了主键(user_id, location, group_id)作为冲突判断目标，但表中还存在另一个表达式唯一索引user_unique_connection。PostgreSQL的ON CONFLICT逻辑只会捕获你显式指定的约束的冲突，其余未指定的约束触发冲突时，不会走DO UPDATE逻辑，会直接抛出错误。
2. 并发插入的极端时序问题
   当两个参数完全相同的请求间隔仅微秒级同时到达时：

• 两个事务先同时检测主键冲突，此时双方都未提交插入操作，在默认的读已提交隔离级别下互相看不到对方的未提交数据，都判定主键无冲突，进入插入流程
• 先执行完插入的事务提交成功
• 后执行的事务在正式写入前，除了检测到主键冲突（本该触发ON CONFLICT更新逻辑），还会触发user_unique_connection的冲突校验，由于你没有在ON CONFLICT中指定该索引作为冲突目标，PostgreSQL直接抛出唯一约束冲突错误。

本地无法复现是因为本地测试的并发请求很难刚好卡中这个极窄的时序窗口，只有生产环境的高并发场景下才会偶发触发。

修复方案 #

两个方案二选一即可：

• 如果user_unique_connection是冗余索引：因为主键已经保证了user_id、group_id、location三个字段的组合唯一性，该索引的冲突在非并发场景下永远不会触发，直接删除该索引即可解决问题。
• 如果需要保留该索引：将ON CONFLICT的冲突目标修改为完全匹配user_unique_connection的定义，修改后的插入语句如下：

insert into user_conn (
    user_id, location, group_id
) values (
    $1, $2, $3
)
on conflict (location, user_id, group_id, coalesce(disconnected_at, '1970-01-01')) 
do update set disconnected_at=null, unlinked_at=null, connected_at=now()
returning *

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