主页 / dba / 问题 / 202711
Accepted
jgrocha
Asked: 2018-03-31 11:26:04 +0800 CST

触发器内的 Postgresql 更新表：重复键值违反唯一约束

  • 772

对于给定的表，我想在我的数据库中拥有所有以前记录的副本。我只在表中插入数据。

我有一个额外的列seq来知道哪个是活动记录。

所以，我seq = 0用于当前记录。每seq < 0一张唱片都是旧唱片。

这是表格（我省略了一些列以使其更简单）

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) );

让我展示一些记录：

"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"

如果我需要插入一条新记录，我会这样做：

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

接着：

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

但他的不起作用。有时，更新失败并出现以下错误：

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

如果更新每次都逐行更改，那么它可能会中断。如果以正确的顺序进行更新（从最小数量开始），则更新将起作用。

如何通过 seq 降序进行更新顺序？

我为此写了一个触发器，但它有时会失败。

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 个回答

  1. Best Answer

    谢谢@a_horse_with_no_name。这DEFERRED是诀窍。

    我能够解决这个问题，将触发功能更改为：

    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

相关问题