Thread: Getting the exact SQL from inside an event trigger

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
separateprocess 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
intoa 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
foreach 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
precisestatement that is being executed from within the ddl_command_event trigger function.  Does anyone know if this
ispossible?
 

Thanks,
-Joe

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

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
extensionsunless 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
beingexecuted.  I didn't fully understand all the detail, but did notice that it maintains a stack of statements for
thecurrent execution, presumably so it knows if the trigger has been called already, e.g. in the case of a DO block.
Thisindicates to me that I can't do what I would like in pure PLPGSQL triggers.
 

-Joe