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

-----Исходное сообщение-----
От: 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();

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

-----Исходное сообщение-----
От: 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

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

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

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

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