Postgres删除行后插入数据仍触发唯一索引重复报错问题

问题成因 #

1. 核心原因：定时任务重叠执行+Postgres MVCC的唯一约束检查逻辑

你遇到的报错90%以上是多个定时任务实例并发执行导致的：

• 你设置的每小时执行一次的任务，存在前一次任务还没执行完（比如数据量大、数据库性能波动导致执行耗时超过1小时），下一次定时任务就触发启动的情况，两个事务会并行操作同一张表。
• 假设有两个并发事务A、B几乎同时启动：
  1. 事务A先执行删除操作，删除payload->>'_index' = 'companies'的所有行，此时事务A还未提交
  2. 事务B也执行同条件的删除操作，由于MVCC的可见性规则，事务B看不到事务A未提交的删除操作，所以它的删除操作只会删除原来的旧数据，不会处理事务A正在操作的行
  3. 事务A先执行批量插入，插入(companies, AC9860)这条数据，没有冲突，事务A提交完成
  4. 事务B开始执行批量插入，此时唯一约束检查会穿透事务快照，看到事务A已经提交的(companies, AC9860)键值，而事务B的删除操作是在事务A提交前执行的，并没有删除事务A刚插入的这条数据，因此直接触发唯一约束冲突报错。

2. 为什么拆分事务后问题缓解？

你把删除和插入拆成两个独立事务提交后，删除操作会先落地生效，后续启动的并发事务在执行删除时，就能看到前一个事务已经提交的删除结果，一定程度上减少了并发冲突的概率，但并没有根本解决问题：如果两个任务还是重叠执行，还是可能出现事务A刚提交完删除，事务B也提交完删除，然后两个事务同时执行插入，还是会触发冲突。

其他低概率成因

• 你的删除条件存在隐式转换或者匹配遗漏，比如payload->>'_index'的取值存在大小写、空格差异，导致没有完全删除目标数据；
• 批量插入的源数据在生成时出现偶发重复，你可以在插入前加一层去重逻辑验证。

解决方案 #

最彻底的方案是保证同一时间只有一个定时任务在操作这张表，你可以用Postgres自带的咨询锁实现：

-- 任务启动时先尝试获取锁，锁ID可以自定义为固定数字，比如123456
SELECT pg_try_advisory_lock(123456) AS lock_acquired;
-- 如果返回f，说明已经有任务在跑，直接退出当前任务
-- 如果返回t，再执行删除、插入操作
-- 任务执行完成后释放锁
SELECT pg_advisory_unlock(123456);

也可以在插入时加冲突处理逻辑，避免报错：

INSERT INTO source (payload) VALUES (...) 
ON CONFLICT ((payload->>'_index'), (payload->>'_id')) DO NOTHING;

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