How can I turn off foreign keys or triggers in Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL-Compatible Edition?

Short description

PostgreSQL doesn't provide a direct function to turn on or off foreign key constraints. When you create a foreign key on a table in PostgreSQL, PostgreSQL creates an internal trigger to check for data integrity. To turn off foreign key constraints, you must have superuser permissions to delete the internal triggers that PostgreSQL creates.

When you run the following command that turns off all triggers on a table, you receive a permission denied error:

ALTER TABLE TestTable DISABLE TRIGGER ALL;
Error report -
ERROR: permission denied: "RI_ConstraintTrigger_c_9999181" is a system trigger

Because RDS for PostgreSQL and Aurora PostgreSQL-Compatible Edition are managed services, they don't allow all superuser permissions, even to the primary user. The ALTER TABLE 'tablename' DISABLE TRIGGER ALL; command isn't allowed.

Resolution

Note: If the trigger isn't turned on, then the integrity of the constraint might be affected. Also, the session_replication_role configuration variable can affect the mechanism that activates the trigger. When you turn on triggers for a replication role that's origin or local, the triggers initiate. For more information, see session_replication_role (enum) on the PostreSQL website. 

Change session_replication_role to replica

To turn off the foreign key constraints in RDS for PostgreSQL or Aurora PostgreSQL-Compatible Edition, change the session_replication_role parameter from origin to replica. When you change this parameter to replica, you allow the pg_restore or pgloader to insert or update the data on the table. 

To change session_replication_role to replica, use one of these options:

• If you use the pgadmin or PostgreSQL session, then run the following command so that the system triggers that internally enforce referential aren't initiated:

postgres=> set session_replication_role = replica;

Note: The change is valid only for the current session.

• Modify the session_replication_role database parameter in the corresponding database parameter group for your Amazon RDS or Aurora DB instance.

Note: After you complete the operation, revert the session_replication_role parameter to origin. If you don't change back session_replication_role to origin, then you might experience referential integrity issues in your other tables.

When you change session_replication_role to replica, you can't run the ALTER TABLE 'table_name' DISABLE TRIGGER ALL; command. The operation fails, but the command still stops the trigger at either the session or instance level.

Drop the foreign key constraint on the table

To turn off a trigger without superuser permissions, run the following command to drop the foreign key constraint on the table:

Alter table table_name drop constraint key_name;

Replace table_name with the table name from your database and key_name with the key name from your table. For more information, see ALTER TABLE on the PostgreSQl website.

Related information

How do I turn on functions, procedures, and triggers for my Amazon RDS for MySQL DB instance?

Using a PostgreSQL database as a target for AWS Database Migration Service

session_replication_role 'replica' behavior on the PostreSQL website