1

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;
  • 1
  • So, essentially every time you insert a new row, you update all the rows with same id. I presume you do this so you can easily find "current" rows with where seq = 0 but it doesn't look very efficient, even if you solve the issue with the duplicate key. – ypercubeᵀᴹ Mar 30 '18 at 21:43
  • @ypercubeᵀᴹ you are right. It is not the most efficient solution. I'm just improving an old windows application and I can't change the app. – jgrocha Mar 31 '18 at 10:21
0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.