pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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









view thread (3+ messages)  latest in thread

Message-ID: <10164c95-ef23-4624-9251-ea6ff594cd1c@app.fastmail.com>
Permalink:  ../10164c95-ef23-4624-9251-ea6ff594cd1c@app.fastmail.com/
Also on:    postgresql.org/message-id/10164c95-ef23-4624-9251-ea6ff594cd1c@app.fastmail.com

 · 

reply

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