第39回 PostgreSQLアンカンファレンス@オンライン Yasuo Honda @yahonda 遅延可能な一意性制約

● Yasuo Honda @yahonda ○ Rails committer ○ 第36回 PostgreSQLアンカンファレンス から2回目の参加です ■ 第36回 PostgreSQLアンカンファレンス@オンライン ■ PostgreSQL 15とRailsと - Speaker Deck ■ CIでのPostgreSQL 14から15への切り替えは何事もなく終わり ました 自己紹介

● Railsのmigration(DSLでDDLを作成、実行する)に遅延可能な一意性制 約を作成できるようにするpull requestがopenされた ○ https://github.com/rails/rails/pull/46192 ● このpull requestに関連するPostgreSQLの動きを知りたい ● Rails main branchはPostgreSQL 9.3以上に対応している ○ 複数バージョンで一貫した動きかどうか知っておく必要がある 背景

● https://www.postgresql.jp/document/14/html/sql-set-constra ints.html ● “SET CONSTRAINTSは、現在のトランザクションにおける制約検査の動 作を設定します。 IMMEDIATE制約は、1つの文の実行が終わるごとに検 査されます。 DEFERRED制約は、トランザクションがコミットされるまで検 査されません。 全ての制約は、IMMEDIATEかDEFERREDのどちらかの モードを持ちます。” 遅延可能とは

● `ADD CONSTRAINT UNIQUE DEFERRABLE`と指定した際の動 き ● `INITIALLY DEFERRED`か`INITIALLY IMMEDIATE`のいずれか ● https://www.postgresql.org/docs/current/sql-set-constraints. html ○ “Upon creation, a constraint is given one of three characteristics: DEFERRABLE INITIALLY DEFERRED, DEFERRABLE INITIALLY IMMEDIATE, or NOT DEFERRABLE.” 知りたいこと(知りたかったこと)

● example1 ○ UNIQUE (row, col) ● example2 ○ UNIQUE (row, col) NOT DEFERRABLE ● example3 ○ UNIQUE (row, col) DEFERRABLE ● example4 ○ UNIQUE (row, col) DEFERRABLE INITIALLY DEFERRED ● example5 ○ UNIQUE (row, col) DEFERRABLE INITIALLY IMMEDIATE ● example6 ○ UNIQUE (row, col) NOT DEFERRABLE INITIALLY DEFERRED ● example7 ○ UNIQUE (row, col) NOT DEFERRABLE INITIALLY IMMEDIATE 試したパターン

https://gist.github.com/yahonda/1adcf1a776a385b8ec47c9cdf5dcf 08b 試した結果

● example1 : UNIQUE (row, col) ○ NOT DEFERRABLE ● example2 : UNIQUE (row, col) NOT DEFERRABLE ○ NOT DEFERRABLE ● example3 : UNIQUE (row, col) DEFERRABLE ○ DEFERRABLE ● example4 : UNIQUE (row, col) DEFERRABLE INITIALLY DEFERRED ○ DEFERRABLE INITIALLY DEFERRED; ● example5 : UNIQUE (row, col) DEFERRABLE INITIALLY IMMEDIATE ○ DEFERRABLE ● example6 : UNIQUE (row, col) NOT DEFERRABLE INITIALLY DEFERRED ○ `ERROR: constraint declared INITIALLY DEFERRED must be DEFERRABLE` ● example7 : UNIQUE (row, col) NOT DEFERRABLE INITIALLY IMMEDIATE ○ NOT DEFERRABLE 試した結果

● `ADD CONSTRAINT UNIQUE DEFERRABLE`と指定した際は、 `INITIALLY IMMEDIATE`になる 確認できたこと

● PostgreSQL 9.3と15とで同じ記述があった ○ https://www.postgresql.org/docs/15/sql-createtable.html ○ https://www.postgresql.org/docs/9.3/sql-createtable.html ○ “If the constraint is INITIALLY IMMEDIATE, it is checked after each statement. This is the default.” ● 9.4から14は調べてないがおそらくバージョンごとの振る舞いの違いはなさ そう 確認できたこと

● `NOT DEFERRABLE INITIALLY DEFERRED` ○ ERROR: constraint declared INITIALLY DEFERRED must be DEFERRABLE ○ 理解できるエラー ● `NOT DEFERRABLE INITIALLY IMMEDIATE` ○ DDLは通り、`pg_constraint`上は、`NOT DEFERRABLE` ○ このDDLもエラーになった方が一貫している気がする(実害なし) ○ NOT DEFERRABLEとINITIALLY IMMEDIATEがデフォルトだから 無指定と同じと解釈されたのかと推測 興味本位で知りたいこと

おわり