1

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.

  • I'm curious as to why you want to do this? – Vérace Apr 1 at 13:42
  • I have some additional information I'd like to store about my tables and the existence of the tables isn't entirely static. – user3747260 Apr 1 at 17:21
  • Can you add all of this (in detail) to the question - the more information you provide, the more we can help you! – Vérace Apr 1 at 17:54
  • What specifically is unclear? I'm not sure what additional information would be helpful here. – user3747260 Apr 1 at 18:04
  • Well, why you are doing this? Apart from satisfying my curiousity, it might help providing a solution for your underlying issue - rather than the database one. I'm not trying to patronise, but posters here frequently neglect to get to the kernel of the issue they're having. – Vérace Apr 1 at 18:08
0

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 ;)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.