Início / dba / Perguntas / 343791
Accepted
Ruud van den Boomen
Asked: 2024-11-22 20:35:54 +0800 CST

Você precisa de NOT VALID ao adicionar uma nova coluna de chave estrangeira

  • 772

Sei que adicionar uma restrição de chave estrangeira requer uma varredura de tabela e um SHARE ROW EXCLUSIVEbloqueio em ambas as tabelas.

Para evitar a possível varredura de tabela longa, a restrição pode ser adicionada com a NOT VALIDabordagem. Mas estou pensando, ao adicionar uma nova coluna, você também deve usar NOT VALIDou o Postgres é inteligente o suficiente para reconhecer que é uma nova coluna e, portanto, a tabela inteira não precisa ser varrida?

Estou usando Django, e o SQL gerado para adicionar uma coluna de chave estrangeira se parece com isto:

ALTER TABLE
    "example"
ADD
    COLUMN "new_column_id" integer NULL CONSTRAINT "example_new_column_id_fk" REFERENCES "another_table"("id") DEFERRABLE INITIALLY DEFERRED;

SET
    CONSTRAINTS "example_new_column_id_b781b6be_fk" IMMEDIATE;
postgresql

1 respostas

  1. Best Answer

    NOT VALIDsó pode ser adicionado a ALTER TABLE ... ADD CONSTRAINT, não ALTER TABLE ... ADD COLUMNdiretamente. O script SQL válido seria (possível em uma única transação):

    ALTER TABLE example
ADD COLUMN new_column_id integer NULL;

ALTER TABLE example
ADD CONSTRAINT example_new_column_id_fk FOREIGN KEY (new_column_id) REFERENCES another_table(id) DEFERRABLE INITIALLY DEFERRED NOT VALID;

    Testei isso no Postgres 16 bloqueando a tabela de destino no ROW EXCLUSIVEmodo - o bloqueio mais leve que entra em conflito com SHARE ROW EXCLUSIVEaquele que cria uma restrição FK assume na tabela de destino - em uma transação:

    BEGIN;
LOCK TABLE usr IN SHARE ROW EXCLUSIVE MODE;

-- waiting idle in transaction ...

-- ROLLBACK;  -- later

    Então executei o script acima em uma segunda transação simultânea.

    O resultado: A segunda transação teve que esperar até que a primeira transação liberasse o ROW EXCLUSIVEbloqueio - com ou sem NOT VALID. Então, adicionar o FK leva a um SHARE ROW EXCLUSIVEbloqueio na tabela de destino em qualquer caso.

    Pelo que Laurenz comentou, a verificação a seguir é muito rápida ou não é realizada devido a valores ausentes na coluna de referência.

    Então:

    • Se o problema for o custo ou a duração do bloqueio, eu diria que NOT VALIDele é desnecessário neste caso específico.
    • Se a fechadura em si for o problema, NOT VALIDinfelizmente não há como evitá-la.
    • 1

relate perguntas