From: | "Joe Wildish" <joe(at)lateraljoin(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Getting the exact SQL from inside an event trigger |
Date: | 2023-03-02 11:12:37 |
Message-ID: | 10164c95-ef23-4624-9251-ea6ff594cd1c@app.fastmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | hubert depesz lubaczewski | 2023-03-02 12:29:02 | Re: Getting the exact SQL from inside an event trigger |
Previous Message | Dominique Devienne | 2023-03-02 09:58:45 | Re: Postgres Index and Updates |