簡単な例
テーブルtestとtest_masterを用意し、testからtest_masterに外部キー制約を付与する。
このとき、外部キー制約が参照するユニークインデックスとしてtest_master_idを作成する。
CREATE TABLE test_master AS
SELECT id FROM generate_series(1, 1000) id;
CREATE TABLE test AS
SELECT num % 1000 + 1 id FROM generate_series(1, 100000) num;
CREATE UNIQUE INDEX test_master_id ON test_master (id);
ALTER TABLE test ADD FOREIGN KEY (id) REFERENCES test_master (id);
外部キー制約から参照されるインデックスtest_master_idを、以下の手順でREINDEXする。
- 同じ定義だが別名のインデックスをCONCURRENTLYオプション付きで作成する。
- 古い外部キー制約の削除と新しい外部キー制約の作成を同一トランザクション内で実行する。
- 古いインデックスとそれを参照する外部キー制約を削除する。
- 新しいインデックスを参照する外部キー制約をNOT VALIDオプション付きで作成する。
- インデックス名を正式な名前に変更する。
- NOT VALIDの外部キー制約をオンラインで検証する。
実際のコマンド例は以下のとおり。
CREATE UNIQUE INDEX CONCURRENTLY test_master_id_tmp ON test_master (id);
BEGIN;
DROP INDEX test_master_id CASCADE;
ALTER TABLE test ADD FOREIGN KEY (id) REFERENCES test_master (id) NOT VALID;
COMMIT;
ALTER INDEX test_master_id_tmp RENAME TO test_master_id;
ALTER TABLE test VALIDATE CONSTRAINT test_id_fkey;
pgbenchの例
bidカラムについて、pgbench_accountsからpgbench_branchesに外部キー制約を作成する。
この外部キー制約は、pgbench_branchesの主キー制約のインデックスpgbench_branches_pkeyを参照することに注意。
ALTER TABLE pgbench_accounts
ADD FOREIGN KEY (bid) REFERENCES pgbench_branches (bid);
REINDEX対象のインデックスが、主キー制約と外部キー制約それぞれから参照される場合は、DROP INDEXの代わりにALTER TABLE DROP CONSTRAINTで古いインデックスを削除する。
実際のコマンド例は以下のとおり。
CREATE UNIQUE INDEX CONCURRENTLY pgbench_branches_pkey_tmp ON pgbench_branches (bid);
BEGIN;
-- pgbenchの試験シナリオでは、pgbench_accounts → pgbench_branchesの順番で処理されることから、
-- その処理とdeadlockを引き起こさないように、ここでは最初にpgbench_accountsに対してロックを獲得する。
LOCK TABLE pgbench_accounts IN ACCESS EXCLUSIVE MODE;
ALTER TABLE pgbench_branches
DROP CONSTRAINT pgbench_branches_pkey CASCADE,
ADD PRIMARY KEY USING INDEX pgbench_branches_pkey_tmp;
ALTER TABLE pgbench_accounts
ADD FOREIGN KEY (bid) REFERENCES pgbench_branches (bid) NOT VALID;
COMMIT;
ALTER INDEX pgbench_branches_pkey_tmp RENAME TO pgbench_branches_pkey;
ALTER TABLE pgbench_accounts VALIDATE CONSTRAINT pgbench_accounts_bid_fkey;