Início / dba / Perguntas / 202711
Accepted
jgrocha
Asked: 2018-03-31 11:26:04 +0800 CST

Tabela de atualização do Postgresql no gatilho: valor de chave duplicado viola restrição exclusiva

  • 772

Para uma determinada tabela, quero ter uma cópia de todos os registros anteriores em meu banco de dados. Eu só insiro dados na tabela.

Tenho uma coluna adicional seqpara saber qual é o registro ativo .

Então, eu uso seq = 0para o registro atual. Cada registro com seq < 0é um antigo.

Esta é a tabela (eu omiti algumas colunas para simplificar)

CREATE TABLE public.machine (
    id character varying(25) NOT NULL,
    seq integer NOT NULL,
    vendor character varying(50),
    model character varying(50),
    CONSTRAINT pk_machine PRIMARY KEY (id, seq) );

Deixe-me mostrar alguns registros:

"cat-1" 0   "Caterpillar"   "D3K2 TIER 4 FINAL"
"cat-1" -1  "Caterpillar"   "D3K2 TIER 4 FINAL"
"cat-1" -2  "Caterpillar"   "D3K2 TIER 4 FINAL"
"cat-1" -3  "Caterpillar"   "D3K2 TIER 4 FINAL"
"cat-1" -4  "Caterpillar"   "D3K2 TIER 4 FINAL"
"cat-1" -5  "Caterpillar"   "D3K2 TIER 4 FINAL"

Se eu precisar inserir um novo registro, eu faço:

UPDATE machine SET seq = seq-1 where id = 'cat-1';

e depois:

insert into machine (id, seq, vendor, model) values ( 'cat-1', 0, 'Caterpillar', 'D3K2 TIER 4 FINAL');

Mas o dele não funciona. Algumas vezes, a atualização falha com o erro:

ERROR:  duplicate key value violates unique constraint "pk_machine"
DETAIL:  Key (id, seq)=(cat-2, 0) already exists.

Se a atualização mudar linha por linha, a cada vez, ela poderá quebrar. Se a atualização for feita na ordem correta (começando pelo número mínimo), a atualização funcionará.

Como faço a ordem de atualização por seq descendente?

Eu escrevi um gatilho para isso, mas ele falha algumas vezes.

CREATE TRIGGER make_history
    BEFORE INSERT
    ON public.machine
    FOR EACH ROW
    EXECUTE PROCEDURE public.history();

CREATE OR REPLACE FUNCTION history() RETURNS TRIGGER AS $$
BEGIN
    UPDATE machine SET seq = seq-1 where id = NEW.id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
postgresql

1 respostas

  1. Best Answer

    Obrigado @a_horse_with_no_name. O DEFERREDera o truque.

    Consegui resolver esse problema alterando a função de gatilho para:

    CREATE OR REPLACE FUNCTION history() RETURNS TRIGGER AS $$
BEGIN
    SET CONSTRAINTS ALL DEFERRED;
    UPDATE machine SET seq = seq-1 where id = NEW.id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
    • 1

relate perguntas