Nice Postgres Trigger for Immutable Data with Audit Log

create_all.sql

--
-- 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();

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.

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.