Skip to content

Instantly share code, notes, and snippets.

@drbobbeaty
Last active January 15, 2019 14:18
Show Gist options
  • Save drbobbeaty/1c4a513e14fdb1e4e123ff9443f978c4 to your computer and use it in GitHub Desktop.
Save drbobbeaty/1c4a513e14fdb1e4e123ff9443f978c4 to your computer and use it in GitHub Desktop.
Nice Postgres Trigger for Immutable Data with Audit Log
--
-- Assume that you have a table - `customers`... create a new table called
-- `customers_audit` with the following qualities:
--
create table if not exists customer (
-- assumming you have a primary key that's a uuid
id uuid not null,
-- ...make sure to have a version and timestamp
version integer not null,
as_of timestamp with time zone not null,
-- add in all the other fields you want...
last_name varchar,
first_name varchar,
age integer,
-- add in the primary key
primary key (id, version, as_of);
);
-- add all the indexes you want...
create index idx_cust_age on customer (age);
-- create the audit table... where all the historical entries will go
-- this will be the same structure, indexes, etc.
create table if not exists customer_audit like customer including all;
--
-- 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();
@drbobbeaty
Copy link
Author

If you want to use a general varchar field for the primary key, you can change the first lines in the trigger to be:

  -- get the advisory lock on this id
  perform pg_advisory_xact_lock(('x' || md5(new.wiggle::varchar))::bit(64)::bigint);

where the column wiggle is of type varchar. We are just computing the MD5 hash of the string, and it needs to be static for the locking of the data... you really don't want to pick something that is itself, mutable.

@drbobbeaty
Copy link
Author

If you want to use a general int field for the primary key, you can change the first lines in the trigger to be:

  -- get the advisory lock on this id
  perform pg_advisory_xact_lock(new.id::bigint);

where the column id is of type int. We are just casting the int to a bigint and that will work just as well.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment