Skip to content

Instantly share code, notes, and snippets.

@matthew-n
Forked from kmoppel/jsonb_generic.sql
Created April 3, 2020 18:38
Show Gist options
  • Save matthew-n/7a3d1e5d7b8e1e1bcb8b977c889c29a1 to your computer and use it in GitHub Desktop.
Save matthew-n/7a3d1e5d7b8e1e1bcb8b977c889c29a1 to your computer and use it in GitHub Desktop.
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();
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();
/* 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();
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment