Upgrade to Pro — share decks privately, control downloads, hide ads and more …

遅延可能な一意性制約

Yasuo Honda
February 20, 2023

 遅延可能な一意性制約

Yasuo Honda

February 20, 2023
Tweet

More Decks by Yasuo Honda

Other Decks in Programming

Transcript

  1. • Yasuo Honda @yahonda ◦ Rails committer ◦ 第36回 PostgreSQLアンカンファレンス

    から2回目の参加です ▪ 第36回 PostgreSQLアンカンファレンス@オンライン ▪ PostgreSQL 15とRailsと - Speaker Deck ▪ CIでのPostgreSQL 14から15への切り替えは何事もなく終わり ました 自己紹介
  2. • `ADD CONSTRAINT <u> 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.” 知りたいこと(知りたかったこと)
  3. • 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 試したパターン
  4. • 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 試した結果
  5. • 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は調べてないがおそらくバージョンごとの振る舞いの違いはなさ そう 確認できたこと
  6. • `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がデフォルトだから 無指定と同じと解釈されたのかと推測 興味本位で知りたいこと