PostgreSQL 18のNOT ENFORCEDな制約とDEFERRABLEの関係

Transcript

1. PostgreSQL 18のNOT ENFORCEDな制約と DEFERRABLEの関係 第56回 PostgreSQLアンカンファレンス@オンライン Yasuo Honda @yahonda
2. 現状 RailsにおけるFixture(テストのためのデータ)のロード disable_referential_integrity メソッドにより参照整合性制約を一時的に無効に している 各データベースアダプターごとに実装が異なる(PostgreSQLの場合) ALTER TABLE <tablename> DISABLE TRIGGER ALL テストデータのロード ALTER TABLE <tablename> ENABLE TRIGGER ALL MySQLの場合は SET FOREIGN_KEY_CHECKS = 0 / SET FOREIGN_KEY_CHECKS = 1
3. 現状の実装の課題 DISABLE/ENABLE TRIGGER ALL にSuperuser権限を必要とする ERROR: permission denied: "RI_ConstraintTrigger_*" is a system trigger DISABLE TRIGGER ALL 後にインサートされたレコードの参照整合性制約の確認は ENABLE TRIGGER ALL 時におこなわれない ERROR: insert or update on table "child" violates foreign key constraint "child_parent_fk" が上がらない https://gist.github.com/yahonda/aa137f2c86a83bd4468628b8b1cc1496
4. PostgreSQL 18でNOT ENFORCED なCHECK制約と参照 整合性制約が設定可能になった PostgreSQL 18以上であれば disable_referential_integrity メソッドを下記のよ うに書き換えられる ALTER TABLE <tablename> ALTER CONSTRAINT <fkname> NOT ENFORCED; テストデータのロード ALTER TABLE <tablename> ALTER CONSTRAINT <fkname> ENFORCED;
5. PostgreSQL 18で解決される課題 Superuser権限がなくてもテーブルオーナーであれば NOT ENFORCED や ENFORCED を実 行できる ALTER CONSTRAINT <fkname> ENFORCED 時にレコードの参照整合性制約の確認が行わ れる ERROR: insert or update on table "child" violates foreign key constraint "child_parent_fk" があがる https://gist.github.com/yahonda/34ca1e096fb2a7571354eb96c83dc729
6. 新たな課題 NOT ENFORCED -> ENFORCED すると参照整合性制約の DEFERRABLE や INITIALLY DEFERRED が戻らない pg_trigger の tgdeferrable 列と tginitdeferred 列が必ず f になる( t に戻 らない) pg_constraint の condeferrable 列と condeferred 列は t に戻る SET CONSTRAINTS <fkname> DEFERRED をおこなっても参照整合性制約をみたさない レコードがINSERT時点でエラーになってしまう ERROR: insert or update on table "child" violates foreign key constraint "child_parent_fk" DEFERRED なので、commit時点まで参照整合性制約の評価は遅延されるべき https://gist.github.com/yahonda/8d0b62b591055072dab78adf39ea0023
7. pgsql-hackers にパッチを投稿した "[PATCH] Fix unexpected loss of DEFERRABLE property after toggling NOT ENFORCED / ENFORCED" https://www.postgresql.org/message- id/CAKmOUTms2nkxEZDdcrsjq5P3b2L_PR266Hv8kW5pANwmVaRJJQ%40m ail.gmail.com 変更内容 pg_trigger の tgdeferrable 列と tginitdeferred 列が t にもどる 遅延制約の評価が意図通りCOMMIT時点になる https://gist.github.com/yahonda/3220c859c5c9cd6ec9cee17602f84ee9 この課題を解決したいので、レビューやコメントなどお願いします
8. End