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;
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
