PostgreSQL导入后主键重复:先查自增序列是否落后

适用范围:PostgreSQL使用serial或identity列的表;应用自己生成ID的项目不直接套用。

历史数据导入成功,新增一行却报23505,不一定要删重复记录。若导入时显式填写了ID,自增序列可能仍在较小的位置。但23505也可能来自邮箱等其他唯一键,必须先看报错约束名。

确认重复的是哪个字段

记录duplicate key错误中的约束名与键名,核对表结构。只有确认应用省略ID、由关联序列生成,而且冲突发生在这个ID上,才继续排查序列。

以 public.orders.id 为例,只读查询:

SELECT pg_get_serial_sequence('public.orders', 'id') AS seq_name;
SELECT MAX(id) AS max_id FROM public.orders;

NULL结果说明没找到此方式关联的序列,不能猜一个名字继续修改。再用返回的真实序列名称查看状态:

SELECT last_value, is_called FROM public.orders_id_seq;

不要用nextval来“看看下一个值”,它会推进序列。表很大时,MAX查询也应先评估索引与负载。[1][2]

正确理解last_value

序列可能缓存号段,不能只用last_value与MAX(id)比较就自动下结论。还要确认increment、起始值、最小最大值、是否循环及是否有连接预取了号段。

如果序列已经领先,不要为了“消除断号”调低。事务回滚、失败插入等都可能消耗编号,编号不连续不等于丢了记录。[1]

只有满足条件才调整

以下方法只适用于非空表、正向步长为1、没有循环、已确认序列落后、所有写入与取号方都暂停的简单情况。先备份并在隔离副本演练;停止写入进程和连接池,处理已有序列缓存,复查MAX和序列状态。

随后由有权限的维护者,使用真实序列名和当时已核验的最大ID执行一次setval,将第三参数设为true。示意如下,12345不是生产值:

SELECT setval('public.orders_id_seq'::regclass, 12345, true);

这表示下一次正常取号从其后推进。空表、负步长、共享序列或分布式取号不能照搬。setval的效果不能依赖普通事务ROLLBACK撤销,应记录修改前状态并独立设计回退。[1]

重新连接并验收

恢复应用前确保旧取号缓存不再使用。用虚构测试记录验证自动生成的ID不冲突,再检查真实业务写入。若冲突仍指向其他唯一键,转向应用逻辑;不能不断增加序列值掩盖重复请求。

继续排查

PostgreSQL备份与恢复验证教程;pg_restore不支持备份版本怎么办。

技术依据:[1] PostgreSQL序列函数及非事务性;[2] PostgreSQL pg_get_serial_sequence。适用版本见本文说明。