Updating key constraints on multiple records simultaneously

Question

We have a table with a unique key which gets updated by ‘aging’ older records, as mentioned by @Tony O’Hagan here.

The table looks as follows:

-- auto-generated definition
create table abc
(
    key   uuid    not null,
    hash  text    not null,
    age   integer not null,
    value varchar(50),
    constraint abc_pkey
        primary key (key, age)
);

We can simulate an ‘aged’ record with the following dummy data:

INSERT INTO public.abc (key, hash, age, value) VALUES ('bec619bb-451c-49d8-b555-4d16e1f724fb', 'asdf', 0, '1');
INSERT INTO public.abc (key, hash, age, value) VALUES ('bec619bb-451c-49d8-b555-4d16e1f724fb', 'asdf', 1, '2');
INSERT INTO public.abc (key, hash, age, value) VALUES ('bec619bb-451c-49d8-b555-4d16e1f724fb', 'asdf', 2, '3');

When I want to add a new record, I must first ‘age’ the older records before inserting a new record with age=0

However I get the following error message when I run the query below:

[23505] ERROR: duplicate key value violates unique constraint "abc_pkey" Detail: Key (key, age)=(bec619bb-451c-49d8-b555-4d16e1f724fb, 2) already exists.

UPDATE abc
SET age = age +1
WHERE key IN (
    'bec619bb-451c-49d8-b555-4d16e1f724fb'
    )

How can I update/age these records?

Your design is not scalable. It's not a great idea to update a thousand rows every time you insert a new one. I would suggest you keep the version number as you are currently doing. To find the latest (active?) one you can add a flag to it or you can store the "current ID" in another table. This way every insert will hit at most two rows. — The Impaler, Apr 07 '22 at 15:29
It does seem odd to have an age field that you must update on every insert. One possible alternative is to have some kind of `start_date` timestamp field instead and calculate age when you do queries. PostgreSQL has a built-in [age function](https://www.postgresql.org/docs/current/functions-datetime.html). You could also create a view for your table that calculates age on the fly. — bfris, Apr 07 '22 at 15:58

score 1 · Answer 1 · answered Apr 07 '22 at 15:41

We can disable the CONSTRAINTS with the commande

SET CONSTRAINTS ALL DEFERRED

✓

which lets us run our update

UPDATE public.abc SET age = age + 1;

3 rows affected

we can then reactivate the CONSTRAINTS with

SET CONSTRAINTS ALL IMMEDIATE

✓

Updating key constraints on multiple records simultaneously

1 Answers1