9

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.

  • Perhaps this should be a stackoverflow question? – Piotr Findeisen Apr 3 '15 at 9:55
  • Disagree - it's very clearly related to database administration. – Vérace Apr 3 '15 at 10:55
  • How do you disable FKs now? Why do you expect it to be different on RDS? Also, why not try yourself? – dezso Apr 3 '15 at 11:08
  • @dezso, thanks for the comment. Sure, I added the code that is used on non-RDS PostgreSQL. – Piotr Findeisen Apr 4 '15 at 21:41
  • Oh yes, this way it won't work. But how about dropping and recreating the FK constraints? – dezso Apr 4 '15 at 21:52
10

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 $$;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.