【PostgreSQL】全テーブルの制約を一時無効化する

はじめに
session_replication_role の設定
制約の削除/再作成
トリガの無効化/有効化
制約の遅延化

はじめに

データのインポート操作やテストデータ投入時など、制約を一時的に無効化したい場合はよくある。

一律設定レベルで変更することはできず、以下の3つの方法のいずれかを使うことになる。

session_replication_role の設定
制約の削除/再作成
トリガの無効化/有効化
制約の遅延化

順に手順を示す。

session_replication_role の設定

PostgreSQL 8.3 以上の場合で、スーパーユーザー権限がある場合は、以下で現在のセッションのすべてのトリガーを無効にできる。

SET session_replication_role = replica;

これにより、現在のセッションのトリガーが無効になる(ただし、ENABLE REPLICA TRIGGER や ENABLE ALWAYS TRIGGER で定義したトリガーは対象外)。

session_replication_role に設定可能な値は origin(デフォルト) replica local で、設定変更した場合は以前にキャッシュされたクエリプランはすべて破棄される。

同じセッションに対して有効戻すには以下とする。

SET session_replication_role = DEFAULT;

制約の削除/再作成

インポート操作やテストデータ投入などで、外部キー制約を一時的に無効化したい場合は、以下で生成したSQLで制約を削除/再作成を行う。

外部キー制約再作成用のSQL生成

copy (
select
'ALTER TABLE ' || pg_namespace.nspname || '.' || pg_class.relname || ' ADD CONSTRAINT ' || pg_constraint.conname || ' ' || pg_get_constraintdef(pg_constraint.oid) || ';'
from
    pg_constraint
    inner join pg_class ON pg_class.oid = pg_constraint.conrelid
    inner join pg_namespace ON pg_namespace.oid = pg_class.relnamespace
where
    pg_namespace.nspname != 'pg_catalog' and
    pg_namespace.nspname != 'information_schema' and
    -- pg_namespace.nspname == 'public' and
    pg_constraint.contype = 'f' -- c:検査制約 f:外部キー制約 p:プライマリキー制約 u:一意性制約 t:制約トリガ x:排他制約
order by
    case when pg_constraint.contype='f' then 0 else 1 end desc,
    pg_constraint.contype desc,
    pg_namespace.nspname desc,
    pg_class.relname desc,
    pg_constraint.conname desc
) to 'restoreConstraints.sql'
;

外部キー制約削除用のSQL生成

copy (
select
  'ALTER TABLE ' || pg_namespace.nspname || '.' || pg_class.relname || ' DROP CONSTRAINT ' || pg_constraint.conname || ';'
from
    pg_constraint
    inner join pg_class on pg_class.oid = pg_constraint.conrelid
    inner join pg_namespace on pg_namespace.oid = pg_class.relnamespace
where
    pg_namespace.nspname != 'pg_catalog' and
    pg_namespace.nspname != 'information_schema' and
    -- pg_namespace.nspname == 'public' and
    pg_constraint.contype = 'f' -- c:検査制約 f:外部キー制約 p:プライマリキー制約 u:一意性制約 t:制約トリガ x:排他制約
order by
    case when pg_constraint.contype = 'f' then 0 else 1 end,
    pg_constraint.contype,
    pg_namespace.nspname,
    pg_class.relname,
    pg_constraint.conname
) to 'dropConstraints.sql'
;

上記では、pg_constraint.contype = 'f' で外部キー制約のみを対象としている。全制約を対象にするには条件を外せば良い。

トリガの無効化/有効化

テーブルのトリガを無効化することで対応できる。

alter table <table_name> disable trigger all;

全テーブルのトリガを無効化するSQLは以下のように得ることができる。

select
    'ALTER TABLE ' || schemaname || '.' || tablename || ' DISABLE TRIGGER ALL;'
from
    pg_catalog.pg_tables
where
    schemaname != 'pg_catalog' and
    schemaname != 'information_schema'
order by
    tablename 
;

有効化SQL取得は以下で取得できる。

select
    'ALTER TABLE ' || schemaname || '.' || tablename || ' ENABLE TRIGGER ALL;'  
from
    pg_catalog.pg_tables
where
    schemaname != 'pg_catalog' and
    schemaname != 'information_schema'
order by
    tablename 
;