# Postgres の ID が番号を飛ばす理由と、インポート後の「duplicate key」エラーの直し方 PostgreSQL の serial 列と ID 列（identity）で番号の欠番が生じる原因（ロールバック、ON CONFLICT、クラッシュ、CACHE）、データのインポート直後に duplicate key value violates unique constraint で挿入が失敗する理由、すべてのシーケンスをまとめてリセットする 1 つのクエリ、integer の ID が尽きたとき、そして欠番のない請求書番号を扱います。すべてのステートメントを Postgres 17.11 で再現しています。 **Postgres の ID については、2 つの質問が何度も繰り返されます。「ID が 5 から 34 に飛んだのはなぜ？」と「データをインポートした直後から、すべての挿入が `duplicate key value violates unique constraint \"..._pkey\"` で失敗するのはなぜ？」です。どちらも同じオブジェクトが原因です。`serial` 列や ID 列の裏にあるシーケンスです。シーケンスは、番号が使われるかどうかを気にせずに番号を払い出すので、欠番が生じるのは正常です。また、シーケンスが知っているのは自分で払い出した番号だけなので、明示的な ID 付きで読み込んだ行があると、シーケンスは取り残されます。この記事では、欠番の種類ごとにそれを再現し、インポートのエラーをまず 1 つのテーブルについて、次にすべてのテーブルについて一度に修正し、`integer` の ID が尽きたときに何が起きるかを示し、本当に必要になりうる唯一の場面のために欠番のない採番を組み立てます。以下のステートメントと出力はすべて、Relyt One が採用しているメジャーバージョンであるローカルの PostgreSQL 17.11 で実行したものです。** ## 要点 | 症状 | 原因 | 修正方法 | | -------------------------------------------------------- | ----------------------------------- | ---------------------------------- | | ID がいくつか飛ぶ | ロールバックされた挿入や失敗した挿入、`ON CONFLICT` | 何もしない。シーケンスはそもそも欠番なしを保証する設計ではない | | クラッシュ後に ID が最大 32 飛ぶ（デフォルトのキャッシュの場合） | シーケンスは WAL に先の値まで記録している | 何もしない。ID の値に意味を持たせない | | セッションごとの ID が入り混じる、またはキャッシュサイズ分飛ぶ | 1 より大きい `CACHE` | 順序が重要ならデフォルトの `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) ``` 3 行に対して、欠番が 3 つあります。ロールバックされた挿入が 2 を取りました。一意な `email` 制約で失敗した挿入は、失敗する前に 5 を取りました。Postgres は制約をチェックする前に、デフォルトの ID を含めた行全体を組み立てるからです。`ON CONFLICT DO NOTHING` も同じ理由で、何も挿入せずに 4 を取りました。ドキュメントにも明記されています。`INSERT ... ON CONFLICT` は行を計算し、その際 "including doing any required nextval calls, before detecting any conflict."（競合を検出する前に、必要な nextval 呼び出しも行う）。アプリが同じ行を何度もアップサートしていると、シーケンスは行数よりずっと速く増えることがあります。（アップサートについては [INSERT ... ON CONFLICT と MERGE](/blog/ja/postgres-upsert-on-conflict-vs-merge) で詳しく取り上げました。） ### クラッシュ：一度に最大 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 回呼んだ後のシーケンス。メモリ上のページは last_value が 5 だが、最初の呼び出しで書かれた WAL レコードはすでに 33 になっている。クラッシュ後のリカバリーは WAL を再生するので、次の値は 34 になり、6 から 33 は使われない。](/blog/assets/cb0c87d5a2ea1546.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`）、再起動してから、もう 1 行挿入します。 ```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` 列と ID 列はこれを使います。 これらの欠番はどれもバグではなく、どれも修正の必要はありません。直すべきは考え方です。ID が示すのは「この行はあの行とは別物だ」ということであり、「これは 6 番目の注文だ」や「注文は 6 件ある」ではありません。 ## インポート後の「duplicate key value violates unique constraint」 こちらは本物のバグで、たいていは移行の翌日に現れます。あるデータベースからテーブルをエクスポートし、別のデータベースに読み込んだところ、アプリの最初の挿入が失敗するのです。3 つのステートメントで全体を再現できます。 ```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 を渡し、主キーがそれを拒否します。ID 列も同じ動作です。`COPY` は入力にある ID を `GENERATED ALWAYS` の列にさえ書き込み（"like the INSERT option OVERRIDING SYSTEM VALUE"、つまり INSERT の OVERRIDING SYSTEM VALUE オプションと同様に）、シーケンスは動きません。 話をややこしくする点が 1 つあります。失敗した挿入も番号を 1 つ消費します。3 回リトライすると 4 回目の挿入は成功します。シーケンスがようやくインポートされた ID を通り越したからです。開発環境（インポートしたのは 3 行）ではこのエラーが「勝手に直る」のに、本番環境（インポートしたのは 300 万行）では直らないのはこのためです。 ### 1 つのテーブルを修正する シーケンスをテーブル内の最大の ID まで進めます。`pg_get_serial_sequence` は `serial` 列や ID 列の裏にあるシーケンスを見つけてくれるので、名前を推測する必要はありません。 ```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) ``` この 1 行には落とし穴が 2 つあります。空のテーブルでは `max(id)` が `NULL` になり、引数が `NULL` の `setval` は `NULL` を返して黙って何もしません。また、シーケンスのデフォルトの最小値は 1 なので、`setval(seq, 0)` は失敗します。 ```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` 列または ID 列に属するすべてのシーケンスを見つけて、それぞれに対する `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 ``` 私たちのテスト用データベースでは、テーブルごとに 1 つのステートメントが生成・実行されました。そのうち 2 つ、空の `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` を付けて実行してください。このクエリが対応しないケースが 2 つあります。減少していくシーケンス（負の `INCREMENT`）では、代わりに `min` と `LEAST` が必要です。また、列が `OWNED BY` なしに `DEFAULT nextval('some_seq')` 経由で使っているだけのシーケンスは、その列に所有されていません。そのようなシーケンスに対して `pg_get_serial_sequence` は `NULL` を返し、上のクエリはそれを飛ばします。これらは手作業で修正してください。 ### どのデータ移行方法でシーケンスが取り残されるか | データの移し方 | シーケンス | | ------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------- | | データベース全体の `pg_dump` を `psql` または `pg_restore` でリストア | 正しく設定される。ダンプにはすべてのシーケンスの `setval` が含まれる | | `pg_dump --data-only` | 正しく設定される。データのみのダンプにも `setval` の呼び出しが含まれる | | `pg_dump -t ` | テーブルが**所有する**シーケンス（`serial`、ID 列）は `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 で止まります。多いように聞こえますが、イベントを記録するテーブルや一括でアップサートするテーブルが、行を保存するよりも速く番号を消費するようになると話は別です（上の欠番を参照）。終わりはこうなります。 ```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/ja/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; ``` 同じトランザクションを、`ROLLBACK` の代わりに `COMMIT` でもう一度実行します。 ```text id | year | no ----+------+---- 2 | 2026 | 1 (1 row) ``` ロールバックがカウンターを元に戻したので、請求書番号は再び 1 です。`id` は 2 です。ID 列は依然としてシーケンスであり、ロールバックで 1 を失ったからです。両方を残しておきましょう。ID 列は主キーとして、欠番のない番号は業務上のフィールドとして使います。 代償は同時実行性です。カウンター行を保持できるのは、一度に 1 つのトランザクションだけです。私たちのテストでは、あるセッションがこの行を 5 秒間保持し、約 1 秒後に開始した 2 つ目のセッションの `UPDATE` は、最初のセッションがコミットするまで待たされました。 ```text last_no --------- 3 (1 row) UPDATE 1 Time: 3943.462 ms (00:03.943) ``` ですから、これらのトランザクションは短く保ってください。番号は最後、`COMMIT` の直前に取り、保持している間は他のサービスを呼び出さないようにします。年ごと（あるいは系列ごと、テナントごと）にカウンター行を 1 つずつ用意すれば、待ちも分散できます。`REPEATABLE READ` や `SERIALIZABLE` では、2 つ目のトランザクションはやはり待たされますが、最初のトランザクションがコミットすると `could not serialize access due to concurrent update` で失敗するので、コード側でリトライする必要があります。 ## serial と ID 列、どちらを使うか 新しいテーブルには `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」のページも同じことを述べています。`serial` について "For new applications, identity columns should be used instead"（新しいアプリケーションでは、代わりに ID 列を使うべきだ）。欠番とインポートについてこの記事で述べたことは、どちらにも当てはまります。違うのはインポートに対する保護だけで、上で示したとおり、`COPY` はいずれにせよそれを迂回します。 ## ブランチでインポートをリハーサルする インポートのバグは、直すのは簡単ですが、見落としやすいものです。データが入った後の最初の挿入でしか現れないからです。ユーザーより先に、その挿入をテストしましょう。Relyt One では、ブランチは親のある時点から始まる新しい履歴の流れで、ページを copy-on-write で共有し、独自のコンピュートを持ちます。エクスポートしたデータをブランチに読み込み、すべてのシーケンスを修正するクエリを実行し、各テーブルに 1 行ずつ挿入し、`pg_sequences` で上限に近いものがないかを確認します。ブランチへの書き込みは親に影響しないので、ブランチのシーケンスは独立して進みます。何か問題が起きたら、ブランチを削除してやり直せば済みます。 Relyt One は PostgreSQL 17 を実行しており、この記事の SQL に拡張機能は必要ありません。`\c` と `\gexec` の行は `psql` のコマンドです。また、クラッシュのテストには自分で停止できる Postgres サーバーが必要なので、それはローカルで実行してください。 [無料で始める](/auth/signin?ref=blog-postgres-sequence-gaps-duplicate-key)