Início / dba / Perguntas / 267947
Accepted
John Bachir
Asked: 2020-05-27 18:40:48 +0800 CST

Como posso definir uma coluna como NOT NULL sem bloquear a tabela durante uma verificação de tabela?

  • 772

(A pergunta anterior era: o Postgres usará um índice de várias colunas ao definir várias colunas não nulas?)

Normalmente, quando defino uma coluna como não nula, se ela não tiver um índice, eu a adiciono primeiro, para que o postgres possa (espero) usar o índice enquanto faz a varredura da tabela enquanto bloqueia a tabela, para que a tabela seja bloqueada por um menor período de tempo.

Eu quero definir várias colunas não nulas, assim:

alter table foos
  alter column bar1 set not null
  alter column bar2 set not null
  alter column bar3 set not null
  alter column bar4 set not null;

Se eu fizer um índice de várias colunas para essas colunas, o postgres o usará ao verificar a tabela bloqueada antes de fazer essa alteração?

CREATE INDEX CONCURRENTLY my_index on foos (bar1, bar2, bar3, bar4);

E se eu fizer um índice parcial em IS NULL (ou IS NOT NULL)

CREATE INDEX CONCURRENTLY my_index on foos (bar1, bar2, bar3, bar4) where bar1 is null and bar2 is null and bar3 is null and bar4 is null;
postgresql index

2 respostas

  1. Best Answer

    Outra resposta de outro colaborador do postgresql.

    O PostgreSQL nem tentará usar nenhum índice durante a execução de "alter table set not null". Simplesmente não é implementado.

    A implementação adequada da verificação de índice é uma parte difícil. Não podemos simplesmente fazer algo como esta consulta

    select exists(select from foos where bar1 is null)

    do comando alter table por várias razões . Esse recurso exigirá um grande pedaço de código (e, provavelmente, código frágil em alguns casos extremos), muito trabalho, apenas para casos de uso limitados. Algo que os desenvolvedores não gostam. Na verdade, a comunidade pgsql-hackers não gosta de como NOT NULL é armazenado no catálogo do sistema. Ficaria mais limpo com um redesenho desta parte do catálogo. Depois disso, seria possível fazer SET NOT NULL NOT VALID com um bloqueio curto e validação de tabela sem um bloqueio exclusivo. Semelhante a alter table .. add constraint ... not valid+ alter table .. validate constraintpara restrição de verificação ou chave estrangeira. Mas esse redesenho é muito mais trabalhoso, e não há quem queira fazê-lo.

    Mas tenho uma boa notícia: no PostgreSQL 12 (e acima) escanear a tabela inteira não é a única opção. alter table set not nullpode provar a correção de NOT NULL por restrições de verificação existentes. Assim, pode-se fazer:

    -- short-time exclusive lock
alter table foos 
  add constraint foos_not_null 
  check (bar1 is not null) not valid;

-- seqscan, but without exclusive lock, concurrent sessions can read/write
alter table foos validate constraint foos_not_null;

-- exclusive lock, but foos_not_null proves: there is no NULL in this column
-- so this alter table would be fast
alter table foos alter column bar1 set not null;
-- not needed anymore
alter table foos drop constraint foos_not_null;

    Esse foi o meu patch . Sim, isso parece uma solução alternativa. Mas, felizmente, ele foi mesclado e agora é possível set not nullsem um longo bloqueio exclusivo.

    • 16

  2. Observei o código-fonte (função ATRewriteTableem src/backend/commands/tablecmds.c), e o PostgreSQL sempre usa uma varredura sequencial da tabela para verificar as NOT NULLrestrições.

    Portanto, criar índices não acelerará a execução.

    • 4

relate perguntas