From: Joe Wildish <joe@lateraljoin.com>
To: pgsql-general@lists.postgresql.org
Subject: Getting the exact SQL from inside an event trigger
Date: Thu, 02 Mar 2023 11:12:37 +0000
Message-ID: <10164c95-ef23-4624-9251-ea6ff594cd1c@app.fastmail.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: joe@lateraljoin.com
Subject: Re: Getting the exact SQL from inside an event trigger
In-Reply-To: <10164c95-ef23-4624-9251-ea6ff594cd1c@app.fastmail.com>
References: <10164c95-ef23-4624-9251-ea6ff594cd1c@app.fastmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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