Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
-- psql -U postgres -h localhost -f /path/to/tardis.sql
SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;
CREATE DATABASE tardis WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';
\connect tardis
SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;
CREATE SCHEMA history;
CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
CREATE EXTENSION IF NOT EXISTS btree_gist WITH SCHEMA public;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA public;
SET search_path = history, pg_catalog;
CREATE FUNCTION log_change() RETURNS trigger
LANGUAGE plpgsql
AS $_$
DECLARE
c refcursor;
tt tstzrange;
BEGIN
IF TG_OP = 'INSERT' THEN
EXECUTE 'INSERT INTO history.' || TG_TABLE_NAME ||
' SELECT $1.*, tstzrange(now(), $$infinity$$, $$[)$$)' USING NEW;
RETURN NEW;
ELSIF TG_OP = 'UPDATE' THEN
OPEN c FOR EXECUTE 'SELECT _validrange FROM history.' || TG_TABLE_NAME ||
' WHERE id = $1 ORDER BY _validrange DESC LIMIT 1 FOR UPDATE'
USING NEW.id;
FETCH FROM c INTO tt;
IF isempty(tstzrange(lower(tt), now(), $$[)$$)) THEN
-- What edge case are we missing by commenting the following lines of code?
-- How can we get the lastxid value?
--IF NOT lastxid = txid_current() THEN
-- RAISE EXCEPTION 'UPDATE would have empty validity: %d!', OLD;
--END IF;
EXECUTE 'DELETE FROM history.' || TG_TABLE_NAME ||
' WHERE CURRENT OF ' || quote_ident(c::text);
ELSE
EXECUTE 'UPDATE history.' || TG_TABLE_NAME || ' SET _validrange = tstzrange($1, now(), $$[)$$)' ||
' WHERE CURRENT OF ' || quote_ident(c::text) USING lower(tt);
END IF;
EXECUTE 'INSERT INTO history.' || TG_TABLE_NAME ||
' SELECT $1.*, tstzrange(now(), $$infinity$$, $$[)$$)' USING NEW;
RETURN NEW;
ELSIF TG_OP = 'DELETE' THEN
OPEN c FOR EXECUTE 'SELECT _validrange FROM history.' || TG_TABLE_NAME ||
' WHERE id = $1 ORDER BY _validrange DESC LIMIT 1 FOR UPDATE' USING OLD.id;
FETCH FROM c into tt;
IF isempty(tstzrange(lower(tt), now(), $$[)$$)) THEN
EXECUTE 'DELETE FROM history.' || TG_TABLE_NAME ||
' WHERE CURRENT OF ' || quote_ident(c::text);
RETURN OLD;
END IF;
EXECUTE 'UPDATE history.' || TG_TABLE_NAME ||
' SET _validrange = tstzrange($1, now(), $$[)$$) WHERE CURRENT OF ' ||
quote_ident(c::text) USING lower(tt);
RETURN OLD;
END IF;
RETURN NULL;
END;
$_$;
CREATE TABLE table1 (
id text NOT NULL,
name text NOT NULL,
_validrange tstzrange
);
ALTER TABLE ONLY table1
ADD CONSTRAINT table1_exclusion EXCLUDE
USING gist (id WITH =, _validrange WITH &&);
SET search_path = public, pg_catalog;
-- We're using text instead of uuid because btree_gist don't support
-- uuid data type yet and we want to use it for the EXCLUDE constraint
-- See: https://commitfest.postgresql.org/7/332/
CREATE TABLE table1 (
id text PRIMARY KEY DEFAULT (uuid_generate_v4())::text NOT NULL,
name text NOT NULL
);
CREATE TRIGGER table1_history BEFORE
INSERT OR DELETE OR UPDATE ON table1
FOR EACH ROW EXECUTE PROCEDURE history.log_change();
-- Data
BEGIN;
INSERT INTO table1 (name) VALUES ('name 1');
INSERT INTO table1 (name) VALUES ('name 2');
COMMIT;
BEGIN;
UPDATE table1 SET name = 'name 1.1' WHERE name = 'name 1';
COMMIT;
BEGIN;
-- Since this is a transaction, the first UPDATE is not recorded
-- in the history.table1 (as expected)
UPDATE table1 SET name = 'name 1.2' WHERE name = 'name 1.1';
UPDATE table1 SET name = 'name 1.3' WHERE name = 'name 1.2';
COMMIT;
SELECT * FROM public.table1 WHERE name = 'name 1.3';
SELECT * FROM history.table1 ORDER BY _validrange DESC;
@iperdomo

This comment has been minimized.

Copy link
Owner Author

iperdomo commented Mar 18, 2016

Example output:

$ psql -U postgres -h localhost -f tardis.ql 

SET
SET
SET
SET
SET
SET
SET
CREATE DATABASE
You are now connected to database "tardis" as user "postgres".
SET
SET
SET
SET
SET
SET
SET
CREATE SCHEMA
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION
SET
CREATE FUNCTION
CREATE TABLE
ALTER TABLE
SET
CREATE TABLE
CREATE TRIGGER
BEGIN
INSERT 0 1
INSERT 0 1
COMMIT
BEGIN
UPDATE 1
COMMIT
BEGIN
UPDATE 1
UPDATE 1
COMMIT
                  id                  |   name   
--------------------------------------+----------
 54e59934-4343-48e2-9d84-467f5ef14c55 | name 1.3
(1 row)

                  id                  |   name   |                            _validrange                            
--------------------------------------+----------+-------------------------------------------------------------------
 54e59934-4343-48e2-9d84-467f5ef14c55 | name 1.3 | ["2016-03-18 16:56:18.624712+01",infinity)
 54e59934-4343-48e2-9d84-467f5ef14c55 | name 1.1 | ["2016-03-18 16:56:18.614535+01","2016-03-18 16:56:18.624712+01")
 a80a645f-9851-4c0c-ac16-a88d5fbf58c6 | name 2   | ["2016-03-18 16:56:18.604209+01",infinity)
 54e59934-4343-48e2-9d84-467f5ef14c55 | name 1   | ["2016-03-18 16:56:18.604209+01","2016-03-18 16:56:18.614535+01")
(4 rows)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.