🙍🏻 🔸 🏇🏾 PostgreSQLアンチパターン：一意のID 💕 🎃 💩

多くの場合、開発者は、レコードの挿入時と読み取り時の両方で、PostgreSQLテーブル内のレコードに対していくつかの一意の識別子を生成する必要があります。

カウンターテーブル

それは思われるでしょう-何が簡単ですか？その中に、カウンター付きのエントリーという別のプレートを設置しました。新しい識別子を取得する必要があります-そこから読み取って新しい値を書き込みます-実行しUPDATEます...実行

しないでください！明日は問題を解決する必要があるため：

PostgreSQLアンチパターンを参照した場合の永続的な重複ロック：「死んだ」大群との戦いUPDATE
カウンターテーブルのデータへのアクセス速度が徐々に低下する

「PostgreSQLアンチパターン：負荷がかかった状態で大きなテーブルを更新する」を参照してください。
...そして気になるアクティブなトランザクションでクリーンアップする必要があります。DBAを

参照してください。VACUUMが合格すると、テーブルを手動でクリーンアップします。

SEQUENCEオブジェクト

このようなタスクのために、PostgreSQLは別のエンティティを提供します- SEQUENCE。これは非トランザクションです。つまり、ロックは発生しませんが、2つの「並列」トランザクションは確かに異なる値を受け取ります。

シーケンスから次のIDを取得するには、次の関数を使用しますnextval。

SELECT nextval('seq_name'::regclass);

たとえば、COPYを介したストリーミング録音の場合、一度に複数のID を取得する必要がある場合があります。これに使用することsetval(currval() + N)は根本的に間違っています！「内部currval」（setval）関数と「外部」（）関数の呼び出しの間に、同時トランザクションによってシーケンスの現在の値が変更される可能性があるという単純な理由からです。正しい方法は、nextval必要な回数だけ呼び出すことです。

SELECT
  nextval('seq_name'::regclass)
FROM
  generate_series(1, N);

シリアル疑似

「手動」モードでシーケンスを操作するのはあまり便利ではありません。しかし、私たちの典型的なタスクは、新しいレコードが新しいシーケンスIDで挿入されていることを確認することです！特にこの目的のために、PostgreSQLが発明されました serial。これは、テーブルを生成するときに、のようなものに「展開」します。フィールドにリンクされた自動生成されたシーケンスの名前を覚えておく必要はありません。このための関数があります。同じ関数を独自の置換で使用できます。たとえば、一度に複数のテーブルに共通のシーケンスを作成する必要がある場合などです。ただし、シーケンスの操作は非トランザクションであるため、シーケンスからのIDがロールバックされたトランザクションによって受信された場合、保存されたテーブルレコード内のIDのシーケンスは「リーク」します。id integer NOT NULL DEFAULT nextval('tbl_id_seq')

pg_get_serial_sequence(table_name, column_name)DEFAULT

..。

生成された列

開始PostgreSQLの10で、宣言することが可能であるID列を（GENERATED AS IDENTITY）SQLに準拠していること：2003標準。バリアントでは、GENERATED BY DEFAULT動作は同等serialですが、GENERATED ALWAYSすべてがより興味深いものになっています。

CREATE TABLE tbl(
  id
    integer
      GENERATED ALWAYS AS IDENTITY
);

INSERT INTO tbl(id) VALUES(DEFAULT);
--   :     10 .
INSERT INTO tbl(id) VALUES(1);
-- ERROR:  cannot insert into column "id"
-- DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
-- HINT:  Use OVERRIDING SYSTEM VALUE to override.

はい、そのような列の「全体」に特定の値を挿入するには、次の方法で追加の作業を行う必要がありますOVERRIDING SYSTEM VALUE。

INSERT INTO tbl(id) OVERRIDING SYSTEM VALUE VALUES(1);
--   :     11 .

テーブルに2つの同一の値があることに注意してくださいid = 1-つまり、GENERATEDは追加のUNIQUE条件とインデックスを課しませんが、純粋に宣言であり、serialです。

一般に、最近のPostgreSQLバージョンでは、シリアルの使用は非推奨であり、の代わりに使用することをお勧めしますGENERATED。おそらく、10未満のPGで動作するクロスバージョンアプリケーションのサポートの状況を除いて。

生成されたUUID

1つのデータベースインスタンス内で作業する限り、すべて問題ありません。しかし、それらがいくつかある場合、シーケンスを同期する適切な方法はありません（ただし、本当に必要な場合は、これによって「不適切に」同期することを防ぐことはできません）。ここで、値を生成するためのタイプUUIDと関数が役に立ちます。私は通常uuid_generate_v4()、最も「カジュアルな」ものとして使用します。

隠しシステムフィールド

tableoid / ctid

テーブルからレコードをフェッチするときに、特定の「物理」レコードに何らかの方法で対処したり、継承を使用して「親」テーブルにアクセスしたときに特定のレコードがどの特定のセクションから取得されたかを確認したりする必要がある場合があります。

この場合、各レコードに存在する非表示のシステムフィールドが役立ちます。

tableoidテーブルのoid-idを格納します-つまりtableoid::regclass::text、特定のテーブルセクションの名前を指定します
ctid -形式のレコードの「物理的」アドレス (<>,<>)

たとえば、主キーのないテーブルをctid使用する操作に使用できますがtableoid、特定の種類の外部キーの実装に使用できます。

oid

属性テーブルを作成するときに、最大11個のPostgreSQLを宣言できましたWITH OIDS。

CREATE TABLE tbl(id serial) WITH OIDS;

このテーブルの各エントリは、データベース内でグローバルに一意の値をoid持つ追加の非表示フィールドを取得します。これは、...などのシステムテーブル用に編成されているためです。テーブルにレコードを挿入すると、生成された値がクエリの結果にすぐに返されます。pg_classpg_namespace

INSERT INTO tbl(id) VALUES(DEFAULT);

  :   OID 16400   11 .

このようなフィールドは、「通常の」テーブルクエリでは表示されません。

SELECT * FROM tbl;

id
--
 1

他のシステムフィールドと同様に、明示的に要求する必要があります。

SELECT tableoid, ctid, xmin, xmax, cmin, cmax, oid, * FROM tbl;

tableoid | ctid  | xmin | xmax | cmin | cmax | oid   | id
---------------------------------------------------------
   16596 | (0,1) |  572 |    0 |    0 |    0 | 16400 |  1

確かに、値oidは32ビットしかないため、オーバーフローが発生しやすく、その後はテーブルを作成するoidことさえできなくなります（新しいテーブルが必要です！）。したがって、PostgreSQL 12以降、WITH OIDSサポートされなくなりました。

「公正な」時間clock_timestamp

クエリまたはプロシージャが長時間実行されている場合、「現在の」時刻をレコードにバインドしたい場合があります。この関数を使用してこれを実行しようとすると、失敗が発生します。トランザクション全体で同じ値now()が返されます。「今」の時間を取得するために、関数（およびその兄弟の別の束）があります。これらの関数の動作の違いは、単純なクエリの例で確認できます。

clock_timestamp()

SELECT
  now()
, clock_timestamp()
FROM
  generate_series(1, 4);

              now              |        clock_timestamp
-------------------------------+-------------------------------
 2020-08-19 16:26:05.626629+03 | 2020-08-19 16:26:05.626758+03
 2020-08-19 16:26:05.626629+03 | 2020-08-19 16:26:05.626763+03
 2020-08-19 16:26:05.626629+03 | 2020-08-19 16:26:05.626764+03
 2020-08-19 16:26:05.626629+03 | 2020-08-19 16:26:05.626765+03

PostgreSQLアンチパターン：一意のID