pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedEvent Triggers unable to capture the DDL script executed
8+ messages / 3 participants
[nested] [flat]
* Event Triggers unable to capture the DDL script executed
@ 2023-02-22 07:57 Neethu P <neeth_3@hotmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Neethu P @ 2023-02-22 07:57 UTC (permalink / raw)
To: pgsql-general
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Event Triggers unable to capture the DDL script executed
@ 2023-02-22 09:51 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Neethu P <neeth_3@hotmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Laurenz Albe @ 2023-02-22 09:51 UTC (permalink / raw)
To: Neethu P <neeth_3@hotmail.com>; pgsql-general
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: Event Triggers unable to capture the DDL script executed
@ 2023-02-22 10:03 n.kobzarev@aeronavigator.ru
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 8+ messages in thread
From: n.kobzarev@aeronavigator.ru @ 2023-02-22 10:03 UTC (permalink / raw)
To: 'Laurenz Albe' <laurenz.albe@cybertec.at>; 'Neethu P' <neeth_3@hotmail.com>; pgsql-general
>>-----Исходное сообщение-----
>>От: 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();
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Event Triggers unable to capture the DDL script executed
@ 2023-02-23 04:10 Neethu P <neeth_3@hotmail.com>
parent: n.kobzarev@aeronavigator.ru
0 siblings, 1 reply; 8+ messages in thread
From: Neethu P @ 2023-02-23 04:10 UTC (permalink / raw)
To: n.kobzarev@aeronavigator.ru <n.kobzarev@aeronavigator.ru>; Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-general
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();
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Event Triggers unable to capture the DDL script executed
@ 2023-02-23 10:50 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Neethu P <neeth_3@hotmail.com>
0 siblings, 2 replies; 8+ messages in thread
From: Laurenz Albe @ 2023-02-23 10:50 UTC (permalink / raw)
To: Neethu P <neeth_3@hotmail.com>; n.kobzarev@aeronavigator.ru <n.kobzarev@aeronavigator.ru>; pgsql-general
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Event Triggers unable to capture the DDL script executed
@ 2023-02-23 11:33 Neethu P <neeth_3@hotmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 0 replies; 8+ messages in thread
From: Neethu P @ 2023-02-23 11:33 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; n.kobzarev@aeronavigator.ru <n.kobzarev@aeronavigator.ru>; pgsql-general
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Event Triggers unable to capture the DDL script executed
@ 2023-02-27 03:52 Neethu P <neeth_3@hotmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 1 reply; 8+ messages in thread
From: Neethu P @ 2023-02-27 03:52 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; n.kobzarev@aeronavigator.ru <n.kobzarev@aeronavigator.ru>; pgsql-general
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Event Triggers unable to capture the DDL script executed
@ 2023-02-27 12:14 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Neethu P <neeth_3@hotmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Laurenz Albe @ 2023-02-27 12:14 UTC (permalink / raw)
To: Neethu P <neeth_3@hotmail.com>; n.kobzarev@aeronavigator.ru <n.kobzarev@aeronavigator.ru>; pgsql-general
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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2023-02-27 12:14 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-02-22 07:57 Event Triggers unable to capture the DDL script executed Neethu P <neeth_3@hotmail.com>
2023-02-22 09:51 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-02-22 10:03 ` n.kobzarev@aeronavigator.ru
2023-02-23 04:10 ` Neethu P <neeth_3@hotmail.com>
2023-02-23 10:50 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-02-23 11:33 ` Neethu P <neeth_3@hotmail.com>
2023-02-27 03:52 ` Neethu P <neeth_3@hotmail.com>
2023-02-27 12:14 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox