Mimicking cascading foreign key referencing system table I'd like to create a table to store some additional metadata about tables, but I'm running into a number of roadblocks since foreign keys and triggers cannot be used with system tables.

On INSERT it seems easy enough to create a trigger on the metadata table that checks for a matching row in pg_class, but I'm struggling to come up with a way to delete the row when the table is dropped.

I've looked into event triggers, but I can't find a way to determine the oid of the dropped table. Based on the information on the event trigger page and here https://www.postgresql.org/docs/current/plpgsql-trigger.html#PLPGSQL-EVENT-TRIGGER it doesn't seem like there's any data provided about the object that the event affects.

Is there any way of ensuring maintained integrity between my metadata table and the referenced rows in pg_class?

edit:
I found https://www.postgresql.org/docs/current/functions-event-triggers.html so it looks like I should be able to get the oid from an event trigger.

Here's what I ended up using

CREATE OR REPLACE FUNCTION delete_table_metadata()
RETURNS event_trigger AS $$
DECLARE
    table_oid oid;
    schema_nm varchar;
BEGIN
    FOR table_oid, schema_nm IN SELECT objid, schema_name FROM pg_event_trigger_dropped_objects()
    LOOP
        IF schema_nm = 'myschema' THEN
            DELETE FROM table_meta WHERE table_meta_id = table_oid;
        END IF;
    END LOOP;
END
$$
LANGUAGE plpgsql;


People seem to be curious why I want to do this.  I have a need for dynamically generated tables and associated application data models.  The metadata I need to store isn't actually about the tables per se, it's for the application models.  Since the models aren't hardcoded I need a way to persistently store some information about them. There's an argument to be made that I should instead control the creation and dropping of this data from the application rather than the database, but this is more foolproof and I can foresee times where the tables might be manipulated directly from the database rather than the application during admin work.
If you are just looking for a way to get ids of the droped table, maybe this code will help you out :

CREATE OR REPLACE FUNCTION master.ddl_trigger_drop_table_fct()
  RETURNS event_trigger
 LANGUAGE plpgsql
   AS $$
DECLARE
    all_variables character varying;
    r RECORD;
    schema_name varchar (250);
object_type varchar (100);
BEGIN
    FOR r IN SELECT * FROM pg_event_trigger_dropped_objects () LOOP
        if r.schema_name = 'master' and r.object_type = 'table'
         then
            schema_name := r.schema_name;
            object_type := r.object_type;
            all_variables := concat(
                'classid: ', r.classid, chr(10)
                , 'objid: ', r.objid, chr(10)
                , 'objsubid: ', r.objsubid, chr(10)
                , 'original: ', r.original, chr(10)
                , 'normal: ', r.normal, chr(10)
                , 'is_temporary: ', r.is_temporary, chr(10)
                , 'object_type: ', r.object_type, chr(10)
                , 'schema_name: ', r.schema_name, chr(10)
                , 'object_name: ', r.object_name, chr(10)
                , 'object_identity: ', r.object_identity, chr(10)
                , 'address_names: ', r.schema_name, chr(10)
                , 'address_args: ', r.address_args);

            RAISE NOTICE '%', all_variables;
        end if;
    END LOOP;

    ... [do some stuff] ...
END;
$$;


and after you have adjusted this code to fit your needs, you just need to bind your function too your event trigger :

CREATE EVENT TRIGGER ddl_trigger_drop_table ON sql_drop WHEN TAG IN ('DROP TABLE')
   EXECUTE FUNCTION [schema_name].ddl_trigger_drop_table_fct();


But same as others : why do you want to do this might interests me a bit ;)