PostgreSQLを再起動するとシーケンスの値が33増える謎

5449 ワード
PostgreSQL SQL Aurora SQL テキストリンク

ある日アプリケーションのDEBUGログを眺めていると、レコードのidカラムの値が実際のレコード数よりも極端に大きくなっていることに気付きました。このカラムの値は、シーケンスを使って採番しているので、1つずつ増えていくことが期待です。「まさかバグか？」と焦りましたが、リソース節約のため毎晩DBを停止していることが原因でした。

再現確認

手っ取り早く PostgreSQL 12.3 の Docker公式イメージ を使って検証してみます。

  1. PostgreSQL を起動する

    $ docker run --name testdb -e POSTGRES_PASSWORD=mysecretpassword -d postgres:12.3

  2. psql で接続する

    $ docker exec -it testdb psql -U postgres
psql (12.3 (Debian 12.3-1.pgdg100+1))
Type "help" for help.

postgres=#

  3. シーケンスを作成して、値を取得する

    postgres=# create sequence myseq;
CREATE SEQUENCE
postgres=# select nextval('myseq');
 nextval
---------
       1
(1 row)

  4. DBを再起動する

    $ docker restart testdb

  5. シーケンスの値を再び取得する → 2じゃない！ 

    postgres=# select nextval('myseq');
 nextval
---------
      34
(1 row)

Sequence Caching: Oracle vs. PostgreSQL によると、再起動したときに 32（固定サイズ）+ 1（シーケンスのキャッシュサイズ。デフォルト値から変えている場合はさらに大きくなる）だけ番号が進んでしまうようです。

補足

参考ページを見てみると「4. DBを再起動する」でDBをきちんと停止していないのがまずそうです。DBを安全に停止する手順でやり直してみます。

  1. シーケンスを作成して、値を取得する

    postgres=# create sequence myseq;
CREATE SEQUENCE
postgres=# select nextval('myseq');
 nextval
---------
       1
(1 row)

  2. DBを再起動する

    $ docker exec -it testdb su postgres -c 'pg_ctl stop -m fast'

waiting for server to shut down....
$ docker start testdb

  3. シーケンスの値を再び取得する

    postgres=# select nextval('myseq');
 nextval
---------
       2
(1 row)

今度は期待通り 2 になりました。DBを無理やり停止するなということですね。

補足2

なお、今回問題が起きた実際の環境では、PostgreSQL ではなく Amazon Aurora PostgreSQL を使っていて、AWS CLIを使って停止させていました。無理やり停止させているつもりはないのですが、なぜ…

$ aws rds stop-db-cluster --db-cluster-identifier mydbcluster

まとめ

シーケンスを使ってカラムの値を採番していると、ときどき欠番が発生することがあります。トランザクションがロールバックされた場合に欠番が発生することは理解していましたが、DBを停止した場合にも欠番が（32も！）発生することは知りませんでした。勉強になりました。

ただ、Aurora の挙動については情報源を見つけられず、Aurora を停止したときにもシーケンスが飛ぶのが期待なのかは分からないままでした。

参考