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.

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.

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:

    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.

    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!