A friend asked me today for the trick I've used in the past to make sure that the data in a database is immutable and versioned. This is nice because it matches what Clojure acts like, internally, and it makes it easy to see who's doing what, when. Assuming we start with a table - for the sake of argument, let's say it's customers, and looks something like:
CREATE TABLE IF NOT EXISTS customer (
id uuid NOT NULL,
version INTEGER NOT NULL,
as_of TIMESTAMP WITH TIME zone NOT NULL,
last_name VARCHAR,
first_name VARCHAR,
PRIMARY KEY (id, version, as_of)
);
where the first three fields are really the key ones for any table to work with this scheme. You need to have a unique key, and in this case, the easiest I've found is a UUID, and then you need a version and a timestamp when that change was made. What's left in the table is really not important here, but it's something.
You then need to make an Audit table that has the same table structure, but has the string _audit added to the name:
CREATE TABLE IF NOT EXISTS customer_audit LIKE customer including ALL;
What we then need to create is the following trigger that intercepts the INSERT and UPDATE commands on the customers table, and places the historical data into the Audit table, and the most recent version of the customer data is always kept in the customer table.
The trigger looks like this:
--
-- create the trigger on INSERT and UPDATE to update the version if it's
-- not provided, and to maintain all versions in the audit table, but have
-- the current version in the non-audit table. Importantly, NOTHING is
-- deleted.
--
CREATE OR REPLACE FUNCTION audit_customer()
RETURNS TRIGGER AS $body$
DECLARE
ver INTEGER;
BEGIN
-- get the advisory lock on this id
perform pg_advisory_xact_lock(('x' || translate(LEFT(NEW.id::text, 18),
'-', ''))::bit(64)::BIGINT);
-- get the max of the existing version for the data now
SELECT MAX(version) INTO ver
FROM customer_audit
WHERE id = NEW.id;
-- and bump it up one and use that
IF ver IS NULL THEN
NEW.version := 1;
ELSE
NEW.version := ver + 1;
END IF;
-- if an update, then we need to insert the new
IF tg_op = 'UPDATE' THEN
-- now let's insert the old row into the audit table
INSERT INTO customer_audit
VALUES (NEW.*);
elsif tg_op = 'INSERT' THEN
-- now let's insert the new row into the audit table
INSERT INTO customer_audit
VALUES (NEW.*);
-- and delete the old one in the customer table
DELETE FROM customer
WHERE id = NEW.id
AND version <= ver;
END IF;
-- finally, return the row to be inserted to customer
RETURN NEW;
END
$body$ LANGUAGE plpgsql;
CREATE TRIGGER set_version BEFORE INSERT OR UPDATE ON customer
FOR each ROW EXECUTE PROCEDURE audit_customer();
At this point, we can INSERT or UPDATE on customers and the previous version of that customer will be mmoved to the Audit table, and the the most recent version will be held in the customers table.
I have found this very useful, and I've put it in a gist for easy access.
The point of:
-- get the advisory lock on this id
perform pg_advisory_xact_lock(('x' || translate(LEFT(NEW.id::text, 18),
'-', ''))::bit(64)::BIGINT);
is to get an exclusive lock on the data for a given id. This is necessary to make sure that updates from multiple services get serialized on the same data. This scheme can't ensure that there are merged changes - only that there is a sequence of changes to the table, and each one is entirely correct for the time it was entered.
So... what happens if you have a string as the primary key, and not a UUID? Well, use can use the MD5 checksum of the string as the lock indicator:
-- get the advisory lock on a general string
perform pg_advisory_xact_lock(('x' || md5(NEW.wiggle::VARCHAR))::bit(64)::BIGINT);
where the field wiggle is a varchar, and here, we are just computing the MD5, and using that as the basis of the lock. Yes, there could be some hash collisions, but that's likely not a huge performance problem, and it's conservative in that we'll over-lock, and not under-lock.
UPDATE: a friend asked about using an int as the primary key, and in that case, the line would be:
-- get the advisory lock on a general string
perform pg_advisory_xact_lock(NEW.id::BIGINT);
where the column id is an int. Again, we just need to get it to the bigint for the advisory lock call. After that, Postgres does the rest.
