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!
Is this related to previous articles where "the_log" table was created and hence is dependent on them?
ReplyDeletetesting=# 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