Cool Stuff in PostgreSQL 10: Auto-logging

We started off by creating a logging infrastructure, then arranging for a single table to use it. Rather than repeat that work for each table, let's use a relatively although not completely new feature: `EVENT TRIGGER`. The idea here is that we fire a trigger on `CREATE TABLE` and see to it that the table is logged. We'll write the trigger first, even though in reality, we'd need to load the function it calls first. ```sql CREATE EVENT TRIGGER add_logger ON ddl_command_end WHEN tag IN ('create table') EXECUTE PROCEDURE add_logger(); COMMENT ON EVENT TRIGGER add_logger IS 'Ensure that each table which is not a log gets logged'; ``` The magic happens inside `add_logger()`, but it's magic we've already seen. First, we'll get the table's name and schema using `pg_event_trigger_ddl_commands()`, filtering out tables which are already log tables. The test here is crude and string-based, but we could easily go to schema-based ones. ```sql CREATE OR REPLACE FUNCTION add_logger() RETURNS event_trigger LANGUAGE plpgsql AS $$$$ DECLARE r RECORD; cmd TEXT; BEGIN SELECT p.*, c.relname as table_name INTO STRICT r FROM pg_catalog.pg_event_trigger_ddl_commands() p JOIN pg_catalog.pg_class c ON (p.objid = c.oid) WHERE p.object_type = 'table' AND c.relname !~ '_log$''; /* No recursing! */ IF NOT FOUND THEN RAISE NOTICE 'Skipping log table'; RETURN; END IF; ``` Next, we add the log table for the table in question: ```sql RAISE NOTICE 'Adding log table(s) for %.%', r.schema_name, r.table_name; cmd := format('CREATE TABLE IF NOT EXISTS %I PARTITION OF the_log FOR VALUES IN (%L) PARTITION BY LIST(table_name);', pg_catalog.concat_ws('_', r.schema_name, 'log'), r.schema_name ); EXECUTE cmd; cmd := format('CREATE TABLE IF NOT EXISTS %I PARTITION OF %s FOR VALUES IN (%L);', pg_catalog.concat_ws('_', r.schema_name, r.table_name, 'log'), pg_catalog.concat_ws('_', r.schema_name, 'log'), r.table_name ); EXECUTE cmd; ``` Finally, the triggers that populate it. ```sql cmd := format( $q$CREATE TRIGGER %I AFTER INSERT ON %I.%I REFERENCING NEW TABLE AS new_table FOR EACH STATEMENT EXECUTE PROCEDURE public.log();$q$, pg_catalog.concat_ws('_', 'log_insert', r.schema_name, r.table_name), r.schema_name, r.table_name ); EXECUTE cmd; cmd := format( $q$CREATE TRIGGER %I AFTER UPDATE ON %I.%I REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table FOR EACH STATEMENT EXECUTE PROCEDURE public.log();$q$, pg_catalog.concat_ws('_', 'log_update', r.schema_name, r.table_name), r.schema_name, r.table_name ); EXECUTE cmd; cmd := format( $q$CREATE TRIGGER %I AFTER DELETE ON %I.%I REFERENCING OLD TABLE AS old_table FOR EACH STATEMENT EXECUTE PROCEDURE public.log();$q$, pg_catalog.concat_ws('_', 'log_delete', r.schema_name, r.table_name), r.schema_name, r.table_name ); EXECUTE cmd; EXCEPTION WHEN no_data_found THEN NULL; WHEN too_many_rows THEN RAISE EXCEPTION 'This function should only fire on one table, not this list: %', r.object_identity; END; $$$$; ``` This concludes this mini-series on auto-logging. I hope you've enjoyed it, and until the next time, keep database doing!

Comments

  1. Michael VitaleOctober 20, 2017 at 3:59 AM

    Is this related to previous articles where "the_log" table was created and hence is dependent on them?

    testing=# create table mytable (akey int not null primary key, avalue text);
    NOTICE: Adding log table(s) for public.mytable
    ERROR: relation "the_log" does not exist
    CONTEXT: SQL statement "CREATE TABLE IF NOT EXISTS public_log
    PARTITION OF the_log
    FOR VALUES IN ('public')
    PARTITION BY LIST(table_name);"
    PL/pgSQL function add_logger() line 28 at EXECUTE

    ReplyDelete

Post a Comment

Popular posts from this blog

Cool Stuff in PostgreSQL 10: Transition Table Triggers

Last time , we created a heirarchy of tables for logging, but we don't have anything they'd log, and we don't have a way to ensure the logging happens automagically.  This time, we'll fix those problems. First, a thing to log: CREATE TABLE foo(     id BIGSERIAL PRIMARY KEY,     t TEXT ); Next, let's make an efficient trigger function.  You may have seen one of these before, but it was probably row-based, so it couldn't be super efficient.  This one is statement-based, and uses the new transition table feature in PostgreSQL 10.  We only need to build one trigger function because our logging infrastructure is schema-agnostic.  We could have separated it into three trigger functions, but that's just more code to maintain.  It starts off as usual: CREATE OR REPLACE FUNCTION log() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN     IF TG_OP NOT IN ('INSERT', 'UPDATE', 'DELETE') THEN    ...
Read more

Cool Stuff in PostgreSQL 10: Partitioned Audit Table

If you've dealt with PostgreSQL's answer to partitioning in the past, you may have put a lot of work into it.  Happily, you have a lot less to bother with as of PostgreSQL 10, which is not out yet as of this writing. Let's dive right in with a table that's a little like an audit trail. CREATE TABLE IF NOT EXISTS the_log (     "timestamp" timestamp with time zone DEFAULT now() NOT NULL,     "user" text NOT NULL DEFAULT CURRENT_USER,     action text NOT NULL,     table_schema text NOT NULL,     table_name text NOT NULL,     old_row jsonb,     new_row jsonb,     CONSTRAINT the_log_check CHECK (         (old_row IS NOT NULL)::integer +         (new_row IS NOT NULL)::integer > 0     ) ) PARTITION BY LIST(table_schema); COMMENT ON CONSTRAINT the_log_check ON the_log IS 'Make...
Read more