Início / dba / Perguntas / 166082
Accepted
jcristovao
Asked: 2017-03-04 02:57:26 +0800 CST

Índice exclusivo adiável no postgres

  • 772

Olhando para a documentação do postgres para alter table , parece que as restrições regulares podem ser marcadas como DEFERRABLE(mais concretamente, INITIALLY DEFERRED, que é o que me interessa).

Os índices também podem ser associados a uma restrição, desde que:

O índice não pode ter colunas de expressão nem ser um índice parcial

O que me leva a acreditar que atualmente não há como ter um índice único com condições, como:

CREATE UNIQUE INDEX unique_booking
  ON public.booking
  USING btree
  (check_in, check_out)
  WHERE booking_status = 1;

Ser INITIALLY DEFERRED, ou seja, que a 'restrição' de exclusividade só será verificada ao final da transação (se SET CONSTRAINTS ALL DEFERRED;for utilizada).

Minha suposição está correta e, em caso afirmativo, existe alguma maneira de alcançar o comportamento pretendido?

Obrigado

postgresql index

2 respostas

  1. Best Answer

    Um índice não pode ser adiado - não importa se é UNIQUEou não, parcial ou não, apenas uma UNIQUErestrição. Outros tipos de restrições ( FOREIGN KEY, PRIMARY KEY, EXCLUDE) também são adiáveis ​​- mas não CHECKrestrições.

    Portanto, o índice parcial exclusivo (e a restrição implícita que ele implementa) será verificado em cada instrução (e, de fato, após cada inserção/atualização de linha na implementação atual), não no final da transação.

    O que você pode fazer, se quiser implementar essa restrição como adiável, é adicionar mais uma tabela no design. Algo assim:

    CREATE TABLE public.booking_status
  ( booking_id int NOT NULL,               -- same types
    check_in timestamp NOT NULL,           -- as in  
    check_out timestamp NOT NULL,          -- booking
    CONSTRAINT unique_booking
        UNIQUE (check_in, check_out)
        DEFERRABLE INITIALLY DEFERRED,
    CONSTRAINT unique_booking_fk
        FOREIGN KEY (booking_id, check_in, check_out)
        REFERENCES public.booking (booking_id, check_in, check_out)
        DEFERRABLE INITIALLY DEFERRED
  ) ;

    Com este design e supondo que booking_statustenha apenas 2 opções possíveis (0 e 1), você pode removê-lo inteiramente de booking(se houver uma linha em booking_status, é 1, se não for 0).

    Outro método seria (ab) usar uma EXCLUDErestrição:

    ALTER TABLE booking
    ADD CONSTRAINT unique_booking
        EXCLUDE 
          ( check_in  WITH =, 
            check_out WITH =, 
            (CASE WHEN booking_status = 1 THEN TRUE END) WITH =
          ) 
        DEFERRABLE INITIALLY DEFERRED ;

    Testado em dbfiddle .

    O que o acima faz:

    • A CASEexpressão se torna NULLquando booking_statusé nulo ou diferente de 1. Poderíamos escrever (CASE WHEN booking_status = 1 THEN TRUE END)como (booking_status = 1 OR NULL)se isso deixasse mais claro.

    • As restrições exclusivas e de exclusão aceitam linhas em que uma ou mais das expressões é NULL. Portanto, ele atua como um índice filtrado com WHERE booking_status = 1.

    • Todos os WITHoperadores são =assim que atua como uma UNIQUErestrição.

    • Esses dois combinados fazem com que a restrição atue como um índice exclusivo filtrado.

    • Mas é uma restrição e as EXCLUDErestrições podem ser adiadas.

    Uma melhoria do método acima (thnx para Denis Ryzhkov ) é usar uma restrição EXCLUDE parcial (filtrada). Usa menos espaço (da mesma forma que um índice parcial) e é adiável:

    ALTER TABLE booking
    ADD CONSTRAINT unique_booking
        EXCLUDE 
          ( check_in  WITH =, 
            check_out WITH =
          ) 
        WHERE (booking_status = 1)
        DEFERRABLE INITIALLY DEFERRED ;

    Testado em dbfiddle-2 .

    • 22

  2. Embora os anos dessa pergunta tenham se passado, gostaria de esclarecer para os falantes de espanhol, os testes foram feitos no Postgres:

    A seguinte restrição foi adicionada a uma tabela de 1337 registros, onde o kit é a chave primária:

    **Bloque 1**
ALTER TABLE ele_kitscompletos
ADD CONSTRAINT unique_div_nkit
PRIMARY KEY (div_nkit)

    Isso cria uma chave primária padrão NOT DEFERRED para a tabela, portanto, ao tentar a próxima UPDATE, recebemos o erro:

    update ele_kitscompletos
set div_nkit = div_nkit + 1;

    ERRO: a chave duplicada viola a restrição de exclusividade «unique_div_nkit»

    No Postgres, executar um UPDATE para cada ROW verifica se a RESTRICTION ou CONSTRAINT foi atendida.

    O CONSTRAINT IMMEDIATE agora é criado e cada instrução é executada separadamente:

    ALTER TABLE ele_kitscompletos
ADD CONSTRAINT unique_div_nkit
PRIMARY KEY (div_nkit)
DEFERRABLE INITIALLY IMMEDIATE

**Bloque 2**
BEGIN;   
UPDATE ele_kitscompletos set div_nkit = div_nkit + 1;
INSERT INTO public.ele_kitscompletos(div_nkit, otro_campo)
VALUES 
  (1338, '888150502');
COMMIT;

    Consulta OK, 0 linhas afetadas (tempo de execução: 0 ms; tempo total: 0 ms) Consulta OK, 1328 linhas afetadas (tempo de execução: 858 ms; tempo total: 858 ms) ERRO: llave duplicada viola restriction de unicidad «unique_div_nkit» DETALHE : Você existe la llave (div_nkit)=(1338).

    Aqui o SI permite alterar a chave primária, pois executa toda a primeira frase completa (1328 linhas); mas apesar de estar em transação (BEGIN), o CONSTRAINT é validado imediatamente ao terminar cada frase sem ter feito COMMIT, portanto gera o erro ao executar o INSERT. Por fim criamos a CONSTRAINT DEFERRED faça o seguinte:

    **Bloque 3**
ALTER TABLE public.ele_edivipol
DROP CONSTRAINT unique_div_nkit RESTRICT;   

ALTER TABLE ele_edivipol
ADD CONSTRAINT unique_div_nkit
PRIMARY KEY (div_nkit)
DEFERRABLE INITIALLY DEFERRED

    Se executarmos cada instrução do **Bloco 2**, cada sentença separadamente, nenhum erro é gerado para o INSERT, pois ele não valida, mas o COMMIT final é executado onde encontrar uma inconsistência.

    Para informações completas em inglês sugiro que verifique os links:

    Restrições SQL adiáveis ​​em profundidade

    NÃO DIFERÍVEL versus DIFERÍVEL INICIALMENTE IMEDIATO

    • 2

relate perguntas