Postgresql update table within trigger: duplicate key value violates unique constraint

Question

For a given table, I want to have a copy of all previous records in my database. I only insert data in the table.

I have an additional column seq to know which is the active record.

So, I use seq = 0 for the current record. Every record with seq < 0 is an old one.

This is the table (I ommit some columns to make it simpler)

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

Let me show some records:

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

If I need to insert a new record, I do:

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

and then:

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

But his does not work. Some times, the update fails with the error:

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

If the update changes row by row, each of a time, then it might break. If the update is made in the right order (starting from the minimum number), the update would work.

How do I do the update order by seq descending?

I wrote a trigger to to this, but it fails some times.

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;

Answer 1

Thanks @a_horse_with_no_name. The DEFERRED was the trick.

I was able to solve this issue changing the trigger function to:

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;