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。适用版本见本文说明。