Postgresql – How to temporarily disable foreign keys in Amazon RDS PostgreSQL

amazon-rdsforeign keypostgresql

I'm migrating existing test environment to Amazon RDS PostgreSQL. The test framework has a feature of reloading data in certain tables to an earlier state. For this it disables foreign keys, deletes existing data, loads save state and enables foreign keys again.

Currently, the test framework disables foreign keys by disabling all triggers (of course, this requires superuser):

alter table tablename disable trigger all;

On RDS, this fails with:

ERROR: permission denied: "RI_ConstraintTrigger_a_20164" is a system trigger

How can I temporarily disable foreign keys in Amazon RDS PostgreSQL?

Note: similar question has already been asked (PostgreSQL on RDS: How to bulk import data with FK constraints?) but it was specifically about offline import and the solution is specific to offline import too.

Best Answer

session_replication_role

I found an alternative way of disabling foreign keys -- https://stackoverflow.com/a/18709987

set session_replication_role = replica;

And re-enabling them with

set session_replication_role = default;

This works on RDS but still requires unusual privileges (i.e. not granted by default).

dropping and recreating FKs

Alternative solution is, as suggested in comments, to temporarily drop the FKs. This brings additional advantage that data is verified when FKs are re-enabled.

Dropping

create table if not exists dropped_foreign_keys (
        seq bigserial primary key,
        sql text
);

do $$ declare t record;
begin
    for t in select conrelid::regclass::varchar table_name, conname constraint_name,
            pg_catalog.pg_get_constraintdef(r.oid, true) constraint_definition
            from pg_catalog.pg_constraint r
            where r.contype = 'f'
            -- current schema only:
            and r.connamespace = (select n.oid from pg_namespace n where n.nspname = current_schema())
        loop

        insert into dropped_foreign_keys (sql) values (
            format('alter table %s add constraint %s %s',
                quote_ident(t.table_name), quote_ident(t.constraint_name), t.constraint_definition));

        execute format('alter table %s drop constraint %s', quote_ident(t.table_name), quote_ident(t.constraint_name));

    end loop;
end $$;

Recreating

do $$ declare t record;
begin
    -- order by seq for easier troubleshooting when data does not satisfy FKs
    for t in select * from dropped_foreign_keys order by seq loop
        execute t.sql;
        delete from dropped_foreign_keys where seq = t.seq;
    end loop;
end $$;
Related Question