Re: Event Triggers unable to capture the DDL script executed

[Neethu P <neeth_3@xxxxxxxxxxx>]

Hi Laurenz,

Actually, current_query() may not help us in our case, as we won't be able to capture the ddl statement completely in case if it's in multiple lines.

Can you please help me with the event trigger in C? & also how can we integrate it with our current postgresql DB?

Thanks & Regards,

Neethu

---

From: n.kobzarev@xxxxxxxxxxxxxxxx <n.kobzarev@xxxxxxxxxxxxxxxx>
Sent: Wednesday, February 22, 2023 3:33 PM
To: 'Laurenz Albe' <laurenz.albe@xxxxxxxxxxx>; 'Neethu P' <neeth_3@xxxxxxxxxxx>; 'pgsql-general' <pgsql-general@xxxxxxxxxxxxxx>
Subject: RE: Event Triggers unable to capture the DDL script executed

 

>>-----Исходное сообщение-----
>>От: Laurenz Albe <laurenz.albe@xxxxxxxxxxx>
>>Отправлено: 22 февраля 2023 г. 12:52
>>Кому: Neethu P <neeth_3@xxxxxxxxxxx>; pgsql-general <pgsql-general@xxxxxxxxxxxxxx>
>>Тема: Re: Event Triggers unable to capture the DDL script executed

>>On Wed, 2023-02-22 at 07:57 +0000, Neethu P wrote:
>>> We are using event triggers to capture the DDL changes in a postgres database.
>>> However, we are unable to get the column information & the actual DDL
>>> script executed, while a table is altered.
>>> Also, in the postgres documentation for pg_event_trigger_ddl_commands ()- it is mentioned as below.
>>> pg_ddl_command      A complete representation of the command, in internal
>>> format. Thiscannot be output directly, but it can be passed to other
>>> functions to obtain different pieces of information about the command.
>>>
>>> Is it possible to access pg_ddl_command in postgresql? Or is there any
>>> scripts which can help to get theactual Alter DDL statement that was executed by the user?

>>That is simple if you write the event trigger in C.  I would say that that is the only way to get at the actual statement.

>>Yours,
>>Laurenz Albe



In MSSQL there is a brilliant possibility to have a server wide trigger to monitor commands. We are using It to have a history for all DDL operations.

Please try this (on new empty database) and give a feedback.

CREATE OR REPLACE FUNCTION public.notice_ddl()
    RETURNS event_trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
DECLARE
    r RECORD;
begin
    raise info '%', session_user || ' ran '||tg_tag||' '||current_query();
    FOR r IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP
        RAISE NOTICE 'we got a % event for object " %"',
            r.command_tag, r.object_identity;
    END LOOP;
end;
$BODY$;

CREATE OR REPLACE FUNCTION public.notice_ddl_drop()
    RETURNS event_trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
DECLARE
    r RECORD;
begin
    raise info '%', session_user || ' ran '||tg_tag||' '||current_query();
    FOR r IN SELECT * FROM pg_event_trigger_dropped_objects()
    LOOP
        RAISE NOTICE 'dropped: type "%" identity %',
                r.object_type, r.object_identity;
    END LOOP;
end;
$BODY$;


CREATE EVENT TRIGGER etg ON DDL_COMMAND_END
    EXECUTE PROCEDURE public.notice_ddl();

CREATE EVENT TRIGGER etg_drop ON SQL_DROP
    EXECUTE PROCEDURE public.notice_ddl_drop();