Обсуждение: [GENERAL] spi/timetravel: unique constraint violation on UPDATE
[GENERAL] spi/timetravel: unique constraint violation on UPDATE
От
postgresql@get-experience.com
Дата:
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
Re: [GENERAL] spi/timetravel: unique constraint violation on UPDATE
От
postgresql@get-experience.com
Дата:
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