jsonb_generic.sql

DROP TABLE IF EXISTS pgbench_generic_log;
CREATE TABLE pgbench_generic_log (
  mtime timestamptz not null default now(),
  action char not null check (action in ('I', 'U', 'D')),
  username text not null,
  table_name text not null,
  row_data jsonb not null
);

CREATE INDEX ON pgbench_generic_log USING brin (mtime);

-- triggers
CREATE OR REPLACE FUNCTION public.pgbench_generic_log()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
  IF TG_OP = 'DELETE' THEN
    INSERT INTO pgbench_generic_log VALUES (now(), 'D', session_user, TG_TABLE_NAME, to_jsonb(OLD));
  ELSE
    INSERT INTO pgbench_generic_log VALUES (now(), TG_OP::char , session_user, TG_TABLE_NAME, to_jsonb(NEW));
  END IF;
  RETURN NULL;
END;
$function$;

CREATE TRIGGER log_pgbench_generic AFTER INSERT OR UPDATE OR DELETE ON pgbench_accounts FOR EACH ROW EXECUTE FUNCTION pgbench_generic_log();
CREATE TRIGGER log_pgbench_generic AFTER INSERT OR UPDATE OR DELETE ON pgbench_branches FOR EACH ROW EXECUTE FUNCTION pgbench_generic_log();
CREATE TRIGGER log_pgbench_generic AFTER INSERT OR UPDATE OR DELETE ON pgbench_tellers FOR EACH ROW EXECUTE FUNCTION pgbench_generic_log();

jsonb_generic_differential.sql

DROP TABLE IF EXISTS pgbench_generic_log_diffed;
CREATE TABLE pgbench_generic_log_diffed (
  mtime timestamptz not null default now(),
  action char not null check (action in ('I', 'U', 'D')),
  username text not null,
  table_name text not null,
  row_data jsonb not null
);

CREATE INDEX ON pgbench_generic_log_diffed USING brin (table_name, mtime);

-- triggers
CREATE OR REPLACE FUNCTION public.pgbench_generic_log_diffed()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
  r record;
  old_row jsonb;
  changed_cols jsonb := jsonb_build_object();
BEGIN
  
  IF TG_OP = 'DELETE' THEN    
    INSERT INTO pgbench_generic_log_diffed VALUES (now(), 'D', session_user, TG_TABLE_NAME, to_jsonb(OLD));
  
  ELSIF TG_OP = 'UPDATE' THEN
    old_row := to_jsonb(OLD);

    FOR r IN SELECT * FROM jsonb_each(to_jsonb(NEW))
    LOOP
      IF r.value IS DISTINCT FROM jsonb_extract_path(old_row, r.key) THEN
        -- RAISE NOTICE 'Change in %.% - OLD: %s, NEW: %s', TG_TABLE_NAME, r.key, jsonb_extract_path(old_row, r.key), r.value;
        changed_cols := jsonb_set(changed_cols, array[r.key], r.value);
      END IF;
    END LOOP;
    
    -- any cols changed?
    IF changed_cols != jsonb_build_object() THEN
      INSERT INTO pgbench_generic_log_diffed VALUES (now(), 'U', session_user, TG_TABLE_NAME, to_jsonb(changed_cols));
    ELSE
      NULL;
      -- RAISE WARNING 'No changes detected for tbl %, OLD: %s, NEW: %s', TG_TABLE_NAME, OLD, NEW;
      -- FYI - if this happens a lot, then declaring CREATE TRIGGER ... WHEN OLD IS DISTINCT FROM NEW could give a small performance boost...
    END IF;
  
  ELSIF TG_OP = 'INSERT' THEN
    INSERT INTO pgbench_generic_log_diffed VALUES (now(), 'I' , session_user, TG_TABLE_NAME, to_jsonb(NEW));
  END IF;

  RETURN NULL;
END;
$function$;


CREATE TRIGGER log_pgbench_generic_diffed AFTER INSERT OR UPDATE OR DELETE ON pgbench_accounts FOR EACH ROW EXECUTE FUNCTION pgbench_generic_log_diffed();
CREATE TRIGGER log_pgbench_generic_diffed AFTER INSERT OR UPDATE OR DELETE ON pgbench_branches FOR EACH ROW EXECUTE FUNCTION pgbench_generic_log_diffed();
CREATE TRIGGER log_pgbench_generic_diffed AFTER INSERT OR UPDATE OR DELETE ON pgbench_tellers FOR EACH ROW EXECUTE FUNCTION pgbench_generic_log_diffed();

standard_explicit_triggers.sql

/* pgbench_accounts */
DROP TABLE IF EXISTS pgbench_accounts_log;
CREATE TABLE pgbench_accounts_log (
  mtime timestamptz not null default now(),
  action char not null check (action in ('I', 'U', 'D')),
  username text not null,
  aid int,
  bid int,
  abalance int,
  filler character(84) /* NB! 84 chars of data stored every time, even when it hasn't
                       changed could be too wasteful / problematic for volume heavy
                       systems so I'd recommend to add some IS DISTINCT FROM filters */
);

CREATE INDEX ON pgbench_accounts_log USING brin (mtime);
/* BRIN is perfect for log timestamps that are also not selected too often */

-- triggers
CREATE OR REPLACE FUNCTION public.pgbench_accounts_log()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
  IF TG_OP = 'DELETE' THEN
    INSERT INTO pgbench_accounts_log VALUES (now(), 'D', session_user, OLD.*);
  ELSE
    INSERT INTO pgbench_accounts_log VALUES (now(), TG_OP::char , session_user, NEW.*);
  END IF;
  RETURN NULL;
END;
$function$;

CREATE TRIGGER log_pgbench_accounts AFTER INSERT OR UPDATE OR DELETE ON pgbench_accounts FOR EACH ROW EXECUTE FUNCTION pgbench_accounts_log();


/* pgbench_branches */
DROP TABLE IF EXISTS pgbench_branches_log;
CREATE TABLE pgbench_branches_log (
  mtime timestamptz not null default now(),
  action char not null check (action in ('I', 'U', 'D')),
  username text not null,
  bid int,
  bbalance int,
  filler character(88)
);

CREATE INDEX ON pgbench_branches_log USING brin (mtime);

-- triggers
CREATE OR REPLACE FUNCTION public.pgbench_branches_log()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
  IF TG_OP = 'DELETE' THEN
    INSERT INTO pgbench_branches_log VALUES (now(), 'D', session_user, OLD.*);
  ELSE
    INSERT INTO pgbench_branches_log VALUES (now(), TG_OP::char , session_user, NEW.*);
  END IF;
  RETURN NULL;
END;
$function$;

CREATE TRIGGER log_pgbench_branches AFTER INSERT OR UPDATE OR DELETE ON pgbench_branches FOR EACH ROW EXECUTE FUNCTION pgbench_branches_log();


/* pgbench_tellers */
DROP TABLE IF EXISTS pgbench_tellers_log;
CREATE TABLE pgbench_tellers_log (
  mtime timestamptz not null default now(),
  action char not null check (action in ('I', 'U', 'D')),
  username text not null,
  tid int,
  bid int,
  tbalance int,
  filler character(84)
);

CREATE INDEX ON pgbench_tellers_log USING brin (mtime);

-- triggers
CREATE OR REPLACE FUNCTION public.pgbench_tellers_log()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
  IF TG_OP = 'DELETE' THEN
    INSERT INTO pgbench_tellers_log VALUES (now(), 'D', session_user, OLD.*);
  ELSE
    INSERT INTO pgbench_tellers_log VALUES (now(), TG_OP::char , session_user, NEW.*);
  END IF;
  RETURN NULL;
END;
$function$;

CREATE TRIGGER log_pgbench_tellers AFTER INSERT OR UPDATE OR DELETE ON pgbench_tellers FOR EACH ROW EXECUTE FUNCTION pgbench_tellers_log();