# Postgres 的 ID 为什么会跳号，以及如何修复导入数据后的「duplicate key」错误 PostgreSQL 的 serial 列和 identity 列中的空洞从何而来（回滚、ON CONFLICT、崩溃、CACHE），为什么导入数据后插入会报 duplicate key value violates unique constraint，一条重置所有序列的查询，integer ID 用完时会发生什么，以及无空洞的发票编号。每条语句都在 Postgres 17.11 上复现过。 **关于 Postgres ID，有两个问题被反复问起。「为什么我的 ID 从 5 跳到了 34？」以及「为什么我刚导入完数据，每次插入都失败，报 `duplicate key value violates unique constraint \"..._pkey\"`？」两者都源于同一个对象：`serial` 列或 identity 列背后的序列。序列发放号码时并不关心你是否用上了它们，所以出现空洞是正常的。而且它只知道自己发出去的号码，所以你用显式 ID 加载的行会把它甩在后面。本文逐一复现每种空洞，先为一张表、再为所有表一次性修复导入错误，展示 `integer` ID 用完时会发生什么，并为你可能真正需要无空洞编号的那一个场景构建方案。下文的每条语句和每段输出都来自本地的 PostgreSQL 17.11，也就是 Relyt One 运行的大版本。** ## 简短版 | 症状 | 原因 | 修复 | | ----------------------------------------------------- | --------------------------- | -------------------------- | | ID 跳过了几个号码 | 被回滚或失败的插入、`ON CONFLICT` | 无需处理。序列在设计上就不保证无空洞 | | 崩溃后 ID 最多跳 32 个（使用默认缓存时） | 序列会在 WAL 中提前记录值 | 无需处理。不要给 ID 值赋予含义 | | 不同会话的 ID 交错出现，或按缓存大小跳号 | `CACHE` 大于 1 | 如果顺序重要，使用默认的 `CACHE 1` | | 导入后报 `duplicate key value violates unique constraint` | 行是带着显式 ID 加载的；序列仍从 1 开始 | 用 `setval` 把序列推过 `max(id)` | | `nextval: reached maximum value of sequence` | `serial` 列是 32 位的 `integer` | 把序列**和**列都改为 `bigint` | | 你需要没有空洞的发票编号 | 序列做不到 | 在同一事务中更新的计数器行 | ## 空洞从何而来 PostgreSQL 文档对此说得很直白：`nextval` 返回的值 “is not reclaimed for re-use if the calling transaction later aborts”（如果调用它的事务随后中止，该值不会被回收再用），并且 “sequence objects cannot be used to obtain 'gapless' sequences.”（序列对象不能用来获得「无空洞」的序列。）这是一个有意的取舍。如果被回滚的事务可以把号码还回去，那么其他每个申请号码的事务都得等着看它是否归还。下面看看空洞在实际中是如何出现的。 ### 回滚、失败的插入与 ON CONFLICT ```sql CREATE TABLE orders ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, email text NOT NULL UNIQUE ); INSERT INTO orders (email) VALUES ('a@example.com'); -- 取得 1 BEGIN; INSERT INTO orders (email) VALUES ('b@example.com'); -- 取得 2 ROLLBACK; -- 2 没了 INSERT INTO orders (email) VALUES ('c@example.com'); -- 取得 3 INSERT INTO orders (email) VALUES ('a@example.com') ON CONFLICT (email) DO NOTHING; -- 消耗掉 4 INSERT INTO orders (email) VALUES ('a@example.com'); -- 消耗掉 5，失败 INSERT INTO orders (email) VALUES ('d@example.com'); -- 取得 6 SELECT id, email FROM orders ORDER BY id; ``` ```text id | email ----+--------------- 1 | a@example.com 3 | c@example.com 6 | d@example.com (3 rows) ``` 三行，三个空洞。被回滚的插入拿走了 2。因违反唯一约束 `email` 而失败的插入在失败之前拿走了 5：Postgres 会先构建整行，包括默认的 ID，然后才检查约束。`ON CONFLICT DO NOTHING` 出于同样的原因拿走了 4，却什么也没插入。文档明确写道：`INSERT ... ON CONFLICT` 会先计算出这一行，“including doing any required nextval calls, before detecting any conflict.”（包括执行所有必需的 nextval 调用，然后才检测冲突。）如果你的应用反复 upsert 同一批行，序列的增长速度可能远远快于行数。（我们在 [INSERT ... ON CONFLICT 与 MERGE](/blog/zh-CN/postgres-upsert-on-conflict-vs-merge) 中详细介绍过 upsert。） ### 崩溃：一次最多 32 个号码（有缓存时更多） 为每次 `nextval` 都写一条 WAL 记录会很慢，所以 Postgres 会提前记录序列：写记录时，它记下一个往后 32 个号码的值，然后在不再写日志的情况下发放中间的号码。这个常量是服务器源码中的 `SEQ_LOG_VALS`，旁边的注释写道：“In the event of crash we can lose (skip over) as many values as we pre-logged.”（发生崩溃时，我们可能丢失（跳过）与预先记录数量相同的值。） ![调用五次 nextval 之后的序列。内存中的页面显示最后一个值为 5。第一次调用写下的 WAL 记录已经是 33。崩溃后，恢复过程重放 WAL，于是下一个值是 34，6 到 33 永远不会被使用。](/blog/assets/188c5cd25ec316cb.svg) 你可以亲眼看到这个过程。`log_cnt` 是剩余的预记录值的数量： ```sql CREATE TABLE events ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, note text ); INSERT INTO events (note) SELECT 'before crash' FROM generate_series(1, 5); SELECT last_value, log_cnt FROM events_id_seq; ``` ```text last_value | log_cnt ------------+--------- 5 | 28 (1 row) ``` 现在在不干净关闭的情况下让服务器崩溃（`pg_ctl stop -m immediate`），再启动它，然后再插入一行： ```text LOG: database system was not properly shut down; automatic recovery in progress ``` ```sql INSERT INTO events (note) VALUES ('after crash') RETURNING id; ``` ```text id ---- 34 (1 row) ``` 恢复过程重放了 WAL，WAL 里写的是 33，所以下一个 ID 是 34。号码 6 到 33 从未被使用。Postgres 还会在每次检查点之后的第一次 `nextval` 时写一条新的 32 个值的记录，所以你会丢失多少号码，取决于崩溃落在这个窗口中的哪个位置。这 32 个是叠加在缓存之上的：一个 `CACHE 20` 的序列（见下一节）会提前记录 20 + 32 个值，也可能丢失这么多。 干净关闭会把真实状态写入磁盘，在我们的测试中，正常重启后从下一个号码继续。那是一台我们自己停止的普通 Postgres 服务器；托管服务上的重启如何发生取决于服务本身，所以要把任何一次重启都当作可能的跳号。 ### CACHE：每个会话取走一整块 用 `CACHE 20` 创建的序列每次给每个会话 20 个号码。会话没用掉的号码在它断开连接时就丢失了： ```sql CREATE SEQUENCE ticket_seq CACHE 20; SELECT nextval('ticket_seq'); -- 会话 1 预留 1..20 \c SELECT nextval('ticket_seq'); -- 会话 2 预留 21..40 ``` ```text nextval --------- 1 (1 row) You are now connected to database "postgres" as user "postgres". nextval --------- 21 (1 row) ``` 文档还警告，使用缓存时，不同会话拿到的号码不再按时间顺序发放：会话 A 拿到 1，随后会话 B 拿到 21，之后 A 才拿到 2。默认值是 `CACHE 1`，除非你修改，`serial` 列和 identity 列用的都是它。 这些空洞都不是 bug，也都不需要修复。要修正的是观念：ID 表达的是「这一行和那一行不同」，而不是「这是第 6 个订单」或「一共有 6 个订单」。 ## 导入后报「duplicate key value violates unique constraint」 这个才是真正的 bug，它通常出现在迁移后的第一天：你从一个数据库导出了一张表，加载到另一个数据库，应用的第一次插入就失败了。下面用三条语句完整重现： ```sql CREATE TABLE customers ( id serial PRIMARY KEY, name text NOT NULL ); -- CSV 导入、ORM 种子数据或从另一个数据库复制时所做的事： COPY customers (id, name) FROM stdin; 1 Ada 2 Grace 3 Edsger \. INSERT INTO customers (name) VALUES ('Barbara'); ``` ```text ERROR: duplicate key value violates unique constraint "customers_pkey" DETAIL: Key (id)=(1) already exists. ``` 这些行自带 ID，所以 `COPY` 从未调用 `nextval`。序列仍然认为还没有发出过任何号码，于是给新行分配 1，主键随即拒绝了它。identity 列的行为也一样：即使是 `GENERATED ALWAYS` 列，`COPY` 也会写入输入中的 ID，“like the INSERT option OVERRIDING SYSTEM VALUE”（就像 INSERT 的 OVERRIDING SYSTEM VALUE 选项一样），而序列不会移动。 有一个让人困惑的细节：每次失败的插入仍然会消耗一个号码。重试三次后，第四次插入就会成功，因为序列终于走过了导入的那些 ID。这就是为什么这个错误有时会在开发环境中「自己好了」（你只导入了三行），而在生产环境中不会（你导入了三百万行）。 ### 修复一张表 把序列移动到表中最大的 ID。`pg_get_serial_sequence` 能找到 `serial` 列或 identity 列背后的序列，所以你不需要去猜它的名字： ```sql SELECT pg_get_serial_sequence('customers', 'id'); SELECT setval(pg_get_serial_sequence('customers', 'id'), max(id)) FROM customers; INSERT INTO customers (name) VALUES ('Barbara') RETURNING id; ``` ```text pg_get_serial_sequence ------------------------- public.customers_id_seq (1 row) setval -------- 3 (1 row) id ---- 4 (1 row) ``` 这条单行命令有两处会出问题。在空表上 `max(id)` 是 `NULL`，而带 `NULL` 参数的 `setval` 会返回 `NULL`，并且悄无声息地什么也不做。另外 `setval(seq, 0)` 会失败，因为序列的默认最小值是 1： ```sql CREATE TABLE audit_log (id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY); SELECT setval(pg_get_serial_sequence('audit_log', 'id'), max(id)) FROM audit_log; SELECT setval(pg_get_serial_sequence('audit_log', 'id'), 0); ``` ```text setval -------- (1 row) ERROR: setval: value 0 is out of bounds for sequence "audit_log_id_seq" (1..9223372036854775807) ``` 适用于空表的写法是 `setval(seq, COALESCE(max(id), 0) + 1, false)`。`false` 表示「下一次 `nextval` 恰好返回这个值」，所以空表会从 1 开始。 还要注意，`setval` 不是事务性的：文档说它的修改 “immediately visible to other transactions, and \[is] not undone if the calling transaction rolls back.”（会立即对其他事务可见，并且在调用它的事务回滚时不会撤销。） ### 一次修复所有序列 批量导入之后，你会想对每张表都这样做。下面的查询找出所有属于 `serial` 列或 identity 列的序列，为每个序列生成一条 `setval`，再由 `psql` 中的 `\gexec` 执行这些生成的语句。每条 `setval` 只会把序列往前推：它取 `max(id) + 1` 和序列下一次将要发放的值中较大的那个。这在已经投入使用的数据库上很重要。如果你删除了某张表中最新的几行，`max(id) + 1` 就会比序列小，而把序列往回移动会发出曾经用过一次、可能仍在别处被引用的 ID。 ```sql SELECT format( 'SELECT setval(%1$L, GREATEST(' 'COALESCE((SELECT max(%2$I) FROM %3$I.%4$I), 0) + 1, ' '(SELECT last_value + is_called::int FROM %1$s)), false);', s.oid::regclass, a.attname, n.nspname, t.relname) FROM pg_class s JOIN pg_depend d ON d.classid = 'pg_class'::regclass AND d.objid = s.oid AND d.refclassid = 'pg_class'::regclass AND d.deptype IN ('a', 'i') -- serial / identity JOIN pg_class t ON t.oid = d.refobjid JOIN pg_namespace n ON n.oid = t.relnamespace JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = d.refobjsubid WHERE s.relkind = 'S' ORDER BY 1 \gexec ``` 在我们的测试数据库上，它为每张表生成并执行了一条语句。下面是其中两条（输出有删节），分别对应空的 `audit_log` 和导入过数据的 `customers`： ```text SELECT setval('audit_log_id_seq', GREATEST(COALESCE((SELECT max(id) FROM public.audit_log), 0) + 1, (SELECT last_value + is_called::int FROM audit_log_id_seq)), false); setval -------- 1 (1 row) SELECT setval('customers_id_seq', GREATEST(COALESCE((SELECT max(id) FROM public.customers), 0) + 1, (SELECT last_value + is_called::int FROM customers_id_seq)), false); setval -------- 5 (1 row) ``` 在导入之后、放流量进来之前运行它；在 `max(id)` 和 `setval` 之间插入的行仍可能冲突。由于序列自己的下一个值也参与了 `GREATEST`，一个在空表上、自定义 `MINVALUE` 或 `START` 大于 1 的序列会保持原位，而不是报错。`\gexec` 遇到错误后会继续执行，所以如果你希望第一个失败就停止后续语句，请配合 `\set ON_ERROR_STOP on` 运行。有两种情况它不覆盖。递减的序列（`INCREMENT` 为负）需要改用 `min` 和 `LEAST`。而某个列仅通过 `DEFAULT nextval('some_seq')` 使用、没有 `OWNED BY` 的序列不归该列所有：`pg_get_serial_sequence` 对它返回 `NULL`，上面的查询也会跳过它。这些请手动修复。 ### 哪些数据迁移方式会让序列落后 | 数据是怎么来的 | 序列 | | ---------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------- | | 整库 `pg_dump`，用 `psql` 或 `pg_restore` 恢复 | 设置正确。转储中包含每个序列的 `setval` | | `pg_dump --data-only` | 设置正确。仅数据的转储同样包含 `setval` 调用 | | `pg_dump -t ` | 表**拥有**的序列（`serial`、identity）连同其 `setval` 一起包含在内。表仅通过 `DEFAULT nextval(...)` 引用的序列则不包含：恢复到没有该序列的数据库会失败，恢复到已有该序列的数据库时，该序列不会被移动 | | 从 CSV 执行 `COPY` 或 `\copy`、ORM 种子文件、带 ID 插入行的脚本 | **不会设置**。运行上面的修复 | | 逻辑复制 | **不会设置**。“Sequence data is not replicated”（序列数据不会被复制）：订阅端的序列一直停留在起始值，直到你在切换之前更新它们 | | `TRUNCATE` 后重新加载 | 除非加上 `RESTART IDENTITY`，否则 `TRUNCATE` 不会重置序列；重新加载相同的 ID 没问题，加载更大的新 ID 则需要上面的修复 | 我们用 `pg_dump` 17.11 逐行核对过。文档解释了 `DEFAULT nextval(...)` 的情况：使用 `-t` 时，“pg_dump makes no attempt to dump any other database objects that the selected table(s) might depend upon.”（pg_dump 不会尝试转储所选表可能依赖的任何其他数据库对象。） ## 当序列用完时 `serial` 会创建一个 `integer` 列和一个 `integer` 序列，所以它止步于 2,147,483,647。这听起来很多，直到某张记录事件或批量 upsert 的表消耗号码的速度超过存储行的速度（见上文的空洞）。走到尽头时是这个样子： ```sql CREATE TABLE clicks (id serial PRIMARY KEY); SELECT setval('clicks_id_seq', 2147483646); -- 为演示而快进 INSERT INTO clicks DEFAULT VALUES RETURNING id; INSERT INTO clicks DEFAULT VALUES RETURNING id; ``` ```text id ------------ 2147483647 (1 row) ERROR: nextval: reached maximum value of sequence "clicks_id_seq" (2147483647) ``` 在它发生之前检查一下离上限还有多远。`pg_sequences` 列出每个序列及其类型和上限： ```sql SELECT schemaname, sequencename, data_type, last_value, max_value, round(100.0 * last_value / max_value, 2) AS pct_used FROM pg_sequences ORDER BY pct_used DESC NULLS LAST, sequencename LIMIT 3; ``` ```text schemaname | sequencename | data_type | last_value | max_value | pct_used ------------+------------------+-----------+------------+---------------------+---------- public | clicks_id_seq | integer | 2147483647 | 2147483647 | 100.00 public | ticket_seq | bigint | 40 | 9223372036854775807 | 0.00 public | audit_log_id_seq | bigint | | 9223372036854775807 | (3 rows) ``` 注意 `ticket_seq` 是 40：在 `CACHE 20` 下，`last_value` 包含了会话已预留但尚未使用的号码。而对于自 `setval(..., false)` 以来还没发出过号码的序列，`last_value` 为 `NULL`，比如上面的修复刚刚处理过的那些；它们排在最后。 这个查询只知道序列自身的上限。如果序列是 `bigint` 而列仍是 `integer`，列会先出错，而查询显示的使用率几乎为 0%。这恰好就是下一个陷阱。 只改序列是不够的，因为列仍然是 `integer`： ```sql ALTER SEQUENCE clicks_id_seq AS bigint; INSERT INTO clicks DEFAULT VALUES RETURNING id; ``` ```text ERROR: integer out of range ``` 列也必须修改，用 `ALTER TABLE clicks ALTER COLUMN id TYPE bigint`。在大表上，这条语句会在持有 `ACCESS EXCLUSIVE` 锁的同时重写整张表，运行多久就阻塞读写多久。我们关于[会锁住表的迁移](/blog/zh-CN/postgres-migration-locks)的文章正好测量了这一变更。成本最低的修复是在第一天就做的那个：ID 用 `bigint`。 ## 当你确实需要无空洞的编号时 有时业务需要没有空洞的编号，发票编号就是常见的例子。序列做不到，所以要用一行数据，并在插入的同一事务中更新它。在默认的 `READ COMMITTED` 隔离级别下，行锁会让并发事务轮流执行，而回滚会把递增和发票一起撤销： ```sql CREATE TABLE invoice_counters ( year int PRIMARY KEY, last_no int NOT NULL ); INSERT INTO invoice_counters VALUES (2026, 0); CREATE TABLE invoices ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, year int NOT NULL, no int NOT NULL, amount numeric NOT NULL, UNIQUE (year, no) ); BEGIN; WITH n AS ( UPDATE invoice_counters SET last_no = last_no + 1 WHERE year = 2026 RETURNING last_no ) INSERT INTO invoices (year, no, amount) SELECT 2026, last_no, 120 FROM n RETURNING id, year, no; ROLLBACK; ``` 把同一个事务再运行一次，用 `COMMIT` 代替 `ROLLBACK`： ```text id | year | no ----+------+---- 2 | 2026 | 1 (1 row) ``` 发票编号又是 1，因为回滚撤销了计数器。`id` 是 2，因为 identity 列仍然是序列，回滚让它丢掉了 1。两者都保留：identity 作为主键，无空洞编号作为业务字段。 代价是并发。同一时间只有一个事务能持有计数器行。在我们的测试中，一个会话持有它五秒，另一个大约一秒后启动的会话，其 `UPDATE` 一直等到第一个会话提交： ```text last_no --------- 3 (1 row) UPDATE 1 Time: 3943.462 ms (00:03.943) ``` 所以要让这些事务保持短小：最后再取号，就在 `COMMIT` 之前，持有期间不要调用其他服务。每年（或每个系列、每个租户）一个计数器行也能分散等待。在 `REPEATABLE READ` 或 `SERIALIZABLE` 下，第二个事务同样会等待，但第一个事务提交后，它会以 `could not serialize access due to concurrent update` 失败，你的代码需要重试。 ## serial 还是 identity？ 新表请使用 `bigint GENERATED ALWAYS AS IDENTITY`。它是 SQL 标准语法，列和它的序列绑定在一起，而且 `GENERATED ALWAYS` 会拒绝意外传入的显式 ID： ```text ERROR: cannot insert a non-DEFAULT value into column "id" DETAIL: Column "id" is an identity column defined as GENERATED ALWAYS. HINT: Use OVERRIDING SYSTEM VALUE to override. ``` PostgreSQL 社区 wiki 的「Don't Do This」页面也这么说：“For new applications, identity columns should be used instead”（对于新应用，应改用 identity 列），而不是 `serial`。本文关于空洞和导入的所有内容对两者都适用；唯一的区别在于导入保护，而正如上文所示，`COPY` 无论如何都会绕过它。 ## 在分支上预演导入 导入 bug 很容易修复，也很容易漏掉，因为它只在数据落地后的第一次插入时才出现。在用户之前先测试这次插入。在 Relyt One 上，分支是一条新的历史线，从父分支的某个时间点开始，以写时复制方式共享其页面，并拥有自己的计算实例。把导出的数据加载到一个分支中，运行修复所有序列的查询，向每张表插入一行，再查看 `pg_sequences` 中是否有接近上限的序列。分支上的写入不会影响其父分支，所以分支的序列独立推进；如果出了问题，删除这个分支重新开始即可。 Relyt One 运行 PostgreSQL 17，本文中的 SQL 不需要任何扩展。`\c` 和 `\gexec` 这两行是 `psql` 命令，而崩溃测试需要一台你能自己停止的 Postgres 服务器，所以请在本地运行它。 [免费开始使用](/auth/signin?ref=blog-postgres-sequence-gaps-duplicate-key)