PostgreSQL使用SEQUENCE生成主键触发duplicate key violation报错求解

错误原因分析 #

首先明确：PostgreSQL的序列本身是线程安全的，正常使用下绝对不会返回重复值，出现该问题99%的概率是以下两种场景导致：

• 序列当前值落后于表中已存在的最大主键值
  最常见的触发场景是：手动向transaction_item表导入过数据、或者有历史写入操作显式指定了transaction_id的值，操作完成后没有同步更新transaction_id_seq的当前值。序列正常自增到已被占用的id区间时，就会触发主键冲突。
• 存在未被梳理的显式写入transaction_id的逻辑
  你当前梳理的插入SQL没有指定transaction_id，但不代表没有其他隐藏的写入逻辑：比如定时脚本、临时运维操作、其他关联服务的写入接口，可能直接指定了transaction_id的值插入，抢占了序列生成的id。

排查&修复步骤 #

1. 优先验证是否为序列值落后问题
   执行以下两条SQL查询对比：

-- 查询表中现存最大主键值
SELECT MAX(transaction_id) FROM transaction_item;
-- 查询序列当前的最新值
SELECT last_value FROM transaction_id_seq;

如果序列的last_value小于等于表中的最大transaction_id，直接执行以下语句重置序列即可解决问题：

SELECT SETVAL('transaction_id_seq', (SELECT MAX(transaction_id) FROM transaction_item) + 1);

2. 排查隐藏的显式写入逻辑
   如果序列值没有落后，开启数据库的SQL审计日志，过滤所有对transaction_item表的INSERT操作，找到携带transaction_id字段的写入语句，对应修改逻辑：

• 非必要场景下删除显式指定transaction_id的代码，统一使用序列默认值生成
• 确实需要手动指定主键的场景（比如数据迁移、批量导入），操作完成后必须执行上述序列重置语句

预防方案 #

• 给业务使用的数据库账号配置权限，禁止对transaction_item.transaction_id字段的写入权限，仅保留默认值生成的权限
• 建立数据导入操作的规范，所有手动写入该表的操作完成后必须校验序列值，避免遗留冲突隐患

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