pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedGetting the exact SQL from inside an event trigger
3+ messages / 2 participants
[nested] [flat]
* Getting the exact SQL from inside an event trigger
@ 2023-03-02 11:12 Joe Wildish <joe@lateraljoin.com>
0 siblings, 1 reply; 3+ messages in thread
From: Joe Wildish @ 2023-03-02 11:12 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
Hello all,
We are using event triggers to capture DDL for subsequent replay on a logical replica.
The intention is to write the DDL statement to a table, inside the same transaction that executes the DDL, and have a separate process on the replica notice changes in this table and execute whatever it finds.
We have declared a trigger on the ddl_command_end event for this purpose. We can get the SQL from running current_query() inside the trigger; oddly, the pg_event_trigger_ddl_commands() function does have an attribute called "command", typed as "pg_ddl_command", but there are no SQL functions that can operate on this type, including turning it into a string.
This process works for a simple case of e.g. "CREATE TABLE t()".
However, in other cases --- e.g. "DO $$ BEGIN CREATE TABLE t(); CREATE TABLE s(); END; $$;" --- the trigger will fire for each CREATE TABLE but the current_query() will evaluate to the entire DO block.
This makes it difficult to capture just the actual statement that is being executed. I am looking for a way to get the precise statement that is being executed from within the ddl_command_event trigger function. Does anyone know if this is possible?
Thanks,
-Joe
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Getting the exact SQL from inside an event trigger
@ 2023-03-02 12:29 hubert depesz lubaczewski <depesz@depesz.com>
parent: Joe Wildish <joe@lateraljoin.com>
0 siblings, 1 reply; 3+ messages in thread
From: hubert depesz lubaczewski @ 2023-03-02 12:29 UTC (permalink / raw)
To: Joe Wildish <joe@lateraljoin.com>; +Cc: pgsql-general@lists.postgresql.org
On Thu, Mar 02, 2023 at 11:12:37AM +0000, Joe Wildish wrote:
> We are using event triggers to capture DDL for subsequent replay on a logical replica.
This might be a bit different answer from what you expect, but have you
seen pgl_ddl_deploy project?
Best regards,
depesz
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Getting the exact SQL from inside an event trigger
@ 2023-03-02 13:01 Joe Wildish <joe@lateraljoin.com>
parent: hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 0 replies; 3+ messages in thread
From: Joe Wildish @ 2023-03-02 13:01 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org; depesz@depesz.com
Hi Depesz,
On Thu, 2 Mar 2023, at 12:29, hubert depesz lubaczewski wrote:
> This might be a bit different answer from what you expect, but have you
> seen pgl_ddl_deploy project?
Thanks --- I was unaware of this project so I will take a look.
However, we are operating under a limitation that the publisher is in a hosted provider, so we cannot install extensions unless they are "blessed"; hence looking for a solution that doesn't require that.
I did go and look at what pgaudit does WRT to this problem as I know that that extension can emit just the statement being executed. I didn't fully understand all the detail, but did notice that it maintains a stack of statements for the current execution, presumably so it knows if the trigger has been called already, e.g. in the case of a DO block. This indicates to me that I can't do what I would like in pure PLPGSQL triggers.
-Joe
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2023-03-02 13:01 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-03-02 11:12 Getting the exact SQL from inside an event trigger Joe Wildish <joe@lateraljoin.com>
2023-03-02 12:29 ` hubert depesz lubaczewski <depesz@depesz.com>
2023-03-02 13:01 ` Joe Wildish <joe@lateraljoin.com>
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