BUG #16177: pg_event_trigger_ddl_commands() returns empty set for ddl_command_start and "drop table"
PG Bug reporting form <[email protected]> Sun, 22 Dec 2019 22:53:04 +0000
| Newsgroups | gmane.comp.db.postgresql.bugs |
|---|---|
| Message-ID | <[email protected]> |
The following bug has been logged on the website: Bug reference: 16177 Logged by: Andrei Pozolotin Email address: [email protected] PostgreSQL version: 11.6 Operating system: linux Description: 1. use case is to delete rows with oid references to the table before "drop table" 2. one way to do that is with event_trigger, see code snippet below 3. the problem is that: pg_event_trigger_ddl_commands() returns empty set for ddl_command_start and "drop table" 4. this issue was already mentioned before: https://postgrespro.com/list/thread-id/2394772 5. sample code: CREATE OR REPLACE FUNCTION pglogical_assign_repset() RETURNS event_trigger AS $$ DECLARE obj record; BEGIN RAISE NOTICE 'pglogical assign: % %', tg_event, tg_tag; FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP IF obj.object_type = 'table' THEN IF obj.schema_name = 'public' THEN IF tg_tag IN ('CREATE TABLE') THEN PERFORM pglogical.replication_set_add_table('default', obj.objid); END IF; IF tg_tag IN ('DROP TABLE') THEN PERFORM pglogical.replication_set_remove_table('default', obj.objid); END IF; END IF; END IF; END LOOP; END; $$ LANGUAGE plpgsql; --- DROP EVENT TRIGGER IF EXISTS pglogical_assign_repset_create; CREATE EVENT TRIGGER pglogical_assign_repset_create ON ddl_command_end WHEN TAG IN ('CREATE TABLE') EXECUTE FUNCTION pglogical_assign_repset(); --- DROP EVENT TRIGGER IF EXISTS pglogical_assign_repset_delete; CREATE EVENT TRIGGER pglogical_assign_repset_delete ON ddl_command_start WHEN TAG IN ('DROP TABLE') EXECUTE FUNCTION pglogical_assign_repset(); ---