Desabilite todas as restrições e verificações de tabela ao restaurar um despejo

Então você procura outras tabelas em uma CHECKrestrição .

CHECKrestrições devem executar IMMUTABLEverificações. O que passa OK para uma linha de uma vez deve passar OK a qualquer momento. É assim que as CHECKrestrições são definidas no padrão SQL. Essa também é a razão dessa restrição no manual :

Atualmente, CHECKas expressões não podem conter subconsultas nem fazer referência a variáveis ​​que não sejam colunas da linha atual.

Ainda assim, as expressões em CHECKrestrições podem usar funções, mesmo funções definidas pelo usuário. Esses devem ser IMMUTABLE, mas o Postgres não impõe isso atualmente. De acordo com esta discussão relacionada em pgsql-hackers , uma razão é permitir referências à hora atual, que não é IMMUTABLEpor natureza.

Mas você está procurando linhas de outra tabela, o que viola completamente como as CHECKrestrições devem funcionar. Não me surpreende que pg_dumpnão forneça isso.

Mova seu cheque em outra tabela para um gatilho (que é a ferramenta certa), e deve funcionar com versões modernas do Postgres.

PostgreSQL 9.2 ou posterior

Embora o acima seja verdadeiro para qualquer versão do Postgres, várias ferramentas foram introduzidas com o Postgres 9.2 para ajudar com sua situação:

opção pg_dump--exclude-table-data

Uma solução simples seria despejar o banco de dados sem dados para a tabela violadora com:

--exclude-table-data=my_schema.my_tbl

Em seguida, anexe apenas os dados para esta tabela no final do dump com:

--data-only --table=my_schema.my_tbl

Mas podem ocorrer complicações com outras restrições na mesma tabela. Existe uma solução ainda melhor :

Solução:NOT VALID

Até o Postgres 9.1, o NOT VALIDmodificador estava disponível apenas para restrições FK. Isso foi estendido para CHECKrestrições no Postgres 9.2. O manual:

Se a restrição estiver marcada NOT VALID, a verificação inicial potencialmente longa para verificar se todas as linhas da tabela satisfazem a restrição será ignorada. A restrição ainda será aplicada contra inserções ou atualizações subsequentes [...]

Um arquivo de despejo simples do Postgres consiste em três "seções":

• pre_data
• data
• post-data

^{O Postgres 9.2 também introduziu uma opção para despejar seções separadamente com -- section=sectionname, mas isso não está ajudando com o problema em questão.}

Aqui é onde fica interessante. O manual:

Os itens pós-dados incluem definições de índices, gatilhos, regras e restrições além das restrições de verificação validadas . Os itens de pré-dados incluem todos os outros itens de definição de dados.

Minha ênfase em negrito.
Você pode alterar a CHECKrestrição incorreta para NOT VALID, que move a restrição para a post-dataseção. Solte e recrie:

ALTER TABLE a
  DROP CONSTRAINT a_constr_1
, ADD  CONSTRAINT a_constr_1 CHECK (fail_if_b_empty()) NOT VALID;

Uma única instrução é mais rápida e exclui condições de corrida com transações simultâneas. (Dois comandos em uma única transação também funcionariam.)

Isso deve resolver seu problema. Você pode até deixar a restrição nesse estado , já que isso reflete melhor o que ela realmente faz: verifica novas linhas, mas não dá garantias para os dados existentes. Não há nada de errado com uma NOT VALIDrestrição de verificação. Se preferir, pode validá-lo mais tarde:

ALTER TABLE a VALIDATE CONSTRAINT a_constr_1;

Mas então você está de volta ao status quo ante.

Parece que isso se deve à forma como pg_dumpcria o dump. Olhando para o dump real, vi que a CHECKrestrição estava presente no arquivo de dump usando a sintaxe que faz parte do CREATE TABLEcomando:

CREATE TABLE a (
    i integer NOT NULL,
    CONSTRAINT a_constr_1 CHECK (fail_if_b_empty())
);      

Isso cria a falha na restauração do banco de dados, pois a verificação é feita antes que a tabela aou a tabela bcontenham quaisquer dados. Se, no entanto, o arquivo de despejo for editado e CHECKadicionado usando a seguinte sintaxe, no final do arquivo de despejo:

ALTER TABLE a ADD CONSTRAINT a_constr_1 CHECK (fail_if_b_empty()); 

... então não há problema na restauração.

A mesma lógica exata pode ser implementada usando um TRIGGERcomo no script a seguir:

CREATE OR REPLACE FUNCTION fail_if_b_empty (
    ) RETURNS BOOLEAN AS $$
    SELECT EXISTS (SELECT 1 FROM b)
$$ LANGUAGE SQL;

DROP TABLE IF EXISTS a;

CREATE TABLE IF NOT EXISTS a (
    i   INTEGER   NOT NULL
);

INSERT INTO a(i) VALUES (0),(1);

CREATE TABLE IF NOT EXISTS b (
    i  INTEGER NOT NULL
);

INSERT INTO b(i) VALUES (0);

CREATE TRIGGER tr1 AFTER INSERT OR UPDATE ON a
FOR EACH ROW
EXECUTE PROCEDURE fail_if_b_empty();  

Neste caso, no entanto, pg_dumpcria (por padrão) o gatilho no final do arquivo de despejo (e não na CREATE TABLEinstrução como no caso de uma verificação) e assim a restauração é bem-sucedida.