Обсуждение: [GENERAL] spi/timetravel: unique constraint violation on UPDATE

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;

postgresql@get-experience.com writes:
> I'm having a problem with the timetravel extension. Following simple schema:
> ...
> 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.

I don't see anywhere in that comment that says "the old valid_from is
already supposed to be updated".  Given that the INSERT of the cloned row
happens during the BEFORE UPDATE trigger, it would take a lot of magic
for things to happen that way ;-).

Perhaps you could make your PK be on (id, valid_from, valid_to).

            regards, tom lane

Den 15. juli 2017 23:15, skrev Tom Lane:
> postgresql@get-experience.com writes:
>> I'm having a problem with the timetravel extension. Following simple schema:
>> ...
>> 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.
>
> I don't see anywhere in that comment that says "the old valid_from is
> already supposed to be updated".  Given that the INSERT of the cloned row
> happens during the BEFORE UPDATE trigger, it would take a lot of magic
> for things to happen that way ;-).
Yeah, good point.

> Perhaps you could make your PK be on (id, valid_from, valid_to).
Doesn't really work because valid_to would change on UPDATE. I'd need to
update foreign relations with another trigger which would be very ugly.


I guess the only solution is to write my own time travel trigger
function and do update before insert...

postgresql@get-experience.com writes:
> Den 15. juli 2017 23:15, skrev Tom Lane:
>> Perhaps you could make your PK be on (id, valid_from, valid_to).

> Doesn't really work because valid_to would change on UPDATE. I'd need to
> update foreign relations with another trigger which would be very ugly.

Um ... wouldn't ON UPDATE CASCADE help?

            regards, tom lane