Início / dba / Perguntas / 285910
Accepted
Paul Draper
Asked: 2021-02-24 10:10:20 +0800 CST

Como atualizo simultaneamente chaves exclusivas no PostgreSQL?

  • 772
CREATE TABLE widget (
  id serial PRIMARY KEY,
  name text NOT NULL,
  ordinal int NOT NULL UNIQUE
);

eu tenho os dados

 id | name | ordinal 
----+------+---------
  1 | A    |       1
  2 | B    |       2
  3 | C    |       3

Eu gostaria de atualizá-lo para

 id | name | ordinal 
----+------+---------
  1 | A    |       3
  2 | B    |       2
  3 | C    |       1

Tocando nos registros o mínimo possível (ou seja, não reescreva todo o conjunto de registros, não inicie gatilhos desnecessários), qual é a abordagem geralmente aplicável para atualizar ordinalos valores de destino?

Uma atualização vanilla apenas me dá violações de restrição, mesmo que aconteça em uma única instrução.

E descartar e recriar a restrição exclusiva é uma concorrência cara e ruim.

Este parece ser um problema comum o suficiente para que deve haver uma boa maneira de fazer isso, que eu simplesmente não consigo pensar.

postgresql update

2 respostas

  1. Best Answer

    Defina a restrição como adiável:

    CREATE TABLE widget (
  id serial PRIMARY KEY,
  name text NOT NULL,
  ordinal int NOT NULL UNIQUE DEFERRABLE
);

    Então você pode atualizá-lo em uma declaração:

    update widget
  set ordinal = t.new_ordinal
from (
  values (1, 3), (3,1) 
) as t(id, new_ordinal)
where t.id = widget.id
    • 3

  2. UNIQUEUma restrição simples é NOT DEFERRABLEpor padrão. Violações únicas são verificadas após cada linha . O manual expressa isso como:

    verificado imediatamente após cada comando

    Mas é realmente verificado para cada linha escrita individual.

    Se você definir a UNIQUErestriçãoDEFERRABLE (como a_horse fornecida), as violações exclusivas serão verificadas após cada instrução . Vários CTEs dentro da mesma consulta ainda contam como uma única instrução a esse respeito.

    CREATE TABLE widget_deferrable (
  id serial PRIMARY KEY,
  ordinal int NOT NULL UNIQUE DEFERRABLE  -- !
);

    Se você também definir a restrição como DEFERRED, a verificação de violações exclusivas será adiada até depois de cada transação .

    CREATE TABLE widget_deferred (
  id serial PRIMARY KEY,
  ordinal int NOT NULL UNIQUE DEFERRABLE INITIALLY DEFERRED  --!
);

    Demonstração completa:

    db<>fique aqui

    Leitura adicional:

    • 1

relate perguntas