ddl_command_end not firing DROP events

Поиск
Список
Период
Сортировка
От Demitri Muna
Тема ddl_command_end not firing DROP events
Дата
Msg-id E73C07B3-2E50-43A4-94BE-DEF6277AA5AB@demitri.com
обсуждение исходный текст
Ответы Re: ddl_command_end not firing DROP events
Список pgsql-general
Hi,

I’ve created a trigger where I want to capture schema-changing events. I’m finding that DROP events are not being
triggeredwhen using “ddl_command_end". The test case below demonstrates this. I am running PostgreSQL 10.4. The trigger
isfired 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.

https://www.postgresql.org/message-id/CAHE3wgjX-N%3DX9mccp4Bs-y-%3DHE%3DqayHEpXpCs1jMT%3DW0ZsYc5A%40mail.gmail.com

The documentation suggests that one should be able to use “ddl_command_end” instead of creating two triggers (i.e. one
forDROPs, 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



В списке pgsql-general по дате отправления:

Предыдущее
От: Matt Dee
Дата:
Сообщение: Specifying WAL Location in Streaming Replication
Следующее
От: Alvaro Herrera
Дата:
Сообщение: Re: ddl_command_end not firing DROP events