Início / dba / Perguntas / 96961
Accepted
Piotr Findeisen
Asked: 2015-04-04 01:54:35 +0800 CST

Como desabilitar temporariamente as chaves estrangeiras no Amazon RDS PostgreSQL?

  • 772

Estou migrando o ambiente de teste existente para o Amazon RDS PostgreSQL. A estrutura de teste possui um recurso de recarregar dados em determinadas tabelas para um estado anterior. Para isso ele desativa as chaves estrangeiras, exclui os dados existentes, carrega o save state e habilita as chaves estrangeiras novamente.

Atualmente, a estrutura de teste desativa as chaves estrangeiras desativando todos os gatilhos (claro, isso requer superusuário):

alter table tablename disable trigger all;

No RDS, isso falha com:

ERRO: permissão negada: "RI_ConstraintTrigger_a_20164" é um gatilho do sistema

Como posso desabilitar temporariamente as chaves estrangeiras no Amazon RDS PostgreSQL?

Observação: uma pergunta semelhante já foi feita ( PostgreSQL no RDS: como importar dados em massa com restrições de FK? ), mas era especificamente sobre importação offline e a solução também é específica para importação offline.

postgresql foreign-key

1 respostas

  1. Best Answer

    session_replication_role

    Encontrei uma maneira alternativa de desabilitar chaves estrangeiras - https://stackoverflow.com/a/18709987

    set session_replication_role = replica;

    E reativá-los com

    set session_replication_role = default;

    Isso funciona no RDS, mas ainda requer privilégios incomuns (ou seja, não concedidos por padrão).

    soltando e recriando FKs

    A solução alternativa é, conforme sugerido nos comentários, descartar temporariamente os FKs. Isso traz a vantagem adicional de que os dados são verificados quando os FKs são reativados.

    caindo

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

    recriando

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

relate perguntas