PostgreSQLでシーケンス重複エラーの原因に立ち向かう

掲載日
更新日

はじめに

職場では(このサイトでも)PostgreSQLを 使っているのですが、そこでシーケンスの重複エラーが出ました。

シーケンスがズレている場合の対処法自体はいろんな場所に載っている(以下を実行してズレを修正する)ので、今回は原因の一つを備忘録として残しておきます。

SELECT setval('シーケンスがズレてるテーブル名_id_seq', coalesce((SELECT max(id) FROM シーケンスがズレてるテーブル名), 1), true);

原因

概要

自分の場合はざっくり以下が原因でした。

  1. transactionAでINSERTが走り、シーケンスの値が＋1される。
  2. transactionAが閉じる前に、別のプロセスでsetvalが実行され、シーケンスの値がもとに戻る。
  3. transactionAがコミットされ、idは＋1されているのにシーケンス値が更新されていない状態となる。
  4. 次のINSERT時に重複エラー。

 

本来setvalは使用する必要がない(普通INSERT時にPostgreSQL側で更新していってくれるので)にもかかわらず、処理に組み込まれていたのが原因でした。

再現方法

以下の方法で再現することができます。

 

テスト用DBとテーブル作成

CREATE DATABASE test;
CREATE TABLE test (
    id serial,
    exec_time timestamp,
    CONSTRAINT test_pkey PRIMARY KEY (id)
);

処理を二つ作成(PHP)

以下の処理をtransaction1.phpとして保存します。

$dbh = new PDO("pgsql:host=localhost; dbname=test; user=postgres; password=postgres");

echo "- Transaction開始\n";
$dbh->beginTransaction();
echo "-- INSERT開始\n";
$dbh->exec("INSERT INTO test(exec_time) VALUES (current_timestamp)");
$dbh->exec("INSERT INTO test(exec_time) VALUES (current_timestamp)");
echo "-- INSERT終了(ここでtransaction2.phpを実行)\n"; 

sleep(10);
$dbh->commit();
echo "- COMMIT\n"; 

echo "- Transaction開始\n";
echo "-- INSERT開始(エラーが出る)\n";
$dbh->beginTransaction();
$dbh->exec("INSERT INTO test(exec_time) VALUES (current_timestamp)");
$dbh->commit();

次に、以下の処理をtransaction2.phpとして保存します。

$dbh = new PDO("pgsql:host=localhost; dbname=test; user=postgres; password=postgres");
echo "- Transaction開始\n";
$dbh->beginTransaction();
echo "-- sequence値をリセット\n";
$dbh->exec("SELECT setval('test_id_seq', coalesce((SELECT max(id) FROM test), 1),true)");
$dbh->commit();
echo "- COMMIT\n";

transaction1.phpを実行し、sleepしている間にtransaction2.phpを実行すると重複エラーを再現することができます。

原因の詳細  

まず、transaction1.php を実行すると、
INSERT INTO test(exec_time) VALUES (current_timestamp)
が2回実行され、シーケンス値が2になります。
sleep10秒で待っている間にtransaction2.phpを実行すると、
SELECT setval('test_id_seq', coalesce((SELECT max(id) FROM test), 1),true)
が実行されます。
この時、transaction1.phpで発行したクエリはまだcommitされていないので(transaction2.phpから見ると、testテーブルには何もデータがないので)、シーケンス値が1になります。

sleepが終わると、transaction1.phpでコミットが行われます。
transaction開始～クエリ実行時にはシーケンス値を更新して値を取得していたので、ここでは問題なくid1,2でそれぞれデータがINSERTされます。

別のtransactionが開始され、testテーブルにINSERTしようとしますがシーケンス値が1に戻っているため、idを2としてINSERTしようとして重複エラーとなっていた、ということのようです。

所感

自分の場合、setvalがcronで頻繁に実行される処理に紛れ込んでいたのがNGだったので、あまり参考にはならない(そういう実装になることは早々ないんじゃないでしょうか。)かもしれませんが誰かの助けになるかもなので備忘録として残しておきます。

調査時に詰まった点

  • テーブル初期化後、INSERT1回なら何故かエラーにならず、追加でINSERTしていくことが出来ました。
    → 一回目のINSERTではtest_id_seqのlast_value値が変わらないため。
    (is_calledで判定している。transaction2.phpのクエリでtrueをfalseにしたらエラーになりました。)
    (参考にさせて頂いたサイト)
記事の作成者のA.W.のアイコン

この記事を書いた人

A.W.
茨城県在住Webエンジニアです。 PHPなどを業務で使用しています。 趣味ではGoやNuxt、Flutterをやってます。

Comment