Início / dba / Perguntas / 104987
Accepted
Petr Přikryl
Asked: 2015-06-25 03:11:05 +0800 CST

Evite violação única em transação atômica

  • 772

É possível criar uma transação atômica no PostgreSQL?

Considere que eu tenho uma categoria de tabela com estas linhas:

id|name
--|---------
1 |'tablets'
2 |'phones'

E o nome da coluna tem restrição exclusiva.

Se eu tentar:

BEGIN;
update "category" set name = 'phones' where id = 1;
update "category" set name = 'tablets' where id = 2;
COMMIT;

Estou entendendo:

ERROR:  duplicate key value violates unique constraint "category_name_key"
DETAIL:  Key (name)=(tablets) already exists.
postgresql transaction

2 respostas

  1. Best Answer

    Além do que o @Craig forneceu (e corrigindo parte dele):

    As restrições efetivas do Postgres 9.4 , UNIQUEe são verificadas imediatamente após cada linha quando definidas . Isso é diferente de outros tipos de restrições (atualmente apenas (chave estrangeira)) que são verificadas após cada instrução . Nós trabalhamos tudo isso sob esta questão relacionada no SO:PRIMARY KEYEXCLUDENOT DEFERRABLENOT DEFERRABLEREFERENCES

    Não é suficiente que uma restrição UNIQUE(or PRIMARY KEYou ) seja para fazer seu código apresentado com várias instruções funcionar.EXCLUDEDEFERRABLE

    E você não pode usar ALTER TABLE ... ALTER CONSTRAINTpara esta finalidade. Por documentação:

    ALTER CONSTRAINT

    Este formulário altera os atributos de uma restrição que foi criada anteriormente. Atualmente , apenas as restrições de chave estrangeira podem ser alteradas .

    Ênfase em negrito minha. Em vez disso, use:

    ALTER TABLE t
   DROP CONSTRAINT category_name_key
 , ADD  CONSTRAINT category_name_key UNIQUE(name) DEFERRABLE;

    Elimine e adicione a restrição de volta em uma única instrução para que não haja janela de tempo para que alguém se infiltre nas linhas ofensivas. Para tabelas grandes, seria tentador conservar o índice exclusivo subjacente de alguma forma, porque é caro excluí-lo e recriá-lo. Infelizmente, isso não parece ser possível com ferramentas padrão (se você tiver uma solução para isso, informe-nos!):

    Para uma única declaração tornar a restrição adiável é suficiente:

    UPDATE category c
SET    name = c_old.name
FROM   category c_old
WHERE  c.id     IN (1,2)
AND    c_old.id IN (1,2)
AND    c.id <> c_old.id;

    Uma consulta com CTEs também é uma única instrução:

    WITH x AS (
    UPDATE category SET name = 'phones' WHERE id = 1
    )
UPDATE category SET name = 'tablets' WHERE id = 2;

    No entanto , para o seu código com várias instruções, você (adicionalmente) precisa realmente adiar a restrição - ou defini-la como INITIALLY DEFERREDqualquer um normalmente é mais caro do que o acima. Mas pode não ser facilmente viável agrupar tudo em uma declaração.

    BEGIN;
SET CONSTRAINTS category_name_key DEFERRED;
UPDATE category SET name = 'phones'  WHERE id = 1;
UPDATE category SET name = 'tablets' WHERE id = 2;
COMMIT;

    Esteja ciente de uma limitação em conexão com as FOREIGN KEYrestrições, no entanto. Por documentação:

    As colunas referenciadas devem ser as colunas de uma restrição de chave primária ou exclusiva não adiável na tabela referenciada.

    Então você não pode ter os dois ao mesmo tempo.

    • 25

  2. Pelo que entendi, seu problema aqui é que a restrição é verificada após cada instrução, mas você deseja que seja verificada no final da transação, para comparar o estado anterior ao estado posterior, ignorando os estados intermediários.

    Nesse caso, isso é possível com uma restrição adiável .

    Consulte SET CONSTRAINTSe DEFERRABLErestrições conforme documentado em CREATE TABLE.

    Observe que as restrições adiadas têm custos - o sistema precisa manter uma lista delas para verificar no momento do commit, portanto, elas não são boas para transações que fazem grandes conjuntos de alterações. Eles também são mais lentos para verificar.

    Então eu acho que você provavelmente quer:

    ALTER TABLE mytable ALTER CONSTRAINT category_name_key DEFERRABLE;

    Observe que parece haver uma limitação na ALTER TABLEdefinição de restrições para DEFERRABLE; você pode ter que DROPrefazer ADDa restrição.

    • 13

relate perguntas