Thread: Event Triggers unable to capture the DDL script executed
Hi team,
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. This cannot 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 the actual Alter DDL statement that was executed by the user?
Thanks & Regards,
Neethu
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
>>-----Исходное сообщение----- >>От: Laurenz Albe <laurenz.albe@cybertec.at> >>Отправлено: 22 февраля 2023 г. 12:52 >>Кому: Neethu P <neeth_3@hotmail.com>; pgsql-general <pgsql-general@postgresql.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 historyfor 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();
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@aeronavigator.ru <n.kobzarev@aeronavigator.ru>
Sent: Wednesday, February 22, 2023 3:33 PM
To: 'Laurenz Albe' <laurenz.albe@cybertec.at>; 'Neethu P' <neeth_3@hotmail.com>; 'pgsql-general' <pgsql-general@postgresql.org>
Subject: RE: Event Triggers unable to capture the DDL script executed
Sent: Wednesday, February 22, 2023 3:33 PM
To: 'Laurenz Albe' <laurenz.albe@cybertec.at>; 'Neethu P' <neeth_3@hotmail.com>; 'pgsql-general' <pgsql-general@postgresql.org>
Subject: RE: Event Triggers unable to capture the DDL script executed
>>-----Исходное сообщение-----
>>От: Laurenz Albe <laurenz.albe@cybertec.at>
>>Отправлено: 22 февраля 2023 г. 12:52
>>Кому: Neethu P <neeth_3@hotmail.com>; pgsql-general <pgsql-general@postgresql.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();
On Thu, 2023-02-23 at 04:10 +0000, Neethu P wrote: > 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. Multiple lines should not be a problem. The problems I see are - you won't catch DDL statements issued in a function with that, since you only see the top-level statement - if you have the DDL statement as string, you need to parse it, which is non-trivial > Can you please help me with the event trigger in C? & also how can we > integrate it with our current postgresql DB? In an e-mail, I cannot do much beyond pointing you to the documentation: https://www.postgresql.org/docs/current/event-trigger-interface.html There is also a simple example: https://www.postgresql.org/docs/current/event-trigger-example.html Getting used to reading and writing PostgreSQL server code takes a while. Yours, Laurenz Albe
Thanks Laurenz. Is there any documentaion example to use the pg_ddl_command to capture the actual ddl statement (for event triggers in C)?
Regards,
Neethu
From: Laurenz Albe <laurenz.albe@cybertec.at>
Sent: Thursday, February 23, 2023 4:20 PM
To: Neethu P <neeth_3@hotmail.com>; n.kobzarev@aeronavigator.ru <n.kobzarev@aeronavigator.ru>; 'pgsql-general' <pgsql-general@postgresql.org>
Subject: Re: Event Triggers unable to capture the DDL script executed
Sent: Thursday, February 23, 2023 4:20 PM
To: Neethu P <neeth_3@hotmail.com>; n.kobzarev@aeronavigator.ru <n.kobzarev@aeronavigator.ru>; 'pgsql-general' <pgsql-general@postgresql.org>
Subject: Re: Event Triggers unable to capture the DDL script executed
On Thu, 2023-02-23 at 04:10 +0000, Neethu P wrote:
> 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.
Multiple lines should not be a problem. The problems I see are
- you won't catch DDL statements issued in a function with that, since you
only see the top-level statement
- if you have the DDL statement as string, you need to parse it, which is
non-trivial
> Can you please help me with the event trigger in C? & also how can we
> integrate it with our current postgresql DB?
In an e-mail, I cannot do much beyond pointing you to the documentation:
https://www.postgresql.org/docs/current/event-trigger-interface.html
There is also a simple example:
https://www.postgresql.org/docs/current/event-trigger-example.html
Getting used to reading and writing PostgreSQL server code takes a while.
Yours,
Laurenz Albe
> 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.
Multiple lines should not be a problem. The problems I see are
- you won't catch DDL statements issued in a function with that, since you
only see the top-level statement
- if you have the DDL statement as string, you need to parse it, which is
non-trivial
> Can you please help me with the event trigger in C? & also how can we
> integrate it with our current postgresql DB?
In an e-mail, I cannot do much beyond pointing you to the documentation:
https://www.postgresql.org/docs/current/event-trigger-interface.html
There is also a simple example:
https://www.postgresql.org/docs/current/event-trigger-example.html
Getting used to reading and writing PostgreSQL server code takes a while.
Yours,
Laurenz Albe
Hi Laurenz,
Is it possible to access the pg_ddl_command using C function? In the shared links, I couldn't find one. Can you please share an example for the same?
Thanks & Regards,
Neethu
From: Laurenz Albe <laurenz.albe@cybertec.at>
Sent: Thursday, February 23, 2023 4:20 PM
To: Neethu P <neeth_3@hotmail.com>; n.kobzarev@aeronavigator.ru <n.kobzarev@aeronavigator.ru>; 'pgsql-general' <pgsql-general@postgresql.org>
Subject: Re: Event Triggers unable to capture the DDL script executed
Sent: Thursday, February 23, 2023 4:20 PM
To: Neethu P <neeth_3@hotmail.com>; n.kobzarev@aeronavigator.ru <n.kobzarev@aeronavigator.ru>; 'pgsql-general' <pgsql-general@postgresql.org>
Subject: Re: Event Triggers unable to capture the DDL script executed
On Thu, 2023-02-23 at 04:10 +0000, Neethu P wrote:
> 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.
Multiple lines should not be a problem. The problems I see are
- you won't catch DDL statements issued in a function with that, since you
only see the top-level statement
- if you have the DDL statement as string, you need to parse it, which is
non-trivial
> Can you please help me with the event trigger in C? & also how can we
> integrate it with our current postgresql DB?
In an e-mail, I cannot do much beyond pointing you to the documentation:
https://www.postgresql.org/docs/current/event-trigger-interface.html
There is also a simple example:
https://www.postgresql.org/docs/current/event-trigger-example.html
Getting used to reading and writing PostgreSQL server code takes a while.
Yours,
Laurenz Albe
> 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.
Multiple lines should not be a problem. The problems I see are
- you won't catch DDL statements issued in a function with that, since you
only see the top-level statement
- if you have the DDL statement as string, you need to parse it, which is
non-trivial
> Can you please help me with the event trigger in C? & also how can we
> integrate it with our current postgresql DB?
In an e-mail, I cannot do much beyond pointing you to the documentation:
https://www.postgresql.org/docs/current/event-trigger-interface.html
There is also a simple example:
https://www.postgresql.org/docs/current/event-trigger-example.html
Getting used to reading and writing PostgreSQL server code takes a while.
Yours,
Laurenz Albe
On Mon, 2023-02-27 at 03:52 +0000, Neethu P wrote: > Is it possible to access the pg_ddl_command using C function? In the shared links, > I couldn't find one. Can you please share an example for the same? No. You could hire a professional. Yours, Laurenz Albe