PostgreSQL または Amazon Aurora PostgreSQL 互換 エディションの DB インスタンスで、Amazon Relational Database Service (Amazon RDS) の外部キーチェックを無効にしたいです。
簡単な説明
PostgreSQLには、外部キー制約を有効または無効にする直接的な関数はありません。PostgreSQL のテーブルに外部キーを作成すると、PostgreSQL はデータの整合性をチェックする内部トリガーを作成します。外部キー制約を無効にするには、PostgreSQL が作成する内部トリガーを削除するためのスーパーユーザーアクセス許可が必要です。
次のコマンドを実行し、テーブルのすべてのトリガーを無効にすると、permission denied エラーが発生します。
ALTER TABLE TestTable DISABLE TRIGGER ALL;
Error report -
ERROR: permission denied: "RI_ConstraintTrigger_c_9999181" is a system trigger
RDS for PostgreSQL と Aurora PostgreSQL 互換エディションはマネージドサービスであるため、プライマリユーザーであっても、スーパーユーザーアクセスは完全には許可されません。ALTER TABLE 'tablename' DISABLE TRIGGER ALL; コマンドは実行できません。
解決策
注: トリガーが有効になっていない場合、制約の整合性が影響を受ける可能性があります。session_replication_role 設定変数によっても、トリガーを有効にするメカニズムが影響を受ける可能性があります。origin または local のレプリケーションロールでトリガーを有効にすると、トリガーが開始されます。詳細については、PostreSQL のウェブサイトで session_replication_role (enum) について参照してください。
session_replication_role をレプリカに変更する
RDS for PostgreSQL または Aurora PostgreSQL 互換エディションの外部キー制約を無効にするには、session_replication_role パラメータを origin から replica に変更します。このパラメータを replica に変更すると、pg_restore または pgloader がテーブルにデータを挿入または更新できるようになります。
session_replication_role を replica に変更するには、次のいずれかの手順を実行します。
- pgadmin または PostgreSQL セッションを使用する場合は、内部的に参照を適用するシステムトリガーが開始することを防ぐために、次のコマンドを実行します。
postgres=> set session_replication_role = replica;
注: 変更は現在のセッションでのみ有効です。
注: 操作の完了後、session_replication_role パラメータを origin に戻します。session_replication_role を origin に戻さない場合、他のテーブルで参照整合性の問題が発生する可能性があります。
テーブルの外部キー制約を削除する
スーパーユーザーアクセス許可を使用せずにトリガーを無効にするには、次のコマンドを実行してテーブルから外部キー制約を削除します。
Alter table table_name drop constraint key_name;
該当するものでそれぞれ、table_name をデータベースのテーブル名に、key_name をテーブルのキー名に置き換えます。詳細については、PostgreSQL のウェブサイトで ALTER TABLE について参照してください。
関連情報
Amazon RDS for MySQL DB インスタンスで関数、プロシージャ、トリガーを有効にする方法を教えてください
PostgreSQL データベースを AWS Database Migration Service のターゲットとして使用する
session_replication_role での 'replica' の動作 (PostgreSQL のウェブサイト)