spi/timetravel: unique constraint violation on UPDATE

From: postgresql(at)get-experience(dot)com
To: pgsql-general(at)postgresql(dot)org
Subject: spi/timetravel: unique constraint violation on UPDATE
Date: 2017-07-15 18:56:27
Message-ID: abe589cc-8189-727f-4f61-42773ae90ce6@get-experience.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hey,

I'm having a problem with the timetravel extension. Following simple schema:

CREATE EXTENSION timetravel;

CREATE TABLE entries (
id SERIAL NOT NULL,
value INT NOT NULL,
valid_from ABSTIME NOT NULL,
valid_to ABSTIME NOT NULL,

PRIMARY KEY (id, valid_from),
UNIQUE (id, valid_to)
);

CREATE TRIGGER time_travel
BEFORE INSERT OR UPDATE OR DELETE ON entries
FOR EACH ROW
EXECUTE PROCEDURE timetravel(valid_from, valid_to);

I need to be able to reference specific versions of an entry, as well as
the always-last version. That's why I'm using a primary key and a unique
constraint/index here. (PK for specific version, unique index to be able
to refer to id+valid_to='infinity').

Now if I try updating a row:

database=# INSERT INTO entries (value) VALUES (12);
INSERT 0 1
database=# SELECT * FROM entries;
id | value | valid_from | valid_to
----+-------+------------------------+----------
1 | 12 | 2017-07-11 17:11:51+02 | infinity
(1 row)

database=# UPDATE entries SET value=14 WHERE id=1;
ERROR: duplicate key value violates unique constraint "entries_pkey"
DETAIL: Key (id, valid_from)=(1, 2017-07-11 17:11:51+02) already
exists.
CONTEXT: SQL statement "INSERT INTO entries VALUES ( $1,$2,$3,$4)"

What am I doing wrong here? According to the function comment in
timetravel.c it should:
a) set last-version row to NEW data; valid_from=now()
b) insert a new row with OLD data; valid_to=now() - at this point the
old valid_from is already supposed to be updated.

> 1. IF an update affects tuple with stop_date eq INFINITY
> then form (and return) new tuple with start_date eq current date
> and stop_date eq INFINITY [ and update_user eq current user ]
> and all other column values as in new tuple, and insert tuple
> with old data and stop_date eq current date
> ELSE - skip updating of tuple.
Source:
https://github.com/postgres/postgres/blob/master/contrib/spi/timetravel.c#L49

Or is INSERT done before UPDATE? Then how can I use UNIQUE constraints
here? I can't make them INITIALLY DEFERRED because I need to be able to
use foreign keys.

I also have some debug logs:

[47-1] DEBUG: 00000: StartTransactionCommand
[47-2] LOCATION: start_xact_command, postgres.c:2442
[47-3] STATEMENT: UPDATE entries SET value=2 WHERE id=1;
[48-1] DEBUG: 00000: StartTransaction
[48-2] LOCATION: ShowTransactionState, xact.c:4929
[48-3] STATEMENT: UPDATE entries SET value=2 WHERE id=1;
[49-1] DEBUG: 00000: name: unnamed; blockState: DEFAULT; state:
INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
[49-2] LOCATION: ShowTransactionStateRec, xact.c:4967
[49-3] STATEMENT: UPDATE entries SET value=2 WHERE id=1;
[50-1] LOG: 00000: statement: UPDATE entries SET value=2 WHERE id=1;
[50-2] LOCATION: exec_simple_query, postgres.c:946
[51-1] DEBUG: 00000: ProcessQuery
[51-2] LOCATION: ProcessQuery, pquery.c:168
[51-3] STATEMENT: UPDATE entries SET value=2 WHERE id=1;
[52-1] DEBUG: 00000: timetravel (entries) update: sql: INSERT INTO
entries VALUES ( $1,$2,$3,$4)
[52-2] LOCATION: timetravel, timetravel.c:336
[52-3] STATEMENT: UPDATE entries SET value=2 WHERE id=1;
[53-1] ERROR: 23505: duplicate key value violates unique constraint
"entries_pkey"
[53-2] DETAIL: Key (id, valid_from)=(1, 2017-07-15 16:50:59+02) already
exists.
[53-3] CONTEXT: SQL statement "INSERT INTO entries VALUES ( $1,$2,$3,$4)"
[53-4] LOCATION: _bt_check_unique, nbtinsert.c:424
[53-5] STATEMENT: UPDATE entries SET value=2 WHERE id=1;

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2017-07-15 21:15:33 Re: spi/timetravel: unique constraint violation on UPDATE
Previous Message dilaz03 . 2017-07-15 13:56:32 Planner statistics usage for composite type