-
-
Save myitcv/9212407 to your computer and use it in GitHub Desktop.
/* | |
Copyright (c) 2015 Paul Jolly <paul@myitcv.org.uk) | |
Permission is hereby granted, free of charge, to any person obtaining a copy | |
of this software and associated documentation files (the "Software"), to deal | |
in the Software without restriction, including without limitation the rights | |
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell | |
copies of the Software, and to permit persons to whom the Software is | |
furnished to do so, subject to the following conditions: | |
The above copyright notice and this permission notice shall be included in | |
all copies or substantial portions of the Software. | |
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR | |
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, | |
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE | |
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER | |
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, | |
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN | |
THE SOFTWARE. | |
*/ | |
CREATE OR REPLACE FUNCTION process_timetravel_before() RETURNS TRIGGER AS $timetravel_before$ | |
DECLARE | |
temp_row RECORD; -- a temporary variable used on updates/deletes | |
time_now TIMESTAMP; -- get the time now just once.... for consistency's sake | |
BEGIN | |
time_now = now(); | |
IF (TG_OP = 'UPDATE' OR TG_OP = 'DELETE') THEN | |
-- the user should not be able to update historic rows | |
IF OLD.valid_to != 'infinity' THEN | |
RAISE EXCEPTION 'Cannot % old row', TG_OP; | |
END IF; | |
-- use of TG_TABLE_NAME keeps this generic and non-table specific | |
-- see http://www.postgresql.org/docs/9.3/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN | |
EXECUTE 'SELECT * FROM ' || TG_TABLE_NAME::regclass || ' WHERE ctid = $1 FOR UPDATE' USING OLD.ctid; | |
-- not sure whether this is strictly required... could we modify OLD without side effects? | |
temp_row := OLD; | |
temp_row.valid_to := time_now; | |
IF (TG_OP = 'UPDATE') THEN | |
-- 'bump' the valid_from and ensure valid_to = 'infinity' | |
NEW.valid_from := time_now; | |
NEW.valid_to := 'infinity'; | |
-- allow the update to continue... so that the correct number of rows are reported | |
-- as having been affected | |
RETURN NEW; | |
ELSIF (TG_OP = 'DELETE') THEN | |
-- we want to allow the delete to continue... so that the correct number of rows are reported | |
-- as having been affected | |
RETURN OLD; | |
END IF; | |
RETURN NULL; -- shouldn't ever get here | |
ELSIF (TG_OP = 'INSERT' AND NEW.valid_from is null OR NEW.valid_to is null) THEN | |
-- this case could well be avoided by having a table definition with defaults: | |
-- | |
-- valid_from = now() | |
-- valid_to = 'infinity' | |
-- | |
-- but we include this as a safety net | |
IF NEW.valid_from is null THEN | |
NEW.valid_from := time_now; | |
END IF; | |
IF NEW.valid_to is null THEN | |
NEW.valid_to := 'infinity'; | |
END IF; | |
-- allow the insert to continue... so that the correct number of rows are reported | |
-- as having been affected | |
RETURN NEW; | |
ELSIF (TG_OP = 'INSERT') THEN | |
-- allow the insert to continue... so that the correct number of rows are reported | |
-- as having been affected | |
RETURN NEW; | |
END IF; | |
RETURN NULL; -- won't get here if we only create the trigger for insert, update and delete | |
END; | |
$timetravel_before$ LANGUAGE plpgsql; | |
CREATE OR REPLACE FUNCTION process_timetravel_after() RETURNS TRIGGER AS $timetravel_after$ | |
DECLARE | |
temp_row RECORD; -- a temporary variable used on updates/deletes | |
time_now TIMESTAMP; -- get the time now just once.... for consistency's sake | |
BEGIN | |
time_now = now(); | |
IF (TG_OP = 'UPDATE' OR TG_OP = 'DELETE') THEN | |
-- not sure whether this is strictly required... could we modify OLD without side effects? | |
temp_row := OLD; | |
IF (TG_OP = 'UPDATE') THEN | |
temp_row.valid_to := NEW.valid_from; | |
ELSIF (TG_OP = 'DELETE') THEN | |
temp_row.valid_to := time_now; | |
END IF; | |
-- again, use of TG_TABLE_NAME keeps this generic and non-table specific | |
EXECUTE 'INSERT INTO ' || TG_TABLE_NAME::regclass || ' SELECT $1.*' USING temp_row; | |
END IF; | |
RETURN NULL; -- return value doesn't matter in after | |
END; | |
$timetravel_after$ LANGUAGE plpgsql; |
This is pretty hot, thanks! I've made a minor tweak to this so that the id column doesn't change except on insert; this way, the id can be a stable primary key.
CREATE OR REPLACE FUNCTION process_timetravel_before() RETURNS TRIGGER AS $timetravel_before$
DECLARE
temp_row RECORD; -- a temporary variable used on updates/deletes
time_now TIMESTAMP; -- get the time now just once.... for consistency's sake
id_next integer; -- <-- a variable to hold the next id
BEGIN
...
ELSIF (TG_OP = 'INSERT') then
-- user needs to create a sequence named {table_name_id_seq}
if new.id is null then
EXECUTE 'SELECT nextval(''' || TG_TABLE_NAME::regclass || '_id_seq' || ''')' into id_next;
new.id := id_next;
end if;
RETURN NEW;
In addition, you can create a view on top of this that acts just like a normal table, with no additional code:
create view foobar as select * from _foobar_shadow where valid_to = 'infinity'
selects, updates, inserts, deletes, joins etc to the view foobar
will behave exactly as if it was a normal table.
@nd2s did you have any luck with ON UPDATE CASCADE
?
@micimize I am sorry i cannot remember what we tried to do back then. I can paste you what we ended up with (might be pretty much the same as the code from the initial post but I don't have time to go through it now). This works for us with PK over <id, valid_from>
and unique constraint over <id, valid_to>
.
CREATE OR REPLACE FUNCTION process_timetravel_before()
RETURNS TRIGGER AS $timetravel_before$
DECLARE
temp_row RECORD; -- tmp var used on updates/deletes
time_now TIMESTAMP; -- stores current time
BEGIN
time_now = now();
IF (TG_OP = 'UPDATE' OR TG_OP = 'DELETE') THEN
-- the user should not be able to update historic rows
IF OLD.valid_to != 'infinity' THEN
RAISE EXCEPTION 'Cannot % old row', TG_OP;
END IF;
-- use of TG_TABLE_NAME keeps this generic and non-table specific
-- see http://www.postgresql.org/docs/9.3/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
EXECUTE 'SELECT * FROM ' || TG_TABLE_NAME::regclass ||
' WHERE ctid = $1 FOR UPDATE' USING OLD.ctid;
-- not sure whether this is strictly required... could we
-- modify OLD without side effects?
temp_row := OLD;
temp_row.valid_to := time_now;
IF (TG_OP = 'UPDATE') THEN
-- 'bump' the valid_from and ensure valid_to = 'infinity'
NEW.valid_from := time_now;
NEW.valid_to := 'infinity';
-- allow the update to continue... so that the correct
-- number of rows are reported as having been affected
RETURN NEW;
ELSIF (TG_OP = 'DELETE') THEN
-- we want to allow the delete to continue... so that
-- the correct number of rows are reported as having
-- been affected
RETURN OLD;
END IF;
RETURN NULL; -- shouldn't ever get here
ELSIF (TG_OP = 'INSERT') THEN
IF NEW.valid_from IS NULL THEN
NEW.valid_from := time_now;
END IF;
IF NEW.valid_to IS NULL THEN
NEW.valid_to := 'infinity';
END IF;
-- allow the insert to continue... so that the correct number
-- of rows are reported as having been affected
RETURN NEW;
END IF;
-- won't get here if we only create the trigger for insert,
-- update and delete
RETURN NULL;
END;
$timetravel_before$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION process_timetravel_after()
RETURNS TRIGGER AS $timetravel_after$
DECLARE
temp_row RECORD; -- tmp var used on updates/deletes
time_now TIMESTAMP; -- stores current time
BEGIN
time_now = now();
IF (TG_OP = 'UPDATE' OR TG_OP = 'DELETE') THEN
-- not sure whether this is strictly required... could we modify
-- OLD without side effects?
temp_row := OLD;
IF (TG_OP = 'UPDATE') THEN
temp_row.valid_to := NEW.valid_from;
ELSIF (TG_OP = 'DELETE') THEN
temp_row.valid_to := time_now;
END IF;
-- again, use of TG_TABLE_NAME keeps this generic and non-table
-- specific
EXECUTE 'INSERT INTO ' || TG_TABLE_NAME::regclass ||
' SELECT $1.*' USING temp_row;
END IF;
-- return value doesn't matter in after
RETURN NULL;
END;
$timetravel_after$ LANGUAGE plpgsql;
@nd2s indeed it looks the same - I figured out the main trick is to only trigger after when (pg_trigger_depth() = 0)
.
I also EXECUTE 'UPDATE ...'; RETURN NULL
on deletes - not sure what the consequences of the return null are though, or if it's really necessary once the trigger clause is modified.
Updated link for the related blog post: https://blog.myitcv.io/2014/02/25/row-level-version-control-with-postgresql.html
Thanks, @breml
This is really useful! I'm having loads of problems with the spi timetravel extension and am trying to switch to a plpgsql trigger approach.
I was trying to modify your code to do an
UPDATE
instead ofDELETE
so I can useON UPDATE CASCADE
on a table referring to (id, valid_to). Inprocess_timetravel_before()
I triedbut this seems to execute the update trigger again. Do you have any idea how to solve this?