ddl_command_end not firing DROP events
Hi,
I’ve created a trigger where I want to capture schema-changing events. I’m finding that DROP events are not being triggered when using “ddl_command_end". The test case below demonstrates this. I am running PostgreSQL 10.4. The trigger is fired for the CREATE event, but not DROP TYPE or DROP TABLE.
I came across the same question on the mailing list, but the solution was to use the “sql_drop” trigger instead.
/messages/by-id/CAHE3wgjX-N=X9mccp4Bs-y-=HE=qayHEpXpCs1jMT=W0ZsYc5A@mail.gmail.com
The documentation suggests that one should be able to use “ddl_command_end” instead of creating two triggers (i.e. one for DROPs, another for everything else).
Test:
CREATE FUNCTION test_event_trigger_for_drops()
RETURNS event_trigger LANGUAGE plpgsql AS $$
DECLARE
obj record;
BEGIN
FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
LOOP
RAISE NOTICE 'caught % event on ''%''',
obj.command_tag,
obj.object_identity;
END LOOP;
END
$$;
CREATE EVENT TRIGGER test_event_trigger_for_drops
ON ddl_command_end
EXECUTE PROCEDURE test_event_trigger_for_drops();
CREATE TYPE foo AS (f1 int, f2 text);
DROP TYPE foo;
CREATE TABLE drop_test ( pk int ) WITH ( OIDS = FALSE );
DROP TABLE drop_test;
-- for ease of copy/paste to delete the above
DROP EVENT TRIGGER test_event_trigger_for_drops;
DROP FUNCTION test_event_trigger_for_drops;
Cheers,
Demitri
On 2018-Jul-08, Demitri Muna wrote:
Hi
I’ve created a trigger where I want to capture schema-changing events.
I’m finding that DROP events are not being triggered when using
“ddl_command_end". The test case below demonstrates this. I am running
PostgreSQL 10.4. The trigger is fired for the CREATE event, but not
DROP TYPE or DROP TABLE.I came across the same question on the mailing list, but the solution
was to use the “sql_drop” trigger instead./messages/by-id/CAHE3wgjX-N=X9mccp4Bs-y-=HE=qayHEpXpCs1jMT=W0ZsYc5A@mail.gmail.com
As I recall, handling of DROP events in the ddl_command_end event is not
completely consistent. You may even find that some DROP events are not
returned by pg_event_trigger_ddl_commands. I suggest you stick to
pg_event_trigger_dropped_objects() instead (for which you need
sql_drop).
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Hi,
On Jul 8, 2018, at 2:19 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
As I recall, handling of DROP events in the ddl_command_end event is not
completely consistent. You may even find that some DROP events are not
returned by pg_event_trigger_ddl_commands. I suggest you stick to
pg_event_trigger_dropped_objects() instead (for which you need
sql_drop).
That's what I'm seeing as well and have a workaround in place that does just that. But to track schema changes requires two triggers and two functions. Which is fine, it works, but I'm wondering if it's a documentation issue or an outright bug.
Cheers,
Demitri