Re: Event Triggers unable to capture the DDL script executed

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(dot)kobzarev(at)aeronavigator(dot)ru <n(dot)kobzarev(at)aeronavigator(dot)ru>
Sent: Wednesday, February 22, 2023 3:33 PM
To: 'Laurenz Albe' <laurenz(dot)albe(at)cybertec(dot)at>; 'Neethu P' <neeth_3(at)hotmail(dot)com>; 'pgsql-general' <pgsql-general(at)postgresql(dot)org>
Subject: RE: Event Triggers unable to capture the DDL script executed

>>-----Исходное сообщение-----
>>От: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
>>Отправлено: 22 февраля 2023 г. 12:52
>>Кому: Neethu P <neeth_3(at)hotmail(dot)com>; pgsql-general <pgsql-general(at)postgresql(dot)org>
>>Тема: 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();