PostgreSQLだけじゃないかもしれないけど、「シーケンス(SEQUENCE)」が厄介な件

f:id:ts0818:20211218173526j:plain

www.excite.co.jp

産業技術総合研究所(産総研)は11月22日、次世代リチウムイオン電池(LIB)の1種である酸化物系全固体LIB向けの高容量正極および負極を開発し、安全性の向上に道筋をつけることができたと発表した。

産総研、室温で作動する全固体リチウム硫黄電池用の正極と負極の開発に成功 (2021年11月24日) - エキサイトニュース

www.softbank.jp

国立研究開発法人物質・材料研究機構(以下「NIMS」)は、ソフトバンク株式会社(以下「ソフトバンク」)と共同で、現行のリチウムイオン電池の重量エネルギー密度(Wh/kg)を大きく上回る500Wh/kg級リチウム空気電池を開発し、室温での充放電反応を実現しました。さらに、世界中で報告されているリチウム空気電池の性能の網羅的な調査により、NIMSが開発したリチウム空気電池は、エネルギー密度ならびに、サイクル数の観点で世界最高レベルであることを示しました。本研究成果は、リチウム空気電池の実用化に向け、大きな一歩となるものです。

500Wh/kg級リチウム空気電池を開発 | プレスリリース | ニュース | 企業・IR | ソフトバンク

⇧ なんか「電池」の研究が非常に進展してるようですね。

いつもの如く、冒頭から脱線しましたが、今回は「PostgreSQL」についての話です。

レッツトライ~。

PostgreSQLにおける「シーケンス(SEQUENCE)」とは?

そもそも、「PostgreSQL」における「シーケンス(SEQUENCE)」とは何なのか?

www.geeksforgeeks.org

A sequence in PostgreSQL is a user-defined schema-bound object that yields a sequence of integers based on a specified specification. The CREATE SEQUENCE statement is used to create sequences in PostgreSQL.

PostgreSQL - CREATE SEQUENCE - GeeksforGeeks

⇧ 上記サイト様によりますと、自分で作成する必要があるらしいのですが、「a sequence of integers based on a specified specification.」ということで、「指定された仕様」ってのが分からんのですが、「整数のシーケンス」らしい。

ちなみに、「Sequence」のWikipediaによると、

In mathematics, a sequence is an enumerated collection of objects in which repetitions are allowed and order matters. Like a set, it contains members (also called elements, or terms). The number of elements (possibly infinite) is called the length of the sequence.

https://en.wikipedia.org/wiki/Sequence

In computing and computer science, finite sequences are sometimes called stringswords or lists, the different names commonly corresponding to different ways to represent them in computer memory; infinite sequences are called streams. The empty sequence ( ) is included in most notions of sequence, but may be excluded depending on the context.

https://en.wikipedia.org/wiki/Sequence

⇧ 上記のような説明で、「PostgreSQL」の「Sequence」は「mathematics」のほうに近い感じなんかな。

PostgreSQL」の公式のドキュメントを見た感じ、

www.postgresql.jp

シーケンスを作成した後、nextvalcurrvalsetval関数を使用してシーケンスを操作します。

https://www.postgresql.jp/document/13/html/sql-createsequence.html

シーケンスはbigint演算に基づいています。 そのため、8バイト整数の範囲(-9223372036854775808から9223372036854775807まで)を越えることはできません。

https://www.postgresql.jp/document/13/html/sql-createsequence.html

nextvalsetvalの呼び出しは決してロールバックされないので、シーケンスの番号について欠番のない割り当てが必要な場合には、シーケンスオブジェクトを使うことはできません。 カウンターを含むテーブルに対して排他ロックを使うことで、欠番のない割り当てを構築することは可能ですが、この解決策はシーケンスオブジェクトに比べてずっと高価で、特に多くのトランザクションが同時にシーケンスの番号を必要とする場合は高価になります。

https://www.postgresql.jp/document/13/html/sql-createsequence.html

⇧ なかなかに面倒くさそう...「欠番」もあり得るってことで、完全な連番を保証するものでもないらしく、いまいちどんな目的で使ったら良いのかが分からん...

そして、悲報...

hhelibex.hatenablog.jp

そもそもPostgreSQLにおけるシーケンスは、CREATE SEQUENCE文を使って自分で定義するか、SERIAL(SERIAL4)型やBIGSERIAL(SERIAL8)型の列を含むテーブルの作成によって自動的に生成される。

シーケンスの情報の取得 - HHeLiBeXの日記 正道編

⇧ なんか、「テーブル」の作りによっては、漏れなく「シーケンス(SEQUENCE)」は作成されてしまうものらしい。

www.postgresql.jp

8.1.4. 連番型

smallserialserialおよびbigserialデータ型は正確にはデータ型ではなく、テーブルの列に一意の識別子を作成する簡便な表記法です (他のデータベースでサポートされるAUTO_INCREMENTプロパティに似ています)。 現在の実装では、

CREATE TABLE tablename ( colname SERIAL );

は以下を指定することと同じです。

CREATE SEQUENCE tablename_colname_seq AS integer; CREATE TABLE tablename ( colname integer NOT NULL DEFAULT nextval('tablename_colname_seq') ); ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

このように整数列を作成し、その列のデフォルト値が連番ジェネレータから割り当てられるようにしました。

8.1. 数値データ型

注記

smallserial、 serialおよびbigserialはシーケンスを使って実装されているため、行の削除が行われていなくとも、列に"穴"や連番の抜けが発生するかもしれません。また、テーブルへ正常に挿入されていないにも関わらず、シーケンスの値を"消費してしまう"こともあります。これは、例えば挿入したトランザクションがロールバックされた時に発生することがあります。詳細は9.17nextval()を参照してください。

8.1. 数値データ型

⇧ な~るほど、そして、旧いドキュメントだと「シリアル型」になってたのが、「連番型」に名称が変わったらしいですな。

「シーケンス(SEQUENCE)」が作られてるのかどうかの確認ですが、

qiita.com

lightgauge.net

⇧ 上記サイト様を参考に確認してみた。

f:id:ts0818:20211218165142p:plain

f:id:ts0818:20211218165345p:plain

⇧ 結果として、自分の環境では「public」スキーマにある「テーブル」に関しては「シーケンス(SEQUENCE)」は作成されていないということらしい。

 

「一意制約違反」が出てるけど、「テーブル」の「主キー」は重複してないが...それ、「シーケンス(SEQUENCE)」と「テーブル」の「主キー」がズレてるかも

で、プロジェクトにおいて「PostgreSQL」を使っている場合なんかで、「一意制約違反」が出たときに、該当する「テーブル」の「主キー」を確認しても、「主キー」は重複してないな~、はて?っていうような時は、

www.xmisao.com

qiita.com

⇧ 上記サイト様によりますと、通常のSQL文などで、INSERTしてる分には問題ないんだけど、「PostgreSQL」には「COPY」コマンドという「CSVファイル」からインポートする機能などがあり、そういった機能でレコードが「テーブル」に登録された場合、「テーブル」の「主キー」に対して「シーケンス(SEQUENCE)」とか作成されていると、

  • 「テーブル」の「主キー」
  • 「テーブル」の「主キー」に対する「シーケンス(SEQUENCE)」

がズレることがあり、「一意制約違反」が発生するらしい、な、何と!紛らわしい...

で、

kamegu.hateblo.jp

⇧ 上記サイト様の説明にあるように、例えば「org.postgresql.copy.CopyManager」っていうようなライブラリを使っていたりするケースだと、「CSVファイル」からCOPYコマンドなどとかを実行できるっぽいので、Java側で例外とかが発生すると、「テーブル」の「主キー」と、それに紐づく「シーケンス(SEQUENCE)」がズレてしまうことなどが起こり「一意制約違反」となってしまうということがあるらしい、「シーケンス(SEQUENCE)」は「ロールバック」されないって説明があったし。

だから、SQL文の実行途中で何かしら障害が起こった場合に、「ロールバック」した場合でも、「シーケンス(SEQUENCE)」側では進んだ値の分は「ロールバック」されないので、ズレが生まれてしまうという...

そう考えると、クリティカルな処理において、「シーケンス(SEQUENCE)」の存在が足枷になってしまわないのかが気になりますな...、教えて偉い人。

まぁ、「PostgreSQL」を使っていて「一意制約違反」とか出た際に、「テーブル」の「主キー」で「重複」が見当たらなかったら、「テーブル」の「主キー」と、それに紐づく「シーケンス(SEQUENCE)」のズレを確認してみると良いかもしれませんという話でした。

毎度モヤモヤ感が半端ない...

今回はこのへんで。