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
        RAISE EXCEPTION 'This function should not fire on %', TG_OP;
    END IF;

    IF TG_OP = 'INSERT' THEN
        INSERT INTO the_log (
            action, table_schema,    table_name, new_row
        )
        SELECT
            TG_OP,  TG_TABLE_SCHEMA, TG_RELNAME, row_to_json(new_table)::jsonb
        FROM
            new_table;

While the rest of it should be familiar, new_table is new.  It's a transition table, which contains the rows as they looked after the action, which is an INSERT.  We've picked an arbitrary name for it, and we'll see how to do that below.  One operation done, two to go.  This next looks pretty similar:

    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO the_log (
            action, table_schema,    table_name, old_row
        )
        SELECT
            TG_OP,  TG_TABLE_SCHEMA, TG_RELNAME, row_to_json(old_table)::jsonb
        FROM
            old_table;


The other transition table, old_table, comes into play here.  For UPDATE, we'll use both:

    ELSE
        /*
         *  DANGER, WILL ROBINSON!  DANGER!
         *  This implementation assumes based on current implementation details
         *  that old_table and new_table have identical orderings.  Should that
         *  implementation detail change, this could get a lot more complicated.
         */

        WITH
            o AS (
                SELECT
                    row_to_json(old_table)::jsonb AS old_row,
                    row_number() OVER () AS ord
                 FROM old_table
            ),
            n AS (
            SELECT
                row_to_json(new_table)::jsonb AS new_row,
                row_number() OVER () AS ord
            FROM new_table
        )
        INSERT INTO the_log (
            action, table_schema,    table_name, old_row, new_row
        )
        SELECT
            TG_OP,  TG_TABLE_SCHEMA, TG_RELNAME, old_row, new_row
        FROM
            o
        JOIN
            n
            USING(ord);
    END IF;
    RETURN NULL;
END;
$$;

This may look a little intimidating, but it's just a way to make sure we've got corresponding rows without mandating primary keys that are never updated.

Next, triggers that call the function.  Because we need the non-empty combinations of old_table and new_table, we'll need three of them:

CREATE TRIGGER log_insert_public_foo
    AFTER INSERT ON public.foo
    REFERENCING NEW TABLE AS new_table
    FOR EACH STATEMENT
        EXECUTE PROCEDURE public.log();

CREATE TRIGGER log_update_public_foo
    AFTER UPDATE ON public.foo
    REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
    FOR EACH STATEMENT
        EXECUTE PROCEDURE public.log();

CREATE TRIGGER log_delete_public_foo
    AFTER DELETE ON public.foo
    REFERENCING OLD TABLE AS old_table
    FOR EACH STATEMENT
        EXECUTE PROCEDURE public.log();
Now, we can INSERT (or COPY), UPDATE, and DELETE from the foo table and know we're capturing the change stream.

One little problem, addressed in our third and final episode of this series, is that there was an awful lot of boilerplate code.  We'll DRY it up.