How do I add a trigger to pg_class?

Question

In postgres, I'd like to dynamically add a trigger to all my tables on insert for each row. I want to be able to add the trigger for all future tables that get added without requiring db users to do something else to create a table (like call a function to create a table instead of using the CREATE TABLE syntax). To do this, I thought it would be easiest to add a trigger to pg_class, but when I do that, I get an error:

ERROR: permission denied: "pg_class" is a system catalog

Here is my code

> docker run --name pg -e POSTGRES_PASSWORD=password postgres:13
> docker exec -ti pg psql -Upostgres
# then run this...

CREATE FUNCTION my_func() RETURNS trigger AS $$
BEGIN
   RAISE NOTICE 'This is a test';
   RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER my_pg_trigger
AFTER INSERT ON pg_catalog.pg_class
FOR EACH ROW
WHEN (NEW.relkind = 'r')
EXECUTE FUNCTION my_func();

I didn't see anything in the documentation saying that you couldn't add triggers. I found docs saying that they are "regular tables", however editing them can severely mess things up: https://www.postgresql.org/docs/13/catalogs.html. However nothing about permissions or limitations.

PostgreSQL's system catalogs are regular tables. You can drop and recreate the tables, add columns, insert and update values, and severely mess up your system that way.

If this isn't possible, can you point me to the documentation on this to help me understand? Better yet, let me know if there is another way you can dynamically add triggers to tables?

user_425549 · Answer

Instead of adding triggers to the pg_class table, I was able to solve the problem by following @Adrian Klaver and @a_horse_with_no_name's suggestion with event triggers.

CREATE OR REPLACE FUNCTION report() RETURNS event_trigger AS $$
DECLARE
    obj record;
    rel_kind char;
BEGIN
    IF tg_tag = 'CREATE TABLE' THEN
        FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
        LOOP
            SELECT relkind INTO rel_kind FROM pg_class WHERE oid = obj.objid;
            -- only log on tables (exclude indices)
            IF rel_kind = 'r' THEN
                RAISE NOTICE 'metric: %', obj.object_identity;
            END IF;
        END LOOP;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER report_trigger ON ddl_command_end EXECUTE FUNCTION report();

For those of you that need a use case:

Let's just say you need to do reporting whenever tables are created and you need to use existing infra, and can't use pgbadger, or something like that. Or you want more real time alerts, and don't want to wait until logs are aggregated at the 10m or 1hr intervals.

How do I add a trigger to pg_class?

1 Answers