Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pXgs0-0001x6-TP for pgsql-general@arkaria.postgresql.org; Thu, 02 Mar 2023 11:13:09 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pXgrz-00048w-Ip for pgsql-general@arkaria.postgresql.org; Thu, 02 Mar 2023 11:13:07 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pXgry-00048Y-Lq for pgsql-general@lists.postgresql.org; Thu, 02 Mar 2023 11:13:07 +0000 Received: from out2-smtp.messagingengine.com ([66.111.4.26]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pXgrv-0005T3-I0 for pgsql-general@lists.postgresql.org; Thu, 02 Mar 2023 11:13:06 +0000 Received: from compute6.internal (compute6.nyi.internal [10.202.2.47]) by mailout.nyi.internal (Postfix) with ESMTP id E66575C0252 for ; Thu, 2 Mar 2023 06:12:58 -0500 (EST) Received: from imap46 ([10.202.2.96]) by compute6.internal (MEProxy); Thu, 02 Mar 2023 06:12:58 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=lateraljoin.com; h=cc:content-type:content-type:date:date:from:from:in-reply-to :message-id:mime-version:reply-to:sender:subject:subject:to:to; s=fm3; t=1677755578; x=1677841978; bh=/pTe0YPbD26wz/h5L8lyvIYym zJ/fahdUDnpaKO7VG4=; b=cVGFCjfnPPKIuH1ZTW3EP5BCKRolTAd2XBaxUR2HP p98NAKi5pHwugSNBSRJjsC0/Kkmcp61L1uCQGan2tWh+aLT+mbzUMpem+MKoNTmq k3+M3CGVc59u7f5EMXfx26xa0Zz3yLIuSmN7H2oFOe88WmgIVrs34ITlpajLePZJ 9UG2Iwi/kM6j5lqN2AWJk+XCugYtQc3uyGb0iJOVaUmfMTirVbBvi5Gt+k7OkUXb s/b7MSOSe2riIEzxlZA5wixnd3j9irXh093/i4SzBf4PfKsoUCyWEHOo34t39zuo iJ2OzVBO7uj4Enywgb+A1bTeJO1bvL6aFrXvWXHyejPEQ== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-type:content-type:date:date :feedback-id:feedback-id:from:from:in-reply-to:message-id :mime-version:reply-to:sender:subject:subject:to:to:x-me-proxy :x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s=fm1; t= 1677755578; x=1677841978; bh=/pTe0YPbD26wz/h5L8lyvIYymzJ/fahdUDn paKO7VG4=; b=pOg4tX46oGP/ldjOEAOqKIujZfTWEUfCsIuY29oxm1soYnfsXKC 4UMgvPAk5UmVCQ40JVeB77WXa5V9FsOVZL5xitqMIe5RpkaWxlUNjPNBaPg9m5S0 iTIa172eZOR1P+Z7m+Lq08I3qog+CDRrmutijKOyvIRmYUmMDDBYbyxIncPgRqMI QHm3MzuXSaHEu4lNZlP6OIIUK2jAhHfNSonmYTP8Wlv6twgPoxmR9kIaqqcjljqc 0p4IMG8lsnRezZ+nKBLnZNNepRiRiznO5yZhkW2tiEhpxsGJpnHTwqZBWUGQSe3F GNYD/5FFWxuJ5ZdCIZrELCsDCcHGBvgirIg== X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvhedrudeljedgvdegucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucenucfjughrpefofgggkfffhffvufgtsehttdertd erredtnecuhfhrohhmpedflfhovgcuhghilhguihhshhdfuceojhhovgeslhgrthgvrhgr lhhjohhinhdrtghomheqnecuggftrfgrthhtvghrnhepfffhjeefvdeghfekveejueekvd ffteegvdehgfegteetffetleegteelhefhgeelnecuvehluhhsthgvrhfuihiivgeptden ucfrrghrrghmpehmrghilhhfrhhomhepjhhovgeslhgrthgvrhgrlhhjohhinhdrtghomh X-ME-Proxy: Feedback-ID: i7cb144c0:Fastmail Received: by mailuser.nyi.internal (Postfix, from userid 501) id B9E8C2A20080; Thu, 2 Mar 2023 06:12:58 -0500 (EST) X-Mailer: MessagingEngine.com Webmail Interface User-Agent: Cyrus-JMAP/3.9.0-alpha0-183-gbf7d00f500-fm-20230220.001-gbf7d00f5 Mime-Version: 1.0 Message-Id: <10164c95-ef23-4624-9251-ea6ff594cd1c@app.fastmail.com> Date: Thu, 02 Mar 2023 11:12:37 +0000 From: "Joe Wildish" To: pgsql-general@lists.postgresql.org Subject: Getting the exact SQL from inside an event trigger Content-Type: text/plain List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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