ぱと隊長日誌

ブログ運用もエンジニアとしての生き方も模索中

PostgreSQLはトランザクション内で制約を一旦外して戻すことができる

データベース

はじめに

こんな tweet を拝見しました。

遅延制約(DEFERRABLE)を使えば制約を一旦外す必要がないのですが、技術的な可否が気になり、調べた結果をまとめます。

ドキュメント調査

PostgreSQL 11 のマニュアルには以下の記載があります。

DDLコマンドの中には、現在はTRUNCATEとテーブルを書き換える形のALTER TABLEだけですが、MVCCセーフでないものがあります。 これは、DDLコマンドをコミットする前に取得したスナップショットを使っていると、切り詰めまたは書き換えのコミット後に、同時実行トランザクションに対してテーブルが空に見えることを意味しています。

13.5. 警告

この具体例は以下の記事を参照ください。
PostgreSQL のトランザクション & MVCC & スナップショットの仕組み

マニュアルには『テーブルを書き換える形のALTER TABLE』とあり、『制約を書き換えるだけの ALTER TABLE』は対象外に思えますが、動作検証で試してみます。『テーブルが空に見える』ということなので、空に見えなければOKとします。

動作検証

PostgreSQL 11.4 で確認しました。

検証手順は遅延制約について解説した記事での手順をベースにしています。
PostgreSQLで遅延制約を使って一意制約カラムを一括更新する - Qiita

これに対して制約のDROP/ADDを追加するとともに、MVCCセーフであるか（テーブルが空に見えないか）？を検証する手順としました。

手順では2つのトランザクションを用いており、TX1/TX2で記載しています。

-- TX1

=# CREATE TABLE posts (id integer, CONSTRAINT posts_pk PRIMARY KEY (id));

=# INSERT INTO posts VALUES(1);
=# INSERT INTO posts VALUES(2);
=# INSERT INTO posts VALUES(3);

=# BEGIN ISOLATION LEVEL SERIALIZABLE;

=# SELECT txid_current();

-- TX2

=# BEGIN ISOLATION LEVEL SERIALIZABLE;

=# SELECT txid_current();

=# ALTER TABLE posts DROP CONSTRAINT posts_pk;

=# UPDATE posts SET id = id + 1;

=# ALTER TABLE posts ADD CONSTRAINT posts_pk PRIMARY KEY (id);

=# COMMIT;

=# SELECT * FROM posts ORDER BY posts;
 id
----
  2
  3
  4
(3 rows)

-- TX1

=# SELECT * FROM posts ORDER BY posts;
 id
----
  1
  2
  3
(3 rows)

PostgreSQLはトランザクション内で「一旦制約を外す ⇒ 操作する ⇒ 制約を戻す」ことが可能であるとわかりました。
また、SELECT結果からトランザクションの順序 (TX1 → TX2) も想定通りであることが確認できました。

注意点

動作検証のTX1の最後の状態でpsqlの\dコマンド（オブジェクトの概要表示）を対象のテーブルに使うとエラーになります。

=# \d posts
ERROR:  cache lookup failed for index 0
STATEMENT:  SELECT c2.relname, i.indisprimary, i.indisunique, i.indisclustered, i.indisvalid, pg_catalog.pg_get_indexdef(i.indexrelid, 0, true),
    pg_catalog.pg_get_constraintdef(con.oid, true), contype, condeferrable, condeferred, i.indisreplident, c2.reltablespace
  FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i
    LEFT JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid AND conindid = i.indexrelid AND contype IN ('p','u','x'))
  WHERE c.oid = '16537' AND c.oid = i.indrelid AND i.indexrelid = c2.oid
  ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname;

今回の記事の目的からは若干外れるので深追いしていませんが、PRIMARY KEY 制約を DROP/ADD したことで index が再生成され、トランザクション分離レベルに従わない関数によってエラーを起こしたのではないかとみています。
PostgreSQLの一部の関数はトランザクション分離レベルに従わない - ぱと隊長日誌

まとめ

PostgreSQLは制約の操作(ADD/DROP)もトランザクションに含めることができます。
ただし、全てのDDLコマンドや関数がMVCCセーフとは限らない点に注意が必要です。
実務では制約を操作するのではなく、遅延制約での実現を検討するのが良いでしょう。