PostgreSQL 插入新数据却主键冲突：从序列漂移到安全校准的排查实践

适用场景

本文适用于 PostgreSQL 表使用 serial、bigserial 或 identity 列生成主键，但执行正常 INSERT 时仍出现主键重复的场景。常见诱因包括数据迁移、手工补数据、COPY 导入、只恢复表数据却漏掉序列状态，或者在不同环境之间合并数据。

典型表结构如下：

CREATE TABLE public.orders (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    order_no text NOT NULL UNIQUE,
    created_at timestamptz NOT NULL DEFAULT now()
);

应用没有显式传入 id，却收到类似错误：

ERROR: duplicate key value violates unique constraint "orders_pkey"
DETAIL: Key (id)=(18427) already exists.

这类故障的核心通常不是“唯一索引坏了”，而是表中最大主键已经超过序列将要发出的值。修复的重点是确认默认值、序列归属和并发边界，再让下一次取号安全地落到现有最大值之后。

现象描述

序列漂移常有以下特征：

不传 id 的普通插入失败，显式传入一个未使用的较大 id 却能成功；
错误中的冲突值接近一批历史数据或迁移数据的主键范围；
表的 MAX(id) 大于序列当前记录的值；
重试可能连续冲突多次，直到序列“追上”表中的已有数据；
主键索引检查正常，表里也确实只有一行使用该主键。

不要用无限重试掩盖问题。每次失败的 nextval() 通常仍会消耗一个序列值，因此重试可能暂时让故障消失，但序列与数据的来源仍未治理，下次导入后还会复发。

可能原因

1. 导入时显式写入主键

COPY 或批量 INSERT 写入了 id，但显式值不会自动推进关联序列。例如导入数据的最大 ID 已经是 20000，序列仍停在 18000，后续由默认值生成的 ID 就会撞上已有记录。

2. 恢复流程漏掉序列状态

标准 pg_dump 通常会包含序列状态恢复语句，但自行拆分 DDL、数据和初始化脚本时，可能只恢复了表数据。若只挑选部分对象执行 pg_restore，也要确认关联序列是否包含在恢复范围内。

3. 手工补数据没有同步校准

应急脚本为了保留外部系统 ID，直接执行：

INSERT INTO public.orders (id, order_no)
VALUES (25001, 'MIGRATED-25001');

数据写入成功并不代表 PostgreSQL 会自动把序列推进到 25002。

4. 应用使用了错误的序列

复制表、改名或手工修改默认值后，列可能引用了另一个序列。此时即便校准了“看起来同名”的序列，真实插入路径仍然会取错号。

5. 修复期间仍有并发写入

如果一边计算 MAX(id)，另一边还有事务写入显式主键，刚校准好的序列可能再次落后。仅执行一条 setval()，却没有停写或加锁，是最常见的二次故障原因。

排查思路

1. 先确认列的真实默认值

SELECT
    column_name,
    column_default,
    is_identity,
    identity_generation
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'orders'
  AND column_name = 'id';

重点看：

column_default 是否包含 nextval(...)；
is_identity 是否为 YES；
identity 列是 ALWAYS 还是 BY DEFAULT。

GENERATED ALWAYS 默认拒绝显式 ID，导入时通常需要明确写出 OVERRIDING SYSTEM VALUE；BY DEFAULT 允许显式 ID，因此更容易在导入后出现序列未同步。

2. 不要猜序列名，查询列关联的序列

SELECT pg_get_serial_sequence('public.orders', 'id') AS sequence_name;

示例结果：

public.orders_id_seq

pg_get_serial_sequence() 会根据列关系返回序列名，适用于 serial 和关联的 identity 列。它比手工拼接 表名_id_seq 更可靠，因为序列可能改过名、位于其他 schema，或者根本没有正确关联。

如果返回 NULL，先停止修复并检查表定义。此时问题可能是列没有默认生成器，或者默认值引用的是未与列建立所有权关系的自定义序列。

3. 对比表内最大值与序列状态

SELECT MAX(id) AS max_id FROM public.orders;

SELECT last_value, is_called
FROM public.orders_id_seq;

需要同时理解两个字段：

last_value 是序列保存的最近状态；
is_called=false 表示下一次 nextval() 会直接返回 last_value；
is_called=true 表示下一次会按 increment_by 继续递增。

因此不能只看到 last_value = MAX(id) + 1 就断言安全，还要结合 is_called 和步长判断下一次实际返回值。

也可以从系统视图确认序列参数：

SELECT
    schemaname,
    sequencename,
    start_value,
    min_value,
    max_value,
    increment_by,
    cycle,
    last_value
FROM pg_sequences
WHERE schemaname = 'public'
  AND sequencename = 'orders_id_seq';

大多数业务主键使用 increment_by = 1 且不循环。如果序列步长不是 1，不能直接套用本文的 MAX(id) + 1 方案，应按实际编号规则计算下一个合法值。

4. 确认冲突确实来自主键

SELECT id, order_no, created_at
FROM public.orders
WHERE id = 18427;

如果错误约束不是 orders_pkey，而是业务唯一键，例如 orders_order_no_key，修序列不会解决问题。先以错误里的约束名和字段为准，避免误判。

5. 查清谁写入了显式 ID

重点检查：

最近的数据迁移和回滚脚本；
COPY public.orders (id, ...) 的字段列表；
ORM 中是否存在手工赋值主键；
灰度期间是否有旧服务仍按历史规则生成 ID；
数据库审计日志或发布记录中是否出现批量导入。

只修当前序列而不找写入来源，等于把故障推迟到下一次迁移。

安全修复方案

以下示例假设主键从 1 开始、步长为 1，目标是让下一次 nextval() 返回 MAX(id) + 1。

方案一：维护窗口内停写后校准

这是风险最低的做法：先停止应用写入或摘除写流量，确认没有迁移任务运行，再执行：

BEGIN;

LOCK TABLE public.orders IN ACCESS EXCLUSIVE MODE;

SELECT setval(
    pg_get_serial_sequence('public.orders', 'id'),
    COALESCE(MAX(id), 0) + 1,
    false
)
FROM public.orders;

COMMIT;

这里第三个参数使用 false，表示下一次 nextval() 直接返回设置值。因此：

非空表最大 ID 为 25001 时，设置值为 25002，下一次生成 25002；
空表时设置值为 1，下一次生成 1。

ACCESS EXCLUSIVE 会阻止其他事务同时读写该表，必须设置合理的锁等待边界，避免修复会话长时间挂住：

SET lock_timeout = '5s';
SET statement_timeout = '30s';

如果拿不到锁，应退出并重新安排停写，而不是无限等待。

需要特别注意：序列操作不是普通表数据更新。setval() 的效果不会因为后续事务回滚而自动撤销，所以要先核对对象和目标值，把它作为修复流程中最后的变更动作，不能把 ROLLBACK 当成可靠回滚方案。

方案二：明确知道最大值时直接设置

若已经在冻结写入后确认 MAX(id) = 25001，可以执行：

SELECT setval('public.orders_id_seq', 25002, false);

这种写法简单，但容易把环境中的旧值复制到生产。更推荐使用 pg_get_serial_sequence() 动态确认关联关系，并在同一维护窗口重新读取 MAX(id)。

不推荐：把序列设置为 MAX(id) 并忽略第三个参数

下面的写法在非空表、默认 is_called=true 且步长为 1 时，下一次通常会返回 MAX(id) + 1：

SELECT setval('public.orders_id_seq', MAX(id))
FROM public.orders;

但它对空表返回 NULL，且读者很容易忽略 is_called 语义。显式计算“下一个值”并使用 false，意图更清楚，也更方便审查。

修复后验证

1. 在事务中验证默认值路径

维护窗口内可插入一条带明显标识的验证记录，并立即回滚表数据：

BEGIN;

INSERT INTO public.orders (order_no)
VALUES ('SEQ-CHECK-20260919')
RETURNING id;

ROLLBACK;

确认返回 ID 大于修复前的 MAX(id)，并且不再出现主键冲突。

注意：回滚会撤销验证行，但不会退回已经取出的序列值。序列出现空洞是正常现象，不应为了“号码连续”再次回拨序列。

2. 恢复流量后观察错误率

至少监控以下指标：

orders_pkey 唯一约束冲突次数；
订单创建接口的 5xx 和数据库错误率；
序列剩余空间，尤其是仍使用 32 位 integer 的老表；
数据导入任务是否继续显式写入主键。

3. 再次检查实际差距

SELECT
    (SELECT MAX(id) FROM public.orders) AS max_id,
    last_value,
    is_called
FROM public.orders_id_seq;

验证插入后，序列状态应不再落后于表内最大主键。由于并发事务会预取或消耗序列值，last_value 大于 MAX(id) 并不一定异常。

数据迁移时的正确做法

导入完成后在同一变更单中校准

如果迁移必须保留旧 ID，应把序列校准写进迁移流程，而不是依赖人工记忆：

COPY public.orders (id, order_no, created_at)
FROM STDIN WITH (FORMAT csv);

-- COPY 数据结束后执行
SELECT setval(
    pg_get_serial_sequence('public.orders', 'id'),
    COALESCE(MAX(id), 0) + 1,
    false
)
FROM public.orders;

真实生产中仍需配合停写或表锁，保证计算最大值到校准完成之间没有并发显式主键写入。

不需要保留旧主键时让数据库生成

迁移数据如果没有外键或外部引用要求，优先省略 identity 列：

INSERT INTO public.orders (order_no, created_at)
SELECT order_no, created_at
FROM staging.orders;

让目标库统一生成 ID，可以从根源上减少序列漂移。但要先评估外键、消息引用、对账文件和外部系统是否依赖旧 ID。

使用标准备份恢复链路

完整迁移优先使用 pg_dump 与 pg_restore，不要只复制业务表。恢复后仍应把“表最大值与序列下一值一致性”纳入验收，特别是选择性恢复、跨版本迁移和数据合并场景。

预防措施

迁移模板固定包含序列校准。 只要脚本显式写入自增主键，就必须在同一发布步骤中校准并验证。
限制应用写主键。 普通业务账号只走默认生成路径，保留显式 ID 的能力给受控迁移账号和审批流程。
建立巡检。 定期比较关键表 MAX(id) 与关联序列状态，发现序列落后立即告警，但不要自动回拨或自动修改生产序列。
避免追求连续编号。 事务回滚、缓存和并发都会产生序列空洞；主键只需唯一、稳定，不应承担连续票据号职责。
检查整数容量。 老表若使用 serial 对应的 32 位 integer，应提前评估迁移到 bigint/bigserial，不要等接近上限才处理。
把并发边界写进操作手册。 每次修复明确停写方式、锁超时、验证 SQL、失败退出条件和负责人。

总结

PostgreSQL “正常插入却主键冲突”往往是表数据与序列状态分离造成的。可靠的处理顺序是：确认报错约束，查清列真正关联的序列，对比 MAX(id)、last_value 与 is_called，在停写或严格加锁的窗口中校准，再通过默认值插入路径验证。

setval() 只需要一条 SQL，但真正决定修复是否安全的是对象识别、并发控制和迁移流程治理。把序列校准纳入每次显式主键导入的标准步骤，才能避免下一次发布后再次出现同类冲突。