Postgresで物理削除したIDが再利用されることはありますでしょうか？

Question

以前MySQLでこの問題に直面したことがあります。

具体的には以下で書かれている事象と全く同じです。
こちらの記事です

Postgresでこちらの現象が起こることはあり得るでしょうか？
（DBシステムに依存したロジックを書くことが良いかどうかは置いておいて、Postgresを使う場合でもこの事象を想定して実装するべきでしょうか？）

Answer 1

MySQLのAUTO INCREMENTはテーブルに属した情報です。
※AUTO INCREMENTはテーブル毎に一つしか指定できないのはその制約からくるものです。
だから、MySQLの再起動でリセットされる仕様というのはありだとも云えます。

問題になったのは、AUTO INCREMENTを他のテーブルとも関連するキーの採番のルールに組み込んでいたからです。

一方PostgresではSEQUENCEとして独立していて、テーブルの定義としてSEQUENCEを関連付ける事で、AUTO INCREMENTを実現させています。

なので、リセットされるような事はありませんが(MySQLも最新ではリセットはされない）、そもそも関連があるのに物理削除していたというのが問題なので、そこはきちんと設計しなければなりません。

Comments

[noob_at]

ありがとうございます。
リセットされるような事はないのですね。

Answer 2

MySQLのAUTO INCREMENTはInnoデータベースのテーブルに付随する機能だと捉えていますが、
PostgreSQLでは連番型の説明にもある通り、
serialというデータ型を使うとテーブル名やカラム名から自動命名されるシーケンスによって
連番が生成されるものになっています。

OWNED BY table_name.column_nameされているカラムで
すでにテーブル上に存在している値が使われていても
シーケンスには遠慮も忖度もないので、
シーケンスの定義によって連番生成されたら、
重複してエラーが発生しまうかもしれませんね。
（INSERT INTO文やUPDATE文で妙なことをしなければいいだけの話。）

また、serialで定義された場合はNO CYCLEでしょうから、
nextval参照したときに上限値に達していればエラーになりこそすれ、
重複することはないはずです。

Answer 3

DBシステムに依存したロジックを書くことが良いかどうかは置いておいて、Postgresを使う場合でもこの事象を想定して実装するべきでしょうか？

逆質問にはなりますが、再利用がなされた場合にどのような状況で困るのでしょうか？

おそらくは、「番号の使い方が悪い」、もしくは「絶対に再利用されれば困る番号なので、採番テーブルを作って厳格に管理するほうがより安全」ということになるのではないかと考えます。

Comments

[noob_at]

それは質問に張っているリンクの事象と同じです。
物理削除したidが、削除もれで他のテーブルに残っていたり、削除ずみidをユニークで管理しているような場合です。
設計が悪いとは思いますが、再利用が起こる可能性があるか・絶対に起こらないかで設計も変わってくると思いますので

[maisumakun]

> 設計が悪いとは思いますが、再利用が起こる可能性があるか・絶対に起こらないかで設計も変わってくると思いますので

「再利用が起こる可能性がある」設計を取っておけば、なくても困ることはありません。

[noob_at]

質問とは直接関係のない話ですが、
>「再利用が起こる可能性がある」設計を取っておけば、なくても困ることはありません。

100%ないと言い切れるものに対して、あるかも？を考慮して設計するのは良いとは私は思えません。

[maisumakun]

自分が言いたかったのは、「重複しないと保証されることが、その必要がないのに重複すると不具合となるような悪い設計の免罪符とはならない」ということです。

ビジネス上も番号の一意性が求められるような状況であれば、積極的に使うことはありだともちろん考えます。

[sazi]

@noob_atさん
サロゲートキーなら再利用はありえないですが、ナチュラルキーの場合は再利用は考えられます。
なので、SEQUENCEをナチュラルキーとして扱うには注意が必要です。