Subject: Re: RFC: Logging plan of the running query

From: Ekaterina Sokolova <[email-masked]>
Date: Tue, 02 Nov 2021 14:32:59 +0300

Hi!

I'm here to answer your questions about contrib/pg_query_state.
>> I only took a quick look at pg_query_state, I have some questions.

>> pg_query_state seems using shm_mq to expose the plan information, but
>> there was a discussion that this kind of architecture would be tricky
>> to do properly [1].
>> Does pg_query_state handle difficulties listed on the discussion?
> [1] 
> https://www.postgresql.org/message-id/9a50371e15e741e295accabc72a41df1%40oss.nttdata.com

I doubt that it was the right link.
But on the topic I will say that extension really use shared memory, 
interaction is implemented by sending / receiving messages. This 
architecture provides the required reliability and convenience.

>> It seems the caller of the pg_query_state() has to wait until the
>> target process pushes the plan information into shared memory, can it
>> lead to deadlock situations?
>> I came up with this question because when trying to make a view for
>> memory contexts of other backends, we encountered deadlock situations.
>> After all, we gave up view design and adopted sending signal and
>> logging.
> 
> Discussion at the following URL.
> https://www.postgresql.org/message-id/9a50371e15e741e295accabc72a41df1%40oss.nttdata.com

Before extracting information about side process we check its state. 
Information will only be retrieved for a process willing to provide it. 
Otherwise, we will receive an error message about impossibility of 
getting query execution statistics + process status. Also checking fact 
of extracting your own status exists. This is even verified in tests.

Thanks for your attention.
Just in case, I am ready to discuss this topic in more detail.

About overhead:
> I haven't measured it yet, but I believe that the overhead for backends
> which are not called pg_log_current_plan() would be slight since the
> patch just adds the logic for saving QueryDesc on ExecutorRun().
> The overhead for backends which is called pg_log_current_plan() might
> not slight, but since the target process are assumed dealing with
> long-running query and the user want to know its plan, its overhead
> would be worth the cost.
I think it would be useful for us to have couple of examples with a 
different number of rows compared to using without this functionality.

Hope this helps.

-- 
Ekaterina Sokolova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

---

From: Daniel Gustafsson <[email-masked]>
Date: Wed, 3 Nov 2021 13:48:51 +0100

This patch no longer applies on top of HEAD, please submit a rebased version.

--
Daniel Gustafsson		https://vmware.com/

---

From: torikoshia <[email-masked]>
Date: Thu, 04 Nov 2021 21:49:21 +0900

On 2021-11-02 20:32, Ekaterina Sokolova wrote:
Thanks for your response!

> I doubt that it was the right link.

Sorry for make you confused, here is the link.

https://www.postgresql.org/message-id/CA%2BTgmobkpFV0UB67kzXuD36--OFHwz1bs%3DL_6PZbD4nxKqUQMw%40mail.gmail.com

> architecture provides the required reliability and convenience.

As described in the link, using shared memory for this kind of work 
would need DSM and It'd be also necessary to exchange information 
between requestor and responder.

For example, when I looked at a little bit of pg_query_state code, it 
looks like the size of the queue is fixed at QUEUE_SIZE, and I wonder 
how plans that exceed QUEUE_SIZE are handled.

> Information will only be retrieved for a process willing to provide
> it. Otherwise, we will receive an error message about impossibility of
> getting query execution statistics + process status. Also checking
> fact of extracting your own status exists. This is even verified in
> tests.
> 

I imagined the following procedure.
Does it cause dead lock in pg_query_state?

- session1
BEGIN; TRUNCATE t;

- session2
BEGIN; TRUNCATE t; -- wait

- session1
SELECT * FROM pg_query_state(<pid of session>); -- wait and dead locked?

> different number of rows compared to using without this functionality.

Do you have any expectaion that the number of rows would affect the 
performance of this functionality?
This patch adds some codes to ExecutorRun(), but I thought the number of 
rows would not give impact on the performance.

-- 
Regards,

--
Atsushi Torikoshi
NTT DATA CORPORATION

---

From: Ekaterina Sokolova <[email-masked]>
Date: Wed, 17 Nov 2021 16:44:51 +0300

Hi!

You forgot my last fix to build correctly on Mac. I have added it.

About our discussion of pg_query_state:

> https://www.postgresql.org/message-id/CA%2BTgmobkpFV0UB67kzXuD36--OFHwz1bs%3DL_6PZbD4nxKqUQMw%40mail.gmail.com

Thank you. I'll see it soon.

> SELECT * FROM pg_query_state(<pid of session>); -- wait and dead 

As I know, pg_query_state use non-blocking read and write. I have wrote 
few tests trying to deadlock it (on 14 version), but all finished 
correctly.

Have a nice day. Please feel free to contact me if you need any further 
information.

From b8367e22d7a9898e4b85627ba8c203be273fc22f Mon Sep 17 00:00:00 2001
From: Atsushi Torikoshi <[email-masked]>
Date: Mon, 15 Nov 2021 22:31:00 +0900
Subject: [PATCH v13] Add function to log the untruncated query string and its
 plan for the query currently running on the backend with the specified
 process ID.

Currently, we have to wait for the query execution to finish
to check its plan. This is not so convenient when
investigating long-running queries on production environments
where we cannot use debuggers.
To improve this situation, this patch adds
pg_log_query_plan() function that requests to log the
plan of the specified backend process.

By default, only superusers are allowed to request to log the
plans because allowing any users to issue this request at an
unbounded rate would cause lots of log messages and which can
lead to denial of service.

On receipt of the request, at the next CHECK_FOR_INTERRUPTS(),
the target backend logs its plan at LOG_SERVER_ONLY level, so
that these plans will appear in the server log but not be sent
to the client.

Since some codes, tests and comments of
pg_log_query_plan() are the same with
pg_log_backend_memory_contexts(), this patch also refactors
them to make them common.

Reviewed-by: Bharath Rupireddy, Fujii Masao, Dilip Kumar, Masahiro Ikeda, Ekaterina Sokolova, Justin Pryzby

---
 doc/src/sgml/func.sgml                       |  45 +++++++
 src/backend/catalog/system_functions.sql     |   2 +
 src/backend/commands/explain.c               | 117 ++++++++++++++++++-
 src/backend/executor/execMain.c              |  10 ++
 src/backend/storage/ipc/procsignal.c         |   4 +
 src/backend/storage/ipc/signalfuncs.c        |  55 +++++++++
 src/backend/storage/lmgr/lock.c              |   9 +-
 src/backend/tcop/postgres.c                  |   7 ++
 src/backend/utils/adt/mcxtfuncs.c            |  36 +-----
 src/backend/utils/init/globals.c             |   1 +
 src/include/catalog/pg_proc.dat              |   6 +
 src/include/commands/explain.h               |   3 +
 src/include/miscadmin.h                      |   1 +
 src/include/storage/lock.h                   |   2 -
 src/include/storage/procsignal.h             |   1 +
 src/include/storage/signalfuncs.h            |  22 ++++
 src/include/tcop/pquery.h                    |   1 +
 src/test/regress/expected/misc_functions.out |  54 +++++++--
 src/test/regress/sql/misc_functions.sql      |  42 +++++--
 19 files changed, 355 insertions(+), 63 deletions(-)
 create mode 100644 src/include/storage/signalfuncs.h

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 24447c0017..7ffaa9a55d 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -25345,6 +25345,26 @@ SELECT collation for ('foo' COLLATE "de_DE");
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_log_query_plan</primary>
+        </indexterm>
+        <function>pg_log_query_plan</function> ( <parameter>pid</parameter> <type>integer</type> )
+        <returnvalue>boolean</returnvalue>
+       </para>
+       <para>
+        Requests to log the plan of the query currently running on the
+        backend with specified process ID along with the untruncated
+        query string.
+        They will be logged at <literal>LOG</literal> message level and
+        will appear in the server log based on the log
+        configuration set (See <xref linkend="runtime-config-logging"/>
+        for more information), but will not be sent to the client
+        regardless of <xref linkend="guc-client-min-messages"/>.
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
@@ -25458,6 +25478,31 @@ LOG:  Grand total: 1651920 bytes in 201 blocks; 622360 free (88 chunks); 1029560
     because it may generate a large number of log messages.
    </para>
 
+   <para>
+    <function>pg_log_query_plan</function> can be used
+    to log the plan of a backend process. For example:
+<programlisting>
+postgres=# SELECT pg_log_query_plan(201116);
+ pg_log_query_plan
+---------------------------
+ t
+(1 row)
+</programlisting>
+The format of the query plan is the same as when <literal>VERBOSE</literal>,
+<literal>COSTS</literal>, <literal>SETTINGS</literal> and
+<literal>FORMAT TEXT</literal> are used in the <command>EXPLAIN</command>
+command. For example:
+<screen>
+LOG:  plan of the query running on backend with PID 17793 is:
+        Query Text: SELECT * FROM pgbench_accounts;
+        Seq Scan on public.pgbench_accounts  (cost=0.00..52787.00 rows=2000000 width=97)
+          Output: aid, bid, abalance, filler
+        Settings: work_mem = '1MB'
+</screen>
+    Note that nested statements (statements executed inside a function) are not
+    considered for logging. Only the plan of the most deeply nested query is logged.
+   </para>
+
   </sect2>
 
   <sect2 id="functions-admin-backup">
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 54c93b16c4..d7f0010e47 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -701,6 +701,8 @@ REVOKE EXECUTE ON FUNCTION pg_ls_dir(text,boolean,boolean) FROM public;
 
 REVOKE EXECUTE ON FUNCTION pg_log_backend_memory_contexts(integer) FROM PUBLIC;
 
+REVOKE EXECUTE ON FUNCTION pg_log_query_plan(integer) FROM PUBLIC;
+
 --
 -- We also set up some things as accessible to standard roles.
 --
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 10644dfac4..d930200987 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -21,6 +21,7 @@
 #include "executor/nodeHash.h"
 #include "foreign/fdwapi.h"
 #include "jit/jit.h"
+#include "miscadmin.h"
 #include "nodes/extensible.h"
 #include "nodes/makefuncs.h"
 #include "nodes/nodeFuncs.h"
@@ -28,6 +29,9 @@
 #include "parser/parsetree.h"
 #include "rewrite/rewriteHandler.h"
 #include "storage/bufmgr.h"
+#include "storage/procarray.h"
+#include "storage/signalfuncs.h"
+#include "tcop/pquery.h"
 #include "tcop/tcopprot.h"
 #include "utils/builtins.h"
 #include "utils/guc_tables.h"
@@ -40,7 +44,6 @@
 #include "utils/typcache.h"
 #include "utils/xml.h"
 
-
 /* Hook for plugins to get control in ExplainOneQuery() */
 ExplainOneQuery_hook_type ExplainOneQuery_hook = NULL;
 
@@ -1594,6 +1597,9 @@ ExplainNode(PlanState *planstate, List *ancestors,
 	/*
 	 * We have to forcibly clean up the instrumentation state because we
 	 * haven't done ExecutorEnd yet.  This is pretty grotty ...
+	 * This cleanup should not be done when the query has already been
+	 * executed, as the target query may use instrumentation and clean
+	 * itself up.
 	 *
 	 * Note: contrib/auto_explain could cause instrumentation to be set up
 	 * even though we didn't ask for it here.  Be careful not to print any
@@ -1601,7 +1607,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 	 * InstrEndLoop call anyway, if possible, to reduce the number of cases
 	 * auto_explain has to contend with.
 	 */
-	if (planstate->instrument)
+	if (planstate->instrument && !es->running)
 		InstrEndLoop(planstate->instrument);
 
 	if (es->analyze &&
@@ -4922,3 +4928,110 @@ escape_yaml(StringInfo buf, const char *str)
 {
 	escape_json(buf, str);
 }
+
+/*
+ * HandleLogCurrentPlanInterrupt
+ *		Handle receipt of an interrupt indicating logging the plan of
+ *		the currently running query.
+ *
+ * All the actual work is deferred to ProcessLogCurrentPlanInterrupt(),
+ * because we cannot safely emit a log message inside the signal handler.
+ */
+void
+HandleLogCurrentPlanInterrupt(void)
+{
+	InterruptPending = true;
+	LogCurrentPlanPending = true;
+	/* latch will be set by procsignal_sigusr1_handler */
+}
+
+/*
+ * ProcessLogCurrentPlanInterrupt
+ * 		Perform logging the plan of the currently running query on this
+ * 		backend process.
+ *
+ * Any backend that participates in ProcSignal signaling must arrange to call
+ * this function if we see LogCurrentPlanPending set.
+ * It is called from CHECK_FOR_INTERRUPTS(), which is enough because the target
+ * process for logging plan is a backend.
+ */
+void
+ProcessLogCurrentPlanInterrupt(void)
+{
+	ExplainState *es;
+	HASH_SEQ_STATUS status;
+	LOCALLOCK  *locallock;
+	LogCurrentPlanPending = false;
+
+	if (ActiveQueryDesc == NULL)
+	{
+		ereport(LOG_SERVER_ONLY,
+				errmsg("backend with PID %d is not running a query",
+					MyProcPid));
+		return;
+	}
+
+	/*
+	 * Ensure no page lock is held on this process.
+	 *
+	 * If page lock is held at the time of the interrupt, we can't acquire any
+	 * other heavyweight lock, which might be necessary for explaining the plan
+	 * when retrieving column names.
+	 *
+	 * This may be overkill, but since page locks are held for a short duration
+	 * we check all the LocalLock entries and when finding even one, give up
+	 * logging the plan.
+	 */
+	hash_seq_init(&status, GetLockMethodLocalHash());
+	while ((locallock = (LOCALLOCK *) hash_seq_search(&status)) != NULL)
+	{
+		if (LOCALLOCK_LOCKTAG(*locallock) == LOCKTAG_PAGE)
+		{
+			ereport(LOG_SERVER_ONLY,
+				errmsg("backend with PID %d is holding a page lock. Try again",
+					MyProcPid));
+			hash_seq_term(&status);
+			return;
+		}
+	}
+
+	es = NewExplainState();
+
+	es->format = EXPLAIN_FORMAT_TEXT;
+	es->settings = true;
+	es->verbose = true;
+	es->running = true;
+
+	ExplainBeginOutput(es);
+	ExplainQueryText(es, ActiveQueryDesc);
+	ExplainPrintPlan(es, ActiveQueryDesc);
+	if (es->costs)
+		ExplainPrintJITSummary(es, ActiveQueryDesc);
+	ExplainEndOutput(es);
+
+	/* Remove last line break */
+	if (es->str->len > 0 && es->str->data[es->str->len - 1] == '\n')
+		es->str->data[--es->str->len] = '\0';
+
+	ereport(LOG_SERVER_ONLY,
+			errmsg("query plan running on backend with PID %d is:\n%s",
+					MyProcPid, es->str->data),
+			 errhidestmt(true),
+			 errhidecontext(true));
+}
+
+/*
+ * pg_log_query_plan
+ *		Signal a backend process to log the query plan of the running query.
+ *
+ * By default, only superusers are allowed to signal to log the plan because
+ * allowing any users to issue this request at an unbounded rate would
+ * cause lots of log messages and which can lead to denial of service.
+ * Additional roles can be permitted with GRANT.
+ */
+Datum
+pg_log_query_plan(PG_FUNCTION_ARGS)
+{
+	int			pid = PG_GETARG_INT32(0);
+	PG_RETURN_BOOL(SendProcSignalForLogInfo(pid, PROCSIG_LOG_CURRENT_PLAN));
+}
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index b3ce4bae53..c74aa1d04e 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -76,6 +76,9 @@ ExecutorEnd_hook_type ExecutorEnd_hook = NULL;
 /* Hook for plugin to get control in ExecCheckRTPerms() */
 ExecutorCheckPerms_hook_type ExecutorCheckPerms_hook = NULL;
 
+/* Currently executing query's QueryDesc */
+QueryDesc *ActiveQueryDesc = NULL;
+
 /* decls for local routines only used within this module */
 static void InitPlan(QueryDesc *queryDesc, int eflags);
 static void CheckValidRowMarkRel(Relation rel, RowMarkType markType);
@@ -299,10 +302,17 @@ ExecutorRun(QueryDesc *queryDesc,
 			ScanDirection direction, uint64 count,
 			bool execute_once)
 {
+	QueryDesc *save_ActiveQueryDesc;
+
+	save_ActiveQueryDesc = ActiveQueryDesc;
+	ActiveQueryDesc = queryDesc;
+
 	if (ExecutorRun_hook)
 		(*ExecutorRun_hook) (queryDesc, direction, count, execute_once);
 	else
 		standard_ExecutorRun(queryDesc, direction, count, execute_once);
+
+	ActiveQueryDesc = save_ActiveQueryDesc;
 }
 
 void
diff --git a/src/backend/storage/ipc/procsignal.c b/src/backend/storage/ipc/procsignal.c
index 6e69398cdd..6ea63e0c53 100644
--- a/src/backend/storage/ipc/procsignal.c
+++ b/src/backend/storage/ipc/procsignal.c
@@ -20,6 +20,7 @@
 #include "access/parallel.h"
 #include "port/pg_bitutils.h"
 #include "commands/async.h"
+#include "commands/explain.h"
 #include "miscadmin.h"
 #include "pgstat.h"
 #include "replication/walsender.h"
@@ -661,6 +662,9 @@ procsignal_sigusr1_handler(SIGNAL_ARGS)
 	if (CheckProcSignal(PROCSIG_LOG_MEMORY_CONTEXT))
 		HandleLogMemoryContextInterrupt();
 
+	if (CheckProcSignal(PROCSIG_LOG_CURRENT_PLAN))
+		HandleLogCurrentPlanInterrupt();
+
 	if (CheckProcSignal(PROCSIG_RECOVERY_CONFLICT_DATABASE))
 		RecoveryConflictInterrupt(PROCSIG_RECOVERY_CONFLICT_DATABASE);
 
diff --git a/src/backend/storage/ipc/signalfuncs.c b/src/backend/storage/ipc/signalfuncs.c
index de69d60e79..7aeec69ea5 100644
--- a/src/backend/storage/ipc/signalfuncs.c
+++ b/src/backend/storage/ipc/signalfuncs.c
@@ -23,6 +23,7 @@
 #include "storage/pmsignal.h"
 #include "storage/proc.h"
 #include "storage/procarray.h"
+#include "storage/signalfuncs.h"
 #include "utils/acl.h"
 #include "utils/builtins.h"
 
@@ -298,3 +299,57 @@ pg_rotate_logfile_v2(PG_FUNCTION_ARGS)
 	SendPostmasterSignal(PMSIGNAL_ROTATE_LOGFILE);
 	PG_RETURN_BOOL(true);
 }
+
+/*
+ * Signal a backend process to log its information.
+ *
+ * By default, only superusers are allowed to signal to log information
+ * because allowing any users to issue this request at an unbounded
+ * rate would cause lots of log messages which can lead to denial of
+ * service. Additional roles can be permitted with GRANT.
+ *
+ * On receipt of this signal, a backend sets the flag in the signal
+ * handler, which causes the next CHECK_FOR_INTERRUPTS() to log the
+ * information.
+ */
+bool
+SendProcSignalForLogInfo(pid_t pid, ProcSignalReason reason)
+{
+	PGPROC	   *proc;
+
+	Assert(reason == PROCSIG_LOG_MEMORY_CONTEXT ||
+		   reason == PROCSIG_LOG_CURRENT_PLAN);
+
+	proc = BackendPidGetProc(pid);
+
+	/*
+	 * BackendPidGetProc returns NULL if the pid isn't valid; but by the time
+	 * we reach kill(), a process for which we get a valid proc here might
+	 * have terminated on its own.  There's no way to acquire a lock on an
+	 * arbitrary process to prevent that. But since this mechanism is usually
+	 * used for below purposes, it might end its own first and the information
+	 * is not logged is not a problem.
+	 * - debug a backend running and consuming lots of memory
+	 * - look into the plan of the long running query
+	 */
+	if (proc == NULL)
+	{
+		/*
+		 * This is just a warning so a loop-through-resultset will not abort
+		 * if one backend terminated on its own during the run.
+		 */
+		ereport(WARNING,
+				errmsg("PID %d is not a PostgreSQL server process", pid));
+		return false;
+	}
+
+	if (SendProcSignal(pid, reason, proc->backendId) < 0)
+	{
+		/* Again, just a warning to allow loops */
+		ereport(WARNING,
+				errmsg("could not send signal to process %d: %m", pid));
+		return false;
+	}
+
+	return true;
+}
diff --git a/src/backend/storage/lmgr/lock.c b/src/backend/storage/lmgr/lock.c
index c25af7fe09..8a70a6d94e 100644
--- a/src/backend/storage/lmgr/lock.c
+++ b/src/backend/storage/lmgr/lock.c
@@ -614,17 +614,18 @@ LockHeldByMe(const LOCKTAG *locktag, LOCKMODE lockmode)
 	return (locallock && locallock->nLocks > 0);
 }
 
-#ifdef USE_ASSERT_CHECKING
 /*
- * GetLockMethodLocalHash -- return the hash of local locks, for modules that
- *		evaluate assertions based on all locks held.
+ * GetLockMethodLocalHash -- return the hash of local locks, mainly for
+ *		modules that evaluate assertions based on all locks held.
+ *
+ * NOTE: When there are many entries in LockMethodLocalHash, calling this
+ * function and looking into all of them can lead to performance problems.
  */
 HTAB *
 GetLockMethodLocalHash(void)
 {
 	return LockMethodLocalHash;
 }
-#endif
 
 /*
  * LockHasWaiters -- look up 'locktag' and check if releasing this
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 0775abe35d..774dadb87a 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -41,6 +41,7 @@
 #include "access/xact.h"
 #include "catalog/pg_type.h"
 #include "commands/async.h"
+#include "commands/explain.h"
 #include "commands/prepare.h"
 #include "executor/spi.h"
 #include "jit/jit.h"
@@ -3366,6 +3367,9 @@ ProcessInterrupts(void)
 
 	if (LogMemoryContextPending)
 		ProcessLogMemoryContextInterrupt();
+
+	if (LogCurrentPlanPending)
+		ProcessLogCurrentPlanInterrupt();
 }

@@ -4289,6 +4293,9 @@ PostgresMain(const char *dbname, const char *username)
 		/* We don't have a transaction command open anymore */
 		xact_started = false;
 
+		/* We have no running query */
+		ActiveQueryDesc = NULL;
+
 		/*
 		 * If an error occurred while we were reading a message from the
 		 * client, we have potentially lost track of where the previous
diff --git a/src/backend/utils/adt/mcxtfuncs.c b/src/backend/utils/adt/mcxtfuncs.c
index 6ddbf70b30..2db662282f 100644
--- a/src/backend/utils/adt/mcxtfuncs.c
+++ b/src/backend/utils/adt/mcxtfuncs.c
@@ -18,8 +18,8 @@
 #include "funcapi.h"
 #include "miscadmin.h"
 #include "mb/pg_wchar.h"
-#include "storage/proc.h"
 #include "storage/procarray.h"
+#include "storage/signalfuncs.h"
 #include "utils/builtins.h"
 
 /* ----------
@@ -175,37 +175,5 @@ Datum
 pg_log_backend_memory_contexts(PG_FUNCTION_ARGS)
 {
 	int			pid = PG_GETARG_INT32(0);
-	PGPROC	   *proc;
-
-	proc = BackendPidGetProc(pid);
-
-	/*
-	 * BackendPidGetProc returns NULL if the pid isn't valid; but by the time
-	 * we reach kill(), a process for which we get a valid proc here might
-	 * have terminated on its own.  There's no way to acquire a lock on an
-	 * arbitrary process to prevent that. But since this mechanism is usually
-	 * used to debug a backend running and consuming lots of memory, that it
-	 * might end on its own first and its memory contexts are not logged is
-	 * not a problem.
-	 */
-	if (proc == NULL)
-	{
-		/*
-		 * This is just a warning so a loop-through-resultset will not abort
-		 * if one backend terminated on its own during the run.
-		 */
-		ereport(WARNING,
-				(errmsg("PID %d is not a PostgreSQL server process", pid)));
-		PG_RETURN_BOOL(false);
-	}
-
-	if (SendProcSignal(pid, PROCSIG_LOG_MEMORY_CONTEXT, proc->backendId) < 0)
-	{
-		/* Again, just a warning to allow loops */
-		ereport(WARNING,
-				(errmsg("could not send signal to process %d: %m", pid)));
-		PG_RETURN_BOOL(false);
-	}
-
-	PG_RETURN_BOOL(true);
+	PG_RETURN_BOOL(SendProcSignalForLogInfo(pid, PROCSIG_LOG_MEMORY_CONTEXT));
 }
diff --git a/src/backend/utils/init/globals.c b/src/backend/utils/init/globals.c
index 381d9e548d..126ed10362 100644
--- a/src/backend/utils/init/globals.c
+++ b/src/backend/utils/init/globals.c
@@ -36,6 +36,7 @@ volatile sig_atomic_t IdleInTransactionSessionTimeoutPending = false;
 volatile sig_atomic_t IdleSessionTimeoutPending = false;
 volatile sig_atomic_t ProcSignalBarrierPending = false;
 volatile sig_atomic_t LogMemoryContextPending = false;
+volatile sig_atomic_t LogCurrentPlanPending = false;
 volatile uint32 InterruptHoldoffCount = 0;
 volatile uint32 QueryCancelHoldoffCount = 0;
 volatile uint32 CritSectionCount = 0;
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index d068d6532e..0b96cf61b2 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8038,6 +8038,12 @@
   prorettype => 'bool', proargtypes => 'int4',
   prosrc => 'pg_log_backend_memory_contexts' },
 
+# logging plan of the running query on the specified backend
+{ oid => '4545', descr => 'log plan of the running query on the specified backend',
+  proname => 'pg_log_query_plan',
+  provolatile => 'v', prorettype => 'bool',
+  proargtypes => 'int4', prosrc => 'pg_log_query_plan' },
+
 # non-persistent series generator
 { oid => '1066', descr => 'non-persistent series generator',
   proname => 'generate_series', prorows => '1000',
diff --git a/src/include/commands/explain.h b/src/include/commands/explain.h
index e94d9e49cf..7c2dce7cdf 100644
--- a/src/include/commands/explain.h
+++ b/src/include/commands/explain.h
@@ -59,6 +59,7 @@ typedef struct ExplainState
 	bool		hide_workers;	/* set if we find an invisible Gather */
 	/* state related to the current plan node */
 	ExplainWorkersState *workers_state; /* needed if parallel plan */
+	bool		running;		/* whether target query is already running */
 } ExplainState;
 
 /* Hook for plugins to get control in ExplainOneQuery() */
@@ -124,4 +125,6 @@ extern void ExplainOpenGroup(const char *objtype, const char *labelname,
 extern void ExplainCloseGroup(const char *objtype, const char *labelname,
 							  bool labeled, ExplainState *es);
 
+extern void HandleLogCurrentPlanInterrupt(void);
+extern void ProcessLogCurrentPlanInterrupt(void);
 #endif							/* EXPLAIN_H */
diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h
index 90a3016065..0fb0da77e2 100644
--- a/src/include/miscadmin.h
+++ b/src/include/miscadmin.h
@@ -94,6 +94,7 @@ extern PGDLLIMPORT volatile sig_atomic_t IdleInTransactionSessionTimeoutPending;
 extern PGDLLIMPORT volatile sig_atomic_t IdleSessionTimeoutPending;
 extern PGDLLIMPORT volatile sig_atomic_t ProcSignalBarrierPending;
 extern PGDLLIMPORT volatile sig_atomic_t LogMemoryContextPending;
+extern PGDLLIMPORT volatile sig_atomic_t LogCurrentPlanPending;
 
 extern PGDLLIMPORT volatile sig_atomic_t CheckClientConnectionPending;
 extern PGDLLIMPORT volatile sig_atomic_t ClientConnectionLost;
diff --git a/src/include/storage/lock.h b/src/include/storage/lock.h
index a5286fab893..4f0eaf83fbc 100644
--- a/src/include/storage/lock.h
+++ b/src/include/storage/lock.h
@@ -561,9 +561,7 @@ extern void LockReleaseSession(LOCKMETHODID lockmethodid);
 extern void LockReleaseCurrentOwner(LOCALLOCK **locallocks, int nlocks);
 extern void LockReassignCurrentOwner(LOCALLOCK **locallocks, int nlocks);
 extern bool LockHeldByMe(const LOCKTAG *locktag, LOCKMODE lockmode);
-#ifdef USE_ASSERT_CHECKING
 extern HTAB *GetLockMethodLocalHash(void);
-#endif
 extern bool LockHasWaiters(const LOCKTAG *locktag,
 						   LOCKMODE lockmode, bool sessionLock);
 extern VirtualTransactionId *GetLockConflicts(const LOCKTAG *locktag,
diff --git a/src/include/storage/procsignal.h b/src/include/storage/procsignal.h
index eec186be2e..c1a7218d69 100644
--- a/src/include/storage/procsignal.h
+++ b/src/include/storage/procsignal.h
@@ -35,6 +35,7 @@ typedef enum
 	PROCSIG_WALSND_INIT_STOPPING,	/* ask walsenders to prepare for shutdown  */
 	PROCSIG_BARRIER,			/* global barrier interrupt  */
 	PROCSIG_LOG_MEMORY_CONTEXT, /* ask backend to log the memory contexts */
+	PROCSIG_LOG_CURRENT_PLAN, /* ask backend to log plan of the current query */
 
 	/* Recovery conflict reasons */
 	PROCSIG_RECOVERY_CONFLICT_DATABASE,
diff --git a/src/include/storage/signalfuncs.h b/src/include/storage/signalfuncs.h
new file mode 100644
index 0000000000..5f6b124372
--- /dev/null
+++ b/src/include/storage/signalfuncs.h
@@ -0,0 +1,22 @@
+/*-------------------------------------------------------------------------
+ *
+ * signalfuncs.h
+ *	  Functions for signaling backends
+ *
+ *
+ * Portions Copyright (c) 1996-2021, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * src/include/storage/signalfuncs.h
+ *
+ *-------------------------------------------------------------------------
+ */
+#ifndef PROCSIGNALFUNC_H
+#define PROCSIGNALFUNC_H
+
+/*
+ * prototypes for functions in signalfuncs.c
+ */
+extern bool SendProcSignalForLogInfo(pid_t pid, ProcSignalReason reason);
+
+#endif							/* PROCSIGNALFUNC_H */
diff --git a/src/include/tcop/pquery.h b/src/include/tcop/pquery.h
index 2318f04ff0..d37a5eb837 100644
--- a/src/include/tcop/pquery.h
+++ b/src/include/tcop/pquery.h
@@ -21,6 +21,7 @@ struct PlannedStmt;				/* avoid including plannodes.h here */

 extern PGDLLIMPORT Portal ActivePortal;
+extern PGDLLIMPORT QueryDesc *ActiveQueryDesc;

 extern PortalStrategy ChoosePortalStrategy(List *stmts);
diff --git a/src/test/regress/expected/misc_functions.out b/src/test/regress/expected/misc_functions.out
index 71d316cad3..1b6bc7a89d 100644
--- a/src/test/regress/expected/misc_functions.out
+++ b/src/test/regress/expected/misc_functions.out
@@ -134,21 +134,22 @@ LINE 1: SELECT num_nulls();
                ^
 HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
 --
--- pg_log_backend_memory_contexts()
+-- Test logging functions
 --
--- Memory contexts are logged and they are not returned to the function.
+-- The outputs of these functions are logged and they are not returned to
+-- the function.
 -- Furthermore, their contents can vary depending on the timing. However,
 -- we can at least verify that the code doesn't fail, and that the
 -- permissions are set properly.
---
+-- pg_log_backend_memory_contexts()
+CREATE ROLE regress_log;
 SELECT pg_log_backend_memory_contexts(pg_backend_pid());
  pg_log_backend_memory_contexts 
 --------------------------------
  t
 (1 row)
 
-CREATE ROLE regress_log_memory;
-SELECT has_function_privilege('regress_log_memory',
+SELECT has_function_privilege('regress_log',
   'pg_log_backend_memory_contexts(integer)', 'EXECUTE'); -- no
  has_function_privilege 
 ------------------------
@@ -156,15 +157,15 @@ SELECT has_function_privilege('regress_log_memory',
 (1 row)
 
 GRANT EXECUTE ON FUNCTION pg_log_backend_memory_contexts(integer)
-  TO regress_log_memory;
-SELECT has_function_privilege('regress_log_memory',
+  TO regress_log;
+SELECT has_function_privilege('regress_log',
   'pg_log_backend_memory_contexts(integer)', 'EXECUTE'); -- yes
  has_function_privilege 
 ------------------------
  t
 (1 row)
 
-SET ROLE regress_log_memory;
+SET ROLE regress_log;
 SELECT pg_log_backend_memory_contexts(pg_backend_pid());
  pg_log_backend_memory_contexts 
 --------------------------------
@@ -173,8 +174,41 @@ SELECT pg_log_backend_memory_contexts(pg_backend_pid());
 
 RESET ROLE;
 REVOKE EXECUTE ON FUNCTION pg_log_backend_memory_contexts(integer)
-  FROM regress_log_memory;
-DROP ROLE regress_log_memory;
+  FROM regress_log;
+-- pg_log_query_plan()
+SELECT pg_log_query_plan(pg_backend_pid());
+ pg_log_query_plan 
+-------------------
+ t
+(1 row)
+
+SELECT has_function_privilege('regress_log',
+  'pg_log_query_plan(integer)', 'EXECUTE'); -- no
+ has_function_privilege 
+------------------------
+ f
+(1 row)
+
+GRANT EXECUTE ON FUNCTION pg_log_query_plan(integer)
+  TO regress_log;
+SELECT has_function_privilege('regress_log',
+  'pg_log_query_plan(integer)', 'EXECUTE'); -- yes
+ has_function_privilege 
+------------------------
+ t
+(1 row)
+
+SET ROLE regress_log;
+SELECT pg_log_query_plan(pg_backend_pid());
+ pg_log_query_plan 
+-------------------
+ t
+(1 row)
+
+RESET ROLE;
+REVOKE EXECUTE ON FUNCTION pg_log_query_plan(integer)
+  FROM regress_log;
+DROP ROLE regress_log;
 --
 -- Test some built-in SRFs
 --
diff --git a/src/test/regress/sql/misc_functions.sql b/src/test/regress/sql/misc_functions.sql
index 8c23874b3f..5cb2a64aad 100644
--- a/src/test/regress/sql/misc_functions.sql
+++ b/src/test/regress/sql/misc_functions.sql
@@ -31,35 +31,55 @@ SELECT num_nonnulls();
 SELECT num_nulls();
 
 --
--- pg_log_backend_memory_contexts()
+-- Test logging functions
 --
--- Memory contexts are logged and they are not returned to the function.
+-- The outputs of these functions are logged and they are not returned to
+-- the function.
 -- Furthermore, their contents can vary depending on the timing. However,
 -- we can at least verify that the code doesn't fail, and that the
 -- permissions are set properly.
---
 
-SELECT pg_log_backend_memory_contexts(pg_backend_pid());
+-- pg_log_backend_memory_contexts()
+CREATE ROLE regress_log;
 
-CREATE ROLE regress_log_memory;
+SELECT pg_log_backend_memory_contexts(pg_backend_pid());
 
-SELECT has_function_privilege('regress_log_memory',
+SELECT has_function_privilege('regress_log',
   'pg_log_backend_memory_contexts(integer)', 'EXECUTE'); -- no
 
 GRANT EXECUTE ON FUNCTION pg_log_backend_memory_contexts(integer)
-  TO regress_log_memory;
+  TO regress_log;
 
-SELECT has_function_privilege('regress_log_memory',
+SELECT has_function_privilege('regress_log',
   'pg_log_backend_memory_contexts(integer)', 'EXECUTE'); -- yes
 
-SET ROLE regress_log_memory;
+SET ROLE regress_log;
 SELECT pg_log_backend_memory_contexts(pg_backend_pid());
 RESET ROLE;
 
 REVOKE EXECUTE ON FUNCTION pg_log_backend_memory_contexts(integer)
-  FROM regress_log_memory;
+  FROM regress_log;
+
+-- pg_log_query_plan()
+SELECT pg_log_query_plan(pg_backend_pid());
+
+SELECT has_function_privilege('regress_log',
+  'pg_log_query_plan(integer)', 'EXECUTE'); -- no
+
+GRANT EXECUTE ON FUNCTION pg_log_query_plan(integer)
+  TO regress_log;
+
+SELECT has_function_privilege('regress_log',
+  'pg_log_query_plan(integer)', 'EXECUTE'); -- yes
+
+SET ROLE regress_log;
+SELECT pg_log_query_plan(pg_backend_pid());
+RESET ROLE;
+
+REVOKE EXECUTE ON FUNCTION pg_log_query_plan(integer)
+  FROM regress_log;
 
-DROP ROLE regress_log_memory;
+DROP ROLE regress_log;
 
 --
 -- Test some built-in SRFs

base-commit: b0f7425ec2445678f32381de8bd3174d3cc2167e
-- 
2.27.0

---

From: torikoshia <[email-masked]>
Date: Fri, 26 Nov 2021 12:39:48 +0900

> You forgot my last fix to build correctly on Mac. I have added it.

Thanks for the notification!
Since the patch could not be applied to the HEAD anymore, I also updated 
it.

> As I know, pg_query_state use non-blocking read and write. I have
> wrote few tests trying to deadlock it (on 14 version), but all
> finished correctly.
> 
> Have a nice day. Please feel free to contact me if you need any
> further information.

Thanks for your information and help!

-- 
Regards,

--
Atsushi Torikoshi
NTT DATA CORPORATION

From b8367e22d7a9898e4b85627ba8c203be273fc22f Mon Sep 17 00:00:00 2001
From: Atsushi Torikoshi <[email-masked]>
Date: Fri, 26 Nov 2021 10:31:00 +0900
Subject: [PATCH v14] Add function to log the untruncated query string and its
 plan for the query currently running on the backend with the specified
 process ID.

---

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 0a725a6711..b84ead4341 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -25358,6 +25358,26 @@ SELECT collation for ('foo' COLLATE "de_DE");
        </para></entry>
       </row>
 
+      <row>
@@ -25471,6 +25491,31 @@ LOG:  Grand total: 1651920 bytes in 201 blocks; 622360 free (88 chunks); 1029560
 
+   <para>
 
   <sect2 id="functions-admin-backup">
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index f6789025a5..a2288b60c9 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -707,6 +707,8 @@ REVOKE EXECUTE ON FUNCTION pg_ls_logicalmapdir() FROM PUBLIC;
 
 REVOKE EXECUTE ON FUNCTION pg_ls_replslotdir(text) FROM PUBLIC;
 
index 09f5253abb..41a6d89b80 100644
 
-

 	if (es->analyze &&
@@ -4925,3 +4931,110 @@ escape_yaml(StringInfo buf, const char *str)
 {
 	escape_json(buf, str);
 }
+
+/*

 void

 

 /*

 
 
 /* ----------
index e934361dc3..2fed1ea5e2 100644
@@ -8042,6 +8042,12 @@

 
 
index a5286fab89..4f0eaf83fb 100644

index 1013d17f87..876052034d 100644

 
 
 RESET ROLE;
index 7ab9b2a150..b4dd725072 100644

 

 

 

base-commit: 99e4d24a9d77e7bb87e15b318e96dc36651a7da2
-- 
2.27.0

---

From: Akshat Jaimini <[email-masked]>
Date: Sat, 08 Mar 2025 15:42:21 +0000

Hi,
I think there is still some problem with the patch. I am not able to apply it to the master branch.

Can you please take another look at it?

Thanks,
Akshat Jaimini

---

From: torikoshia <[email-masked]>
Date: Mon, 10 Mar 2025 14:10:27 +0900

> Can you please take another look at it?

Thanks for pointing it out!
Modified it.

BTW the patch adds about 400 lines to explain.c and it may be better to 
split the file as well as 9173e8b6046, but I leave it as it is for now.

-- 
Regards,

--
Atsushi Torikoshi
Seconded from NTT DATA GROUP CORPORATION to SRA OSS K.K.

From 699264fbc1f4e99114966eaeba55a0ec5184e1c2 Mon Sep 17 00:00:00 2001
From: Atsushi Torikoshi <[email-masked]>
Date: Mon, 10 Mar 2025 14:01:54 +0900
Subject: [PATCH v41]  Add function to log the plan of the currently running
 query

Currently, we have to wait for the query execution to finish
to know its plan either using EXPLAIN ANALYZE or auto_explain.
This is not so convenient, for example when investigating
long-running queries on production environments.
To improve this situation, this patch adds pg_log_query_plan()
function that requests to log the plan of the currently
executing query.

On receipt of the request, codes for logging plan is wrapped
to every ExecProcNode and when the executor runs one of
ExecProcNode, the plan is actually logged. These wrappers are
unwrapped when once the plan is logged.
In this way, we can avoid adding the overhead which we'll face
when adding CHECK_FOR_INTERRUPTS() like mechanisms in somewhere
in executor codes safely.
---
 contrib/auto_explain/auto_explain.c          |  23 +-
 doc/src/sgml/func.sgml                       |  50 +++
 src/backend/access/transam/xact.c            |   7 +
 src/backend/catalog/system_functions.sql     |   2 +
 src/backend/commands/explain.c               | 372 ++++++++++++++++++-
 src/backend/executor/execMain.c              |  12 +
 src/backend/storage/ipc/procsignal.c         |   4 +
 src/backend/tcop/postgres.c                  |   4 +
 src/backend/utils/init/globals.c             |   2 +
 src/include/catalog/pg_proc.dat              |   6 +
 src/include/commands/explain.h               |  12 +
 src/include/miscadmin.h                      |   1 +
 src/include/nodes/execnodes.h                |   3 +
 src/include/storage/procsignal.h             |   2 +
 src/include/tcop/pquery.h                    |   2 +-
 src/test/regress/expected/misc_functions.out |  54 ++-
 src/test/regress/sql/misc_functions.sql      |  41 +-
 17 files changed, 555 insertions(+), 42 deletions(-)

diff --git a/contrib/auto_explain/auto_explain.c b/contrib/auto_explain/auto_explain.c
index 7007a226c0..85cfe1b4f4 100644
--- a/contrib/auto_explain/auto_explain.c
+++ b/contrib/auto_explain/auto_explain.c
@@ -408,26 +408,9 @@ explain_ExecutorEnd(QueryDesc *queryDesc)
 			es->format = auto_explain_log_format;
 			es->settings = auto_explain_log_settings;
 
-			ExplainBeginOutput(es);
-			ExplainQueryText(es, queryDesc);
-			ExplainQueryParameters(es, queryDesc->params, auto_explain_log_parameter_max_length);
-			ExplainPrintPlan(es, queryDesc);
-			if (es->analyze && auto_explain_log_triggers)
-				ExplainPrintTriggers(es, queryDesc);
-			if (es->costs)
-				ExplainPrintJITSummary(es, queryDesc);
-			ExplainEndOutput(es);
-
-			/* Remove last line break */
-			if (es->str->len > 0 && es->str->data[es->str->len - 1] == '\n')
-				es->str->data[--es->str->len] = '\0';
-
-			/* Fix JSON to output an object */
-			if (auto_explain_log_format == EXPLAIN_FORMAT_JSON)
-			{
-				es->str->data[0] = '{';
-				es->str->data[es->str->len - 1] = '}';
-			}
+			ExplainAssembleLogOutput(es, queryDesc, auto_explain_log_format,
+									 auto_explain_log_triggers,
+									 auto_explain_log_parameter_max_length);
 
 			/*
 			 * Note: we rely on the existing logging of context or
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 51dd8ad657..d1a16a89fb 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -28553,6 +28553,25 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_log_query_plan</primary>
+        </indexterm>
+        <function>pg_log_query_plan</function> ( <parameter>pid</parameter> <type>integer</type> )
+        <returnvalue>boolean</returnvalue>
+       </para>
+       <para>
+        Requests to log the plan of the query currently running on the
+        backend with specified process ID.
+        It will be logged at <literal>LOG</literal> message level and
+        will appear in the server log based on the log
+        configuration set (See <xref linkend="runtime-config-logging"/>
+        for more information), but will not be sent to the client
+        regardless of <xref linkend="guc-client-min-messages"/>.
+        </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
@@ -28671,6 +28690,37 @@ LOG:  Grand total: 1651920 bytes in 201 blocks; 622360 free (88 chunks); 1029560
     because it may generate a large number of log messages.
    </para>
 
+   <para>
+    <function>pg_log_query_plan</function> can be used
+    to log the plan of a backend process. For example:
+<programlisting>
+postgres=# SELECT pg_log_query_plan(201116);
+ pg_log_query_plan
+---------------------------
+ t
+(1 row)
+</programlisting>
+The format of the query plan is the same as when <literal>VERBOSE</literal>,
+<literal>COSTS</literal>, <literal>SETTINGS</literal> and
+<literal>FORMAT TEXT</literal> are used in the <command>EXPLAIN</command>
+command. For example:
+<screen>
+LOG:  query plan running on backend with PID 201116 is:
+        Query Text: SELECT * FROM pgbench_accounts;
+        Seq Scan on public.pgbench_accounts  (cost=0.00..52787.00 rows=2000000 width=97)
+          Output: aid, bid, abalance, filler
+        Settings: work_mem = '1MB'
+        Query Identifier: 8621255546560739680
+</screen>
+    Note that when the target is executing nested statements(statements executed
+    inside a function), only the innermost query plan is logged.
+   </para>
+
+   <para>
+    When a subtransaction is aborted, <function>pg_log_query_plan</function>
+    cannot log the query plan after the abort.
+   </para>
+
   </sect2>
 
   <sect2 id="functions-admin-backup">
diff --git a/src/backend/access/transam/xact.c b/src/backend/access/transam/xact.c
index 1b4f21a88d..ca973a0368 100644
--- a/src/backend/access/transam/xact.c
+++ b/src/backend/access/transam/xact.c
@@ -36,6 +36,7 @@
 #include "catalog/pg_enum.h"
 #include "catalog/storage.h"
 #include "commands/async.h"
+#include "commands/explain.h"
 #include "commands/tablecmds.h"
 #include "commands/trigger.h"
 #include "common/pg_prng.h"
@@ -2897,6 +2898,9 @@ AbortTransaction(void)
 	/* Reset snapshot export state. */
 	SnapBuildResetExportedSnapshotState();
 
+	/* Reset pg_log_query_plan() related state. */
+	ResetLogQueryPlanState();
+
 	/*
 	 * If this xact has started any unfinished parallel operation, clean up
 	 * its workers and exit parallel mode.  Don't warn about leaked resources.
@@ -5285,6 +5289,9 @@ AbortSubTransaction(void)
 	/* Reset logical streaming state. */
 	ResetLogicalStreamingState();
 
+	/* Reset pg_log_query_plan() related state. */
+	ResetLogQueryPlanState();
+
 	/*
 	 * No need for SnapBuildResetExportedSnapshotState() here, snapshot
 	 * exports are not supported in subtransactions.
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 566f308e44..ec2e1c1fd9 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -775,6 +775,8 @@ REVOKE EXECUTE ON FUNCTION pg_ls_logicalmapdir() FROM PUBLIC;

index d8a7232ced..7f63f47fce 100644
@@ -24,6 +24,7 @@
 #include "libpq/pqformat.h"
 #include "libpq/protocol.h"
@@ -31,7 +32,9 @@
+#include "utils/backend_status.h"
 #include "utils/json.h"
@@ -43,6 +46,11 @@
 
+bool		ProcessLogQueryPlanInterruptActive = false;
+
+/* Currently executing query's QueryDesc */
+QueryDesc *ActiveQueryDesc = NULL;
+
 
 /* Hook for plugins to get control in ExplainOneQuery() */
 ExplainOneQuery_hook_type ExplainOneQuery_hook = NULL;
@@ -154,6 +162,9 @@ static ExplainWorkersState *ExplainCreateWorkersState(int num_workers);
 static void ExplainOpenWorker(int n, ExplainState *es);
 static void ExplainCloseWorker(int n, ExplainState *es);
 static void ExplainFlushWorkersState(ExplainState *es);
+static TupleTableSlot *ExecProcNodeWithExplain(PlanState *ps);
+static void WrapExecProcNodeWithExplain(PlanState *ps);
+static void UnwrapExecProcNodeWithExplain(PlanState *ps);

@@ -875,6 +886,37 @@ ExplainPrintSettings(ExplainState *es)
 	}
 }
 
+/*
+ * ExplainAssembleLogOutput -
+ *    Assemble es->str for logging according to specified options and format
+ */
+
+void
+ExplainAssembleLogOutput(ExplainState *es, QueryDesc *queryDesc, int logFormat,
+						 bool logTriggers, int logParameterMaxLength)
+{
+	ExplainBeginOutput(es);
+	ExplainQueryText(es, queryDesc);
+	ExplainQueryParameters(es, queryDesc->params, logParameterMaxLength);
+	ExplainPrintPlan(es, queryDesc);
+	if (es->analyze && logTriggers)
+		ExplainPrintTriggers(es, queryDesc);
+	if (es->costs)
+		ExplainPrintJITSummary(es, queryDesc);
+	ExplainEndOutput(es);
+
+	/* Remove last line break */
+	if (es->str->len > 0 && es->str->data[es->str->len - 1] == '\n')
+		es->str->data[--es->str->len] = '\0';
+
+	/* Fix JSON to output an object */
+	if (logFormat == EXPLAIN_FORMAT_JSON)
+	{
+		es->str->data[0] = '{';
+		es->str->data[es->str->len - 1] = '}';
+	}
+}
+
 /*
  * ExplainPrintPlan -
  *	  convert a QueryDesc's plan tree to text and append it to es->str
@@ -1947,6 +1989,9 @@ ExplainNode(PlanState *planstate, List *ancestors,
 	/*
 	 * We have to forcibly clean up the instrumentation state because we
 	 * haven't done ExecutorEnd yet.  This is pretty grotty ...
+	 * This cleanup should not be done when the query has already been executed
+	 * and explain has been called by signal, as the target query may use
+	 * instrumentation and clean itself up.
 	 *
 	 * Note: contrib/auto_explain could cause instrumentation to be set up
 	 * even though we didn't ask for it here.  Be careful not to print any
@@ -1954,7 +1999,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 	 * InstrEndLoop call anyway, if possible, to reduce the number of cases
 	 * auto_explain has to contend with.
 	 */
-	if (planstate->instrument)
+	if (planstate->instrument && !es->signaled)
 		InstrEndLoop(planstate->instrument);
 
 	if (es->analyze &&
@@ -4931,3 +4976,328 @@ ExplainFlushWorkersState(ExplainState *es)
 	pfree(wstate->worker_state_save);
 	pfree(wstate);
 }
+
+/*
+ * HandleLogQueryPlanInterrupt -
+ *    Handle receipt of an interrupt indicating logging the plan of the currently
+ *    running query.
+ *
+ * All the actual work is deferred to ProcessLogQueryPlanInterrupt(),
+ * because we cannot safely emit a log message inside the signal handler.
+ */
+void
+HandleLogQueryPlanInterrupt(void)
+{
+	InterruptPending = true;
+	LogQueryPlanPending = true;
+	/* latch will be set by procsignal_sigusr1_handler */
+}
+
+/*
+ * ResetLogQueryPlanState -
+ *   Clear pg_log_query_plan() related state during (sub)transaction abort
+ */
+void
+ResetLogQueryPlanState(void)
+{
+	/*
+	 * After abort, some elements of ActiveQueryDesc is freed. To avoid
+	 * accessing them, reset ActiveQueryDesc here.
+	 */
+	ActiveQueryDesc = NULL;
+	ProcessLogQueryPlanInterruptActive = false;
+}
+
+/*
+ * WrapMultiExecProcNodesWithExplain -
+ *	  Wrap array of PlanStates ExecProcNodes with ExecProcNodeWithExplain
+ */
+static void
+WrapMultiExecProcNodesWithExplain(PlanState **planstates, int nplans)
+{
+	int			i;
+
+	for (i = 0; i < nplans; i++)
+		WrapExecProcNodeWithExplain(planstates[i]);
+}
+
+/*
+ * WrapCustomPlanChildExecProcNodesWithExplain -
+ *	  Wrap CustomScanstate children's ExecProcNodes with ExecProcNodeWithExplain
+ */
+static void
+WrapCustomPlanChildExecProcNodesWithExplain(CustomScanState *css)
+{
+	ListCell   *cell;
+
+	foreach(cell, css->custom_ps)
+		WrapExecProcNodeWithExplain((PlanState *) lfirst(cell));
+}
+
+/*
+ * WrapExecProcNodeWithExplain -
+ *	  Wrap ExecProcNode with ExecProcNodeWithExplain recursively
+ */
+static void
+WrapExecProcNodeWithExplain(PlanState *ps)
+{
+	/* wrapping can be done only once */
+	if (ps->ExecProcNodeOriginal != NULL)
+		return;
+
+	check_stack_depth();
+
+	ps->ExecProcNodeOriginal = ps->ExecProcNode;
+	ps->ExecProcNode = ExecProcNodeWithExplain;
+
+	if (ps->lefttree != NULL)
+		WrapExecProcNodeWithExplain(ps->lefttree);
+	if (ps->righttree != NULL)
+		WrapExecProcNodeWithExplain(ps->righttree);
+
+	/* special child plans */
+	switch (nodeTag(ps->plan))
+	{
+		case T_Append:
+			ereport(DEBUG1, errmsg("wrapping Append"));
+			WrapMultiExecProcNodesWithExplain(((AppendState *) ps)->appendplans,
+											  ((AppendState *) ps)->as_nplans);
+			break;
+		case T_MergeAppend:
+			ereport(DEBUG1, errmsg("wrapping MergeAppend"));
+			WrapMultiExecProcNodesWithExplain(((MergeAppendState *) ps)->mergeplans,
+											  ((MergeAppendState *) ps)->ms_nplans);
+			break;
+		case T_BitmapAnd:
+			ereport(DEBUG1, errmsg("wrapping BitmapAndState"));
+			WrapMultiExecProcNodesWithExplain(((BitmapAndState *) ps)->bitmapplans,
+											  ((BitmapAndState *) ps)->nplans);
+			break;
+		case T_BitmapOr:
+			ereport(DEBUG1, errmsg("wrapping BitmapOrtate"));
+			WrapMultiExecProcNodesWithExplain(((BitmapOrState *) ps)->bitmapplans,
+											  ((BitmapOrState *) ps)->nplans);
+			break;
+		case T_SubqueryScan:
+			ereport(DEBUG1, errmsg("wrapping Subquery"));
+			WrapExecProcNodeWithExplain(((SubqueryScanState *) ps)->subplan);
+			break;
+		case T_CustomScan:
+			ereport(DEBUG1, errmsg("wrapping CustomScanState"));
+			WrapCustomPlanChildExecProcNodesWithExplain((CustomScanState *) ps);
+			break;
+		default:
+			break;
+	}
+}
+
+/*
+ * UnwrapMultiExecProcNodesWithExplain -
+ *	  Unwrap array of PlanStates ExecProcNodes with ExecProcNodeWithExplain
+ */
+static void
+UnwrapMultiExecProcNodesWithExplain(PlanState **planstates, int nplans)
+{
+	int			i;
+
+	for (i = 0; i < nplans; i++)
+		UnwrapExecProcNodeWithExplain(planstates[i]);
+}
+
+/*
+ * UnwrapCustomPlanChildExecProcNodesWithExplain -
+ *	  Unwrap CustomScanstate children's ExecProcNodes with ExecProcNodeWithExplain
+ */
+static void
+UnwrapCustomPlanChildExecProcNodesWithExplain(CustomScanState *css)
+{
+	ListCell   *cell;
+
+	foreach(cell, css->custom_ps)
+		UnwrapExecProcNodeWithExplain((PlanState *) lfirst(cell));
+}
+
+/*
+ * UnwrapExecProcNodeWithExplain -
+ *	  Unwrap ExecProcNode with ExecProcNodeWithExplain recursively
+ */
+static void
+UnwrapExecProcNodeWithExplain(PlanState *ps)
+{
+	Assert(ps->ExecProcNodeOriginal != NULL);
+
+	check_stack_depth();
+
+	ps->ExecProcNode = ps->ExecProcNodeOriginal;
+	ps->ExecProcNodeOriginal = NULL;
+
+	if (ps->lefttree != NULL)
+		UnwrapExecProcNodeWithExplain(ps->lefttree);
+	if (ps->righttree != NULL)
+		UnwrapExecProcNodeWithExplain(ps->righttree);
+
+	/* special child plans */
+	switch (nodeTag(ps->plan))
+	{
+		case T_Append:
+			ereport(DEBUG1, errmsg("unwrapping Append"));
+			UnwrapMultiExecProcNodesWithExplain(((AppendState *) ps)->appendplans,
+												((AppendState *) ps)->as_nplans);
+			break;
+		case T_MergeAppend:
+			ereport(DEBUG1, errmsg("unwrapping MergeAppend"));
+			UnwrapMultiExecProcNodesWithExplain(((MergeAppendState *) ps)->mergeplans,
+												((MergeAppendState *) ps)->ms_nplans);
+			break;
+		case T_BitmapAnd:
+			ereport(DEBUG1, errmsg("unwrapping BitmapAndState"));
+			UnwrapMultiExecProcNodesWithExplain(((BitmapAndState *) ps)->bitmapplans,
+												((BitmapAndState *) ps)->nplans);
+			break;
+		case T_BitmapOr:
+			ereport(DEBUG1, errmsg("unwrapping BitmapOrtate"));
+			UnwrapMultiExecProcNodesWithExplain(((BitmapOrState *) ps)->bitmapplans,
+												((BitmapOrState *) ps)->nplans);
+			break;
+		case T_SubqueryScan:
+			ereport(DEBUG1, errmsg("unwrapping Subquery"));
+			UnwrapExecProcNodeWithExplain(((SubqueryScanState *) ps)->subplan);
+			break;
+		case T_CustomScan:
+			ereport(DEBUG1, errmsg("unwrapping CustomScanState"));
+			UnwrapCustomPlanChildExecProcNodesWithExplain((CustomScanState *) ps);
+			break;
+		default:
+			break;
+	}
+}
+
+/*
+ * ExecProcNodeWithExplain -
+ *	  Wrap ExecProcNode with codes which logs currently running plan
+ */
+static TupleTableSlot *
+ExecProcNodeWithExplain(PlanState *ps)
+{
+	ExplainState *es;
+	MemoryContext cxt;
+	MemoryContext old_cxt;
+
+	check_stack_depth();
+
+	cxt = AllocSetContextCreate(CurrentMemoryContext,
+								"log_query_plan temporary context",
+								ALLOCSET_DEFAULT_SIZES);
+
+	old_cxt = MemoryContextSwitchTo(cxt);
+
+	es = NewExplainState();
+
+	es->format = EXPLAIN_FORMAT_TEXT;
+	es->settings = true;
+	es->verbose = true;
+	es->signaled = true;
+
+	ExplainAssembleLogOutput(es, ActiveQueryDesc, EXPLAIN_FORMAT_TEXT, 0, -1);
+
+	ereport(LOG_SERVER_ONLY,
+			errmsg("query plan running on backend with PID %d is:\n%s",
+				   MyProcPid, es->str->data),
+			errhidestmt(true),
+			errhidecontext(true));
+
+	MemoryContextSwitchTo(old_cxt);
+	MemoryContextDelete(cxt);
+
+	UnwrapExecProcNodeWithExplain(ActiveQueryDesc->planstate);
+
+	/*
+	 * Since unwrapping has already done, call ExecProcNode() not
+	 * ExecProcNodeOriginal().
+	 */
+	return ps->ExecProcNode(ps);
+}
+
+/*
+ * ProcessLogQueryPlanInterrupt
+ *	  Add wrapper which logs explain of the plan to ExecProcNodes
+ *
+ * Since running EXPLAIN codes at any arbitrary CHECK_FOR_INTERRUPTS() is
+ * unsafe, this function just wraps every ExecProcNode.
+ * In this way, EXPLAIN code is only executed at the timing of ExecProcNode,
+ * which seems safe.
+ */
+void
+ProcessLogQueryPlanInterrupt(void)
+{
+	LogQueryPlanPending = false;
+
+	/* Cannot re-enter */
+	if (ProcessLogQueryPlanInterruptActive)
+		return;
+
+	ProcessLogQueryPlanInterruptActive = true;
+
+	if (ActiveQueryDesc == NULL)
+	{
+		ereport(LOG_SERVER_ONLY,
+				errmsg("backend with PID %d is not running a query or a subtransaction is aborted",
+					   MyProcPid),
+				errhidestmt(true),
+				errhidecontext(true));
+
+		ProcessLogQueryPlanInterruptActive = false;
+		return;
+	}
+
+	WrapExecProcNodeWithExplain(ActiveQueryDesc->planstate);
+	ProcessLogQueryPlanInterruptActive = false;
+}
+
+/*
+ * pg_log_query_plan
+ *    Signal a backend process to log the query plan of the running query.
+ *
+ * By default, only superusers are allowed to signal to log the plan because
+ * allowing any users to issue this request at an unbounded rate would
+ * cause lots of log messages and which can lead to denial of service.
+ * Additional roles can be permitted with GRANT.
+ */
+Datum
+pg_log_query_plan(PG_FUNCTION_ARGS)
+{
+	int			pid = PG_GETARG_INT32(0);
+	PGPROC	   *proc;
+	PgBackendStatus *be_status;
+
+	proc = BackendPidGetProc(pid);
+
+	if (proc == NULL)
+	{
+		/*
+		 * This is just a warning so a loop-through-resultset will not abort
+		 * if one backend terminated on its own during the run.
+		 */
+		ereport(WARNING,
+				(errmsg("PID %d is not a PostgreSQL backend process", pid)));
+		PG_RETURN_BOOL(false);
+	}
+
+	be_status = pgstat_get_beentry_by_proc_number(proc->vxid.procNumber);
+	if (be_status->st_backendType != B_BACKEND)
+	{
+		ereport(WARNING,
+				(errmsg("PID %d is not a PostgreSQL client backend process", pid)));
+		PG_RETURN_BOOL(false);
+	}
+
+	if (SendProcSignal(pid, PROCSIG_LOG_QUERY_PLAN, proc->vxid.procNumber) < 0)
+	{
+		/* Again, just a warning to allow loops */
+		ereport(WARNING,
+				(errmsg("could not send signal to process %d: %m", pid)));
+		PG_RETURN_BOOL(false);
+	}
+
+	PG_RETURN_BOOL(true);
+}
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 0493b7d536..63284517fa 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -43,6 +43,7 @@
 #include "access/xact.h"
 #include "catalog/namespace.h"
 #include "catalog/partition.h"
+#include "commands/explain.h"
 #include "commands/matview.h"
 #include "commands/trigger.h"
 #include "executor/executor.h"
@@ -362,10 +363,21 @@ void
 ExecutorRun(QueryDesc *queryDesc,
 			ScanDirection direction, uint64 count)
 {
+	/*
+	 * Update ActiveQueryDesc here to enable retrieval of the currently
+	 * running queryDesc for nested queries.
+	 */
+	QueryDesc  *save_ActiveQueryDesc;
+
+	save_ActiveQueryDesc = ActiveQueryDesc;
+	ActiveQueryDesc = queryDesc;
+
 	if (ExecutorRun_hook)
 		(*ExecutorRun_hook) (queryDesc, direction, count);
 	else
 		standard_ExecutorRun(queryDesc, direction, count);
+
+	ActiveQueryDesc = save_ActiveQueryDesc;
 }
 
 void
diff --git a/src/backend/storage/ipc/procsignal.c b/src/backend/storage/ipc/procsignal.c
index 7d20196550..ced80588ab 100644
--- a/src/backend/storage/ipc/procsignal.c
+++ b/src/backend/storage/ipc/procsignal.c
@@ -19,6 +19,7 @@
 
@@ -690,6 +691,9 @@ procsignal_sigusr1_handler(SIGNAL_ARGS)
 
+	if (CheckProcSignal(PROCSIG_LOG_QUERY_PLAN))
+		HandleLogQueryPlanInterrupt();
+
 	if (CheckProcSignal(PROCSIG_PARALLEL_APPLY_MESSAGE))
 		HandleParallelApplyMessageInterrupt();
 
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 947ffb4042..f4c4e64f9f 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -37,6 +37,7 @@
 #include "catalog/pg_type.h"
 #include "commands/async.h"
 #include "commands/event_trigger.h"
+#include "commands/explain.h"
 #include "commands/prepare.h"
 #include "common/pg_prng.h"
 #include "jit/jit.h"
@@ -3499,6 +3500,9 @@ ProcessInterrupts(void)
 	if (LogMemoryContextPending)
 		ProcessLogMemoryContextInterrupt();
 
+	if (LogQueryPlanPending)
+		ProcessLogQueryPlanInterrupt();
+
 	if (ParallelApplyMessagePending)
 		ProcessParallelApplyMessages();
 }
diff --git a/src/backend/utils/init/globals.c b/src/backend/utils/init/globals.c
index b844f9fdae..1e4d90f58b 100644
--- a/src/backend/utils/init/globals.c
+++ b/src/backend/utils/init/globals.c
@@ -39,6 +39,8 @@ volatile sig_atomic_t IdleSessionTimeoutPending = false;
 volatile sig_atomic_t ProcSignalBarrierPending = false;
 volatile sig_atomic_t LogMemoryContextPending = false;
 volatile sig_atomic_t IdleStatsUpdateTimeoutPending = false;
+volatile sig_atomic_t LogQueryPlanPending = false;
+
 volatile uint32 InterruptHoldoffCount = 0;
 volatile uint32 QueryCancelHoldoffCount = 0;
 volatile uint32 CritSectionCount = 0;
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index cede992b6e..c8ed884109 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8499,6 +8499,12 @@
   prorettype => 'bool', proargtypes => 'int4',
   prosrc => 'pg_log_backend_memory_contexts' },
 
+# logging plan of the running query on the specified backend
+{ oid => '8000', descr => 'log plan of the running query on the specified backend',
+  proname => 'pg_log_query_plan',
+  provolatile => 'v', prorettype => 'bool',
+  proargtypes => 'int4', prosrc => 'pg_log_query_plan' },
+
 # non-persistent series generator
 { oid => '1066', descr => 'non-persistent series generator',
   proname => 'generate_series', prorows => '1000',
diff --git a/src/include/commands/explain.h b/src/include/commands/explain.h
index 64547bd9b9..47c8376a60 100644
--- a/src/include/commands/explain.h
+++ b/src/include/commands/explain.h
@@ -17,6 +17,9 @@
 #include "lib/stringinfo.h"
 #include "parser/parse_node.h"
 
+extern PGDLLIMPORT QueryDesc *ActiveQueryDesc;
+extern PGDLLIMPORT bool ProcessLogQueryPlanInterruptActive;
+
 typedef enum ExplainSerializeOption
 {
 	EXPLAIN_SERIALIZE_NONE,
@@ -71,6 +74,7 @@ typedef struct ExplainState
 								 * entry */
 	/* state related to the current plan node */
 	ExplainWorkersState *workers_state; /* needed if parallel plan */
+	bool		signaled;		/* whether explain is called by signal */
 } ExplainState;
 
 /* Hook for plugins to get control in ExplainOneQuery() */
@@ -112,6 +116,10 @@ extern void ExplainOnePlan(PlannedStmt *plannedstmt, CachedPlan *cplan,
 						   const BufferUsage *bufusage,
 						   const MemoryContextCounters *mem_counters);
 
+extern void ExplainAssembleLogOutput(ExplainState *es, QueryDesc *queryDesc,
+									 int logFormat, bool logTriggers,
+									 int logParameterMaxLength);
+
 extern void ExplainPrintPlan(ExplainState *es, QueryDesc *queryDesc);
 extern void ExplainPrintTriggers(ExplainState *es, QueryDesc *queryDesc);
 
@@ -120,4 +128,8 @@ extern void ExplainPrintJITSummary(ExplainState *es, QueryDesc *queryDesc);
 extern void ExplainQueryText(ExplainState *es, QueryDesc *queryDesc);
 extern void ExplainQueryParameters(ExplainState *es, ParamListInfo params, int maxlen);
 
+extern void HandleLogQueryPlanInterrupt(void);
+extern void ProcessLogQueryPlanInterrupt(void);
+extern void ResetLogQueryPlanState(void);
+
 #endif							/* EXPLAIN_H */
diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h
index a2b63495ee..291c20187d 100644
--- a/src/include/miscadmin.h
+++ b/src/include/miscadmin.h
@@ -96,6 +96,7 @@ extern PGDLLIMPORT volatile sig_atomic_t IdleSessionTimeoutPending;
 extern PGDLLIMPORT volatile sig_atomic_t ProcSignalBarrierPending;
 extern PGDLLIMPORT volatile sig_atomic_t LogMemoryContextPending;
 extern PGDLLIMPORT volatile sig_atomic_t IdleStatsUpdateTimeoutPending;
+extern PGDLLIMPORT volatile sig_atomic_t LogQueryPlanPending;
 
 extern PGDLLIMPORT volatile sig_atomic_t CheckClientConnectionPending;
 extern PGDLLIMPORT volatile sig_atomic_t ClientConnectionLost;
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index a323fa98bb..73431a3223 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -1160,6 +1160,9 @@ typedef struct PlanState
 	ExecProcNodeMtd ExecProcNodeReal;	/* actual function, if above is a
 										 * wrapper */
 
+	ExecProcNodeMtd ExecProcNodeOriginal;	/* temporary place when adding
+											 * process for ExecProcNode */
+
 	Instrumentation *instrument;	/* Optional runtime stats for this node */
 	WorkerInstrumentation *worker_instrument;	/* per-worker instrumentation */
 
diff --git a/src/include/storage/procsignal.h b/src/include/storage/procsignal.h
index 022fd8ed93..c666a4d708 100644
--- a/src/include/storage/procsignal.h
+++ b/src/include/storage/procsignal.h
@@ -35,6 +35,8 @@ typedef enum
 	PROCSIG_WALSND_INIT_STOPPING,	/* ask walsenders to prepare for shutdown  */
 	PROCSIG_BARRIER,			/* global barrier interrupt  */
 	PROCSIG_LOG_MEMORY_CONTEXT, /* ask backend to log the memory contexts */
+	PROCSIG_LOG_QUERY_PLAN,		/* ask backend to log plan of the current
+								 * query */
 	PROCSIG_PARALLEL_APPLY_MESSAGE, /* Message from parallel apply workers */
 
 	/* Recovery conflict reasons */
diff --git a/src/include/tcop/pquery.h b/src/include/tcop/pquery.h
index fa3cc5f2df..73e16cf7c4 100644
--- a/src/include/tcop/pquery.h
+++ b/src/include/tcop/pquery.h
@@ -21,7 +21,7 @@ struct PlannedStmt;				/* avoid including plannodes.h here */

 
diff --git a/src/test/regress/expected/misc_functions.out b/src/test/regress/expected/misc_functions.out
index d3f5d16a67..7dcb9951e7 100644
--- a/src/test/regress/expected/misc_functions.out
+++ b/src/test/regress/expected/misc_functions.out
@@ -316,14 +316,15 @@ SELECT test_canonicalize_path('./abc/./def/.././ghi/../../../jkl/mno');
  ../jkl/mno
 (1 row)
 
@@ -337,8 +338,8 @@ SELECT pg_log_backend_memory_contexts(pid) FROM pg_stat_activity
  t
 (1 row)
 
-CREATE ROLE regress_log_memory;
-SELECT has_function_privilege('regress_log_memory',
+CREATE ROLE regress_log_function;
+SELECT has_function_privilege('regress_log_function',
   'pg_log_backend_memory_contexts(integer)', 'EXECUTE'); -- no
  has_function_privilege 
 ------------------------
@@ -346,15 +347,15 @@ SELECT has_function_privilege('regress_log_memory',
 (1 row)
 
 GRANT EXECUTE ON FUNCTION pg_log_backend_memory_contexts(integer)
-  TO regress_log_memory;
-SELECT has_function_privilege('regress_log_memory',
+  TO regress_log_function;
+SELECT has_function_privilege('regress_log_function',
   'pg_log_backend_memory_contexts(integer)', 'EXECUTE'); -- yes
  has_function_privilege 
 ------------------------
  t
 (1 row)
 
-SET ROLE regress_log_memory;
+SET ROLE regress_log_function;
 SELECT pg_log_backend_memory_contexts(pg_backend_pid());
  pg_log_backend_memory_contexts 
 --------------------------------
@@ -363,8 +364,41 @@ SELECT pg_log_backend_memory_contexts(pg_backend_pid());
 
 RESET ROLE;
 REVOKE EXECUTE ON FUNCTION pg_log_backend_memory_contexts(integer)
-  FROM regress_log_memory;
-DROP ROLE regress_log_memory;
+  FROM regress_log_function;
+-- pg_log_query_plan()
+SELECT pg_log_query_plan(pg_backend_pid());
+ pg_log_query_plan 
+-------------------
+ t
+(1 row)
+
+SELECT has_function_privilege('regress_log_function',
+  'pg_log_query_plan(integer)', 'EXECUTE'); -- no
+ has_function_privilege 
+------------------------
+ f
+(1 row)
+
+GRANT EXECUTE ON FUNCTION pg_log_query_plan(integer)
+  TO regress_log_function;
+SELECT has_function_privilege('regress_log_function',
+  'pg_log_query_plan(integer)', 'EXECUTE'); -- yes
+ has_function_privilege 
+------------------------
+ t
+(1 row)
+
+SET ROLE regress_log_function;
+SELECT pg_log_query_plan(pg_backend_pid());
+ pg_log_query_plan 
+-------------------
+ t
+(1 row)
+
+RESET ROLE;
+REVOKE EXECUTE ON FUNCTION pg_log_query_plan(integer)
+  FROM regress_log_function;
+DROP ROLE regress_log_function;
 --
 -- Test some built-in SRFs
 --
diff --git a/src/test/regress/sql/misc_functions.sql b/src/test/regress/sql/misc_functions.sql
index aaebb29833..92330355f6 100644
--- a/src/test/regress/sql/misc_functions.sql
+++ b/src/test/regress/sql/misc_functions.sql
@@ -109,39 +109,60 @@ SELECT test_canonicalize_path('./abc/./def/.');
 SELECT test_canonicalize_path('./abc/././def/.');
 SELECT test_canonicalize_path('./abc/./def/.././ghi/../../../jkl/mno');

 
 SELECT pg_log_backend_memory_contexts(pid) FROM pg_stat_activity
   WHERE backend_type = 'checkpointer';
 
-CREATE ROLE regress_log_memory;
+CREATE ROLE regress_log_function;
 
-SELECT has_function_privilege('regress_log_memory',
+SELECT has_function_privilege('regress_log_function',
   'pg_log_backend_memory_contexts(integer)', 'EXECUTE'); -- no
 
 GRANT EXECUTE ON FUNCTION pg_log_backend_memory_contexts(integer)
-  TO regress_log_memory;
+  TO regress_log_function;
 
-SELECT has_function_privilege('regress_log_memory',
+SELECT has_function_privilege('regress_log_function',
   'pg_log_backend_memory_contexts(integer)', 'EXECUTE'); -- yes
 
-SET ROLE regress_log_memory;
+SET ROLE regress_log_function;
 SELECT pg_log_backend_memory_contexts(pg_backend_pid());
 RESET ROLE;
 
 REVOKE EXECUTE ON FUNCTION pg_log_backend_memory_contexts(integer)
-  FROM regress_log_memory;
+  FROM regress_log_function;
+
+-- pg_log_query_plan()
+SELECT pg_log_query_plan(pg_backend_pid());
+
+SELECT has_function_privilege('regress_log_function',
+  'pg_log_query_plan(integer)', 'EXECUTE'); -- no
+
+GRANT EXECUTE ON FUNCTION pg_log_query_plan(integer)
+  TO regress_log_function;
+
+SELECT has_function_privilege('regress_log_function',
+  'pg_log_query_plan(integer)', 'EXECUTE'); -- yes
+
+SET ROLE regress_log_function;
+SELECT pg_log_query_plan(pg_backend_pid());
+RESET ROLE;
+
+REVOKE EXECUTE ON FUNCTION pg_log_query_plan(integer)
+  FROM regress_log_function;
 
-DROP ROLE regress_log_memory;
+DROP ROLE regress_log_function;

base-commit: e033696596566d422a0eae47adca371a210ed730
-- 
2.48.1

---

From: torikoshia <[email-masked]>
Date: Fri, 21 Mar 2025 21:40:00 +0900

Hi,

Rebased it again.

On 2025-03-10 14:10, torikoshia wrote:
> BTW the patch adds about 400 lines to explain.c and it may be better
> to split the file as well as 9173e8b6046, but I leave it as it is for
> now.

This part remains unchanged.

-- 
Regards,

From ea5092a13e7ac426ac40397ceff5f67355b7723f Mon Sep 17 00:00:00 2001
From: Atsushi Torikoshi <[email-masked]>
Date: Fri, 21 Mar 2025 21:34:08 +0900
Subject: [PATCH v42] Add function to log the plan of the currently running
 query

 src/include/commands/explain.h               |  13 +
 src/include/commands/explain_state.h         |   1 +
 18 files changed, 557 insertions(+), 42 deletions(-)

diff --git a/contrib/auto_explain/auto_explain.c b/contrib/auto_explain/auto_explain.c
index 3b73bd1910..5807135000 100644
--- a/contrib/auto_explain/auto_explain.c
+++ b/contrib/auto_explain/auto_explain.c
@@ -409,26 +409,9 @@ explain_ExecutorEnd(QueryDesc *queryDesc)
 			es->format = auto_explain_log_format;
 			es->settings = auto_explain_log_settings;

 			/*
 			 * Note: we rely on the existing logging of context or
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 6fa1d6586b..da80b7ef8a 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -28570,6 +28570,25 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
        </para></entry>
       </row>
 
+      <row>
@@ -28688,6 +28707,37 @@ LOG:  Grand total: 1651920 bytes in 201 blocks; 622360 free (88 chunks); 1029560
 
+   <para>
 
index b885513f76..1b4c0abea5 100644
@@ -2904,6 +2905,9 @@ AbortTransaction(void)
 
@@ -5296,6 +5300,9 @@ AbortSubTransaction(void)

 
index 391b34a2af..bdfe545e76 100644
@@ -26,6 +26,7 @@
@@ -33,7 +34,9 @@
@@ -45,6 +48,11 @@

@@ -165,6 +173,9 @@ static ExplainWorkersState *ExplainCreateWorkersState(int num_workers);

@@ -756,6 +767,37 @@ ExplainPrintSettings(ExplainState *es)
 	}
 }
 
+/*
@@ -1828,6 +1870,9 @@ ExplainNode(PlanState *planstate, List *ancestors,
@@ -1835,7 +1880,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 
 	if (es->analyze &&
@@ -4987,3 +5032,328 @@ ExplainFlushWorkersState(ExplainState *es)
index e9bd98c773..755387d70b 100644
 
 void

 
index 0554a4ae3c..cf97f2030b 100644
@@ -3507,6 +3508,9 @@ ProcessInterrupts(void)
 
index 890822eaf7..f47359fe3c 100644
@@ -8509,6 +8509,12 @@
 
index 387839eb5d..dba686ac97 100644
@@ -18,6 +18,9 @@
 
 struct ExplainState;			/* defined in explain_state.h */
 
+extern PGDLLIMPORT QueryDesc *ActiveQueryDesc;
+extern PGDLLIMPORT bool ProcessLogQueryPlanInterruptActive;
+
 /* Hook for plugins to get control in ExplainOneQuery() */
 typedef void (*ExplainOneQuery_hook_type) (Query *query,
 										   int cursorOptions,
@@ -72,6 +75,12 @@ extern void ExplainOnePlan(PlannedStmt *plannedstmt, CachedPlan *cplan,
 						   const BufferUsage *bufusage,
 						   const MemoryContextCounters *mem_counters);
 
+extern void ExplainAssembleLogOutput(struct ExplainState *es, QueryDesc *queryDesc,
+									 int logFormat, bool logTriggers,
+									 int logParameterMaxLength);
+
+extern void ExplainPrintPlan(struct ExplainState *es, QueryDesc *queryDesc);
+extern void ExplainPrintTriggers(struct ExplainState *es, QueryDesc *queryDesc);
 extern void ExplainPrintPlan(struct ExplainState *es, QueryDesc *queryDesc);
 extern void ExplainPrintTriggers(struct ExplainState *es,
 								 QueryDesc *queryDesc);
@@ -83,4 +92,8 @@ extern void ExplainQueryText(struct ExplainState *es, QueryDesc *queryDesc);
 extern void ExplainQueryParameters(struct ExplainState *es,
 								   ParamListInfo params, int maxlen);
 
+extern void HandleLogQueryPlanInterrupt(void);
+extern void ProcessLogQueryPlanInterrupt(void);
+extern void ResetLogQueryPlanState(void);
+
 #endif							/* EXPLAIN_H */
diff --git a/src/include/commands/explain_state.h b/src/include/commands/explain_state.h
index 32728f5d1a..fcef956396 100644
--- a/src/include/commands/explain_state.h
+++ b/src/include/commands/explain_state.h
@@ -71,6 +71,7 @@ typedef struct ExplainState
 								 * entry */
 	/* state related to the current plan node */
 	ExplainWorkersState *workers_state; /* needed if parallel plan */
+	bool		signaled;		/* whether explain is called by signal */
 	/* extensions */
 	void	  **extension_state;
 	int			extension_state_allocated;
diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h
index 603d042435..3f46354cda 100644
--- a/src/include/miscadmin.h
+++ b/src/include/miscadmin.h
@@ -96,6 +96,7 @@ extern PGDLLIMPORT volatile sig_atomic_t IdleSessionTimeoutPending;
 extern PGDLLIMPORT volatile sig_atomic_t ProcSignalBarrierPending;
 extern PGDLLIMPORT volatile sig_atomic_t LogMemoryContextPending;
 extern PGDLLIMPORT volatile sig_atomic_t IdleStatsUpdateTimeoutPending;
+extern PGDLLIMPORT volatile sig_atomic_t LogQueryPlanPending;
 
index d4d4e65518..b95e4b97d0 100644

 

 

 

 RESET ROLE;

 

 

 

base-commit: 1d617a20284f887cb9cdfe5693eec155e8016517
-- 
2.48.1

---

From: Sadeq Dousti <[email-masked]>
Date: Mon, 31 Mar 2025 20:51:28 +0200

Hi,

Sergey and I reviewed this patch and have a few comments, listed below.

> to split the file as well as 9173e8b6046, but I leave it as it is for

1. As rightfully described by the OP above, explain.c has grown too big. In
addition, explain.h is added to quite a number of other files.

2. We wanted to entertain the possibility of a GUC variable to enable the
feature. That is, having it disabled by default, and the DBA can
selectively enable it on the fly. The reasoning is that it can affect some
workloads in an unforeseen way, so this choice can make the next Postgres
release safer. In future releases, we can make the default "enabled",
assuming enough real-world scenarios have proven the feature safe (i.e.,
not affecting the DB performance noticeably).

3. In the email thread for this patch, we saw some attempts to measure the
performance overhead of the feature. We suggest a more rigorous study,
including memory overhead in addition to time overhead. We came to the
conclusion that analytical workloads - with complicated query plans - and a
high query rate are the best to attempt such benchmarks.

4. In PGConf EU 2024, there was a talk by Rafael Castro titled "Debugging
active queries" [1], and he also submitted a recent patch titled "Proposal:
Progressive explain" [2]. We see a lot of similarities in the idea behind
that patch and this one, and would like to suggest joining forces for an
ideal outcome.

[1] https://www.youtube.com/watch?v=6ahTb-7C05c
[2] https://commitfest.postgresql.org/patch/5473/

Best Regards,
Sadeq Dousti & Sergey Dudoladov

---

From: Robert Haas <[email-masked]>
Date: Mon, 31 Mar 2025 15:24:51 -0400

Hi,

I apologize for not having noticed this thread sooner. I just became
aware of it as a result of a discussion in the hacking Discord server.
I think this has got a lot over overlap with the progressive EXPLAIN
patch from Rafael Castro, which I have been reviewing, but I am not
sure why we have two different patch sets here. Why is that?

Thanks,

-- 
Robert Haas
EDB: http://www.enterprisedb.com

---

From: torikoshia <[email-masked]>
Date: Thu, 03 Apr 2025 14:34:27 +0900

> Sergey and I reviewed this patch and have a few comments, listed
Thanks for your review!

> 1. As rightfully described by the OP above, explain.c has grown too
> big. In addition, explain.h is added to quite a number of other files.
Agreed.

> 
> 2. We wanted to entertain the possibility of a GUC variable to enable
> the feature. That is, having it disabled by default, and the DBA can
> selectively enable it on the fly. The reasoning is that it can affect
> some workloads in an unforeseen way, so this choice can make the next
> Postgres release safer. In future releases, we can make the default
> "enabled", assuming enough real-world scenarios have proven the
> feature safe (i.e., not affecting the DB performance noticeably).
Agreed.

> 3. In the email thread for this patch, we saw some attempts to measure
> the performance overhead of the feature. We suggest a more rigorous
> study, including memory overhead in addition to time overhead. We came
> to the conclusion that analytical workloads - with complicated query
> plans - and a high query rate are the best to attempt such benchmarks.
Agreed.

> 4. In PGConf EU 2024, there was a talk by Rafael Castro titled
> "Debugging active queries" [1], and he also submitted a recent patch
> titled "Proposal: Progressive explain" [2]. We see a lot of
> similarities in the idea behind that patch and this one, and would
> like to suggest joining forces for an ideal outcome.
As we have been discussing in this thread recently, I believe that since 
this patch has a narrower scope, it makes sense to complete it first and 
then extend it to support progress tracking in a step-by-step manner.

-- 
Regards,

---

From: torikoshia <[email-masked]>
Date: Thu, 03 Apr 2025 14:59:02 +0900

On 2025-04-03 01:22, Sami Imseih wrote:
>> > FWIW, I have been thinking about auto_explain for another task,
>> > remote plans for fdw [0], and perhaps there are now other good
>> > reasons, some that you mention, that can be simplified if "auto_explain"
>> > becomes a core feature. This could be a proposal taken up in 19.
>> 
>> For what we're talking about here, I don't think we would need to go
>> that far -- maybe put a few functions in core but no real need to move
>> the whole module into core. However, I don't rule out that there are
>> other reasons to do as you suggest.
> 
> This is the first core feature that will allow users to log explain 
> plans for
> a live workload. EXPLAIN is not really good for this purpose. So this
> proposal is a step in the correct direction. I think the appetite for 
> more
> plan logging/visibility options will likely increase, and 
> auto_explainlike
> features in core will be desired IMO. We will see.
> 
> As far as this patch goes, I took a look and I have some comments:

Thanks for your comments!

> 2/
> It should be noted that the plan will not print to the log until
> the plan begins executing the next plan node? depending on the
> operation, that could take some time ( i.e.long seq scan of a table, 
> etc.)
> Does this behavior need to be called out in docs?

Seems reasonable, but long seq scan of a table would not cause the case 
since ExecProcNode() is called at least the number of the rows in a 
table, as far as I remember.
Of course there can be the case where long time can elapse before 
executing ExecProcNode(), so I agree with adding the doc about this.

I'm going to improve including other than this comment in the next 
patch.

-- 
Regards,

---

From: Atsushi Torikoshi <[email-masked]>
Date: Sat, 5 Apr 2025 15:13:47 +0900

On Thu, Apr 3, 2025 at 11:10 PM Robert Haas <[email-masked]> wrote:
> Looking at ExplainAssembleLogOutput() is making me realize that
> auto_explain is in serious need of some cleanup. That's not really the
> fault of this patch, but the hack whereby we overwrite the [] that
> would have surrounded the JSON output with {} is not very nice. I also
> think that the auto_explain GUCs need rethinking. In theory, new
> EXPLAIN options should be mirrored into auto_explain, but if you
> compare ExplainAssembleLogOutput() to ExplainOnePlan(), you can see
> that they are diverging. The PLANNING, SUMMARY, and SERIALIZE options
> that are known to regular EXPLAIN aren't known to auto_explain, and
> any customizable options that use explain_per_plan_hook won't be able
> to work with auto_explain, either. Changing this is non-trivial
> because SERIALIZE, for example, can't work the same way for
> auto_explain as it does for EXPLAIN, and a full solution might also
> require user-visible changes like replacing
> auto_explain.log_<whatever> with auto_explain.explain, so I don't
> really know. Maybe we should just live with it the way it is for now,
> but it doesn't look very nice.

I agree that the current state isn’t ideal, and that addressing it wouldn’t
be trivial.
While I do think it would be better to tackle this before proceeding with
the current patch, I don’t think it's a prerequisite.
Also, since I’m not yet sure what the best way to fix it would be, I’ve
left it as-is for now.

> I don't think it's a good idea to put the logic to update
> ActiveQueryDesc into ExecutorRun. I think that function should really
> just call the hook, or standard_ExecutorRun. I don't know for sure
> whether this work should be moved down into ExecutorRun or up into the
> caller, but I don't think it should stay where it is.

Moved the logic from ExecutorRun() to standard_ExecutorRun(), since it
performs the necessary setup for collecting information during plan
execution i.e. calling InstrStartNode().

> My comment about the naming of WrapMultiExecProcNodesWithExplain() on
> the other thread also applies here: MultiExecProcNode() is an
> unrelated function.

Renamed WrapMultiExecProcNodesWithExplain to WrapPlanStatesWithExplain.

> Likewise, WrapExecProcNodeWithExplain() misses
> walking the node's subplan list.

Added logic for subplan to WrapExecProcNodeWithExplain() and
UnwrapExecProcNodeWithExplain().

> Also, I don't think an
> ereport(DEBUG1, ...) is appropriate here.

Removed these ereport(DEBUG1).

> Do we really need es->signaled? Couldn't we test es->analyze instead?

I think it's necessary.
Although when implementing progressive EXPLAIN, I believe we can use
es->analyze instead, this patch aims to retrieve plans for queries that
have neither an EXPLAIN nor an ANALYZE specified.

> Do we really need ExecProcNodeOriginal? Can we find some way to reuse
> ExecProcNodeReal instead of making the structure bigger?

I also wanted to implement this without adding elements to PlanState if
possible, but I haven't found a good solution, so the patch uses
ExecSetExecProcNode.

> I do think we should try to move some of this new code out into a
> separate source file, but I'm not yet sure what we should call it. We
> might want to share infrastructure with something what Rafael's patch,
> which he called progressive EXPLAIN but which is really closer to a
> general query progress facility, albeit perhaps too expensive to have
> enabled by default.

Made new files dynamic_explain.h and dynamic_explain.c, which you named and
it seems fine to me, and move most of the code to them.

> Does the documentation typically mention when a function is
> superuser-only? If so, it should do so here, too, using similar
> wording.

Added below explanation like other functions:

  This function is restricted to superusers by default, but other
  users can be granted EXECUTE to run the function.

> It seems unnecessary to remark that you can't log a query plan after a
> subtransaction abort, because the query wouldn't be running any more.

Removed the description.

> It's also true that you can't log a query after a toplevel abort, even
> if you're still waiting for the aborted transaction to roll back. But
> it also seems like once the aborted subtransaction is popped off the
> stack and we return to the parent transaction, we should be able to
> log any query running at the outer level.

It works as below:

  session-1
    begin;
    savepoint s1;
    err;
    ERROR:  syntax error at or near "err"
    rollback to s1;
    select pg_sleep(5);

  session-2
    select pg_log_query_plan(pid of session-1);

  log
    LOG:  query plan running on backend with PID 40025 is:
          Query Text: select pg_sleep(5);
          Result  (cost=0.00..0.01 rows=1 width=4)
            Output: pg_sleep('5'::double precision)

> Does ActiveQueryDesc really need to be exposed to the whole system?
> Could it be a file-level variable?

On Thu, Apr 3, 2025 at 11:10 PM Robert Haas <[email-masked]> wrote:
> I think the question of whether ActiveQueryDesc can be file-level is
> separate from whether prior configuration is needed. If it is
> important to touch this from multiple source files, then it is fine
> for it to be global. However, if we have a new source file, say
> dynamic_explain.c, then you could have functions
> ProcessDynamicExplainInterrupt() and DynamicExplainCleanup() in that
> file to set, use, clear ActiveQueryDesc, and the rest of the system
> might not need to know about it.

Thanks for the advice, I took this approach and made ActiveQueryDesc
file-level variable.

On Thu, Apr 3, 2025 at 1:23 AM Sami Imseih <[email-masked]> wrote:
> 7/
> Why do we only support TEXT format? I tried it with JSON
> and it looks fine in the log as well. I can imagine automated
> tools will want to be able to retrieve the plans using the
> structured formats.

I agree that allowing the choice of format would be useful.
However, I believe implementing this would require introducing new GUC or
creating a mechanism to pass the format from the executor of
pg_log_query_plan() to the target process.
For now, I think it would be better to minimize the scope of the initial
patch.

I think I have reflected your other comments in the attached patch.

Regards,
Atsushi Torikoshi

---

From: Hannu Krosing <[email-masked]>
Date: Mon, 28 Apr 2025 01:55:31 +0200

Have you also checked out
https://github.com/postgrespro/pg_query_state which logs running query
plan AND collected counts and timings as a response to a signal?

Has this ever been discussed for inclusion in core ?

On Thu, Apr 24, 2025 at 2:49 PM torikoshia <[email-masked]> wrote:
>
> Hi,
>
> Attached a rebased version of the patch.
>
> --
> Regards,
>
> --
> Atsushi Torikoshi

---

From: torikoshia <[email-masked]>
Date: Tue, 20 May 2025 22:17:59 +0900

> possible, but I haven't found a good solution, so the patch uses 

I tackled this again and the attached patch removes ExecProcNodeOriginal 
from Planstate.
Instead of adding a new field, this version builds the behavior into the 
existing wrapper function, ExecProcNodeFirst().

Since ExecProcNodeFirst() is already handling instrumentation-related 
logic, the patch has maybe become a bit more complex to accommodate both 
that and the new behavior.

While it might make sense to introduce a more general mechanism that 
allows for stacking an arbitrary number of wrappers around ExecProcNode, 
I’m not sure it's possible or worth the added complexity—such layered 
wrapping doesn't seem like something we typically need.

What do you think?

-- 
Regards,

From 41944eb943f8f6b2fb731125ed0d50ad29bbd338 Mon Sep 17 00:00:00 2001
From: Atsushi Torikoshi <[email-masked]>
Date: Tue, 20 May 2025 22:01:40 +0900
Subject: [PATCH v45] Add function to log the plan of the currently running
 query

On receipt of the request, codes for logging plan is wrapped
to every ExecProcNode under the current executing plan node,
and when the executor runs one of ExecProcNode, the plan is
actually logged. These wrappers are unwrapped when once the
plan is logged.
In this way, we can avoid adding the overhead which we'll face
when adding CHECK_FOR_INTERRUPTS() like mechanisms in somewhere
in executor codes safely.

Our initial idea was to send a signal to the target backend
process, which invokes EXPLAIN logic at the next
CHECK_FOR_INTERRUPTS() call. However, we realized during
prototyping that EXPLAIN is complex and may not be safely
executed at arbitrary interrupt points.

By default, only superusers are allowed to request to log the
plans because allowing any users to issue this request at an
unbounded rate would cause lots of log messages and which can
lead to denial of service.
---
 contrib/auto_explain/auto_explain.c          |  24 +-
 doc/src/sgml/func.sgml                       |  55 +++
 src/backend/access/transam/xact.c            |  13 +
 src/backend/catalog/system_functions.sql     |   2 +
 src/backend/commands/Makefile                |   1 +
 src/backend/commands/dynamic_explain.c       | 387 +++++++++++++++++++
 src/backend/commands/explain.c               |  38 +-
 src/backend/commands/meson.build             |   1 +
 src/backend/executor/execMain.c              |  10 +
 src/backend/executor/execProcnode.c          |  13 +-
 src/backend/storage/ipc/procsignal.c         |   4 +
 src/backend/tcop/postgres.c                  |   4 +
 src/backend/utils/init/globals.c             |   2 +
 src/include/catalog/pg_proc.dat              |   6 +
 src/include/commands/dynamic_explain.h       |  28 ++
 src/include/commands/explain.h               |   5 +
 src/include/commands/explain_state.h         |   1 +
 src/include/executor/executor.h              |   1 +
 src/include/miscadmin.h                      |   1 +
 src/include/storage/procsignal.h             |   2 +
 src/include/tcop/pquery.h                    |   1 -
 src/test/regress/expected/misc_functions.out |  54 ++-
 src/test/regress/sql/misc_functions.sql      |  41 +-
 23 files changed, 648 insertions(+), 46 deletions(-)
 create mode 100644 src/backend/commands/dynamic_explain.c
 create mode 100644 src/include/commands/dynamic_explain.h

diff --git a/contrib/auto_explain/auto_explain.c b/contrib/auto_explain/auto_explain.c
index cd6625020a..e720ddf39f 100644
--- a/contrib/auto_explain/auto_explain.c
+++ b/contrib/auto_explain/auto_explain.c
@@ -15,6 +15,7 @@
 #include <limits.h>
 
 #include "access/parallel.h"
+#include "commands/dynamic_explain.h"
 #include "commands/explain.h"
 #include "commands/explain_format.h"
 #include "commands/explain_state.h"
@@ -412,26 +413,9 @@ explain_ExecutorEnd(QueryDesc *queryDesc)
 			es->format = auto_explain_log_format;
 			es->settings = auto_explain_log_settings;
 
+			ExplainStringAssemble(es, queryDesc, auto_explain_log_format,
 
 			/*
 			 * Note: we rely on the existing logging of context or
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index b405525a46..700fdde184 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -28821,6 +28821,29 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_log_query_plan</primary>
+        </indexterm>
+        <function>pg_log_query_plan</function> ( <parameter>pid</parameter> <type>integer</type> )
+        <returnvalue>boolean</returnvalue>
+       </para>
+       <para>
+        Requests to log the plan of the query currently running on the
+        backend with specified process ID.
+        It will be logged at <literal>LOG</literal> message level and
+        will appear in the server log based on the log
+        configuration set (See <xref linkend="runtime-config-logging"/>
+        for more information), but will not be sent to the client
+        regardless of <xref linkend="guc-client-min-messages"/>.
+       </para>
+       <para>
+        This function is restricted to superusers by default, but other
+        users can be granted EXECUTE to run the function.
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
@@ -28971,6 +28994,38 @@ stats_timestamp  | 2025-03-24 13:55:47.796698+01
       will be less resource intensive when only the local backend is of interest.
      </para>
     </note>
+     </para>
+
+     <para>
+    <function>pg_log_query_plan</function> can be used
+    to log the plan of a backend process. For example:
+<programlisting>
+postgres=# SELECT pg_log_query_plan(201116);
+ pg_log_query_plan
+---------------------------
+ t
+(1 row)
+</programlisting>
+The format of the query plan is the same as when <literal>VERBOSE</literal>,
+<literal>COSTS</literal>, <literal>SETTINGS</literal> and
+<literal>FORMAT TEXT</literal> are used in the <command>EXPLAIN</command>
+command. For example:
+<screen>
+LOG:  query plan running on backend with PID 201116 is:
+        Query Text: SELECT * FROM pgbench_accounts;
+        Seq Scan on public.pgbench_accounts  (cost=0.00..52787.00 rows=2000000 width=97)
+          Output: aid, bid, abalance, filler
+        Settings: work_mem = '1MB'
+        Query Identifier: 8621255546560739680
+</screen>
+    Note that when the target is executing nested statements(statements executed
+    inside a function), only the innermost query plan is logged.
+    Note that logging plan may take some time, as it occurs when
+    the plan node is executed. For example, when a query is
+    running <function>pg_sleep</function>, the plan will not be
+    logged until the function execution completes.
+    Similarly, when a query is running under the extended query
+    protocol, the plan is logged only during the execute step.
    </para>
 
   </sect2>
diff --git a/src/backend/access/transam/xact.c b/src/backend/access/transam/xact.c
index b885513f76..6ba9b8f824 100644
--- a/src/backend/access/transam/xact.c
+++ b/src/backend/access/transam/xact.c
@@ -36,6 +36,7 @@
 #include "catalog/pg_enum.h"
 #include "catalog/storage.h"
 #include "commands/async.h"
+#include "commands/dynamic_explain.h"
 #include "commands/tablecmds.h"
 #include "commands/trigger.h"
 #include "common/pg_prng.h"
@@ -2904,6 +2905,12 @@ AbortTransaction(void)
 	/* Reset snapshot export state. */
 	SnapBuildResetExportedSnapshotState();
 
+	/*
+	 * After abort, some elements of ActiveQueryDesc are freed. To avoid
+	 * accessing them, reset ActiveQueryDesc here.
+	 */
+	ResetLogQueryPlanState();
+
 	/*
 	 * If this xact has started any unfinished parallel operation, clean up
 	 * its workers and exit parallel mode.  Don't warn about leaked resources.
@@ -5296,6 +5303,12 @@ AbortSubTransaction(void)
 	/* Reset logical streaming state. */
 	ResetLogicalStreamingState();
 
+	/*
+	 * After abort, some elements of ActiveQueryDesc are freed. To avoid

+REVOKE EXECUTE ON FUNCTION pg_log_query_plan(integer) FROM PUBLIC;
+
 --
 -- We also set up some things as accessible to standard roles.
 --
diff --git a/src/backend/commands/Makefile b/src/backend/commands/Makefile
index cb2fbdc7c6..5e83907eb1 100644
--- a/src/backend/commands/Makefile
+++ b/src/backend/commands/Makefile
@@ -32,6 +32,7 @@ OBJS = \
 	define.o \
 	discard.o \
 	dropcmds.o \
+	dynamic_explain.o \
 	event_trigger.o \
 	explain.o \
 	explain_dr.o \
diff --git a/src/backend/commands/dynamic_explain.c b/src/backend/commands/dynamic_explain.c
new file mode 100644
index 0000000000..55629ea554
--- /dev/null
+++ b/src/backend/commands/dynamic_explain.c
@@ -0,0 +1,387 @@
+/*-------------------------------------------------------------------------
+ *
+ * dynamic_explain.c
+ *	  Explain query plans during execution
+ *
+ * Portions Copyright (c) 1996-2025, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994-5, Regents of the University of California
+ *
+ * IDENTIFICATION
+ *	  src/backend/commands/dynamic_explain.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+#include "commands/dynamic_explain.h"
+#include "commands/explain.h"
+#include "commands/explain_format.h"
+#include "commands/explain_state.h"
+#include "miscadmin.h"
+#include "storage/proc.h"
+#include "storage/procarray.h"
+#include "utils/backend_status.h"
+
+/*
+ * True while this backend is processing a log query plan request,
+ * from the start of wrapping plan nodes until the log output is completed.
+ */
+static bool ProcessLogQueryPlanInterruptActive = false;
+
+/* Currently executing query's QueryDesc */
+static QueryDesc *ActiveQueryDesc = NULL;
+
+static void WrapExecProcNodeWithExplain(PlanState *ps);
+static void UnwrapExecProcNodeWithExplain(PlanState *ps);
+
+/*
+ * Handle receipt of an interrupt indicating logging the plan of the currently
+ * running query.
+ *
+ * All the actual work is deferred to ProcessLogQueryPlanInterrupt(),
+ * because we cannot safely emit a log message inside the signal handler.
+ */
+void
+HandleLogQueryPlanInterrupt(void)
+{
+	InterruptPending = true;
+	LogQueryPlanPending = true;
+	/* latch will be set by procsignal_sigusr1_handler */
+}
+
+/*
+ * Clear pg_log_query_plan() related state during (sub)transaction abort
+ */
+void
+ResetLogQueryPlanState(void)
+{
+	ActiveQueryDesc = NULL;
+	ProcessLogQueryPlanInterruptActive = false;
+}
+
+/*
+ * Wrap array of PlanState ExecProcNodes with ExecProcNodeWithExplain
+ */
+static void
+WrapPlanStatesWithExplain(PlanState **planstates, int nplans)
+{
+	int			i;
+
+	for (i = 0; i < nplans; i++)
+		WrapExecProcNodeWithExplain(planstates[i]);
+}
+
+/*
+ * Wrap CustomScanState children's ExecProcNodes with ExecProcNodeWithExplain
+ */
+static void
+WrapCustomPlanChildWithExplain(CustomScanState *css)
+{
+	ListCell   *cell;
+
+	foreach(cell, css->custom_ps)
+		WrapExecProcNodeWithExplain((PlanState *) lfirst(cell));
+}
+
+/*
+ * Recursively wrap all possible ExecProcNode().
+ *
+ * Recursion is necessary because the next ExecProcNode() call may be invoked
+ * not only through the current node, but also via lefttree, righttree, subPlan,
+ * or other special child plans.
+ */
+static void
+WrapExecProcNodeWithExplain(PlanState *ps)
+{
+	check_stack_depth();
+
+	ExecSetExecProcNode(ps, ps->ExecProcNodeReal);
+
+	if (ps->lefttree != NULL)
+		WrapExecProcNodeWithExplain(ps->lefttree);
+	if (ps->righttree != NULL)
+		WrapExecProcNodeWithExplain(ps->righttree);
+	if (ps->subPlan != NULL)
+	{
+		ListCell   *l;
+
+		foreach(l, ps->subPlan)
+		{
+			SubPlanState *sstate = (SubPlanState *) lfirst(l);
+			WrapExecProcNodeWithExplain(sstate->planstate);
+		}
+	}
+
+	/* special child plans */
+	switch (nodeTag(ps->plan))
+	{
+		case T_Append:
+			WrapPlanStatesWithExplain(((AppendState *) ps)->appendplans,
+									  ((AppendState *) ps)->as_nplans);
+			break;
+		case T_MergeAppend:
+			WrapPlanStatesWithExplain(((MergeAppendState *) ps)->mergeplans,
+									  ((MergeAppendState *) ps)->ms_nplans);
+			break;
+		case T_BitmapAnd:
+			WrapPlanStatesWithExplain(((BitmapAndState *) ps)->bitmapplans,
+									  ((BitmapAndState *) ps)->nplans);
+			break;
+		case T_BitmapOr:
+			WrapPlanStatesWithExplain(((BitmapOrState *) ps)->bitmapplans,
+									  ((BitmapOrState *) ps)->nplans);
+			break;
+		case T_SubqueryScan:
+			WrapExecProcNodeWithExplain(((SubqueryScanState *) ps)->subplan);
+			break;
+		case T_CustomScan:
+			WrapCustomPlanChildWithExplain((CustomScanState *) ps);
+			break;
+		default:
+			break;
+	}
+}
+
+/*
+ * Unwrap array of PlanStates ExecProcNodes with ExecProcNodeWithExplain
+ */
+static void
+UnwrapPlanStatesWithExplain(PlanState **planstates, int nplans)
+{
+	int			i;
+
+	for (i = 0; i < nplans; i++)
+		UnwrapExecProcNodeWithExplain(planstates[i]);
+}
+
+/*
+ * Unwrap CustomScanState children's ExecProcNodes with ExecProcNodeWithExplain
+ */
+static void
+UnwrapCustomPlanChildWithExplain(CustomScanState *css)
+{
+	ListCell   *cell;
+
+	foreach(cell, css->custom_ps)
+		UnwrapExecProcNodeWithExplain((PlanState *) lfirst(cell));
+}
+
+/*
+ * Recursively unwrap all possible ExecProcNode().
+ *
+ * Unwrap ExecProcNode() or wrap it for instrumentation if needed.
+ * Since ExecProcNodeWithExplain() is wrapped ealier in ExecProcNodeFirst(),
+ * perform instrumentation wrapping in this function.
+ */
+static void
+UnwrapExecProcNodeWithExplain(PlanState *ps)
+{
+	check_stack_depth();
+
+	if (ps->instrument && INSTR_TIME_IS_ZERO(ps->instrument->starttime))
+		ps->ExecProcNode = ExecProcNodeInstr;
+	else
+		ps->ExecProcNode = ps->ExecProcNodeReal;
+
+	if (ps->lefttree != NULL)
+		UnwrapExecProcNodeWithExplain(ps->lefttree);
+	if (ps->righttree != NULL)
+		UnwrapExecProcNodeWithExplain(ps->righttree);
+	if (ps->subPlan != NULL)
+	{
+		ListCell   *l;
+
+		foreach(l, ps->subPlan)
+		{
+			SubPlanState *sstate = (SubPlanState *) lfirst(l);
+
+			UnwrapExecProcNodeWithExplain(sstate->planstate);
+		}
+	}
+
+	/* special child plans */
+	switch (nodeTag(ps->plan))
+	{
+		case T_Append:
+			UnwrapPlanStatesWithExplain(((AppendState *) ps)->appendplans,
+										((AppendState *) ps)->as_nplans);
+			break;
+		case T_MergeAppend:
+			UnwrapPlanStatesWithExplain(((MergeAppendState *) ps)->mergeplans,
+										((MergeAppendState *) ps)->ms_nplans);
+			break;
+		case T_BitmapAnd:
+			UnwrapPlanStatesWithExplain(((BitmapAndState *) ps)->bitmapplans,
+										((BitmapAndState *) ps)->nplans);
+			break;
+		case T_BitmapOr:
+			UnwrapPlanStatesWithExplain(((BitmapOrState *) ps)->bitmapplans,
+										((BitmapOrState *) ps)->nplans);
+			break;
+		case T_SubqueryScan:
+			UnwrapExecProcNodeWithExplain(((SubqueryScanState *) ps)->subplan);
+			break;
+		case T_CustomScan:
+			UnwrapCustomPlanChildWithExplain((CustomScanState *) ps);
+			break;
+		default:
+			break;
+	}
+}
+
+/*
+ * Wrapper for logging currently running plan.
+ *
+ * ExecProcNode wrapper that performs logging plan of the currently running query.
+ */
+TupleTableSlot *
+ExecProcNodeWithExplain(PlanState *ps)
+{
+	ExplainState *es;
+	MemoryContext cxt;
+	MemoryContext old_cxt;
+
+	check_stack_depth();
+
+	cxt = AllocSetContextCreate(CurrentMemoryContext,
+								"log_query_plan temporary context",
+								ALLOCSET_DEFAULT_SIZES);
+
+	old_cxt = MemoryContextSwitchTo(cxt);
+
+	es = NewExplainState();
+
+	es->format = EXPLAIN_FORMAT_TEXT;
+	es->settings = true;
+	es->verbose = true;
+	es->signaled = true;
+
+	/*
+	 * ActiveQueryDesc is valid only during standard_ExecutorRun(). However,
+	 * ExecProcNode() can still be called afterward, such as ExecPostprocessPlan().
+	 * To handle the case, check ActiveQueryDesc.
+	 */
+	if (ActiveQueryDesc == NULL)
+		ereport(LOG_SERVER_ONLY,
+				errmsg("backend with PID %d is finishing query",
+					   MyProcPid),
+				errhidestmt(true),
+				errhidecontext(true));
+	else
+	{
+		ExplainStringAssemble(es, ActiveQueryDesc, es->format, 0, -1);
+
+		ereport(LOG_SERVER_ONLY,
+				errmsg("query plan running on backend with PID %d is:\n%s",
+					   MyProcPid, es->str->data),
+				errhidestmt(true),
+				errhidecontext(true));
+	}
+
+	MemoryContextSwitchTo(old_cxt);
+	MemoryContextDelete(cxt);
+
+	UnwrapExecProcNodeWithExplain(ps);
+
+	ProcessLogQueryPlanInterruptActive = false;
+
+	return ps->ExecProcNode(ps);
+}
+
+/*
+ * Perform logging plan for the currently running query.
+ *
+ * Since executing EXPLAIN-related code at an arbitrary CHECK_FOR_INTERRUPTS()
+ * point is potentially unsafe, this function just wraps the ExecProcNode() to
+ * log the query plan. This ensures that EXPLAIN code is executed only during
+ * ExecProcNode(), where it is considered safe.
+ */
+void
+ProcessLogQueryPlanInterrupt(void)
+{
+	LogQueryPlanPending = false;
+
+	/* Prevent re-entrance until the plan has been logged and the unwrapping has done */
+	if (ProcessLogQueryPlanInterruptActive)
+		return;
+
+	ProcessLogQueryPlanInterruptActive = true;
+
+	if (ActiveQueryDesc == NULL)
+	{
+		ereport(LOG_SERVER_ONLY,
+				errmsg("backend with PID %d is not running a query or a subtransaction is aborted",
+					   MyProcPid),
+				errhidestmt(true),
+				errhidecontext(true));
+
+		ProcessLogQueryPlanInterruptActive = false;
+		return;
+	}
+
+	WrapExecProcNodeWithExplain(ActiveQueryDesc->planstate);
+}
+
+bool
+GetProcessLogQueryPlanInterruptActive(void)
+{
+	return ProcessLogQueryPlanInterruptActive;
+}
+
+inline QueryDesc *
+GetActiveQueryDesc(void)
+{
+	return ActiveQueryDesc;
+}
+
+void
+inline SetActiveQueryDesc(QueryDesc *queryDesc)
+{
+	ActiveQueryDesc = queryDesc;
+}
+
+/*
+ * Signal a backend process to log the query plan of the running query.
+ *
+ * By default, only superusers are allowed to signal to log the plan because
+ * allowing any users to issue this request at an unbounded rate would
+ * cause lots of log messages and which can lead to denial of service.
+ * Additional roles can be permitted with GRANT.
+ */
+Datum
+pg_log_query_plan(PG_FUNCTION_ARGS)
+{
+	int			pid = PG_GETARG_INT32(0);
+	PGPROC	   *proc;
+	PgBackendStatus *be_status;
+
+	proc = BackendPidGetProc(pid);
+
+	if (proc == NULL)
+	{
+		/*
+		 * This is just a warning so a loop-through-resultset will not abort
+		 * if one backend terminated on its own during the run.
+		 */
+		ereport(WARNING,
+				(errmsg("PID %d is not a PostgreSQL backend process", pid)));
+		PG_RETURN_BOOL(false);
+	}
+
+	be_status = pgstat_get_beentry_by_proc_number(proc->vxid.procNumber);
+	if (be_status->st_backendType != B_BACKEND)
+	{
+		ereport(WARNING,
+				(errmsg("PID %d is not a PostgreSQL client backend process", pid)));
+		PG_RETURN_BOOL(false);
+	}
+
+	if (SendProcSignal(pid, PROCSIG_LOG_QUERY_PLAN, proc->vxid.procNumber) < 0)
+	{
+		/* Again, just a warning to allow loops */
+		ereport(WARNING,
+				(errmsg("could not send signal to process %d: %m", pid)));
+		PG_RETURN_BOOL(false);
+	}
+
+	PG_RETURN_BOOL(true);
+}
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 786ee865f1..fd37772504 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -1100,6 +1100,37 @@ ExplainQueryParameters(ExplainState *es, ParamListInfo params, int maxlen)
 		ExplainPropertyText("Query Parameters", str, es);
 }
 
+/*
+ * ExplainStringAssemble -
+ExplainStringAssemble(ExplainState *es, QueryDesc *queryDesc, int logFormat,
  * report_triggers -
  *		report execution stats for a single relation's triggers
@@ -1827,7 +1858,10 @@ ExplainNode(PlanState *planstate, List *ancestors,
 
 	/*
 	 * We have to forcibly clean up the instrumentation state because we
-	 * haven't done ExecutorEnd yet.  This is pretty grotty ...
+	 * haven't done ExecutorEnd yet.  This is pretty grotty ... This cleanup
+	 * should not be done when the query has already been executed and explain
+	 * has been called by signal, as the target query may use instrumentation
+	 * and clean itself up.
 	 *
 	 * Note: contrib/auto_explain could cause instrumentation to be set up
 	 * even though we didn't ask for it here.  Be careful not to print any
@@ -1835,7 +1869,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 	 * InstrEndLoop call anyway, if possible, to reduce the number of cases
 	 * auto_explain has to contend with.
 	 */
-	if (planstate->instrument)
+	if (planstate->instrument && !es->signaled)
 		InstrEndLoop(planstate->instrument);
 
 	if (es->analyze &&
diff --git a/src/backend/commands/meson.build b/src/backend/commands/meson.build
index dd4cde41d3..4a64b8e9d0 100644
--- a/src/backend/commands/meson.build
+++ b/src/backend/commands/meson.build
@@ -20,6 +20,7 @@ backend_sources += files(
   'define.c',
   'discard.c',
   'dropcmds.c',
+  'dynamic_explain.c',
   'event_trigger.c',
   'explain.c',
   'explain_dr.c',
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 7230f96810..b1f3e83b62 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -43,6 +43,7 @@
 #include "access/xact.h"
 #include "catalog/namespace.h"
 #include "catalog/partition.h"
+#include "commands/dynamic_explain.h"
 #include "commands/matview.h"
 #include "commands/trigger.h"
 #include "executor/executor.h"
@@ -380,6 +381,7 @@ standard_ExecutorRun(QueryDesc *queryDesc,
 	DestReceiver *dest;
 	bool		sendTuples;
 	MemoryContext oldcontext;
+	QueryDesc  *oldActiveQueryDesc;
 
 	/* sanity checks */
 	Assert(queryDesc != NULL);
@@ -393,6 +395,13 @@ standard_ExecutorRun(QueryDesc *queryDesc,
 	/* caller must ensure the query's snapshot is active */
 	Assert(GetActiveSnapshot() == estate->es_snapshot);
 
+	/*
+	 * Save ActiveQueryDesc here to enable retrieval of the currently running
+	 * queryDesc for nested queries.
+	 */
+	oldActiveQueryDesc = GetActiveQueryDesc();
+	SetActiveQueryDesc(queryDesc);
+
 	/*
 	 * Switch into per-query memory context
 	 */
@@ -455,6 +464,7 @@ standard_ExecutorRun(QueryDesc *queryDesc,
 		InstrStopNode(queryDesc->totaltime, estate->es_processed);
 
 	MemoryContextSwitchTo(oldcontext);
+	SetActiveQueryDesc(oldActiveQueryDesc);
 }
 
 /* ----------------------------------------------------------------
diff --git a/src/backend/executor/execProcnode.c b/src/backend/executor/execProcnode.c
index f5f9cfbeea..dbdd99830b 100644
--- a/src/backend/executor/execProcnode.c
+++ b/src/backend/executor/execProcnode.c
@@ -72,6 +72,7 @@
  */
 #include "postgres.h"
 
+#include "commands/dynamic_explain.h"
 #include "executor/executor.h"
 #include "executor/nodeAgg.h"
 #include "executor/nodeAppend.h"
@@ -120,7 +121,6 @@
 #include "nodes/nodeFuncs.h"
 
 static TupleTableSlot *ExecProcNodeFirst(PlanState *node);
-static TupleTableSlot *ExecProcNodeInstr(PlanState *node);
 static bool ExecShutdownNode_walker(PlanState *node, void *context);

@@ -456,12 +456,19 @@ ExecProcNodeFirst(PlanState *node)
 	 */
 	check_stack_depth();
 
+	/*
+	 * If logging plan is requested, handle it first. If instrumentation is also
+	 * requested, update the wrapper accordingly after logging plan is completed.
+	 * See ExecProcNodeWithExplain().
+	 */
+	if (GetProcessLogQueryPlanInterruptActive())
+		node->ExecProcNode = ExecProcNodeWithExplain;
 	/*
 	 * If instrumentation is required, change the wrapper to one that just
 	 * does instrumentation.  Otherwise we can dispense with all wrappers and
 	 * have ExecProcNode() directly call the relevant function from now on.
 	 */
-	if (node->instrument)
+	else if (node->instrument)
 		node->ExecProcNode = ExecProcNodeInstr;
 	else
 		node->ExecProcNode = node->ExecProcNodeReal;
@@ -475,7 +482,7 @@ ExecProcNodeFirst(PlanState *node)
  * this a separate function, we avoid overhead in the normal case where
  * no instrumentation is wanted.
  */
-static TupleTableSlot *
+TupleTableSlot *
 ExecProcNodeInstr(PlanState *node)
 {
 	TupleTableSlot *result;
diff --git a/src/backend/storage/ipc/procsignal.c b/src/backend/storage/ipc/procsignal.c
index ce69e26d72..765b5c6599 100644
--- a/src/backend/storage/ipc/procsignal.c
+++ b/src/backend/storage/ipc/procsignal.c
@@ -19,6 +19,7 @@
 
 #include "access/parallel.h"
 #include "commands/async.h"
+#include "commands/dynamic_explain.h"
 #include "miscadmin.h"
 #include "pgstat.h"
 #include "port/pg_bitutils.h"
@@ -694,6 +695,9 @@ procsignal_sigusr1_handler(SIGNAL_ARGS)
 	if (CheckProcSignal(PROCSIG_GET_MEMORY_CONTEXT))
 		HandleGetMemoryContextInterrupt();

diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 1ae51b1b39..082a714629 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -37,6 +37,7 @@
 #include "catalog/pg_type.h"
 #include "commands/async.h"
 #include "commands/event_trigger.h"
+#include "commands/dynamic_explain.h"
 #include "commands/prepare.h"
 #include "common/pg_prng.h"
 #include "jit/jit.h"
@@ -3538,6 +3539,9 @@ ProcessInterrupts(void)
 	if (PublishMemoryContextPending)
 		ProcessGetMemoryContextInterrupt();
 
+	if (LogQueryPlanPending)
+		ProcessLogQueryPlanInterrupt();
+
 	if (ParallelApplyMessagePending)
 		ProcessParallelApplyMessages();
 }
diff --git a/src/backend/utils/init/globals.c b/src/backend/utils/init/globals.c
index 92b0446b80..ffd5f712de 100644
--- a/src/backend/utils/init/globals.c
+++ b/src/backend/utils/init/globals.c
@@ -41,6 +41,8 @@ volatile sig_atomic_t ProcSignalBarrierPending = false;
 volatile sig_atomic_t LogMemoryContextPending = false;
 volatile sig_atomic_t PublishMemoryContextPending = false;
 volatile sig_atomic_t IdleStatsUpdateTimeoutPending = false;
+volatile sig_atomic_t LogQueryPlanPending = false;
+
 volatile uint32 InterruptHoldoffCount = 0;
 volatile uint32 QueryCancelHoldoffCount = 0;
 volatile uint32 CritSectionCount = 0;
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 62beb71da2..ae793299e2 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8581,6 +8581,12 @@
   proargnames => '{pid, summary, timeout, name, ident, type, path, level, total_bytes, total_nblocks, free_bytes, free_chunks, used_bytes, num_agg_contexts, stats_timestamp}',
   prosrc => 'pg_get_process_memory_contexts' },
 
+# logging plan of the running query on the specified backend
+{ oid => '8000', descr => 'log plan of the running query on the specified backend',
+  proname => 'pg_log_query_plan',
+  provolatile => 'v', prorettype => 'bool',
+  proargtypes => 'int4', prosrc => 'pg_log_query_plan' },
+
 # non-persistent series generator
 { oid => '1066', descr => 'non-persistent series generator',
   proname => 'generate_series', prorows => '1000',
diff --git a/src/include/commands/dynamic_explain.h b/src/include/commands/dynamic_explain.h
new file mode 100644
index 0000000000..75b864f63a
--- /dev/null
+++ b/src/include/commands/dynamic_explain.h
@@ -0,0 +1,28 @@
+/*-------------------------------------------------------------------------
+ *
+ * dynamic_explain.h
+ *	  prototypes for dynamic_explain.c
+ *
+ * Portions Copyright (c) 1996-2025, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994-5, Regents of the University of California
+ *
+ * src/include/commands/dynamic_explain.h
+ *
+ *-------------------------------------------------------------------------
+ */
+#ifndef DYNAMIC_EXPLAIN_H
+#define DYNAMIC_EXPLAIN_H
+
+#include "executor/executor.h"
+#include "commands/explain_state.h"
+
+extern void HandleLogQueryPlanInterrupt(void);
+extern void ResetLogQueryPlanState(void);
+extern void ProcessLogQueryPlanInterrupt(void);
+extern bool GetProcessLogQueryPlanInterruptActive(void);
+extern QueryDesc *GetActiveQueryDesc(void);
+extern void SetActiveQueryDesc(QueryDesc *queryDesc);
+extern TupleTableSlot *ExecProcNodeWithExplain(PlanState *ps);
+extern Datum pg_log_query_plan(PG_FUNCTION_ARGS);
+
+#endif							/* DYNAMIC_EXPLAIN_H */
diff --git a/src/include/commands/explain.h b/src/include/commands/explain.h
index 03c5b3d73e..083567caf0 100644
--- a/src/include/commands/explain.h
+++ b/src/include/commands/explain.h
@@ -72,6 +72,8 @@ extern void ExplainOnePlan(PlannedStmt *plannedstmt, CachedPlan *cplan,
 						   const BufferUsage *bufusage,
 						   const MemoryContextCounters *mem_counters);
 
+extern void ExplainPrintPlan(struct ExplainState *es, QueryDesc *queryDesc);
+extern void ExplainPrintTriggers(struct ExplainState *es, QueryDesc *queryDesc);
 extern void ExplainPrintPlan(struct ExplainState *es, QueryDesc *queryDesc);
 extern void ExplainPrintTriggers(struct ExplainState *es,
 								 QueryDesc *queryDesc);
@@ -82,5 +84,8 @@ extern void ExplainPrintJITSummary(struct ExplainState *es,
 extern void ExplainQueryText(struct ExplainState *es, QueryDesc *queryDesc);
 extern void ExplainQueryParameters(struct ExplainState *es,
 								   ParamListInfo params, int maxlen);
+extern void ExplainStringAssemble(struct ExplainState *es, QueryDesc *queryDesc,
+								  int logFormat, bool logTriggers,
+								  int logParameterMaxLength);
 
 #endif							/* EXPLAIN_H */
diff --git a/src/include/commands/explain_state.h b/src/include/commands/explain_state.h
index 32728f5d1a..fcef956396 100644
--- a/src/include/commands/explain_state.h
+++ b/src/include/commands/explain_state.h
@@ -71,6 +71,7 @@ typedef struct ExplainState
 								 * entry */
 	/* state related to the current plan node */
 	ExplainWorkersState *workers_state; /* needed if parallel plan */
+	bool		signaled;		/* whether explain is called by signal */
 	/* extensions */
 	void	  **extension_state;
 	int			extension_state_allocated;
diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h
index ae99407db8..93faf9c811 100644
--- a/src/include/executor/executor.h
+++ b/src/include/executor/executor.h
@@ -295,6 +295,7 @@ extern void EvalPlanQualEnd(EPQState *epqstate);
  */
 extern PlanState *ExecInitNode(Plan *node, EState *estate, int eflags);
 extern void ExecSetExecProcNode(PlanState *node, ExecProcNodeMtd function);
+extern TupleTableSlot *ExecProcNodeInstr(PlanState *node);
 extern Node *MultiExecProcNode(PlanState *node);
 extern void ExecEndNode(PlanState *node);
 extern void ExecShutdownNode(PlanState *node);
diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h
index 1e59a7f910..2b7d664812 100644
--- a/src/include/miscadmin.h
+++ b/src/include/miscadmin.h
@@ -97,6 +97,7 @@ extern PGDLLIMPORT volatile sig_atomic_t ProcSignalBarrierPending;
 extern PGDLLIMPORT volatile sig_atomic_t LogMemoryContextPending;
 extern PGDLLIMPORT volatile sig_atomic_t IdleStatsUpdateTimeoutPending;
 extern PGDLLIMPORT volatile sig_atomic_t PublishMemoryContextPending;
+extern PGDLLIMPORT volatile sig_atomic_t LogQueryPlanPending;
 
 extern PGDLLIMPORT volatile sig_atomic_t CheckClientConnectionPending;
 extern PGDLLIMPORT volatile sig_atomic_t ClientConnectionLost;
diff --git a/src/include/storage/procsignal.h b/src/include/storage/procsignal.h
index 345d5a0ecb..60d6d80c4f 100644
--- a/src/include/storage/procsignal.h
+++ b/src/include/storage/procsignal.h
@@ -36,6 +36,8 @@ typedef enum
 	PROCSIG_BARRIER,			/* global barrier interrupt  */
 	PROCSIG_LOG_MEMORY_CONTEXT, /* ask backend to log the memory contexts */
 	PROCSIG_GET_MEMORY_CONTEXT, /* ask backend to send the memory contexts */
+	PROCSIG_LOG_QUERY_PLAN,		/* ask backend to log plan of the current
+								 * query */
 	PROCSIG_PARALLEL_APPLY_MESSAGE, /* Message from parallel apply workers */
 
 	/* Recovery conflict reasons */
diff --git a/src/include/tcop/pquery.h b/src/include/tcop/pquery.h
index fa3cc5f2df..7949abf76b 100644
--- a/src/include/tcop/pquery.h
+++ b/src/include/tcop/pquery.h
@@ -22,7 +22,6 @@ struct PlannedStmt;				/* avoid including plannodes.h here */

-
 
 extern List *FetchPortalTargetList(Portal portal);
diff --git a/src/test/regress/expected/misc_functions.out b/src/test/regress/expected/misc_functions.out
index cc517ed5e9..2c4d2c3178 100644
--- a/src/test/regress/expected/misc_functions.out
+++ b/src/test/regress/expected/misc_functions.out
@@ -316,14 +316,15 @@ SELECT test_canonicalize_path('./abc/./def/.././ghi/../../../jkl/mno');
  ../jkl/mno
 (1 row)

 

 RESET ROLE;
index 5f9c77512d..d9df928389 100644

 

 

 

base-commit: cbf53e2b8a8ed3fc6f554095a4e99591bd5193f6
-- 
2.43.0

---

From: Robert Haas <[email-masked]>
Date: Thu, 22 May 2025 11:07:50 -0400

> wrapping doesn't seem like something we typically need.

Hmm, I'm not convinced that this is correct. If
GetProcessLogQueryPlanInterruptActive() is true but node->instrument
is also non-NULL, your implementation of ExecProcNodeFirst() will
handle the first but ignore the second. Plus, I don't understand why
ExecProcNodeFirst() does node->ExecProcNode = ExecProcNodeWithExplain
instead of just directly doing the necessary work. It seems to me that
this will result in the first call to ExecProcNode calling
ExecProcNodeFirst to install ExecProcNodeWithExplain; and then the
second call to ExecProcNode will call ExecProcNodeWithExplain which
will actually do the work. But this seems unnecessary to me: I think
it could just say if (GetProcessLogQueryPlanInterruptActive())
LogQueryPlan(ps).

Backing up a step, I think you've got a good idea here in thinking
that we can probably reuse ExecProcNodeFirst for this purpose. It
appears to me that it is always valid to reset a node's ExecProcNode
pointer back to ExecProcNodeFirst. It might be unnecessary and cost
some performance to do it when not required, but it is safe, because
ExecProcNodeFirst will simply reset node->ExecProcNode and then call
the appropriate function. So what we can do is just add the additional
logic to check whether we need to print the query plan after the
check_stack_depth() call and before the rest of the logic in the
function. I've attached a sample patch to show what I have in mind.

---

From: Atsushi Torikoshi <[email-masked]>
Date: Fri, 23 May 2025 17:50:48 +0900

On Fri, May 23, 2025 at 12:08 AM Robert Haas <[email-masked]> wrote:
Thanks for your review!

> handle the first but ignore the second.
In that case, the patch performs instrumentation during unwrapping --
specifically when executing UnwrapExecProcNodeWithExplain() -- so that
the query plan can still be logged for statements like "EXPLAIN
ANALYZE SELECT ..".
However, I admit this isn’t a good implementation: it’s hard to follow.

> Plus, I don't understand why
> instead of just directly doing the necessary work.
Indeed!

> It seems to me that

> function. I've attached a sample patch to show what I have in mind.

Thanks for the idea and the sample patch!
Agreed. I’ll go ahead and implement a new patch based on this approach.

--
Atsushi Torikoshi

---

From: torikoshia <[email-masked]>
Date: Mon, 02 Jun 2025 21:56:38 +0900

> Agreed. I’ll go ahead and implement a new patch based on this approach.

Updated the patch.

Here are some notes:

As with the previous patches, this one wraps not only the currently 
executing plan node but also recursively wraps the left, right, and 
child nodes' ExecProcNode with ExecProcNodeFirst.
This is because modifying only the currently executing node caused 
significant delays in plan logging when the left, right, or child nodes 
took a long time to execute.
I observed the situation with the following query:

   SELECT count(*) FROM pgbench_accounts a CROSS JOIN pgbench_accounts b;

Previous patches implemented unwrappers, but this one doesn’t.
This is because once the log is output, 
GetProcessLogQueryPlanInterruptActive() returns false, so LogQueryPlan() 
will no longer be called.

In the sample you previously provided, the LogQueryPlan function takes a 
PlanState as an argument, but in this patch, it’s defined as void.
I made this change under the assumption that the plan can be obtained 
from ActiveQueryDesc, and that PlanState is therefore unnecessary.
Please let me know if I’ve misunderstood anything.

Regards,

From 3483da9204475fb9dc7bdd67d5db32510bd32ab4 Mon Sep 17 00:00:00 2001
From: Atsushi Torikoshi <[email-masked]>
Date: Mon, 2 Jun 2025 21:10:06 +0900
Subject: [PATCH v45] Add function to log the plan of the currently running
 query

On receipt of the request, ExecProcnodes of the current plan
node and its subsidiary nodes are wrapped with
ExecProcNodeFirst, which implelements logging query plan.
When executor executes the one of the wrapped nodes, the
query plan is logged.

Upon receiving a request to log the query plan, the ExecProcNode
functions of the current plan node, as well as its left, right,
and other child nodes, are wrapped with ExecProcNodeFirst, which
implements the logging mechanism. When the executor invokes any
of the wrapped nodes, the query plan is logged.

By default, only superusers are allowed to request to log the
plans because allowing any users to issue this request at an
unbounded rate would cause lots of log messages and which can
lead to denial of service.
Squashed commit of the following:
---
 contrib/auto_explain/auto_explain.c          |  24 +--
 doc/src/sgml/func.sgml                       |  57 ++++++
 src/backend/access/transam/xact.c            |  13 ++
 src/backend/catalog/system_functions.sql     |   2 +
 src/backend/commands/Makefile                |   1 +
 src/backend/commands/dynamic_explain.c       | 195 +++++++++++++++++++
 src/backend/commands/explain.c               |  38 +++-
 src/backend/commands/meson.build             |   1 +
 src/backend/executor/execMain.c              |  17 ++
 src/backend/executor/execProcnode.c          | 120 +++++++++++-
 src/backend/storage/ipc/procsignal.c         |   4 +
 src/backend/tcop/postgres.c                  |   4 +
 src/backend/utils/init/globals.c             |   2 +
 src/include/catalog/pg_proc.dat              |   6 +
 src/include/commands/dynamic_explain.h       |  29 +++
 src/include/commands/explain.h               |   5 +
 src/include/commands/explain_state.h         |   1 +
 src/include/executor/executor.h              |   1 +
 src/include/miscadmin.h                      |   2 +-
 src/include/storage/procsignal.h             |   2 +
 src/include/tcop/pquery.h                    |   1 -
 src/test/regress/expected/misc_functions.out |  57 +++++-
 src/test/regress/sql/misc_functions.sql      |  45 ++++-
 23 files changed, 573 insertions(+), 54 deletions(-)
 create mode 100644 src/backend/commands/dynamic_explain.c
 create mode 100644 src/include/commands/dynamic_explain.h

diff --git a/contrib/auto_explain/auto_explain.c b/contrib/auto_explain/auto_explain.c
index 1f4badb492..6c4217c9d1 100644
--- a/contrib/auto_explain/auto_explain.c
+++ b/contrib/auto_explain/auto_explain.c
@@ -15,6 +15,7 @@
 #include <limits.h>
 
@@ -404,26 +405,9 @@ explain_ExecutorEnd(QueryDesc *queryDesc)

 			/*
 			 * Note: we rely on the existing logging of context or
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index c67688cbf5..75a48a5744 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -28684,6 +28684,29 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
        </para></entry>
       </row>
 
+      <row>
@@ -28802,6 +28825,40 @@ LOG:  Grand total: 1651920 bytes in 201 blocks; 622360 free (88 chunks); 1029560
 
+   <para>
+    <function>pg_log_query_plan</function> can be used
+    to log the plan of a backend process. For example:
+<programlisting>
+postgres=# SELECT pg_log_query_plan(201116);
+ pg_log_query_plan
+---------------------------
+ t
+(1 row)
+</programlisting>
+The format of the query plan is the same as when <literal>VERBOSE</literal>,
+<literal>COSTS</literal>, <literal>SETTINGS</literal> and
+<literal>FORMAT TEXT</literal> are used in the <command>EXPLAIN</command>
+command. For example:
+<screen>
+LOG:  query plan running on backend with PID 201116 is:
+        Query Text: SELECT * FROM pgbench_accounts;
+        Seq Scan on public.pgbench_accounts  (cost=0.00..52787.00 rows=2000000 width=97)
+          Output: aid, bid, abalance, filler
+        Settings: work_mem = '1MB'
+        Query Identifier: 8621255546560739680
+</screen>
+    When the target is executing nested statements(statements executed
+    inside a function), only the innermost query plan is logged.
+    Logging plan may take some time, as it occurs when the plan node is
+    executed. For example, when a query is running <function>pg_sleep</function>,
+    the plan will not be logged until the function execution completes.
+    Similarly, when a query is running under the extended query
+    protocol, the plan is logged only during the execute step.
+    <function>pg_log_query_plan()</function> may return <literal>true</literal>
+    even if no plan is actually logged, when the query is already about to
+    finish and the plan logging request comes too late.
+   </para>
+
   </sect2>
 
index 2e67e998ad..5df4dd0253 100644
@@ -2932,6 +2933,12 @@ AbortTransaction(void)
 
+	/*
@@ -5324,6 +5331,12 @@ AbortSubTransaction(void)
 
+	/*

+REVOKE EXECUTE ON FUNCTION pg_log_query_plan(integer) FROM PUBLIC;
+
 --
 -- We also set up some things as accessible to standard roles.
 --
diff --git a/src/backend/commands/Makefile b/src/backend/commands/Makefile
index cb2fbdc7c6..5e83907eb1 100644
--- a/src/backend/commands/Makefile
+++ b/src/backend/commands/Makefile
@@ -32,6 +32,7 @@ OBJS = \
 	define.o \
 	discard.o \
 	dropcmds.o \
+	dynamic_explain.o \
 	event_trigger.o \
 	explain.o \
 	explain_dr.o \
diff --git a/src/backend/commands/dynamic_explain.c b/src/backend/commands/dynamic_explain.c
new file mode 100644
index 0000000000..cfc13ce029
--- /dev/null
+++ b/src/backend/commands/dynamic_explain.c
@@ -0,0 +1,195 @@
+/*-------------------------------------------------------------------------
+ *
+ * dynamic_explain.c
+ *	  Explain query plans during execution
+ *
+ * Portions Copyright (c) 1996-2025, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994-5, Regents of the University of California
+ *
+ * IDENTIFICATION
+ *	  src/backend/commands/dynamic_explain.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+#include "commands/dynamic_explain.h"
+#include "commands/explain.h"
+#include "commands/explain_format.h"
+#include "commands/explain_state.h"
+#include "miscadmin.h"
+#include "storage/proc.h"
+#include "storage/procarray.h"
+#include "utils/backend_status.h"
+
+/* Currently executing query's QueryDesc */
+static QueryDesc *ActiveQueryDesc = NULL;
+
+/*
+ * Handle receipt of an interrupt indicating logging the plan of the currently
+ * running query.
+ *
+ * All the actual work is deferred to ProcessLogQueryPlanInterrupt(),
+ * because we cannot safely emit a log message inside the signal handler.
+ */
+void
+HandleLogQueryPlanInterrupt(void)
+{
+	InterruptPending = true;
+	LogQueryPlanPending = true;
+	/* latch will be set by procsignal_sigusr1_handler */
+}
+
+/*
+ * Clear pg_log_query_plan() related state during (sub)transaction abort.
+ */
+void
+ResetLogQueryPlanState(void)
+{
+	ActiveQueryDesc = NULL;
+	LogQueryPlanPending = false;
+}
+
+/*
+ * Actual plan logging function.
+ */
+void
+LogQueryPlan(void)
+{
+	ExplainState *es;
+	MemoryContext cxt;
+	MemoryContext old_cxt;
+
+	cxt = AllocSetContextCreate(CurrentMemoryContext,
+								"log_query_plan temporary context",
+								ALLOCSET_DEFAULT_SIZES);
+
+	old_cxt = MemoryContextSwitchTo(cxt);
+
+	es = NewExplainState();
+
+	es->format = EXPLAIN_FORMAT_TEXT;
+	es->settings = true;
+	es->verbose = true;
+	es->signaled = true;
+
+	/*
+	 * ActiveQueryDesc is valid only during standard_ExecutorRun. However,
+	 * ExecProcNode can be called afterward(i.e., ExecPostprocessPlan). To
+	 * handle the case, check ActiveQueryDesc.
+	 */
+	if (ActiveQueryDesc == NULL)
+	{
+		LogQueryPlanPending = false;
+
+		ereport(LOG_SERVER_ONLY,
+				errmsg("backend with PID %d is finishing query execution and cannot log the plan.",
+					   MyProcPid),
+				errhidestmt(true),
+				errhidecontext(true));
+	}
+
+	ExplainStringAssemble(es, ActiveQueryDesc, es->format, 0, -1);
+
+	ereport(LOG_SERVER_ONLY,
+			errmsg("query plan running on backend with PID %d is:\n%s",
+				   MyProcPid, es->str->data),
+			errhidestmt(true),
+			errhidecontext(true));
+
+	MemoryContextSwitchTo(old_cxt);
+	MemoryContextDelete(cxt);
+
+	LogQueryPlanPending = false;
+}
+
+/*
+ * Process the request for logging query plan at CHECK_FOR_INTERRUPTS().
+ *
+ * Since executing EXPLAIN-related code at an arbitrary CHECK_FOR_INTERRUPTS()
+ * point is potentially unsafe, this function just wraps the nodes of
+ * ExecProcNode with ExecProcNodeFirst, which logs corrent query plan if
+ * requested. This way ensures that EXPLAIN code is executed only during
+ * ExecProcNodeFirst, where it is considered safe.
+ */
+void
+ProcessLogQueryPlanInterrupt(void)
+{
+	if (ActiveQueryDesc == NULL)
+	{
+		ereport(LOG_SERVER_ONLY,
+				errmsg("backend with PID %d is not running a query or a subtransaction is aborted",
+					   MyProcPid),
+				errhidestmt(true),
+				errhidecontext(true));
+
+		LogQueryPlanPending = false;
+		return;
+	}
+
+	/* Wrap ExecProcNodes */
+	ExecSetExecProcNodeRecurse(ActiveQueryDesc->planstate);
+}
+
+/*
+ * Returns ActiveQueryDesc.
+ */
+QueryDesc *
+GetActiveQueryDesc(void)
+{
+	return ActiveQueryDesc;
+}
+
+/*
+ Set ActiveQueryDesc to queryDesc.
+ */
+void
+SetActiveQueryDesc(QueryDesc *queryDesc)
+{
+	ActiveQueryDesc = queryDesc;
+}
+
+/*
+ * Signal a backend process to log the query plan of the running query.
+ *
+ * By default, only superusers are allowed to signal to log the plan because
+ * allowing any users to issue this request at an unbounded rate would
+ * cause lots of log messages and which can lead to denial of service.
+ * Additional roles can be permitted with GRANT.
+ */
+Datum
+pg_log_query_plan(PG_FUNCTION_ARGS)
+{
+	int			pid = PG_GETARG_INT32(0);
+	PGPROC	   *proc;
+	PgBackendStatus *be_status;
+
+	proc = BackendPidGetProc(pid);
+
+	if (proc == NULL)
+	{
+		/*
+		 * This is just a warning so a loop-through-resultset will not abort
+		 * if one backend terminated on its own during the run.
+		 */
+		ereport(WARNING,
+				(errmsg("PID %d is not a PostgreSQL backend process", pid)));
+		PG_RETURN_BOOL(false);
+	}
+
+	be_status = pgstat_get_beentry_by_proc_number(proc->vxid.procNumber);
+	if (be_status->st_backendType != B_BACKEND)
+	{
+		ereport(WARNING,
+				(errmsg("PID %d is not a PostgreSQL client backend process", pid)));
+		PG_RETURN_BOOL(false);
+	}
+
+	if (SendProcSignal(pid, PROCSIG_LOG_QUERY_PLAN, proc->vxid.procNumber) < 0)
+	{
+		ereport(WARNING,
+				(errmsg("could not send signal to process %d: %m", pid)));
+		PG_RETURN_BOOL(false);
+	}
+
+	PG_RETURN_BOOL(true);
+}
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 7e2792ead7..a68958c1ad 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -1084,6 +1084,37 @@ ExplainQueryParameters(ExplainState *es, ParamListInfo params, int maxlen)
 		ExplainPropertyText("Query Parameters", str, es);
 }
 
+/*
@@ -1815,7 +1846,10 @@ ExplainNode(PlanState *planstate, List *ancestors,
 
 	/*
+	 * has been requested by signal, as the target query may use instrumentation
@@ -1823,7 +1857,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 
 	if (es->analyze &&
index 0391798dd2..99727e5571 100644
@@ -313,6 +314,7 @@ standard_ExecutorRun(QueryDesc *queryDesc,
 
 	/* sanity checks */
 	Assert(queryDesc != NULL);
@@ -325,6 +327,13 @@ standard_ExecutorRun(QueryDesc *queryDesc,
 	/* caller must ensure the query's snapshot is active */
 	Assert(GetActiveSnapshot() == estate->es_snapshot);
 
+	/*
@@ -387,6 +396,14 @@ standard_ExecutorRun(QueryDesc *queryDesc,
 
 	MemoryContextSwitchTo(oldcontext);
+	SetActiveQueryDesc(oldActiveQueryDesc);
+
+	/*
+	 * Ensure LogQueryPlanPending is initialized in case there was no time for
+	 * logging the plan. Othewise plan will be logged at the next query
+	 * execution on the same session.
+	 */
+	LogQueryPlanPending = false;
 }
 
index f5f9cfbeea..e7749d15af 100644
 
+#include "commands/dynamic_explain.h"
 #include "executor/executor.h"
 #include "executor/nodeAgg.h"
 #include "executor/nodeAppend.h"
@@ -431,9 +432,10 @@ ExecSetExecProcNode(PlanState *node, ExecProcNodeMtd function)
 {
 	/*
 	 * Add a wrapper around the ExecProcNode callback that checks stack depth
-	 * during the first execution and maybe adds an instrumentation wrapper.
-	 * When the callback is changed after execution has already begun that
-	 * means we'll superfluously execute ExecProcNodeFirst, but that seems ok.
+	 * and query logging request during the execution and maybe adds an
+	 * instrumentation wrapper. When the callback is changed after execution
+	 * has already begun that means we'll superfluously execute
+	 * ExecProcNodeFirst, but that seems ok.
 	 */
 	node->ExecProcNodeReal = function;
 	node->ExecProcNode = ExecProcNodeFirst;
@@ -441,27 +443,43 @@ ExecSetExecProcNode(PlanState *node, ExecProcNodeMtd function)

 /*
- * ExecProcNode wrapper that performs some one-time checks, before calling
+ * ExecProcNode wrapper that performs some extra checks, before calling
  * the relevant node method (possibly via an instrumentation wrapper).
+ *
+ * Normally, this is just invoked once for the first call to any given node,
+ * and thereafter we arrange to call ExecProcNodeInstr or the relevant node
+ * method directly. However, it's legal to reset node->ExecProcNode back to
+ * this function at any time, and we do that whenever the query plan might
+ * need to be printed, so that we only incur the cost of checking for that
+ * case when required.
  */
 static TupleTableSlot *
 ExecProcNodeFirst(PlanState *node)
 {
 	/*
-	 * Perform stack depth check during the first execution of the node.  We
-	 * only do so the first time round because it turns out to not be cheap on
-	 * some common architectures (eg. x86).  This relies on the assumption
-	 * that ExecProcNode calls for a given plan node will always be made at
-	 * roughly the same stack depth.
+	 * Perform a stack depth check.  We don't want to do this all the time
+	 * because it turns out to not be cheap on some common architectures (eg.
+	 * x86).  This relies on the assumption that ExecProcNode calls for a
+	 * given plan node will always be made at roughly the same stack depth.
 	 */
 	check_stack_depth();
 
+	/*
+	 * If we have been asked to print the query plan, do that now. We dare not
+	 * try to do this directly from CHECK_FOR_INTERRUPTS() because we don't
+	 * really know what the executor state is at that point, but we assume
+	 * that when entering a node the state will be sufficiently consistent
+	 * that trying to print the plan makes sense.
+	 */
+	if (LogQueryPlanPending)
+		LogQueryPlan();
+
 	/*
 	 * If instrumentation is required, change the wrapper to one that just
 	 * does instrumentation.  Otherwise we can dispense with all wrappers and
 	 * have ExecProcNode() directly call the relevant function from now on.
 	 */
-	if (node->instrument)
+	else if (node->instrument)
 		node->ExecProcNode = ExecProcNodeInstr;
 	else
 		node->ExecProcNode = node->ExecProcNodeReal;
@@ -546,6 +564,88 @@ MultiExecProcNode(PlanState *node)
 	return result;
 }
 
+/*
+ * Wrap array of PlanState ExecProcNode with ExecProcNodeFirst.
+ */
+static void
+ExecSetExecProcNodeArray(PlanState **planstates, int nplans)
+{
+	int			i;
+
+	for (i = 0; i < nplans; i++)
+		ExecSetExecProcNodeRecurse(planstates[i]);
+}
+
+/*
+ * Wrap CustomScanState children's ExecProcNode with ExecProcNodeFirst.
+ */
+static void
+CSSChildExecSetExecProcNodeArray(CustomScanState *css)
+{
+	ListCell   *cell;
+
+	foreach(cell, css->custom_ps)
+		ExecSetExecProcNodeRecurse((PlanState *) lfirst(cell));
+}
+
+/*
+ * Recursively wrap all the underlying ExecProcNode with ExecProcNodeFirst.
+ *
+ * Recursion is usually necessary because the next ExecProcNode() call may be
+ * invoked not only through the current node, but also via lefttree, righttree,
+ * subPlan, or other special child plans.
+ */
+void
+ExecSetExecProcNodeRecurse(PlanState *ps)
+{
+	ExecSetExecProcNode(ps, ps->ExecProcNodeReal);
+
+	if (ps->lefttree != NULL)
+		ExecSetExecProcNodeRecurse(ps->lefttree);
+	if (ps->righttree != NULL)
+		ExecSetExecProcNodeRecurse(ps->righttree);
+	if (ps->subPlan != NULL)
+	{
+		ListCell   *l;
+
+		foreach(l, ps->subPlan)
+		{
+			SubPlanState *sstate = (SubPlanState *) lfirst(l);
+
+			ExecSetExecProcNodeRecurse(sstate->planstate);
+		}
+	}
+
+	/* special child plans */
+	switch (nodeTag(ps->plan))
+	{
+		case T_Append:
+			ExecSetExecProcNodeArray(((AppendState *) ps)->appendplans,
+									 ((AppendState *) ps)->as_nplans);
+			break;
+		case T_MergeAppend:
+			ExecSetExecProcNodeArray(((MergeAppendState *) ps)->mergeplans,
+									 ((MergeAppendState *) ps)->ms_nplans);
+			break;
+		case T_BitmapAnd:
+			ExecSetExecProcNodeArray(((BitmapAndState *) ps)->bitmapplans,
+									 ((BitmapAndState *) ps)->nplans);
+			break;
+		case T_BitmapOr:
+			ExecSetExecProcNodeArray(((BitmapOrState *) ps)->bitmapplans,
+									 ((BitmapOrState *) ps)->nplans);
+			break;
+		case T_SubqueryScan:
+			ExecSetExecProcNodeRecurse(((SubqueryScanState *) ps)->subplan);
+			break;
+		case T_CustomScan:
+			CSSChildExecSetExecProcNodeArray((CustomScanState *) ps);
+			break;
+		default:
+			break;
+	}
+}
+
 
  *		ExecEndNode
index a9bb540b55..2226ad0216 100644
 
@@ -691,6 +692,9 @@ procsignal_sigusr1_handler(SIGNAL_ARGS)

index 2f8c3d5f91..6bdc7ba77a 100644
@@ -3533,6 +3534,9 @@ ProcessInterrupts(void)
 
index d31cb45a05..927ccda030 100644
@@ -40,6 +40,8 @@ volatile sig_atomic_t IdleSessionTimeoutPending = false;
index d3d28a263f..e3bf1cb9cd 100644
@@ -8571,6 +8571,12 @@
 
index 0000000000..be387a8cf9
@@ -0,0 +1,29 @@
+extern void ResetProcessLogQueryPlanInterruptActive(void);
+extern void LogQueryPlan(void);
index 3b122f79ed..6aa838764e 100644
@@ -70,6 +70,8 @@ extern void ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into,
 
@@ -80,5 +82,8 @@ extern void ExplainPrintJITSummary(struct ExplainState *es,
 
 #endif							/* EXPLAIN_H */
index 104b059544..05a023940a 100644
@@ -292,6 +292,7 @@ extern void EvalPlanQualEnd(EPQState *epqstate);
+extern void ExecSetExecProcNodeRecurse(PlanState *ps);
 extern void ExecSetTupleBound(int64 tuples_needed, PlanState *child_node);
index 1bef98471c..b26d7b919c 100644
@@ -96,7 +96,7 @@ extern PGDLLIMPORT volatile sig_atomic_t IdleSessionTimeoutPending;
 
index afeeb1ca01..ea26242c86 100644

 
-
 
index c3b2b9d860..1ac584fa7f 100644

 
 
@@ -363,8 +364,44 @@ SELECT pg_log_backend_memory_contexts(pg_backend_pid());
 
 RESET ROLE;
 REVOKE EXECUTE ON FUNCTION pg_log_backend_memory_contexts(integer)
-  FROM regress_log_memory;
-DROP ROLE regress_log_memory;
+  FROM regress_log_function;
+-- pg_log_query_plan()
+-- Note that we're using a slightly more complex query here because
+-- a simple 'SELECT pg_log_query_plan(pg_backend_pid())' would finish
+-- before it reaches the code path that actually outputs the plan.
+SELECT result FROM (SELECT pg_log_query_plan(pg_backend_pid())) result;
+ result 
+--------
+ (t)
+(1 row)
+
+SELECT has_function_privilege('regress_log_function',
+  'pg_log_query_plan(integer)', 'EXECUTE'); -- no
+ has_function_privilege 
+------------------------
+ f
+(1 row)
+
+GRANT EXECUTE ON FUNCTION pg_log_query_plan(integer)
+  TO regress_log_function;
+SELECT has_function_privilege('regress_log_function',
+  'pg_log_query_plan(integer)', 'EXECUTE'); -- yes
+ has_function_privilege 
+------------------------
+ t
+(1 row)
+
+SET ROLE regress_log_function;
+SELECT pg_log_query_plan(pg_backend_pid());
+ pg_log_query_plan 
+-------------------
+ t
+(1 row)
+
+RESET ROLE;
+REVOKE EXECUTE ON FUNCTION pg_log_query_plan(integer)
+  FROM regress_log_function;
+DROP ROLE regress_log_function;
 --
 -- Test some built-in SRFs
 --
diff --git a/src/test/regress/sql/misc_functions.sql b/src/test/regress/sql/misc_functions.sql
index 23792c4132..d8c141024e 100644
--- a/src/test/regress/sql/misc_functions.sql
+++ b/src/test/regress/sql/misc_functions.sql
@@ -109,39 +109,64 @@ SELECT test_canonicalize_path('./abc/./def/.');
 SELECT test_canonicalize_path('./abc/././def/.');
 SELECT test_canonicalize_path('./abc/./def/.././ghi/../../../jkl/mno');

 

 

 
 REVOKE EXECUTE ON FUNCTION pg_log_backend_memory_contexts(integer)
-  FROM regress_log_memory;
+  FROM regress_log_function;
+
+-- pg_log_query_plan()
+-- Note that we're using a slightly more complex query here because
+-- a simple 'SELECT pg_log_query_plan(pg_backend_pid())' would finish
+-- before it reaches the code path that actually outputs the plan.
+
+SELECT result FROM (SELECT pg_log_query_plan(pg_backend_pid())) result;
+
+SELECT has_function_privilege('regress_log_function',
+  'pg_log_query_plan(integer)', 'EXECUTE'); -- no
+
+GRANT EXECUTE ON FUNCTION pg_log_query_plan(integer)
+  TO regress_log_function;
+
+SELECT has_function_privilege('regress_log_function',
+  'pg_log_query_plan(integer)', 'EXECUTE'); -- yes
+
+SET ROLE regress_log_function;
+SELECT pg_log_query_plan(pg_backend_pid());
+RESET ROLE;
+
+REVOKE EXECUTE ON FUNCTION pg_log_query_plan(integer)
+  FROM regress_log_function;

base-commit: fc32be3c941f9d98dd9f549153a5fcea6c3e9b8b
-- 
2.48.1

---

From: torikoshia <[email-masked]>
Date: Mon, 01 Sep 2025 22:35:11 +0900

>> Agreed. I’ll go ahead and implement a new patch based on this 

Rebased just because of doc refactoring.

-- 
Regards,

--
Atsushi Torikoshi
Seconded from NTT DATA Japan Corporation to SRA OSS K.K.

From 630c09643ee2787e96b4809878b432ff5390005d Mon Sep 17 00:00:00 2001
From: Atsushi Torikoshi <[email-masked]>
Date: Mon, 1 Sep 2025 22:18:40 +0900
Subject: [PATCH v46] Add function to log the plan of the currently running
 query

 doc/src/sgml/func/func-admin.sgml            |  24 +++
 23 files changed, 540 insertions(+), 54 deletions(-)

diff --git a/contrib/auto_explain/auto_explain.c b/contrib/auto_explain/auto_explain.c
index 1f4badb4928..6c4217c9d1f 100644
--- a/contrib/auto_explain/auto_explain.c
+++ b/contrib/auto_explain/auto_explain.c
@@ -15,6 +15,7 @@
 #include <limits.h>

 
 			/*
 			 * Note: we rely on the existing logging of context or
diff --git a/doc/src/sgml/func/func-admin.sgml b/doc/src/sgml/func/func-admin.sgml
index 57ff333159f..cda6e0d7fac 100644
--- a/doc/src/sgml/func/func-admin.sgml
+++ b/doc/src/sgml/func/func-admin.sgml
@@ -184,6 +184,30 @@
        </para></entry>
       </row>
 
+      <row>
index b46e7e9c2a6..773ef89f1ed 100644
@@ -2916,6 +2917,12 @@ AbortTransaction(void)
 
+	/*
@@ -5308,6 +5315,12 @@ AbortSubTransaction(void)
 
+	/*
index 566f308e443..ec2e1c1fd9a 100644

index cb2fbdc7c60..5e83907eb17 100644
index 00000000000..cfc13ce0292
index 8345bc0264b..ded9c7aa856 100644
 
+/*
 
 	/*
 
 	if (es->analyze &&
index dd4cde41d32..4a64b8e9d09 100644
index b8b9d2a85f7..98e81a87945 100644
@@ -312,6 +313,7 @@ standard_ExecutorRun(QueryDesc *queryDesc,
 
 	/* sanity checks */
 	Assert(queryDesc != NULL);
@@ -324,6 +326,13 @@ standard_ExecutorRun(QueryDesc *queryDesc,
 	/* caller must ensure the query's snapshot is active */
 	Assert(GetActiveSnapshot() == estate->es_snapshot);
 
+	/*
@@ -386,6 +395,14 @@ standard_ExecutorRun(QueryDesc *queryDesc,

index f5f9cfbeead..e7749d15af8 100644

 /*
 
+	/*
 
+/*
 
index 087821311cc..07fcac7f0bc 100644

 
index 0cecd464902..f955b075ada 100644
@@ -3534,6 +3535,9 @@ ProcessInterrupts(void)
 
index d31cb45a058..927ccda0307 100644
index 118d6da1ace..c305ccd609a 100644
@@ -8597,6 +8597,12 @@
 
index 00000000000..be387a8cf95
index 3b122f79ed8..6aa838764ee 100644

 #endif							/* EXPLAIN_H */
index 32728f5d1a1..fcef9563960 100644
index 10dcea037c3..60b1abc4869 100644
@@ -294,6 +294,7 @@ extern void EvalPlanQualEnd(EPQState *epqstate);
index 1bef98471c3..b26d7b919cb 100644
 
index afeeb1ca019..ea26242c868 100644
 
index fa3cc5f2dfc..7949abf76be 100644

-
 
index c3b2b9d8603..1ac584fa7f9 100644

 

 RESET ROLE;
index 23792c4132a..d8c141024e1 100644

---

From: Robert Haas <[email-masked]>
Date: Tue, 9 Sep 2025 13:59:46 -0400

> Rebased just because of doc refactoring.

I haven't had time to review this in a while -- sorry about that --
and I have only limited time now, but let me try to give you some
comments in the time that I do have.

I bet some users would really like a feature that allows them to get
the plans for their own running queries. They will be sad with the
limitation of this feature, that it only writes to the logs. We should
probably just live with that limitation, because doing anything else
seems a lot more complicated. Still, it's not great.

+ ereport(LOG_SERVER_ONLY,
+ errmsg("backend with PID %d is finishing query execution and cannot
log the plan.",
+    MyProcPid),
+ errhidestmt(true),
+ errhidecontext(true));

+ ereport(LOG_SERVER_ONLY,
+ errmsg("backend with PID %d is not running a query or a
subtransaction is aborted",
+    MyProcPid),
+ errhidestmt(true),
+ errhidecontext(true));

The first of these messages ends with a period, which is contrary to
style guidelines. All of them do errhidestmt() and errhidecontext(),
which is an unusual choice. I don't see why it's appropriate here.
Wanting to see both the query and the query plan seems pretty
reasonable to me. The first two messages seem fairly unhelpful to me:
the user isn't going to understand the distinction between those two
states and it's unclear why we should give them that information. I'm
not sure if we should log a generic message in these kinds of cases or
log nothing at all, but I feel like this is too much technical
information. Also, I think that we don't normally put the PID of the
process that is performing an action in the primary error message,
because the user can include %p in log_line_prefix if they so desire.

But there's another, subtler point here, which is that I feel like "is
not running a query or a subtransaction is aborted" is pointing to a
design defect in the patch. When we enter an inner query, we switch
activeQueryDesc to point to the inner QueryDesc. When we abort a
subtransaction, instead of resetting it to the outer query's
QueryDesc, we reset it to NULL. I don't really think that's
acceptable. Consider this:

SELECT some_slow_function_that_uses_a_subtransaction_which_aborts(g)
FROM generate_series(1,1000000) g;

What's going to happen here is that for 99.9999% of the execution time
of this function, you can't print the query plan. And that won't be
because of any fundamental thing is preventing you from so doing -- it
will just be because the patch doesn't have the right bookkeeping. If
you added a QueryDesc * pointer to TransactionStateData, then
ExecutorRun could (1) save the current value of that pointer from the
topmost element of the transaction state stack, (2) update the pointer
value to the new QueryDesc, and (3) put the old pointer back at the
end. Then, you wouldn't need any cleanup in AbortSubTransaction, and
this value would always be right, even after an outer query continues
after an abort, because the subtransaction abort would pop the
transaction state stack, leaving the right thing on top.

The changes to miscadmin.h delete a blank line. They probably shouldn't.

The only change to pquery.h is to delete a blank line. That hunk needs
reverting.

I seriously doubt that this will be stable across the entire
buildfarm. You're going to need a different approach here.

Is there any real reason to rename regress_log_memory to
regress_log_function, vs. just introducing a separate regress_log_plan
role?

---

From: torikoshia <[email-masked]>
Date: Tue, 16 Sep 2025 22:30:31 +0900

> comments in the time that I do have.

Thank you very much for taking the time to review this!

> seems a lot more complicated. Still, it's not great.

I agree.
Regarding output in a form other than the logs, I think the idea 
proposed in [1] — making it possible to inspect memory contexts of other 
backends via a view — would be a useful reference.

[1] 
https://www.postgresql.org/message-id/flat/[email-masked]

> style guidelines.

Removed the period.

> All of them do errhidestmt() and errhidecontext(),
> reasonable to me.

Removed errhidestmt() and errhidecontext().

> The first two messages seem fairly unhelpful to me:

I'm not sure if this is the best approach, but I changed them to log 
nothing in these cases.
In addition to the reason you mentioned, I found that when 
pg_log_query_plan() is executed repeatedly at very short intervals 
(e.g., every 0.01 seconds), ereport() could lead to 
ProcessLogQueryPlanInterrupt() being invoked again inside errfinish via 
CFI(), which resulted in a stack overflow.
While this could be processed with check_stack_depth(), it doesn’t seem 
worthwhile to use it just to emit a message of limited usefulness.
Additionally, there are other cases where the plan cannot be logged 
(e.g., when the query is in a state which ExecProcNode will not be 
invoked any further).
So instead, I added the following note to the documentation:

   Note that depending on the execution state of the query,
   it may not be possible to log the plan.

> Also, I think that we don't normally put the PID of the

That makes sense, but
I had used the following message from pg_log_backend_memory_contexts() 
as a reference
and put the PID in the message:

   errmsg("logging memory contexts of PID %d", MyProcPid)));

Since both pg_log_query_plan() and pg_log_backend_memory_contexts() 
output information about another other backend, it feels slightly odd 
that their log messages would be inconsistent.
Perhaps should we consider changing pg_log_backend_memory_contexts() for 
consistency as well?

> end.

Thank you for pointing out the issue and suggesting a fix.
Attached patch added a QueryDesc pointer to TransactionStateData and 
updated it accordingly.
With this change, while running the query below, pg_log_query_plan() is 
now able to output the plan for SELECT gen_subtxn(g) FROM 
generate_series(1,1000000) g as below:

   (session1)=# CREATE OR REPLACE FUNCTION gen_subtxn(i int)
   RETURNS int LANGUAGE plpgsql AS $$
   DECLARE
       result int;
   BEGIN
       BEGIN
           PERFORM 1 / 0;
       EXCEPTION
           WHEN division_by_zero THEN
               result := -i;
       END;
       RETURN result;
   END;
   $$;

   =# SELECT gen_subtxn(g) from generate_series(1,1000000) g;

   (session2)=# SELECT pg_log_query_plan(pid) FROM pg_stat_activity WHERE 
backend_type = 'client backend';
   (session2)=# \watch 0.1

   (in the log)
    LOG:  00000: query and its plan running on the backend are:
    Query Text: SELECT gen_subtxn(g) from generate_series(1,1000000) g;
    Function Scan on pg_catalog.generate_series g  (cost=0.00..260000.00 
rows=1000000 width=4)
      Output: gen_subtxn(g)
      Function Call: generate_series(1, 1000000)

> Then, you wouldn't need any cleanup in AbortSubTransaction, and

It seems that simply making the above change is not sufficient.
In particular, when raising an error inside a subtransaction, I 
sometimes observed segfaults:

   BEGIN;
   savepoint x;
   CREATE TABLE koju (a INT UNIQUE);
   INSERT INTO koju VALUES (1);
   INSERT INTO koju VALUES (1);  -- key duplicate error

Inspecting the crash showed that the argument *ps to 
ExecSetExecProcNodeRecurse() was 0x7f7f7f....

   (lldb) f 0
   frame #0: 0x000000010102ec40 
postgres`ExecSetExecProcNodeRecurse(ps=0x7f7f7f7f7f7f7f7f) at 
execProcnode.c:601:30
      600  {
   -> 601          ExecSetExecProcNode(ps, ps->ExecProcNodeReal);

This happens when the plan-logging function is called after an ERROR is 
raised but before the transaction state stack is popped.
So in the attached patch, as in the previous version, I reset QueryDesc 
to NULL during that interval.
I have also confirmed that even with this change, the above test (SELECT 
gen_subtxn(g) from generate_series(1,1000000) g;) can still have its 
plan logged successfully.

> The changes to miscadmin.h delete a blank line. They probably 
Fixed.

> The only change to pquery.h is to delete a blank line. That hunk needs
Fixed.

> buildfarm. You're going to need a different approach here.

Changed it to the following query:

   WITH t AS MATERIALIZED (SELECT pg_log_query_plan(pg_backend_pid()))
     SELECT * FROM t;

Also since when the target query is using CTE, pg_log_query_plan() had 
few chance to log the plan, attached patch added T_CteScan handling.

> regress_log_function, vs. just introducing a separate regress_log_plan

I don’t have any particular reason to insist on unifying them.
In the attached patch, I created a regress_log_plan role and used that 
to run the same test.

-- 
Regards,

From 40acd5bf9ecbb386ba984a74552ac7393e6cc75a Mon Sep 17 00:00:00 2001
From: Atsushi Torikoshi <[email-masked]>
Date: Tue, 16 Sep 2025 22:07:13 +0900
Subject: [PATCH v47] Add function to log the plan of the currently running 
 query

On receipt of the request, ExecProcNodes of the current plan
node and its subsidiary nodes are wrapped with
ExecProcNodeFirst, which implelements logging query plan.
When executor executes the one of the wrapped nodes, the
query plan is logged.

By default, only superusers are allowed to request to log the
plans because allowing any users to issue this request at an
unbounded rate would cause lots of log messages and which can
lead to denial of service.
---
 contrib/auto_explain/auto_explain.c          |  24 +--
 doc/src/sgml/func/func-admin.sgml            |  28 +++
 src/backend/access/transam/xact.c            |  30 ++++
 src/backend/catalog/system_functions.sql     |   2 +
 src/backend/commands/Makefile                |   1 +
 src/backend/commands/dynamic_explain.c       | 173 +++++++++++++++++++
 src/backend/commands/explain.c               |  38 +++-
 src/backend/commands/meson.build             |   1 +
 src/backend/executor/execMain.c              |  17 ++
 src/backend/executor/execProcnode.c          | 123 +++++++++++--
 src/backend/storage/ipc/procsignal.c         |   4 +
 src/backend/tcop/postgres.c                  |   4 +
 src/backend/utils/init/globals.c             |   2 +
 src/include/access/xact.h                    |   3 +
 src/include/catalog/pg_proc.dat              |   6 +
 src/include/commands/dynamic_explain.h       |  27 +++
 src/include/commands/explain.h               |   9 +-
 src/include/commands/explain_state.h         |   1 +
 src/include/executor/executor.h              |   1 +
 src/include/miscadmin.h                      |   1 +
 src/include/storage/procsignal.h             |   2 +
 src/test/regress/expected/misc_functions.out |  43 +++++
 src/test/regress/sql/misc_functions.sql      |  32 ++++
 23 files changed, 538 insertions(+), 34 deletions(-)
 create mode 100644 src/backend/commands/dynamic_explain.c
 create mode 100644 src/include/commands/dynamic_explain.h

 
 
 			/*
index 57ff333159f..1dc19bbe3b4 100644
@@ -184,6 +184,34 @@
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_log_query_plan</primary>
+        </indexterm>
+        <function>pg_log_query_plan</function> ( <parameter>pid</parameter> <type>integer</type> )
+        <returnvalue>boolean</returnvalue>
+       </para>
+       <para>
+        Requests to log the plan of the query currently running on the
+        backend with specified process ID.
+        It will be logged at <literal>LOG</literal> message level and
+        will appear in the server log based on the log
+        configuration set (See <xref linkend="runtime-config-logging"/>
+        for more information), but will not be sent to the client
+        regardless of <xref linkend="guc-client-min-messages"/>.
+       </para>
+       <para>
+        Note that depending on the execution state of the query,
+        it may not be possible to log the plan.
+        </para>
+       <para>
+        This function is restricted to superusers by default, but other
+        users can be granted EXECUTE to run the function.
+       </para></entry>
+      </row>
+
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
diff --git a/src/backend/access/transam/xact.c b/src/backend/access/transam/xact.c
index b46e7e9c2a6..8c2e7d5061f 100644
--- a/src/backend/access/transam/xact.c
+++ b/src/backend/access/transam/xact.c
@@ -36,6 +36,7 @@
 #include "catalog/pg_enum.h"
 #include "catalog/storage.h"
 #include "commands/async.h"
+#include "commands/dynamic_explain.h"
 #include "commands/tablecmds.h"
 #include "commands/trigger.h"
 #include "common/pg_prng.h"
@@ -215,6 +216,7 @@ typedef struct TransactionStateData
 	bool		parallelChildXact;	/* is any parent transaction parallel? */
 	bool		chain;			/* start a new block after this one */
 	bool		topXidLogged;	/* for a subxact: is top-level XID logged? */
+	QueryDesc  *queryDesc;		/* my current QueryDesc */
 	struct TransactionStateData *parent;	/* back link to parent */
 } TransactionStateData;
 
@@ -248,6 +250,7 @@ static TransactionStateData TopTransactionStateData = {
 	.state = TRANS_DEFAULT,
 	.blockState = TBLOCK_DEFAULT,
 	.topXidLogged = false,
+	.queryDesc = NULL,
 };
 
 /*
@@ -933,6 +936,27 @@ GetCurrentTransactionNestLevel(void)
 	return s->nestingLevel;
 }
 
+/*
+ * SetCurrentQueryDesc
+ */
+void
+SetCurrentQueryDesc(QueryDesc *queryDesc)
+{
+	TransactionState s = CurrentTransactionState;
+
+	s->queryDesc = queryDesc;
+}
+
+/*
+ * GetCurrentQueryDesc
+ */
+QueryDesc *
+GetCurrentQueryDesc(void)
+{
+	TransactionState s = CurrentTransactionState;
+
+	return s->queryDesc;
+}
 
 /*
  *	TransactionIdIsCurrentTransactionId
@@ -2916,6 +2940,9 @@ AbortTransaction(void)
 	/* Reset snapshot export state. */
 	SnapBuildResetExportedSnapshotState();
 
+	/* Reset state for logging current query plan. */
+	ResetLogQueryPlanState();
+
 	/*
 	 * If this xact has started any unfinished parallel operation, clean up
 	 * its workers and exit parallel mode.  Don't warn about leaked resources.
@@ -5308,6 +5335,9 @@ AbortSubTransaction(void)
 	/* Reset logical streaming state. */
 	ResetLogicalStreamingState();
 
+	/* Reset state for logging current query plan. */

index 00000000000..a60cc9bea2e
@@ -0,0 +1,173 @@
+#include "access/xact.h"
+	 * After abort, some elements of current QueryDesc are freed. To avoid
+	 * accessing them, reset it.
+	SetCurrentQueryDesc(NULL);
+	QueryDesc  *queryDesc;
+	 * Current QueryDesc is valid only during standard_ExecutorRun. However,
+	 * handle the case, check whether we have QueryDesc now.
+	queryDesc = GetCurrentQueryDesc();
+	if (queryDesc == NULL)
+	ExplainStringAssemble(es, queryDesc, es->format, 0, -1);
+			errmsg("query and its plan running on the backend are:\n%s",
+				   es->str->data));
+ * ExecProcNode with ExecProcNodeFirst, which logs query plan if requested.
+ * This way ensures that EXPLAIN-related code is executed only during
+	QueryDesc  *querydesc = GetCurrentQueryDesc();
+	if (querydesc == NULL)
+	/* Wrap ExecProcNodes with ExecProcNodeFirst  */
+	ExecSetExecProcNodeRecurse(querydesc->planstate);
index 8345bc0264b..556779bafac 100644
 
+/*
 
 	/*
+	 * has been requested by signal, as the target query may use
 
 	if (es->analyze &&
index ff12e2e1364..b6f72d78ae6 100644
+	QueryDesc  *oldQueryDesc;
 
 	/* sanity checks */
 
+	/*
+	 * Save current QueryDesc here to enable retrieval of the currently
+	 * running queryDesc for nested queries.
+	 */
+	oldQueryDesc = GetCurrentQueryDesc();
+	SetCurrentQueryDesc(queryDesc);
+
 	/*
 	 * Switch into per-query memory context
 	 */
@@ -386,6 +395,14 @@ standard_ExecutorRun(QueryDesc *queryDesc,
 		InstrStopNode(queryDesc->totaltime, estate->es_processed);
 
+	SetCurrentQueryDesc(oldQueryDesc);
 
index f5f9cfbeead..bf1e26b7af1 100644

 /*
 
+	/*
@@ -546,6 +564,91 @@ MultiExecProcNode(PlanState *node)
 	return result;
 }
 
+/*
+		case T_CteScan:
+			ExecSetExecProcNodeRecurse(((CteScanState *) ps)->cteplanstate);

 
 
index d356830f756..c5a5cd47c8f 100644
 
+	if (LogQueryPlanPending)
+		ProcessLogQueryPlanInterrupt();
+
 	if (ParallelApplyMessagePending)
 		ProcessParallelApplyMessages();
 }
diff --git a/src/backend/utils/init/globals.c b/src/backend/utils/init/globals.c
index d31cb45a058..927ccda0307 100644
--- a/src/backend/utils/init/globals.c
+++ b/src/backend/utils/init/globals.c
@@ -40,6 +40,8 @@ volatile sig_atomic_t IdleSessionTimeoutPending = false;
 volatile sig_atomic_t ProcSignalBarrierPending = false;
 volatile sig_atomic_t LogMemoryContextPending = false;
 volatile sig_atomic_t IdleStatsUpdateTimeoutPending = false;
+volatile sig_atomic_t LogQueryPlanPending = false;
+
 volatile uint32 InterruptHoldoffCount = 0;
 volatile uint32 QueryCancelHoldoffCount = 0;
 volatile uint32 CritSectionCount = 0;
diff --git a/src/include/access/xact.h b/src/include/access/xact.h
index 4528e51829e..3c6e3b7f2ce 100644
--- a/src/include/access/xact.h
+++ b/src/include/access/xact.h
@@ -432,6 +432,7 @@ typedef struct xl_xact_parsed_abort
 	TimestampTz origin_timestamp;
 } xl_xact_parsed_abort;
 
+typedef struct QueryDesc QueryDesc;
 
 /* ----------------
  *		extern definitions
@@ -458,6 +459,8 @@ extern TimestampTz GetCurrentStatementStartTimestamp(void);
 extern TimestampTz GetCurrentTransactionStopTimestamp(void);
 extern void SetCurrentStatementStartTimestamp(void);
 extern int	GetCurrentTransactionNestLevel(void);
+QueryDesc  *GetCurrentQueryDesc(void);
+void		SetCurrentQueryDesc(QueryDesc *queryDesc);
 extern bool TransactionIdIsCurrentTransactionId(TransactionId xid);
 extern void CommandCounterIncrement(void);
 extern void ForceSyncCommit(void);
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 03e82d28c87..6b5ab18bebd 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8609,6 +8609,12 @@
   prorettype => 'bool', proargtypes => 'int4',
   prosrc => 'pg_log_backend_memory_contexts' },
 
index 00000000000..83c158731a1
@@ -0,0 +1,27 @@
index 6e51d50efc7..0dc160fa7b4 100644
@@ -70,8 +70,10 @@ extern void ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into,
 
-extern void ExplainPrintPlan(ExplainState *es, QueryDesc *queryDesc);
-extern void ExplainPrintTriggers(ExplainState *es,
+extern void ExplainPrintPlan(struct ExplainState *es, QueryDesc *queryDesc);
+extern void ExplainPrintTriggers(struct ExplainState *es, QueryDesc *queryDesc);
+extern void ExplainPrintPlan(struct ExplainState *es, QueryDesc *queryDesc);
+extern void ExplainPrintTriggers(struct ExplainState *es,
 								 QueryDesc *queryDesc);
 
 extern void ExplainPrintJITSummary(ExplainState *es,
@@ -80,5 +82,8 @@ extern void ExplainPrintJITSummary(ExplainState *es,
 extern void ExplainQueryText(ExplainState *es, QueryDesc *queryDesc);
 extern void ExplainQueryParameters(ExplainState *es,
 								   ParamListInfo params, int maxlen);
+extern void ExplainStringAssemble(struct ExplainState *es, QueryDesc *queryDesc,
+								  int logFormat, bool logTriggers,
+								  int logParameterMaxLength);
 
 #endif							/* EXPLAIN_H */
index ba073b86918..bf40fdabff5 100644
index 3248e78cd28..c66df11e71a 100644
index 1bef98471c3..22b945e918c 100644

 	/* Recovery conflict reasons */
diff --git a/src/test/regress/expected/misc_functions.out b/src/test/regress/expected/misc_functions.out
index c3b2b9d8603..c2cc2609e94 100644
--- a/src/test/regress/expected/misc_functions.out
+++ b/src/test/regress/expected/misc_functions.out
@@ -366,6 +366,49 @@ REVOKE EXECUTE ON FUNCTION pg_log_backend_memory_contexts(integer)
   FROM regress_log_memory;
 DROP ROLE regress_log_memory;
 --
+-- pg_log_query_plan()
+--
+-- Plans are logged and they are not returned to the function.
+-- Furthermore, their contents can vary depending on the optimizer. However,
+-- we can at least verify that the code doesn't fail, and that the
+-- permissions are set properly.
+WITH t AS MATERIALIZED (SELECT pg_log_query_plan(pg_backend_pid()))
+    SELECT * FROM t;
+ pg_log_query_plan 
+-------------------
+ t
+(1 row)
+
+CREATE ROLE regress_log_plan;
+SELECT has_function_privilege('regress_log_plan',
+  'pg_log_query_plan(integer)', 'EXECUTE'); -- no
+ has_function_privilege 
+------------------------
+ f
+(1 row)
+
+GRANT EXECUTE ON FUNCTION pg_log_query_plan(integer)
+  TO regress_log_plan;
+SELECT has_function_privilege('regress_log_plan',
+  'pg_log_query_plan(integer)', 'EXECUTE'); -- yes
+ has_function_privilege 
+------------------------
+ t
+(1 row)
+
+SET ROLE regress_log_plan;
+WITH t AS MATERIALIZED (SELECT pg_log_query_plan(pg_backend_pid()))
+    SELECT * FROM t;
+ pg_log_query_plan 
+-------------------
+ t
+(1 row)
+
+RESET ROLE;
+REVOKE EXECUTE ON FUNCTION pg_log_query_plan(integer)
+  FROM regress_log_plan;
+DROP ROLE regress_log_plan;
+--
 -- Test some built-in SRFs
 --
 -- The outputs of these are variable, so we can't just print their results
diff --git a/src/test/regress/sql/misc_functions.sql b/src/test/regress/sql/misc_functions.sql
index 23792c4132a..88be4b6ef2a 100644
--- a/src/test/regress/sql/misc_functions.sql
+++ b/src/test/regress/sql/misc_functions.sql
@@ -143,6 +143,38 @@ REVOKE EXECUTE ON FUNCTION pg_log_backend_memory_contexts(integer)
 
 DROP ROLE regress_log_memory;
 
+--
+-- pg_log_query_plan()
+--
+-- Plans are logged and they are not returned to the function.
+-- Furthermore, their contents can vary depending on the optimizer. However,
+-- we can at least verify that the code doesn't fail, and that the
+-- permissions are set properly.
+
+WITH t AS MATERIALIZED (SELECT pg_log_query_plan(pg_backend_pid()))
+    SELECT * FROM t;
+
+CREATE ROLE regress_log_plan;
+
+SELECT has_function_privilege('regress_log_plan',
+  'pg_log_query_plan(integer)', 'EXECUTE'); -- no
+
+GRANT EXECUTE ON FUNCTION pg_log_query_plan(integer)
+  TO regress_log_plan;
+
+SELECT has_function_privilege('regress_log_plan',
+  'pg_log_query_plan(integer)', 'EXECUTE'); -- yes
+
+SET ROLE regress_log_plan;
+WITH t AS MATERIALIZED (SELECT pg_log_query_plan(pg_backend_pid()))
+    SELECT * FROM t;
+RESET ROLE;
+
+REVOKE EXECUTE ON FUNCTION pg_log_query_plan(integer)
+  FROM regress_log_plan;
+
+DROP ROLE regress_log_plan;
+
 --
 -- Test some built-in SRFs
 --
-- 
2.48.1

---

From: Robert Haas <[email-masked]>
Date: Tue, 16 Sep 2025 11:05:57 -0400

> worthwhile to use it just to emit a message of limited usefulness.

Yes, it seems as though we should set a flag to prevent reentrancy
here -- if we are already in the code path where we log the query
plan, we shouldn't accept an interrupt telling us to log the query
plan. I haven't looked at the updated patch so maybe that's not
necessary for some reason, but in general reentrancy is a concern with
features of this type.

>    it may not be possible to log the plan.

This seems to me to be too vague to be useful. I expect readers to
read this to mean "sometimes this feature may not work." But that
seems too pessimistic if the only case in which it doesn't work is
when we are the very tail end of the query and it was just about to
stop running, we probably don't need to document anything, as it will
happen rarely and will look about the same as if the query finished
very slightly earlier and we just missed it. If there are more cases
than that in which this feature won't work, we should talk about
those, and maybe fix them.

> Perhaps should we consider changing pg_log_backend_memory_contexts() for

Yeah, maybe. I don't know what the reason behind the unusual framing
of that message is, so perhaps there is an argument for being
consistent with it, rather than changing it. However, it looks unusual
to me.

> generate_series(1,1000000) g as below:

Excellent!

> to NULL during that interval.

I think a better fix would be to dump nothing when the current
(sub)transaction is (sub)aborted. If you don't do that, then you're
hoping that you can manage to set QueryDesc to null quickly enough
after the transaction is no longer in a valid state, which does not
seem like a great way to make things robust.

>    WITH t AS MATERIALIZED (SELECT pg_log_query_plan(pg_backend_pid()))

I don't see why that wouldn't have a race condition?

> few chance to log the plan, attached patch added T_CteScan handling.

Shouldn't all node types be handled equally?

---

From: torikoshia <[email-masked]>
Date: Fri, 19 Sep 2025 21:42:16 +0900

On Wed, Sep 17, 2025 at 12:06 AM Robert Haas <[email-masked]> 
wrote:
Thanks for the comments!

> necessary for some reason, but in general reentrancy is a concern with

Agreed. In the attached patch added a flag.

> those, and maybe fix them.

As you described, I think it's a rare case to have no chance to log 
plan, removed the note.

> consistent with it, rather than changing it. However, it looks unusual

Looking back at the discussion on the development of 
pg_log_backend_memory_contexts(), it seems that I had already proposed 
outputting the PID in the log message from the very beginning [1].
Originally, I had suggested making the memory context available directly 
as a function result rather than logging it, but we gave up on that idea 
and switched to logging, so I think the PID output may be a leftover 
from that change.

  [1] 
https://www.postgresql.org/message-id/70ae4b79eb8b0dcf42161c80a00e3f22%40oss.nttdata.com

It’s possible that users who don’t include %p in log_line_prefix could 
have trouble identifying which PID’s memory context is being logged when 
pg_log_backend_memory_contexts() is called concurrently for multiple 
processes.
However, I suspect that few users run without %p in log_line_prefix.
So I think it would be fine to remove the PID from 
pg_log_backend_memory_contexts()’s log message when this patch is 
merged.

> seem like a great way to make things robust.

To detect “when the current (sub)transaction is (sub)aborted,” attached 
patch uses IsTransactionState().
With that, the logging code is not executed when the transaction state 
is anything other than TRANS_INPROGRESS, and in some cases this works as 
expected.
On the other hand, when calling pg_log_query_plan() repeatedly during 
make installcheck, I still encountered cases where segfaults occurred.

Specifically, this seems to happen when, after a (sub)abort, the next 
query starts in the same session and a CFI() occurs after 
StartTransaction() has been executed but before Executor() runs.  In 
that case, the transaction state has already been changed to 
TRANS_INPROGRESS by StartTransaction(), but the QueryDesc from the 
aborted transaction is still present, which causes the problem.

To address this, attached patch initializes QueryDesc within 
CleanupTransaction().
Since this function already resets various members of 
CurrentTransactionState, I feel it’s not so unreasonable to initialize 
QueryDesc there as well.
Does this approach make sense, or is it problematic?

> I don't see why that wouldn't have a race condition?

The idea is that (1) pg_log_query_plan() in the WITH clause wraps 
ExecProcNode, and then (2) SELECT * FROM t invokes ExecProcNode, which 
causes the plan to be logged.  I think that’s what currently happens on 
HEAD.
When you mention a "race condition", do you mean that if the timing of 
the wrapping in step (1) -- that is, when CFI() is called -- changes in 
the future, then the logging might not work?

> Shouldn't all node types be handled equally?

IIUC the node types that would need to be handled in the same way are 
those PlanState subclasses that satisfy both of the following:
- Structs in execnodes.h whose first field is a PlanState, and
- Subclasses that also contain another PlanState as a member in addition 
to the first field.

If that’s the case, it seems to me that we already have the full list.

BTW I haven’t looked into this in detail yet, but I’m a little curious 
whether the absence of T_CteScan in functions like 
planstate_tree_walker_impl() is intentional.

-- 
Regards,

From 37d9e93b0dbfd5c662827160bfe85e4e8e3fd76b Mon Sep 17 00:00:00 2001
From: Atsushi Torikoshi <[email-masked]>
Date: Fri, 19 Sep 2025 21:29:51 +0900
Subject: [PATCH v48] Add function to log the plan of the currently running
 query

Todo: Remove the PID from the log output of
pg_log_backend_memory_contexts() to match this patch.
---
 contrib/auto_explain/auto_explain.c          |  24 +--
 doc/src/sgml/func/func-admin.sgml            |  24 +++
 src/backend/access/transam/xact.c            |  29 ++-
 src/backend/catalog/system_functions.sql     |   2 +
 src/backend/commands/Makefile                |   1 +
 src/backend/commands/dynamic_explain.c       | 181 +++++++++++++++++++
 src/backend/commands/explain.c               |  38 +++-
 src/backend/commands/meson.build             |   1 +
 src/backend/executor/execMain.c              |  17 ++
 src/backend/executor/execProcnode.c          | 123 ++++++++++++-
 src/backend/storage/ipc/procsignal.c         |   4 +
 src/backend/tcop/postgres.c                  |   4 +
 src/backend/utils/init/globals.c             |   2 +
 src/include/access/xact.h                    |   3 +
 src/include/catalog/pg_proc.dat              |   6 +
 src/include/commands/dynamic_explain.h       |  25 +++
 src/include/commands/explain.h               |   9 +-
 src/include/commands/explain_state.h         |   1 +
 src/include/executor/executor.h              |   1 +
 src/include/miscadmin.h                      |   1 +
 src/include/storage/procsignal.h             |   2 +
 src/test/regress/expected/misc_functions.out |  43 +++++
 src/test/regress/sql/misc_functions.sql      |  32 ++++
 23 files changed, 537 insertions(+), 36 deletions(-)
 create mode 100644 src/backend/commands/dynamic_explain.c
 create mode 100644 src/include/commands/dynamic_explain.h

 
 
 			/*
index 1b465bc8ba7..66bfb5ab4df 100644
 
+      <row>
index b46e7e9c2a6..b9b374b9911 100644

 /*
 
+/*
+ * SetCurrentQueryDesc
+ */
+void
 
 /*
  *	TransactionIdIsCurrentTransactionId
@@ -3058,10 +3082,11 @@ CleanupTransaction(void)
 	nParallelCurrentXids = 0;
 
 	/*
-	 * done with abort processing, set current transaction state back to
-	 * default
+	 * done with abort processing, set current transaction state and QueryDesc
+	 * back to default
 	 */
 	s->state = TRANS_DEFAULT;
+	s->queryDesc = NULL;
 }
 
 /*
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 2d946d6d9e9..67beafcc82c 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -782,6 +782,8 @@ REVOKE EXECUTE ON FUNCTION pg_ls_logicalmapdir() FROM PUBLIC;

index 00000000000..d68203fe8aa
@@ -0,0 +1,181 @@
+static bool isProcessingLogQueryPlan = false;
+	QueryDesc  *querydesc;
+	/* Prevent re-entrant */
+	if (isProcessingLogQueryPlan)
+	isProcessingLogQueryPlan = true;
+	/* Cannot log query plan outside a transaction */
+	if (!IsTransactionState())
+		isProcessingLogQueryPlan = false;
+	querydesc = GetCurrentQueryDesc();
+	/* If current query has already finished, we can do nothing but exit */
+		isProcessingLogQueryPlan = false;
+	isProcessingLogQueryPlan = false;
 
+/*
 
 	/*
 
 	if (es->analyze &&
index 831c55ce787..df5b55ae64d 100644
 
 	/* sanity checks */
 
+	/*

 

 /*
 
+	/*
 
+/*

 

 
 
 /* ----------------
  *		extern definitions
index 01eba3b5a19..6cbfa0af91f 100644
 
index 00000000000..83a9e27deef
@@ -0,0 +1,25 @@

 
 #endif							/* EXPLAIN_H */

index 36164a99c83..67adc601a9f 100644

+--
+-- pg_log_query_plan()
+--

---

From: Rafael Thofehrn Castro <[email-masked]>
Date: Fri, 19 Sep 2025 14:03:57 -0300

Hi folks,

apologies for not replying earlier. Thanks torikoshia for having
reviewed the related patch I sent that includes instrumentation.
It makes total sense to focus on this one first.

Been thinking about the current strategy of having to iterate through
the execution tree to add the custom ExecProcNode to avoid logging
the plan in CHECK_FOR_INTERRUPTS(). I see you folks are still
discussing all the nuances in the recursive tree traversal function.

Taking a step back and proposing a different approach, have we thought
about logging the query plan in the regions of the code related to the
query executor, NEXT to CHECK_FOR_INTERRUPTS calls instead of IN
that function?

For example, in this part of executor/nodeHashjoin.c:

for (;;)
{
  /*
   * It's possible to iterate this loop many times before returning a
   * tuple, in some pathological cases such as needing to move much of
   * the current batch to a later batch.  So let's check for interrupts
   * each time through.
   */
  CHECK_FOR_INTERRUPTS();

We replace CHECK_FOR_INTERRUPTS() for a new function that does
query plan logging logic + CHECK_FOR_INTERRUPTS().

Would that be considered a safe operation given that we would always be
in the query execution context? The current ExecProcNode wrapper strategy
logs the query plan in ExecProcNodeFirst(). That function will then call
the real ExecProcNode, which points to one of the functions that contain
the CHECK_FOR_INTERRUPTS in the first place. I don't see much difference
in terms of logging safety between the two approaches, but maybe there
is :)

Rafael.

---

From: Atsushi Torikoshi <[email-masked]>
Date: Sat, 20 Sep 2025 12:21:39 +0900

> It makes total sense to focus on this one first.

Thank you as well for agreeing on the direction for how to proceed!

> query executor, NEXT to CHECK_FOR_INTERRUPTS calls instead of IN

As a related discussion, there was an idea of splitting
CHECK_FOR_INTERRUPTS() into two variants: one for cases where it’s
safe to perform operations like plan logging, and one where it isn’t:

https://www.postgresql.org/message-id/CAAaqYe-gMkdL%3DM4v47%3DH0F3%2B-zi2qL9zFqAv3QsizkRjFiQR0w%40mail.gmail.com

However, as I mentioned later in this thread, there were some issues,
so the current discussion has focused on the node-wrapping approach
instead.

> in terms of logging safety between the two approaches, but maybe there

IIUC, the differences between the two approaches are:

(1) Timing of plan output
Current patch: The plan is logged the first time each node’s
ExecProcNode() begins execution.
Your idea: The plan is logged at each “NEXT to CHECK_FOR_INTERRUPTS()”
point inside the node.

(2) Number of checks for whether plan output is needed
Current patch: Once after logging plan is requested
Your idea: Every time execution reaches a “NEXT to
CHECK_FOR_INTERRUPTS()” point inside the node.

At least Robert and I believe that the first execution of each node’s
ExecProcNode() represents a sufficiently consistent state for plan
output, as noted in the patch comments:

  +   /*

Investigating consistency at each of those points would not be easy,
and given the variety of node types, while not impossible, it would be
very difficult.

As for point (2), it’s not directly about safety, but the idea could
introduce performance overhead due to the repeated checks.

Regards,
Atsushi Torikoshi

---

From: Robert Haas <[email-masked]>
Date: Wed, 24 Sep 2025 12:34:47 -0400

> Agreed. In the attached patch added a flag.

This doesn't look safe. If we error out of the section where the flag
is set to true, it will remain true forever.

> pg_log_backend_memory_contexts()’s log message when this patch is

Let's leave the PID in there for now for consistency with the existing
message. We can discuss changing it at another time.

> Does this approach make sense, or is it problematic?

Hmm, I think it looks quite odd. I believe it's quite intentional that
the very last thing that CleanupTransaction does is reset s->state, so
I don't think we should be doing this after that. But also, this leads
to an odd inconsistency between CleanupTransaction and
CleanupSubTransaction. What I'm wondering is whether we should instead
reset s->queryDesc inside AbortTransaction() and
AbortSubTransaction(). Perhaps if we do that, then we don't need an
InTransactionState() test elsewhere. What do you think?

> the future, then the logging might not work?

I don't think we're guaranteed that the signal is delivered instantly,
so it seems possible to me that (1) would happen after (2).

> planstate_tree_walker_impl() is intentional.

I don't think that function needs any special handling for T_CteScan.
planstate_tree_walker_impl() and other functions need special handling
for cases where a node has children that are not in the lefttree,
righttree, initPlan list, or subPlan list; but a CteScan has no extra
Plan pointer:

typedef struct CteScan
{
        Scan            scan;
        /* ID of init SubPlan for CTE */
        int                     ctePlanId;
        /* ID of Param representing CTE output */
        int                     cteParam;
} CteScan;

---

From: torikoshia <[email-masked]>
Date: Wed, 01 Oct 2025 18:11:23 +0900

> is set to true, it will remain true forever.

Ugh.. Updated the patch to reset the flag even when an error occurs, 
using PG_FINALLY(), similar to [1].

[1] 
https://www.postgresql.org/message-id/27da56de-17c4-4af2-8032-3c58ae0c7b00%40oss.nttdata.com

Agreed.

> CleanupSubTransaction.

Make sense.

> What I'm wondering is whether we should instead

Resetting QueryDesc in AbortTransaction() and AbortSubTransaction() was 
what we did in an earlier version of the patch (before v46), so that 
doesn’t feel strange to me.

Updated the patch and confirmed that we can still log the parent’s plan 
in cases where subtransactions continuously abort, following the same 
steps as [2].

[2] 
https://www.postgresql.org/message-id/6143f00af4bfdba95a85cf866f4acb41%40oss.nttdata.com

> so it seems possible to me that (1) would happen after (2).

True, but I haven’t found a reliable way to trigger actual logging 
within a single backend query.

I was also considering using an isolation test and injection points, 
like in the attached PoC patch. The main steps are:

   In session1, set an injection point to wait during query execution.
   In session1, run a query that waits at the injection point.
   In session2, call pg_log_query_plan().
   In session2, wake up session1.

However, as you pointed out, since the signals are not guaranteed to be 
delivered instantly, I’m worried that the query could complete before 
the signal is delivered, causing test instability.

Additionally, for a similar function that logs information via signal 
delivery, pg_log_backend_memory_contexts(), the existing tests only 
verify that the function succeeds and that permission checks work as 
described in the comment below; I'm a bit concerned that this might be 
overkill.

   -- pg_log_backend_memory_contexts()
   --
   -- Memory contexts are logged and they are not returned to the 
function.
   -- Furthermore, their contents can vary depending on the timing. 
However,
   -- we can at least verify that the code doesn't fail, and that the
   -- permissions are set properly.

If we align with that test strategy, just executing SELECT 
pg_log_query_plan(pg_backend_pid()) and verifying that it succeeds would 
be enough.
However, unlike pg_log_backend_memory_contexts(), this function doesn’t 
log anything for this query. I’m not sure if that’s acceptable.

I’d appreciate any thoughts or suggestions on what kind of test coverage 
would be appropriate here.

>         /* ID of Param representing CTE output */

Thanks for looking into this! understood.

-- 
Regards,

From 3089479df3bd3c3e4d996b1e20d4ebfd67aa82c4 Mon Sep 17 00:00:00 2001
From: Atsushi Torikoshi <[email-masked]>
Date: Wed, 1 Oct 2025 17:50:28 +0900
Subject: [PATCH v49] Add function to log the plan of the currently running
 query

Todo: Consider removing the PID from the log output of
pg_log_backend_memory_contexts() and pg_log_query_plan().
For detail, see the discussion:

https://www.postgresql.org/message-id/CA%2BTgmoY81r7npTS34N_5MLA_u6ghfor5HoSaar53veXUYu1OxQ%40mail.gmail.com
---
 contrib/auto_explain/auto_explain.c          |  24 +--
 doc/src/sgml/func/func-admin.sgml            |  24 +++
 src/backend/access/transam/xact.c            |  34 ++++
 src/backend/catalog/system_functions.sql     |   2 +
 src/backend/commands/Makefile                |   1 +
 src/backend/commands/dynamic_explain.c       | 183 +++++++++++++++++++
 src/backend/commands/explain.c               |  38 +++-
 src/backend/commands/meson.build             |   1 +
 src/backend/executor/execMain.c              |  17 ++
 src/backend/executor/execProcnode.c          | 123 ++++++++++++-
 src/backend/storage/ipc/procsignal.c         |   4 +
 src/backend/tcop/postgres.c                  |   4 +
 src/backend/utils/init/globals.c             |   2 +
 src/include/access/xact.h                    |   3 +
 src/include/catalog/pg_proc.dat              |   6 +
 src/include/commands/dynamic_explain.h       |  25 +++
 src/include/commands/explain.h               |   9 +-
 src/include/commands/explain_state.h         |   1 +
 src/include/executor/executor.h              |   1 +
 src/include/miscadmin.h                      |   1 +
 src/include/storage/procsignal.h             |   2 +
 src/test/regress/expected/misc_functions.out |  42 +++++
 src/test/regress/sql/misc_functions.sql      |  31 ++++
 23 files changed, 544 insertions(+), 34 deletions(-)
 create mode 100644 src/backend/commands/dynamic_explain.c
 create mode 100644 src/include/commands/dynamic_explain.h

 
 
 			/*
 
+      <row>
index 2cf3d4e92b7..56c3c196f45 100644

 /*
 
+/*
+ * SetCurrentQueryDesc
+ */
+void
 
 /*
 
+	/* Reset current query plan state used for logging. */
+	SetCurrentQueryDesc(NULL);
+
 	/*
 	 * If this xact has started any unfinished parallel operation, clean up
 	 * its workers and exit parallel mode.  Don't warn about leaked resources.
@@ -5308,6 +5335,13 @@ AbortSubTransaction(void)
 	/* Reset logical streaming state. */
 	ResetLogicalStreamingState();
 
+	/*
+	 * Reset current query plan state used for logging. Note that even after
+	 * this reset, it's still possible to obtain the parent transaction's
+	 * query plans, since they are preserved in standard_ExecutorRun().
+	 */
+	SetCurrentQueryDesc(NULL);
+
 	/*
 	 * No need for SnapBuildResetExportedSnapshotState() here, snapshot
 	 * exports are not supported in subtransactions.
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 2d946d6d9e9..67beafcc82c 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -782,6 +782,8 @@ REVOKE EXECUTE ON FUNCTION pg_ls_logicalmapdir() FROM PUBLIC;

index 00000000000..b21a51aafe4
@@ -0,0 +1,183 @@
+/* Is plan node wrapping for query plan logging currently in progress? */
+static bool WrapNodesInProgress = false;
+			errmsg("query and its plan running on backend with PID %d are:\n%s",
+				   MyProcPid, es->str->data));
+	 * Exit immediately if wrapping plan is already in progress. This prevents
+	 * recursive calls, which could occur if logging is requested repeatedly and
+	 * rapidly, potentially leading to infinite recursion and crash.
+	if (WrapNodesInProgress)
+	WrapNodesInProgress = true;
+	PG_TRY();
+		 * Wrap ExecProcNodes with ExecProcNodeFirst, which logs query plan
+		 * when LogQueryPlanPending is true.
+	PG_FINALLY();
+		WrapNodesInProgress = false;
+	PG_END_TRY();
index 207f86f1d39..92f02260e38 100644
@@ -1085,6 +1085,37 @@ ExplainQueryParameters(ExplainState *es, ParamListInfo params, int maxlen)
 
+/*
@@ -1820,7 +1851,10 @@ ExplainNode(PlanState *planstate, List *ancestors,
 
 	/*
@@ -1828,7 +1862,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 
 	if (es->analyze &&
 
 	/* sanity checks */
 
+	/*

 

 /*
 
+	/*
 
+/*

 

 
 
 /* ----------------
  *		extern definitions

 
 
 #endif							/* EXPLAIN_H */

index 36164a99c83..53fef3f9ee3 100644
@@ -366,6 +366,48 @@ REVOKE EXECUTE ON FUNCTION pg_log_backend_memory_contexts(integer)
index 23792c4132a..693b54b8316 100644
@@ -143,6 +143,37 @@ REVOKE EXECUTE ON FUNCTION pg_log_backend_memory_contexts(integer)

+--
+-- pg_log_query_plan()
+--

diff --git a/src/backend/commands/dynamic_explain.c b/src/backend/commands/dynamic_explain.c
index b21a51aafe4..41951966838 100644
--- a/src/backend/commands/dynamic_explain.c
+++ b/src/backend/commands/dynamic_explain.c
@@ -22,6 +22,7 @@
 #include "storage/proc.h"
 #include "storage/procarray.h"
 #include "utils/backend_status.h"
+#include "utils/injection_point.h"
 
 /* Is plan node wrapping for query plan logging currently in progress? */
 static bool WrapNodesInProgress = false;
@@ -80,6 +81,8 @@ LogQueryPlan(void)
 
 	ExplainStringAssemble(es, queryDesc, es->format, 0, -1);
 
+	INJECTION_POINT("logging-query-plan", NULL);
+
 	ereport(LOG_SERVER_ONLY,
 			errmsg("query and its plan running on backend with PID %d are:\n%s",
 				   MyProcPid, es->str->data));
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index df5b55ae64d..db792e97c4b 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -59,6 +59,7 @@
 #include "tcop/utility.h"
 #include "utils/acl.h"
 #include "utils/backend_status.h"
+#include "utils/injection_point.h"
 #include "utils/lsyscache.h"
 #include "utils/partcache.h"
 #include "utils/rls.h"
@@ -333,6 +334,8 @@ standard_ExecutorRun(QueryDesc *queryDesc,
 	oldQueryDesc = GetCurrentQueryDesc();
 	SetCurrentQueryDesc(queryDesc);
 
+	INJECTION_POINT("standard-executor-run", NULL);
+
 	/*
 	 * Switch into per-query memory context
 	 */
diff --git a/src/test/isolation/expected/pg_log_query_plan.out b/src/test/isolation/expected/pg_log_query_plan.out
new file mode 100644
index 00000000000..2d126785c18
--- /dev/null
+++ b/src/test/isolation/expected/pg_log_query_plan.out
@@ -0,0 +1,36 @@
+Parsed test spec with 2 sessions
+
+starting permutation: query1 logreq2 wakeup2 detach2
+injection_points_attach
+-----------------------
+                       
+(1 row)
+
+step query1: SELECT COUNT(*) FROM foo; <waiting ...>
+step logreq2: SELECT pg_log_query_plan(pid) FROM pg_stat_activity WHERE backend_type = 'client backend';
+pg_log_query_plan
+-----------------
+t                
+t                
+t                
+(3 rows)
+
+step wakeup2: SELECT injection_points_wakeup('standard-executor-run');
+injection_points_wakeup
+-----------------------
+                       
+(1 row)
+
+s1: NOTICE:  notice triggered for injection point logging-query-plan
+step query1: <... completed>
+count
+-----
+  100
+(1 row)
+
+step detach2: SELECT injection_points_detach('standard-executor-run');
+injection_points_detach
+-----------------------
+                       
+(1 row)
+
diff --git a/src/test/isolation/specs/pg_log_query_plan.spec b/src/test/isolation/specs/pg_log_query_plan.spec
new file mode 100644
index 00000000000..11c344d9fdd
--- /dev/null
+++ b/src/test/isolation/specs/pg_log_query_plan.spec
@@ -0,0 +1,27 @@
+# pg_log_query_plan() test
+
+setup
+{
+	CREATE EXTENSION injection_points;
+	CREATE TABLE foo AS SELECT generate_series(1,100);
+}
+teardown
+{
+	DROP EXTENSION injection_points;
+	DROP TABLE foo;
+}
+
+session s1
+setup	{
+	SELECT injection_points_set_local();
+	SELECT injection_points_attach('logging-query-plan', 'notice');
+	SELECT injection_points_attach('standard-executor-run', 'wait');
+}
+step query1		{ SELECT COUNT(*) FROM foo; }
+
+session s2
+step logreq2	{ SELECT pg_log_query_plan(pid) FROM pg_stat_activity WHERE backend_type = 'client backend'; }
+step wakeup2	{ SELECT injection_points_wakeup('standard-executor-run'); }
+step detach2	{ SELECT injection_points_detach('standard-executor-run'); }
+
+permutation query1 logreq2 wakeup2 detach2

---

From: Robert Haas <[email-masked]>
Date: Thu, 16 Oct 2025 16:15:54 -0400

>    In session2, wake up session1.

The key thing here is that we need to verify that each thing we do in
each session actually takes effect before doing the next thing. When
we're running an SQL statement to completion, nothing special is
needed: we just wait for completion -- but in any other case, we need
some kind of an explicit wait step after performing the action. Also,
I don't think we need the standard-executor-run injection point you've
introduced, because we have other ways to make query execution wait
already, such as (a) pg_sleep() with a very long timeout or (b)
pg_advisory_lock() on a lock held by another process. So I imagine the
outline maybe looking something like this:

S1: Set an injection point to wait in HandleLogQueryPlanInterrupt
[runs to completion].
S2: Take an advisory lock [runs to completion].
S1: Start a query that attempts to acquire the same advisory lock.
Use $node->wait_for_event() to be sure that S1 is now waiting on the lock.
S2: Commit, thus releasing the advisory lock [runs to completion].
Use $node->wait_for_event() to be sure that S1 is now waiting inside
HandleLogQueryPlanInterrupt.
Remember the log offset, as in src/test/modules/test_misc/t/005_timeouts.pl
Detach the injection point.
Use $node->wait_for_log() to wait for the expected log message to appear.

It's really hard to make these kinds of sequences race-free, so there
could well be bugs in the above outline, but I hope it is close to the
right thing.

---

From: torikoshia <[email-masked]>
Date: Mon, 20 Oct 2025 21:15:03 +0900

On 2025-10-17 05:15, Robert Haas wrote:
> some kind of an explicit wait step after performing the action.

That makes sense.

> Also,
> Remember the log offset, as in 
> src/test/modules/test_misc/t/005_timeouts.pl

Thanks for the detailed outline!
Attached patch adds a test following the suggestion.

-- 
Regards,

From 8fc81ac64ff0c3f9b77812f15eccc9a43a27b838 Mon Sep 17 00:00:00 2001
From: Atsushi Torikoshi <[email-masked]>
Date: Mon, 20 Oct 2025 21:02:54 +0900
Subject: [PATCH v50] Add function to log the plan of the currently running

https://www.postgresql.org/message-id/CA%2BTgmoY81r7npTS34N_5MLA_u6ghfor5HoSaar53veXUYu1OxQ%40mail.gmail.com
---
 contrib/auto_explain/auto_explain.c           |  24 +--
 doc/src/sgml/func/func-admin.sgml             |  24 +++
 src/backend/access/transam/xact.c             |  34 ++++
 src/backend/catalog/system_functions.sql      |   2 +
 src/backend/commands/Makefile                 |   1 +
 src/backend/commands/dynamic_explain.c        | 187 ++++++++++++++++++
 src/backend/commands/explain.c                |  38 +++-
 src/backend/commands/meson.build              |   1 +
 src/backend/executor/execMain.c               |  17 ++
 src/backend/executor/execProcnode.c           | 123 +++++++++++-
 src/backend/storage/ipc/procsignal.c          |   4 +
 src/backend/tcop/postgres.c                   |   4 +
 src/backend/utils/init/globals.c              |   2 +
 src/include/access/xact.h                     |   3 +
 src/include/catalog/pg_proc.dat               |   6 +
 src/include/commands/dynamic_explain.h        |  25 +++
 src/include/commands/explain.h                |   9 +-
 src/include/commands/explain_state.h          |   1 +
 src/include/executor/executor.h               |   1 +
 src/include/miscadmin.h                       |   1 +
 src/include/storage/procsignal.h              |   2 +
 .../test_misc/t/009_pg_log_query_plan.pl      | 103 ++++++++++
 src/test/regress/expected/misc_functions.out  |  38 ++++
 src/test/regress/sql/misc_functions.sql       |  31 +++
 24 files changed, 647 insertions(+), 34 deletions(-)
 create mode 100644 src/backend/commands/dynamic_explain.c
 create mode 100644 src/include/commands/dynamic_explain.h
 create mode 100644 src/test/modules/test_misc/t/009_pg_log_query_plan.pl

 
 
 			/*
 
+      <row>

 /*
 
+/*
+ * SetCurrentQueryDesc
+ */
+void
 
 /*

+	/*

index 00000000000..95d4e330b2f
@@ -0,0 +1,187 @@
+#ifdef USE_INJECTION_POINTS
+	INJECTION_POINT("log-query-interrupt", NULL);
+#endif
index e6edae0845c..9f1e69d68d3 100644
 
+/*
 
 	/*
 
 	if (es->analyze &&
index 713e926329c..663545e0e5b 100644
 
 	/* sanity checks */
 
+	/*

 

 /*
 
+	/*
 
+/*

 
 
index 7dd75a490aa..98b62a9e3a0 100644
@@ -3539,6 +3540,9 @@ ProcessInterrupts(void)

 
 /* ----------------
  *		extern definitions
index b51d2b17379..8444bb075de 100644
@@ -8617,6 +8617,12 @@

 
 
 #endif							/* EXPLAIN_H */

 	/* Recovery conflict reasons */
diff --git a/src/test/modules/test_misc/t/009_pg_log_query_plan.pl b/src/test/modules/test_misc/t/009_pg_log_query_plan.pl
new file mode 100644
index 00000000000..64dc78188a1
--- /dev/null
+++ b/src/test/modules/test_misc/t/009_pg_log_query_plan.pl
@@ -0,0 +1,103 @@
+
+# Copyright (c) 2024-2025, PostgreSQL Global Development Group
+
+use strict;
+use warnings FATAL => 'all';
+use locale;
+
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+# Test that pg_log_query_plan() actually logs the query plan of
+# another backend executing a query.
+
+# This test requires timing cordinations:
+#  1) The target backend must be executing a query when
+#     pg_log_query_plan() sends the signal.
+#  2) We must confirm that the target backend actually received the
+#     signal that requests logging of the plan.
+#
+# We use an advisory lock and an injection point to control them
+# respectively.
+
+if ($ENV{enable_injection_points} ne 'yes')
+{
+	plan skip_all => 'Injection points not supported by this build';
+}
+
+# Node initialization
+my $node = PostgreSQL::Test::Cluster->new('node');
+$node->init();
+$node->start;
+
+# Check if the extension injection_points is available, as it may be
+# possible that this script is run with installcheck, where the module
+# would not be installed by default.
+if (!$node->check_extension('injection_points'))
+{
+	plan skip_all => 'Extension injection_points not installed';
+}
+
+$node->safe_psql('postgres', 'CREATE EXTENSION injection_points;');
+
+my $psql_session1 = $node->background_psql('postgres');
+my $psql_session2 = $node->background_psql('postgres');
+
+my $session1_pid = $psql_session1->query_safe("select pg_backend_pid()");
+
+# Set injection point in the logging plan request handler to ensure
+# that session1 received the signal of pg_log_query_plan().
+$psql_session1->query_safe(
+	qq[
+	SELECT injection_points_set_local();
+	SELECT injection_points_attach('log-query-interrupt', 'wait');
+]);
+
+# Use an advisory lock to make session1 blocked during query execution.
+$psql_session2->query_safe(
+	qq[
+	BEGIN;
+	SELECT pg_advisory_xact_lock(1);
+]);
+
+$psql_session1->query_until(
+	qr/wait_on_advisory_lock/, q(
+	\echo wait_on_advisory_lock
+	BEGIN;
+	SELECT pg_advisory_xact_lock(1);
+));
+
+# Confirm that session1 is actually waiting on the advisory lock.
+$node->wait_for_event('client backend', 'advisory');
+
+# Run pg_log_query_plan().
+# Then commit the session 2 to release the advisory lock.
+$psql_session2->query_safe(
+	qq[
+	SELECT pg_log_query_plan($session1_pid);
+	COMMIT;
+]);
+
+# Ensure that the signal of pg_log_query_plan() is actually
+# rececived by confirming session1 is waiting on the injection point.
+$node->wait_for_event('client backend', 'log-query-interrupt');
+
+my $log_offset = -s $node->logfile;
+
+# Detach the injection point to start logging the plan.
+$psql_session2->query_safe(
+	qq[
+    SELECT injection_points_wakeup('log-query-interrupt');
+    SELECT injection_points_detach('log-query-interrupt');
+]);
+
+$node->wait_for_log('query and its plan running on backend with PID',
+	$log_offset);
+
+$psql_session1->query_safe("COMMIT;");
+
+ok($psql_session1->quit);
+ok($psql_session2->quit);
+
+done_testing();
diff --git a/src/test/regress/expected/misc_functions.out b/src/test/regress/expected/misc_functions.out
index 36164a99c83..fc8c6fd0dc5 100644
--- a/src/test/regress/expected/misc_functions.out
+++ b/src/test/regress/expected/misc_functions.out
@@ -366,6 +366,44 @@ REVOKE EXECUTE ON FUNCTION pg_log_backend_memory_contexts(integer)
   FROM regress_log_memory;
 DROP ROLE regress_log_memory;
 --
+-- pg_log_query_plan()
+--
+-- Plans are logged and they are not returned to the function.
+-- Furthermore, their contents can vary depending on the optimizer.
+-- Here we just verifies that the permissions are set properly.
+--
+-- The test that verifies the backend's query plan is actually
+-- logged is implemented in
+-- src/test/modules/test_misc/t/009_pg_log_query_plan.pl.
+CREATE ROLE regress_log_plan;
+SELECT has_function_privilege('regress_log_plan',
+  'pg_log_query_plan(integer)', 'EXECUTE'); -- no
+ has_function_privilege 
+------------------------
+ f
+(1 row)
+
+GRANT EXECUTE ON FUNCTION pg_log_query_plan(integer)
+  TO regress_log_plan;
+SELECT has_function_privilege('regress_log_plan',
+  'pg_log_query_plan(integer)', 'EXECUTE'); -- yes
+ has_function_privilege 
+------------------------
+ t
+(1 row)
+
+SET ROLE regress_log_plan;
+SELECT pg_log_query_plan(pg_backend_pid());
+ pg_log_query_plan 
+-------------------
+ t
+(1 row)
+
+RESET ROLE;
+REVOKE EXECUTE ON FUNCTION pg_log_query_plan(integer)
+  FROM regress_log_plan;
+DROP ROLE regress_log_plan;
+--
 -- Test some built-in SRFs
 --
 -- The outputs of these are variable, so we can't just print their results
diff --git a/src/test/regress/sql/misc_functions.sql b/src/test/regress/sql/misc_functions.sql
index 23792c4132a..bd69ed550a8 100644
--- a/src/test/regress/sql/misc_functions.sql
+++ b/src/test/regress/sql/misc_functions.sql
@@ -143,6 +143,37 @@ REVOKE EXECUTE ON FUNCTION pg_log_backend_memory_contexts(integer)

+--
+-- pg_log_query_plan()
+--
+-- Plans are logged and they are not returned to the function.
+-- Furthermore, their contents can vary depending on the optimizer.
+-- Here we just verifies that the permissions are set properly.
+--
+-- The test that verifies the backend's query plan is actually
+-- logged is implemented in
+-- src/test/modules/test_misc/t/009_pg_log_query_plan.pl.
+
+CREATE ROLE regress_log_plan;
+
+SELECT has_function_privilege('regress_log_plan',
+  'pg_log_query_plan(integer)', 'EXECUTE'); -- no
+
+GRANT EXECUTE ON FUNCTION pg_log_query_plan(integer)
+  TO regress_log_plan;
+
+SELECT has_function_privilege('regress_log_plan',
+  'pg_log_query_plan(integer)', 'EXECUTE'); -- yes
+
+SET ROLE regress_log_plan;
+SELECT pg_log_query_plan(pg_backend_pid());
+RESET ROLE;
+
+REVOKE EXECUTE ON FUNCTION pg_log_query_plan(integer)
+  FROM regress_log_plan;
+
+DROP ROLE regress_log_plan;
+
 --
 -- Test some built-in SRFs
 --

base-commit: 762faf702c6f7292bd02705553078700d92c15f1
-- 
2.48.1

---

From: Akshat Jaimini <[email-masked]>
Date: Tue, 18 Nov 2025 20:19:46 +0000

Hi,
I have a question:

In src/backend/executor/execMain.c:

```
```

It would be really helpful if you could elaborate on any cases where this specific situation might arise i.e. where 'there was no time for logging the plan'. Are we referencing to something like a sudden shutdown of the postmaster process or is this referring to something else entirely?

Regards,
Akshat Jaimini

---

From: torikoshia <[email-masked]>
Date: Wed, 19 Nov 2025 12:43:22 +0900

On 2025-11-19 05:19, Akshat Jaimini wrote:

Thanks for your review!

> It would be really helpful if you could elaborate on any cases where
> this specific situation might arise i.e. where 'there was no time for
> logging the plan'. Are we referencing to something like a sudden
> shutdown of the postmaster process or is this referring to something
> else entirely?

What I have in mind are cases where a query finishes before 
LogQueryPlan() is ever invoked.
Since LogQueryPlan() is called from ExecProcNodeFirst(), this generally 
means pg_log_query_plan() was called at the moment just before query 
execution completes.
Also, very short queries fall into this category:

   =# select pg_log_query_plan(pg_backend_pid());
    pg_log_query_plan
   -------------------
    t
   (1 row)

   =# select 1;

With the current patch, nothing is logged here.
But if I comment out the "LogQueryPlanPending = false" line, the plan 
for "SELECT 1" ends up being logged:

   LOG:  00000: query and its plan running on backend with PID 33040 are:
   Query Text: select 1;
   Result  (cost=0.00..0.01 rows=1 width=4)
          Output: 1
          Settings: jit = 'off'

-- 
Regards,

---

From: Robert Haas <[email-masked]>
Date: Wed, 19 Nov 2025 12:23:01 -0500

> Attached patch adds a test following the suggestion.

Thanks. I'm not sure about this part:

This does two relevant things: one is to send a signal (the SELECT)
and the other is to release a lock (the COMMIT). Both of these events
will soon be perceived by the other session, but I am not sure whether
we have a guarantee about the order. If it's possible for the lock
release to be observed by the target session before the log-query-plan
interrupt arrives, the test will fail. If that is possible, I think we
should try to find a way to plug the gap. If it's not possible, I
think we should add a comment explaining why it can't happen.

Also, my apologies for taking some time to return to this thread.

Thanks,

---

From: torikoshia <[email-masked]>
Date: Thu, 20 Nov 2025 10:52:25 +0900

On 2025-11-20 02:23, Robert Haas wrote:
> should try to find a way to plug the gap.

I think it's possible.
Updated the test so that COMMIT happens only after we confirm that the 
backend is already waiting at the injection point.

> Also, my apologies for taking some time to return to this thread.

Not at all -- I really appreciate your continued review.

-- 
Regards,

From 019829ef95d9b145cea530c4bf07f8ea3ef61c88 Mon Sep 17 00:00:00 2001
From: Atsushi Torikoshi <[email-masked]>
Date: Thu, 20 Nov 2025 10:33:05 +0900
Subject: [PATCH v51] Add function to log the plan of the currently running

 .../test_misc/t/010_pg_log_query_plan.pl      | 101 ++++++++++
 24 files changed, 645 insertions(+), 34 deletions(-)
 create mode 100644 src/test/modules/test_misc/t/010_pg_log_query_plan.pl

 
 
 			/*
 
+      <row>
index 092e197eba3..be5a9cef8a9 100644
@@ -216,6 +217,7 @@ typedef struct TransactionStateData
 
@@ -249,6 +251,7 @@ static TransactionStateData TopTransactionStateData = {
 	.state = TRANS_DEFAULT,
 	.blockState = TBLOCK_DEFAULT,
 	.topXidLogged = false,
+	.queryDesc = NULL,
 };
 
 /*
@@ -934,6 +937,27 @@ GetCurrentTransactionNestLevel(void)
 	return s->nestingLevel;
 }
 
+/*
+ * SetCurrentQueryDesc
+ */
+void
 
 /*
  *	TransactionIdIsCurrentTransactionId
@@ -2922,6 +2946,9 @@ AbortTransaction(void)
 	/* Reset snapshot export state. */
 	SnapBuildResetExportedSnapshotState();
 
@@ -5314,6 +5341,13 @@ AbortSubTransaction(void)
 
+	/*

index f99acfd2b4b..ac8e3996ae9 100644
index 7e699f8595e..01d343f50b8 100644
 
+/*
 
 	/*
 
 	if (es->analyze &&
index 9f640ad4810..69010ad5ec9 100644
index 27c9eec697b..8ce81aace52 100644
 
 	/* sanity checks */
 
+	/*

 

 /*
 
+	/*
 
+/*

 

 
 
 /* ----------------
  *		extern definitions
index aaadfd8c748..a930a673610 100644

 
 
 #endif							/* EXPLAIN_H */
index fa2b657fb2f..bf2822d6e9b 100644
@@ -298,6 +298,7 @@ extern void EvalPlanQualEnd(EPQState *epqstate);
index 9a7d733ddef..772c01c95e1 100644

diff --git a/src/test/modules/test_misc/t/010_pg_log_query_plan.pl b/src/test/modules/test_misc/t/010_pg_log_query_plan.pl
index 00000000000..817ff3a1dcd
+++ b/src/test/modules/test_misc/t/010_pg_log_query_plan.pl
@@ -0,0 +1,101 @@
+$psql_session2->query_safe("SELECT pg_log_query_plan($session1_pid);");
+# Commit the session 2 to release the advisory lock.
+$psql_session2->query_safe("COMMIT;");
index e76e28b95ce..b53b1f784f6 100644
@@ -397,6 +397,44 @@ REVOKE EXECUTE ON FUNCTION pg_log_backend_memory_contexts(integer)
index 220472d5ad1..4b068c71220 100644
@@ -154,6 +154,37 @@ REVOKE EXECUTE ON FUNCTION pg_log_backend_memory_contexts(integer)

+--
+-- pg_log_query_plan()
+--

base-commit: aaf035790aebb4de6d85b60f8f3089c3c656b325
-- 
2.48.1

---

From: Robert Haas <[email-masked]>
Date: Thu, 20 Nov 2025 08:17:58 -0500

> backend is already waiting at the injection point.

Thanks, that looks better to me. Looking at this version, I wondered
whether this was OK:

If the COMMIT causes something to appear in the log, we're not
guaranteed as to whether that will happen before or after we record
the log offset. However, the string for which we're looking will (if I
understand correctly) only appear after the wakeup/detach from the
injection point, so I don't think there's any real problem here.

A couple of testing suggestions, if you haven't already:

1. Run the test in a loop, say 100 times, to check for random failures.

2. Insert a sleep(10) after each line of the strict in turn and run it
(perhaps a few tiimes) and check to make sure that the tests still
pass. (I don't mean put sleep(10) after every line -- I mean put a
sleep(10) in one place at a time and keep moving it after you've
verified that the current location doesn't cause a failure.)

I know from experience that it's quite hard to get these tests to be
fully reliable and I won't feel too bad if it turns out we missed
something, but at least we can try.

---

From: torikoshia <[email-masked]>
Date: Tue, 25 Nov 2025 21:43:46 +0900

> injection point, so I don't think there's any real problem here.

I have the same understanding.

> A couple of testing suggestions, if you haven't already:

Thank you for the suggestions.

> 1. Run the test in a loop, say 100 times, to check for random failures.

I ran 010_pg_log_query_plan.pl 300 times, but it never failed.

> verified that the current location doesn't cause a failure.)

I inserted sleep(10) at each line in turn and ran the test 10 times for 
each location, but fortunately (or unfortunately?) I did not observe any 
failures.

> something, but at least we can try.

BTW to test not 010_pg_log_query_plan but pg_log_query_plan(), I also 
invoked pg_log_query_plan() in 3 parallel sessions at 0.1-second 
intervals(\watch 0.1) during running `make intallcheck` 10 times, and I 
did not see any crashes.

-- 
Regards,

---

From: Lukas Fittl <[email-masked]>
Date: Mon, 8 Jun 2026 01:08:13 -0500

Hi,

On Mon, Mar 16, 2026 at 8:14 AM torikoshia <[email-masked]> wrote:
> Rebased the patch.

I was reminded about this patch at PG DATA in Chicago last week, and
figured I'd see if I can contribute a review / help move this forward
in PG20.

First of all, find attached a v54 that is rebased over changes late in
the PG19 cycle, with other minimal fixes:

- Add a missing call to explain_per_plan_hook in ExplainStringAssemble
(this hook was newly added after your last patch version)
- The TAP test number (11) was overlapping with an existing test
(011_lock_stats) that was recently added, renumbered to
014_pg_log_query_plan.pl
- Add missing reference for the TAP test in Meson builds
- Adjust auto_explain.c to not include "dynamic_explain.h" (its not
needed, ExplainStringAssemble is defined in explain.h which is already
included)
- pgindent run to fix minor whitespace issues in dynamic_explain.c

I've also read through the other thread re: progressive explain, and
it seems like this infrastructure here to log the current plan
(without ANALYZE) would be required in any case (and I think the
overall approach makes sense), and then separately we can solve the
progressive explain's parallel worker and other design issues around
execution statistics.

In terms of code review:

> index aafc53e0164..09e51b3d75a 100644
> @@ -217,6 +218,7 @@ typedef struct TransactionStateData

Somehow putting the current QueryDesc into TransactionStateData seemed
a bit odd to me, and I briefly experimented with putting this into the
active portal instead - but that doesn't work with subtransactions
that don't have their own portal, which presumably is how you landed
on this design. So I think this makes sense as-is, given those
challenges.

> @@ -2953,6 +2977,9 @@ AbortTransaction(void)
> @@ -5352,6 +5379,13 @@ AbortSubTransaction(void)

It seems better to me if we avoided overly specific code comments in
xact.c that talk about plan logging behavior, i.e. look at this more
as a general purpose "what's the current queryDesc for the current
transaction" facility.

> index 7e40b852517..96cb22daf80 100644
> ...
> ...

I'm not sure if this was discussed earlier already, but I find the
flow here a bit hard to follow in regards to how we execute the actual
node function again.

Specifically, since this uses "else if" we don't touch ExecProcNode
when LogQueryPlanPending = true, and since node->ExecProcNode still
points to ExecProcNodeFirst, the function basically calls itself. On
the second go around we assume that LogQueryPlanPending is no longer
set, so we get back to the intended ExecProcNode *if*
LogQueryPlanPending was set to false (which it is in LogQueryPlan, but
that's not easy to see from just that function).

Why not do it like this:

if (LogQueryPlanPending)
    LogQueryPlan();

/* Unmodified existing code */
if (node->instrument)
    node->ExecProcNode = ExecProcNodeInstr;
else
    node->ExecProcNode = node->ExecProcNodeReal;

(it seems like the patch suggestion that Robert had earlier did this,
but not clear why it evolved from that?)

Thanks,
Lukas

-- 
Lukas Fittl

---

From: Atsushi Torikoshi <[email-masked]>
Date: Mon, 8 Jun 2026 20:48:55 +0900

> figured I'd see if I can contribute a review / help move this forward
Thanks for your help! I really appreciate it.

> the PG19 cycle, with other minimal fixes:

It may take me a few days, but I'll take a look at the updated patch.

--
Atsushi Torikoshi

---

From: torikoshia <[email-masked]>
Date: Mon, 15 Jun 2026 22:26:32 +0900

On 2026-06-08 15:08, Lukas Fittl wrote:

Thanks for your review and update again.

> - pgindent run to fix minor whitespace issues in dynamic_explain.c

Agreed on those changes.

> as a general purpose "what's the current queryDesc for the current

Removed specific comments about plan logging.

>     node->ExecProcNode = node->ExecProcNodeReal;

Make sense. Modified it.

I also fixed some typos in the attached patch.

Thanks,

--
Atsushi Torikoshi
Seconded from NTT DATA CORPORATION to SRA OSS K.K.

From 6698abfe569bcd6ba5e7998001cd10e9d13b4baf Mon Sep 17 00:00:00 2001
From: Atsushi Torikoshi <[email-masked]>
Date: Mon, 15 Jun 2026 22:18:24 +0900
Subject: [PATCH v55] Add function to log the plan of the currently running

Todo: Consider removing the PID from the log output of
pg_log_backend_memory_contexts() and pg_log_query_plan().
For detail, see the discussion:
https://www.postgresql.org/message-id/CA%2BTgmoY81r7npTS34N_5MLA_u6ghfor5HoSaar53veXUYu1OxQ%40mail.gmail.com
---
 contrib/auto_explain/auto_explain.c           |  29 +--
 doc/src/sgml/func/func-admin.sgml             |  24 +++
 src/backend/access/transam/xact.c             |  34 ++++
 src/backend/commands/Makefile                 |   1 +
 src/backend/commands/dynamic_explain.c        | 188 ++++++++++++++++++
 src/backend/commands/explain.c                |  44 +++-
 src/backend/commands/meson.build              |   1 +
 src/backend/executor/execMain.c               |  17 ++
 src/backend/executor/execProcnode.c           | 121 ++++++++++-
 src/backend/storage/ipc/procsignal.c          |   4 +
 src/backend/tcop/postgres.c                   |   4 +
 src/backend/utils/init/globals.c              |   2 +
 src/include/access/xact.h                     |   3 +
 src/include/catalog/pg_proc.dat               |   7 +
 src/include/commands/dynamic_explain.h        |  25 +++
 src/include/commands/explain.h                |   5 +
 src/include/commands/explain_state.h          |   1 +
 src/include/executor/executor.h               |   1 +
 src/include/miscadmin.h                       |   1 +
 src/include/storage/procsignal.h              |   2 +
 src/test/modules/test_misc/meson.build        |   1 +
 .../test_misc/t/014_pg_log_query_plan.pl      | 101 ++++++++++
 src/test/regress/expected/misc_functions.out  |  38 ++++
 src/test/regress/sql/misc_functions.sql       |  31 +++
 24 files changed, 648 insertions(+), 37 deletions(-)
 create mode 100644 src/backend/commands/dynamic_explain.c
 create mode 100644 src/include/commands/dynamic_explain.h
 create mode 100644 src/test/modules/test_misc/t/014_pg_log_query_plan.pl

diff --git a/contrib/auto_explain/auto_explain.c b/contrib/auto_explain/auto_explain.c
index 97ce498d0c1..07014cab366 100644
--- a/contrib/auto_explain/auto_explain.c
+++ b/contrib/auto_explain/auto_explain.c
@@ -451,32 +451,9 @@ explain_ExecutorEnd(QueryDesc *queryDesc)
 
 			apply_extension_options(es, extension_options);
 
-			if (explain_per_plan_hook)
-				(*explain_per_plan_hook) (queryDesc->plannedstmt,
-										  NULL, es,
-										  queryDesc->sourceText,
-										  queryDesc->params,
-										  queryDesc->estate->es_queryEnv);
 
 			/*
index 0eae1c1f616..ba319c94160 100644
 
+      <row>
index 5586fbe5b07..a2d880befe6 100644
 
@@ -250,6 +252,7 @@ static TransactionStateData TopTransactionStateData = {
 	.state = TRANS_DEFAULT,
 	.blockState = TBLOCK_DEFAULT,
 	.topXidLogged = false,
+	.queryDesc = NULL,
 };
 
 /*
@@ -935,6 +938,27 @@ GetCurrentTransactionNestLevel(void)
 	return s->nestingLevel;
 }
 
+/*
+ * SetCurrentQueryDesc
+ */
+void
 
 /*
 
+	/* Reset current query plan state. */
 
+	/*
+	 * Reset current query plan state. Note that even after this reset, it's
+	 * still possible to obtain the parent transaction's query plans, since
+	 * they are preserved in standard_ExecutorRun().
+	 */
index 5b9d084977e..8f13c7f5752 100644
@@ -31,6 +31,7 @@ OBJS = \
index 00000000000..0f63e6a3a81
@@ -0,0 +1,188 @@
+ * Portions Copyright (c) 1996-2026, PostgreSQL Global Development Group
+#include "storage/procsignal.h"
+	 * recursive calls, which could occur if logging is requested repeatedly
+	 * and rapidly, potentially leading to infinite recursion and crash.
index 112c17b0d64..51648b5d510 100644
@@ -1092,6 +1092,43 @@ ExplainQueryParameters(ExplainState *es, ParamListInfo params, int maxlen)
 
+/*
+	if (explain_per_plan_hook)
+		(*explain_per_plan_hook) (queryDesc->plannedstmt,
+								  NULL, es,
+								  queryDesc->sourceText,
+								  queryDesc->params,
+								  queryDesc->estate->es_queryEnv);
@@ -1827,7 +1864,10 @@ ExplainNode(PlanState *planstate, List *ancestors,
 
 	/*
@@ -1835,7 +1875,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 
 	if (es->analyze &&
diff --git a/src/backend/commands/meson.build b/src/backend/commands/meson.build
index 9f258d566eb..2f2b30cea0c 100644
--- a/src/backend/commands/meson.build
+++ b/src/backend/commands/meson.build
@@ -19,6 +19,7 @@ backend_sources += files(
   'define.c',
   'discard.c',
   'dropcmds.c',
+  'dynamic_explain.c',
   'event_trigger.c',
   'explain.c',
   'explain_dr.c',
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 4b30f768680..4b9f4f2b7a8 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -44,6 +44,7 @@
 #include "access/xact.h"
 #include "catalog/namespace.h"
 #include "catalog/partition.h"
+#include "commands/dynamic_explain.h"
 #include "commands/matview.h"
 #include "commands/trigger.h"
 #include "executor/executor.h"
@@ -323,6 +324,7 @@ standard_ExecutorRun(QueryDesc *queryDesc,
 	DestReceiver *dest;
 	bool		sendTuples;
 	MemoryContext oldcontext;
+	QueryDesc  *oldQueryDesc;
 
 	/* sanity checks */
 	Assert(queryDesc != NULL);
@@ -335,6 +337,13 @@ standard_ExecutorRun(QueryDesc *queryDesc,
 	/* caller must ensure the query's snapshot is active */
 	Assert(GetActiveSnapshot() == estate->es_snapshot);
 
+	/*
+	 * Save current QueryDesc here to enable retrieval of the currently
+	 * running queryDesc for nested queries.
+	 */
+	oldQueryDesc = GetCurrentQueryDesc();
+	SetCurrentQueryDesc(queryDesc);
+
 	/*
 	 * Switch into per-query memory context
 	 */
@@ -397,6 +406,14 @@ standard_ExecutorRun(QueryDesc *queryDesc,
 		InstrStop(queryDesc->query_instr);
 
+	 * logging the plan. Otherwise plan will be logged at the next query
 
index 7c4c66e323f..081597a31d0 100644
 
 #include "executor/instrument.h"
@@ -441,21 +443,37 @@ ExecSetExecProcNode(PlanState *node, ExecProcNodeMtd function)

 /*
 
+	/*
@@ -527,6 +545,91 @@ MultiExecProcNode(PlanState *node)
 	return result;
 }
 
+/*
 
index 1397f65f67b..bb01f605d00 100644
 
 #include "access/parallel.h"
 #include "commands/async.h"
+#include "commands/dynamic_explain.h"
 #include "commands/repack.h"
 #include "miscadmin.h"
 #include "pgstat.h"
@@ -713,6 +714,9 @@ procsignal_sigusr1_handler(SIGNAL_ARGS)
 	if (CheckProcSignal(PROCSIG_LOG_MEMORY_CONTEXT))
 		HandleLogMemoryContextInterrupt();

index dbef734a93f..98413ad58fd 100644
@@ -3609,6 +3610,9 @@ ProcessInterrupts(void)

index bbd28d14d99..de84fd9cfa3 100644
index a8cbdf247c8..f2881d956c0 100644

 /* ----------------
  *		extern definitions
 extern int	GetTopReadOnlyTransactionNestLevel(void);
index be157a5fbe9..92976b1e532 100644
@@ -8721,6 +8721,13 @@
   prosrc => 'pg_log_backend_memory_contexts', proacl => '{POSTGRES=X}' },
 
+  proargtypes => 'int4', prosrc => 'pg_log_query_plan',
+  proacl => '{POSTGRES=X}' },
index 00000000000..f3c4f6fb5af
+ * Portions Copyright (c) 1996-2026, PostgreSQL Global Development Group
index 472e141bba3..42f1f7d61ff 100644
@@ -71,6 +71,8 @@ extern void ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into,
 
+extern void ExplainPrintPlan(ExplainState *es, QueryDesc *queryDesc);
+extern void ExplainPrintTriggers(ExplainState *es, QueryDesc *queryDesc);
 extern void ExplainPrintPlan(ExplainState *es, QueryDesc *queryDesc);
 extern void ExplainPrintTriggers(ExplainState *es,
 								 QueryDesc *queryDesc);
@@ -81,5 +83,8 @@ extern void ExplainPrintJITSummary(ExplainState *es,
 extern void ExplainQueryText(ExplainState *es, QueryDesc *queryDesc);
 extern void ExplainQueryParameters(ExplainState *es,
 								   ParamListInfo params, int maxlen);
+extern void ExplainStringAssemble(ExplainState *es, QueryDesc *queryDesc,
+								  int logFormat, bool logTriggers,
+								  int logParameterMaxLength);
 
 #endif							/* EXPLAIN_H */
index 97bc7ed49f6..b9a98157815 100644
@@ -73,6 +73,7 @@ typedef struct ExplainState
index 650baab3efc..12a1673d00c 100644
@@ -299,6 +299,7 @@ extern void EvalPlanQualEnd(EPQState *epqstate);
index 7170a4bff98..0ab775e964f 100644
@@ -98,6 +98,7 @@ extern PGDLLIMPORT volatile sig_atomic_t IdleSessionTimeoutPending;
 
index aaa158bfd66..c2142043e2d 100644
 	PROCSIG_SLOTSYNC_MESSAGE,	/* ask slot synchronization to stop */
 	PROCSIG_REPACK_MESSAGE,		/* Message from repack worker */
diff --git a/src/test/modules/test_misc/meson.build b/src/test/modules/test_misc/meson.build
index 969e90b396d..4c23bb794c7 100644
--- a/src/test/modules/test_misc/meson.build
+++ b/src/test/modules/test_misc/meson.build
@@ -22,6 +22,7 @@ tests += {
       't/011_lock_stats.pl',
       't/012_ddlutils.pl',
       't/013_temp_obj_multisession.pl',
+      't/014_pg_log_query_plan.pl',
     ],
     # The injection points are cluster-wide, so disable installcheck
     'runningcheck': false,
diff --git a/src/test/modules/test_misc/t/014_pg_log_query_plan.pl b/src/test/modules/test_misc/t/014_pg_log_query_plan.pl
index 00000000000..2055641283e
+++ b/src/test/modules/test_misc/t/014_pg_log_query_plan.pl
+# Copyright (c) 2024-2026, PostgreSQL Global Development Group
+# This test requires timing coordinations:
+# received by confirming session1 is waiting on the injection point.
index c3261bff209..5d310939a75 100644
@@ -357,6 +357,44 @@ REVOKE EXECUTE ON FUNCTION pg_log_backend_memory_contexts(integer)
index 946ee5726cd..e7ab81cca60 100644
@@ -113,6 +113,37 @@ REVOKE EXECUTE ON FUNCTION pg_log_backend_memory_contexts(integer)

+--
+-- pg_log_query_plan()
+--

---

From: Robert Haas <[email-masked]>
Date: Mon, 22 Jun 2026 09:50:52 -0400

> I also fixed some typos in the attached patch.

In my earlier reviews, I have been focusing on core correctness
issues. This time, I decided to leave that aside to focus on a few
more cosmetic and user-interface points:

I think the #ifdef is not required here. Look at how INJECTION_POINT()
is defined.

So, we're establishing a non-default set of settings here which the
user has no way to configure. I think that's hard to justify. I think
we either need to accept the defaults that NewExplainState()
configures, or we need to provide a GUC for the user to configure
things as they wish.

We have four explain-related source files currently, all of them have
"explain" at the start of the name rather than at the end. We should
probably do the same here. I would suggest explain_running.c rather
than explain_dynamic.c. Or some other word that conveys "the query is
in progress" -- dynamic seems vague.

It's worth thinking about whether restricting this to the superuser by
default is the right decision. I don't think I believe this rationale:
a user can do lots of things that generate lots of log volume, and we
don't restrict any of the other ones. For example, any user can do
this:

do $$ begin for i in 1..1000000 loop raise log '% is an extremely long
string', repeat('hoge',100000); end loop; end; $$;

I thought about whether we should actually relax this and let people
log their own queries, but then I realized what I believe the real
issue to be: the superuser is presumed to have access to the log files
-- after all, they can configure them, and escape to the shell -- but
other users very likely don't. If some of them do, the superuser knows
which ones.

There's no such function (any more, anyway).

---

From: torikoshia <[email-masked]>
Date: Wed, 24 Jun 2026 21:56:17 +0900

> more cosmetic and user-interface points:

Thank you for the review!

Agreed.

> configures, or we need to provide a GUC for the user to configure

At this stage, what I would like to focus on is developing the mechanism
for obtaining the plan of a currently running query. So I think it is
better to start with the default EXPLAIN options.
Updated the patch that way.

> in progress" -- dynamic seems vague.

Thanks for the suggestion. Renamed it to explain_running.c.

> other users very likely don't. If some of them do, the superuser knows

That makes sense.
Even if a non-superuser has EXECUTE permission on this function, it 
would
probably not be very useful, because the user would not be able to read
the server log by themselves as you pointed out.

Would it make sense to restrict EXECUTE to superusers by default for 
that
reason?
In the attached patch, I have rewritten the comment as follows:

   + * By default, only superusers are allowed to signal a backend to log 
its
   + * plan because the output is written to the server log, which in 
many cases
   + * can only be read by superusers.

The current restriction was modeled after 
pg_log_backend_memory_contexts().
So I think the same discussion would also apply to that function. If 
needed,
we could also revisit the comment there from the same perspective:

   * pg_log_backend_memory_contexts
   *      Signal a backend or an auxiliary process to log its memory 
contexts.
   *
   * By default, only superusers are allowed to signal to log the memory
   * contexts because allowing any users to issue this request at an 
unbounded
   * rate would cause lots of log messages and which can lead to denial 
of
   * service. Additional roles can be permitted with GRANT.

BTW I'm interested in the proposal of 
pg_get_process_memory_contexts()[1],
which would return memory context information directly from the function
rather than writing it to the log. If a similar approach were adopted
for running query plans in the future, then it would make sense to relax
the default privilege for that.

> There's no such function (any more, anyway).

Removed.

[1] 
https://www.postgresql.org/message-id/495F9D76-6B8B-45C3-95DA-EF5A8762DA19%40yesql.se

Thanks,

From 34d678ca3c424d1e8f3b33938547b4b77e1cadda Mon Sep 17 00:00:00 2001
From: Atsushi Torikoshi <[email-masked]>
Date: Wed, 24 Jun 2026 21:30:01 +0900
Subject: [PATCH v56] Add function to log the plan of the currently running
 query

Todo: Consider removing the PID from the log output of
pg_log_backend_memory_contexts() and pg_log_query_plan().
For detail, see the discussion:
https://www.postgresql.org/message-id/CA%2BTgmoY81r7npTS34N_5MLA_u6ghfor5HoSaar53veXUYu1OxQ%40mail.gmail.com
---
 contrib/auto_explain/auto_explain.c           |  29 +--
 doc/src/sgml/func/func-admin.sgml             |  24 +++
 src/backend/access/transam/xact.c             |  34 ++++
 src/backend/commands/Makefile                 |   1 +
 src/backend/commands/explain.c                |  44 ++++-
 src/backend/commands/explain_running.c        | 182 ++++++++++++++++++
 src/backend/commands/meson.build              |   1 +
 src/backend/executor/execMain.c               |  17 ++
 src/backend/executor/execProcnode.c           | 121 +++++++++++-
 src/backend/storage/ipc/procsignal.c          |   4 +
 src/backend/tcop/postgres.c                   |   4 +
 src/backend/utils/init/globals.c              |   2 +
 src/include/access/xact.h                     |   3 +
 src/include/catalog/pg_proc.dat               |   7 +
 src/include/commands/explain.h                |   5 +
 src/include/commands/explain_running.h        |  24 +++
 src/include/commands/explain_state.h          |   1 +
 src/include/executor/executor.h               |   1 +
 src/include/miscadmin.h                       |   1 +
 src/include/storage/procsignal.h              |   2 +
 src/test/modules/test_misc/meson.build        |   1 +
 .../test_misc/t/014_pg_log_query_plan.pl      | 101 ++++++++++
 src/test/regress/expected/misc_functions.out  |  38 ++++
 src/test/regress/sql/misc_functions.sql       |  31 +++
 24 files changed, 641 insertions(+), 37 deletions(-)
 create mode 100644 src/backend/commands/explain_running.c
 create mode 100644 src/include/commands/explain_running.h
 create mode 100644 src/test/modules/test_misc/t/014_pg_log_query_plan.pl

 
 
 			/*
 
+      <row>
index de4cf96eaa2..cc37ca565f1 100644
+#include "commands/explain_running.h"

 /*
 
+/*
+ * SetCurrentQueryDesc
+ */
+void
 
 /*

+	/*
index 5b9d084977e..671f036fa76 100644
@@ -35,6 +35,7 @@ OBJS = \
 	explain_format.o \
+	explain_running.o \
 	explain_state.o \
 	extension.o \
 	foreigncmds.o \
index a40d03d35f3..2221b8e9569 100644
 
+/*
 
 	/*
 
 	if (es->analyze &&
diff --git a/src/backend/commands/explain_running.c b/src/backend/commands/explain_running.c
new file mode 100644
index 00000000000..0fe2ae3f191
--- /dev/null
+++ b/src/backend/commands/explain_running.c
@@ -0,0 +1,182 @@
+ * explain_running.c
+ *	  src/backend/commands/explain_running.c
+#include "commands/explain_running.h"
+ * By default, only superusers are allowed to signal a backend to log its
+ * plan because the output is written to the server log, which in many cases
+ * can only be read by superusers.
index 9f258d566eb..d630dedd5d7 100644
@@ -23,6 +23,7 @@ backend_sources += files(
   'explain_format.c',
+  'explain_running.c',
   'explain_state.c',
   'extension.c',
   'foreigncmds.c',
index 4b30f768680..cca01d3c4fc 100644
+#include "commands/explain_running.h"
 
 	/* sanity checks */
 
+	/*

index 7c4c66e323f..109016d0602 100644
 
+#include "commands/explain_running.h"

 /*
 
+	/*
 
+/*
 
index 1397f65f67b..4b442d4cdad 100644
 
+#include "commands/explain_running.h"

index dbef734a93f..7f8790d340a 100644
+#include "commands/explain_running.h"

 
 
 /* ----------------
  *		extern definitions
index fa76c7923f0..dba77450b22 100644

 
 #endif							/* EXPLAIN_H */
diff --git a/src/include/commands/explain_running.h b/src/include/commands/explain_running.h
new file mode 100644
index 00000000000..316ef25be60
--- /dev/null
+++ b/src/include/commands/explain_running.h
@@ -0,0 +1,24 @@
+ * explain_running.h
+ *	  prototypes for explain_running.c
+ * src/include/commands/explain_running.h
+#ifndef EXPLAIN_RUNNING_H
+#define EXPLAIN_RUNNING_H
+#endif							/* EXPLAIN_RUNNING_H */
 
index c3261bff209..30297d7bd4e 100644
+-- src/test/modules/test_misc/t/014_pg_log_query_plan.pl.
index 946ee5726cd..d3cf5b6f852 100644

+--
+-- pg_log_query_plan()
+--
+-- src/test/modules/test_misc/t/014_pg_log_query_plan.pl.

---

From: Andrei Lepikhov <[email-masked]>
Date: Wed, 24 Jun 2026 16:35:29 +0200

Hi,

I began discovering how it works.
Some minor issues after skimming through the code:
1. pg_log_query_plan - This function just sends a signal. There are no
guarantees that actual action will be performed. So, the 'request' word makes
more sense for me.
2. GetCurrentQueryDesc, SetCurrentQueryDesc - need 'extern' in the .h file
3. ExplainPrintPlan, ExplainPrintTriggers - have exact duplicates in the explain.h
4. ExplainStringAssemble() has '0' input for a boolean parameter.
5. ExecSetExecProcNodeRecurse: I don't see PlanState::initPlan. Is this intentional?
6. INJECTION_POINT("log-query-interrupt", ...) - it is called inside a
signal-safe zone. In tests, the caller might do a lot of things in the attached
routine, causing hidden problems later.

-- 
regards, Andrei Lepikhov,
pgEdge

---

From: Robert Haas <[email-masked]>
Date: Thu, 25 Jun 2026 05:43:50 -0400

> guarantees that actual action will be performed. So, the 'request' word makes

I don't agree with this particular comment. Of course nothing is
absolutely guaranteed because the database system could crash or the
world could end, but if the target backend is running a query, it
should log the query plan. Therefore, I don't see the need for a word
like "request".

---

From: Andrei Lepikhov <[email-masked]>
Date: Thu, 25 Jun 2026 12:33:14 +0200

> should log the query plan. Therefore, I don't see the need for a word

Ok, so just mention this behaviour in the documentation - let people know that
they should potentially wait for a minute or two more to see the EXPLAIN.
Real-life with huge queries and big machines provide us with examples where a
backend might delay a response to a signal for quite a substantial time.

Sometimes nothing happens after such an async operation, and we need to identify
the problem: has the backend stalled, is the ‘logging plan’ algorithm
ineffective, or is something else happening?

---

From: Robert Haas <[email-masked]>
Date: Fri, 26 Jun 2026 08:50:56 -0400

> ineffective, or is something else happening?

I don't think we document this in other, similar cases. Many things
can be delayed if the machine is overloaded, but unless I am missing
something, having to wait over a minute for this to work would be
*extremely* unusual and only happen in case of a machine that is
absolutely being crushed by the load. And in that case everything else
will be slow, too.

---

From: torikoshia <[email-masked]>
Date: Mon, 29 Jun 2026 13:23:57 +0900

On Wed, Jun 24, 2026 at 11:35 PM Andrei Lepikhov <[email-masked]> 
wrote:
Thanks for your review!

> routine, causing hidden problems later.

I agree that these points should be addressed.
I'll post an updated patch.

I've also received some off-list feedback, and I'm planning to 
incorporate
those comments into the updated patch as well.

> absolutely being crushed by the load. And in that case everything else

I think a delay caused by signal handling can also occur with the 
existing
functions, such as pg_cancel_backend() and 
pg_log_backend_memory_contexts().

Since the documentation does not specifically mention such delays for 
those
functions, I thought it may not be necessary to document this point for
pg_log_query_plan() either.

On the other hand, since pg_log_query_plan() emits the log output at the
next time ExecProcNodeFirst() is executed, if there is a delay before
ExecProcNodeFirst() is called, there would also be a delay before the
plan is logged.

For example, this can happen with a query such as SELECT pg_sleep(100),
or when executing a time-consuming sort operation like the following:

   BEGIN;
   DECLARE sort_probe NO SCROLL CURSOR FOR
   SELECT g, md5(g::text) AS k
   FROM generate_series(1, 100000000) AS g
   ORDER BY k;

   FETCH 1 FROM sort_probe;

I think this behavior is specific to pg_log_query_plan(), so I am
starting to wonder whether something should be mentioned in the
documentation.

Thanks,

---

From: Robert Haas <[email-masked]>
Date: Mon, 29 Jun 2026 12:30:47 -0400

> starting to wonder whether something should be mentioned in the

Yeah, that's fair. In that case, the delay could be longer than what
people might expect, so it could be worth calling out for that reason.
It still feels optional to me, but I'm fine if you want to mention it.

---

From: Andrei Lepikhov <[email-masked]>
Date: Wed, 1 Jul 2026 12:42:53 +0200

> starting to wonder whether something should be mentioned in the
I took a closer look at the solution, and it looks quite good.

1. EXPLAIN needs a more or less consistent execution plan state. And this patch
decides to cut in the executor right before a node should start producing the
next tuple.

I’ve never seen any races or other issues with this approach in Postgres forks,
except that users sometimes felt irritated because a HashJoin gets stuck
rebalancing hash batches and does not respond for a long time, or when an OLAP
query causes a situation close to OOM, and the user can’t get an explain because
of a massive memory operation.

The bad example of such a feature can be found in the AQO project [1]. In this
first attempt, we used the built-in RegisterTimeout() to stop execution and take
an EXPLAIN. And from time to time, it fails due to the executor's inconsistent
state. So, the current way looks right.

2. Adding another event processing type to the ProcessInterrupts() routine is a
brilliant idea. This way, overhead is only added when someone requests an
injection into the execution process.

3. But the idea of the ExecSetExecProcNodeRecurse() seems fragile to me. It
depends on the PlanState tree structure and a choice on the current QueryDesc.
When adding a new node, we will need to remember to update this code, too. I’d
propose introducing a tiny hook (register callback?) into ExecProcNode() and
just setting it in ProcessLogQueryPlanInterrupt. Next call of the ExecProcNode()
will cause the EXPLAIN machinery whenever it happens. It might also simplify
this function's code, since races won't be a danger anymore. Just don't forget
to restore the hook state after the EXPLAIN preparation or top-level query end.

In addition, such ExecProcNode_hook may serve the longstanding desire of
extension developers to look inside the execution of a query: large queries run
for seconds, minutes, or even longer. And it is quite a frequent dilemma: wait a
little more or interrupt and replan the query? Even with this feature being
committed, we still might want to get an EXPLAIN of the top-level query, not a
nested one, that the GetCurrentQueryDesc provides us with. Exactly this way was
implemented in the replan feature [2] - unfortunately, it is still closed source
code.

4. Also, I’m not sure it is safe to store the QueryDesc pointer at all at the
ProcessLogQueryPlanInterrupt. Maybe the next call to ExecProcNode() will be made
from an external query (or from a deeper one). The current implementation will
not produce any EXPLAIN in this case, but query still executes.

I think, combination of a global hook and queryDesc pointer, saved somewhere in
the EState may solve the problem. It is available at each executor's node, and
the EXPLAIN machinery might use an exact descriptor related to the currently
executing query.

5. The phrase ‘only the plan of the most deeply nested query is logged’ is
missed in the docs. So the current decision has not yet been documented.

[1]
https://github.com/postgrespro/aqo/blob/4a7fcd7291a8c4209c9102d1736f9f754d311cf2/postprocessing.c#L621
[2] https://postgrespro.com/docs/enterprise/16/realtime-query-replanning

---

From: torikoshia <[email-masked]>
Date: Wed, 01 Jul 2026 22:09:52 +0900

> I'll post an updated patch.

Fixed these points.

> incorporate those comments into the updated patch as well.

I also made the following changes:

- Updated the documentation to mention that pg_log_query_plan() is 
asynchronous and that plan logging can be delayed if the target backend 
does not reach an executor point where the plan can be inspected.
- Fixed LogQueryPlan() so that its temporary memory context is always 
restored and deleted, even when there is no current QueryDesc.
- Added defensive checks for pgstat_get_beentry_by_proc_number() 
returning NULL, and verified that the backend status entry still matches 
the requested PID.
- Wrapped the main LogQueryPlan() body in PG_TRY/PG_FINALLY so 
LogQueryPlanPending and memory context cleanup are handled even if 
EXPLAIN generation throws an error.

On Wed, Jul 1, 2026 at 7:42 PM Andrei Lepikhov <[email-masked]> 
wrote:
> I took a closer look at the solution

Thanks for the comments!
I will mainly consider the 3rd and 4th points.

Regards,

From d518511ec057aae29ac4edd38dfda28b373d3dc8 Mon Sep 17 00:00:00 2001
From: Atsushi Torikoshi <[email-masked]>
Date: Wed, 1 Jul 2026 21:37:47 +0900
Subject: [PATCH v57] Add function to log the plan of the currently running
 query

On receipt of the request, ExecProcNodes of the current plan
node and its subsidiary nodes are wrapped with
ExecProcNodeFirst, which implements logging query plan.
When the executor next invokes one of the wrapped nodes, the
query plan is logged.

 doc/src/sgml/func/func-admin.sgml             |  32 +++
 src/backend/access/transam/xact.c             |  33 ++++
 src/backend/commands/explain_running.c        | 187 ++++++++++++++++++
 src/backend/executor/execProcnode.c           | 132 ++++++++++++-
 24 files changed, 662 insertions(+), 37 deletions(-)

 
 
 			/*
index 0eae1c1f616..877d75d9c5e 100644
@@ -184,6 +184,38 @@
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_log_query_plan</primary>
+        </indexterm>
+        <function>pg_log_query_plan</function> ( <parameter>pid</parameter> <type>integer</type> )
+        <returnvalue>boolean</returnvalue>
+       </para>
+       <para>
+        Requests to log the plan of the query currently running on the
+        backend with specified process ID.
+        It will be logged at <literal>LOG</literal> message level and
+        will appear in the server log based on the log
+        configuration set (See <xref linkend="runtime-config-logging"/>
+        for more information), but will not be sent to the client
+        regardless of <xref linkend="guc-client-min-messages"/>.
+       </para>
+       <para>
+        The plan is logged when the target backend next reaches a
+        point where the running plan can be inspected.  If the backend
+        is spending a long time in work that does not reach such a
+        point, the log output can be delayed until that work
+        completes. If the query finishes before such a point is
+        reached, no plan may be logged.
+       </para>
+       <para>
+        This function is restricted to superusers by default, but other
+        users can be granted EXECUTE to run the function.
+       </para></entry>
+      </row>
+
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
diff --git a/src/backend/access/transam/xact.c b/src/backend/access/transam/xact.c
index de4cf96eaa2..e130afe2c11 100644
--- a/src/backend/access/transam/xact.c
+++ b/src/backend/access/transam/xact.c
@@ -217,6 +217,7 @@ typedef struct TransactionStateData
 	bool		parallelChildXact;	/* is any parent transaction parallel? */
 	bool		chain;			/* start a new block after this one */
 	bool		topXidLogged;	/* for a subxact: is top-level XID logged? */
+	QueryDesc  *queryDesc;		/* my current QueryDesc */
 	struct TransactionStateData *parent;	/* back link to parent */
 } TransactionStateData;
 
@@ -250,6 +251,7 @@ static TransactionStateData TopTransactionStateData = {
 	.state = TRANS_DEFAULT,
 	.blockState = TBLOCK_DEFAULT,
 	.topXidLogged = false,
+	.queryDesc = NULL,
 };
 
 /*
@@ -935,6 +937,27 @@ GetCurrentTransactionNestLevel(void)
 	return s->nestingLevel;
 }
 
+/*
+ * SetCurrentQueryDesc
+ */
+void
 
 /*
  *	TransactionIdIsCurrentTransactionId
@@ -2953,6 +2976,9 @@ AbortTransaction(void)
 	/* Reset snapshot export state. */
 	SnapBuildResetExportedSnapshotState();
 
+	/* Reset current QueryDesc. */
+	SetCurrentQueryDesc(NULL);
+
 	/*
 	 * If this xact has started any unfinished parallel operation, clean up
 	 * its workers and exit parallel mode.  Don't warn about leaked resources.
@@ -5352,6 +5378,13 @@ AbortSubTransaction(void)
 	/* Reset logical streaming state. */
 	ResetLogicalStreamingState();
 
+	/*
+	 * Reset current QueryDesc. Note that even after this reset, it's still
+	 * possible to obtain the parent transaction's query plans, since they are
+	 * preserved in standard_ExecutorRun().
+	 */
 
+/*
 
 	/*
 
 	if (es->analyze &&
index 00000000000..ecc9f28e60f
+	 * ExecProcNode can be called afterward (i.e., ExecPostprocessPlan). To
+		if (queryDesc != NULL)
+			ExplainStringAssemble(es, queryDesc, es->format, false, -1);
+	PgBackendStatus *be_status = NULL;
+	if (proc != NULL)
+	if (proc == NULL || be_status == NULL || be_status->st_procpid != pid)
 
 	/* sanity checks */
 
+	/*

index 7c4c66e323f..b8c118dea4f 100644

 /*
 
+	/*
@@ -527,6 +545,102 @@ MultiExecProcNode(PlanState *node)
 	return result;
 }
 
+/*
+ * initPlan, subPlan, or other special child plans.
+	if (ps->initPlan != NULL)
+		foreach(l, ps->initPlan)

 

 
index a8cbdf247c8..0570fdd3277 100644

 /* ----------------
  *		extern definitions
+extern QueryDesc *GetCurrentQueryDesc(void);
+extern void SetCurrentQueryDesc(QueryDesc *queryDesc);
 
index 472e141bba3..633ac94ea58 100644
@@ -81,5 +81,8 @@ extern void ExplainPrintJITSummary(ExplainState *es,
 
 #endif							/* EXPLAIN_H */

 
+--
+-- pg_log_query_plan()
+--

---

From: Andrei Lepikhov <[email-masked]>
Date: Thu, 2 Jul 2026 13:05:57 +0200

Thanks,

I read the EXPLAIN generation part of the code. It looks good.

It prepares a clean EXPLAIN that’s good for stability and safety, because the
hardest part of the task has been correctly gathering the instrumentation for
the partially executed query: interrupting parallel workers and merging their
instrumentation is always the pain.

Also, it leaves room for extensions or future development if the user wants to
make a hard stop and take a full picture of the current state, including rows,
buffers, WAL, and whatever is possible with current instrumentation.

The only 'es→signaled' flag is an eyesore. It would be better to base the
decision on the es→analyze flag, which would break the long-standing
auto_explain rule, if I understand correctly. So, at least this flag should be
renamed to something like es→running, or es→query_in_progress.

Also, not sure I understand how this code regulates the explain format,
verbosity and other EXPLAIN settings.

---

From: torikoshia <[email-masked]>
Date: Mon, 06 Jul 2026 15:21:44 +0900

On Wed, Jul 1, 2026 at 7:42 PM Andrei Lepikhov <[email-masked]> 
wrote:

> I’d
> just setting it in ProcessLogQueryPlanInterrupt.

This approach would also be possible, but we are concerned that
checking the hook on every ExecProcNode() call would add too much
overhead and could have a significant performance impact.

This point was discussed previously:
https://www.postgresql.org/message-id/20240215185911.v4o6fo444md6a3w7%40awork3.anarazel.de

> ProcessLogQueryPlanInterrupt.

I think the current implementation does not store a pointer to the
queryDesc passed to ProcessLogQueryPlanInterrupt().
That queryDesc is used only to obtain the PlanState nodes that need
to be wrapped. For plan logging itself, LogQueryPlan() uses a
queryDesc obtained separately.

> Maybe the next call to ExecProcNode() will be made

That said, I think a similar situation can still occur if execution
moves to an external or deeper queryDesc after the individual plan
nodes have been wrapped. In that case, the plan does not be logged
even though the query continues executing.

> the EXPLAIN machinery might use an exact descriptor related to the 

Given the performance concern mentioned above, my impression was that
using a global hook would not be a viable approach.

Even if we could address that point, I suspect it would still be
difficult to eliminate this kind of issue entirely, because signal
handling through CFI() and wrapping plan nodes and its execution are
asynchronous.

My understanding is that this problem can occur only when the next
ExecProcNode() call after wrapping the plan nodes happens to be
for an inner or external query.

So personally, I hope it might be acceptable to treat this as a
case where the backend did not reach a point at which the plan
could be logged, as described in the documentation change below:

   +        The plan is logged when the target backend next reaches a
   +        point where the running plan can be inspected. (..snip..)
   +        If the query finishes before such a point is
   +        reached, no plan may be logged.

> missed in the docs. So the current decision has not yet been 

Added the explanation:

+        If the target backend is executing a nested query when it
+        processes the request, only the plan of the innermost
+        executing query is logged.

> renamed to something like es→running, or es→query_in_progress.

Modified to es->running.

> verbosity and other EXPLAIN settings.

As discussed in the following thread, the current implementation uses 
the default
EXPLAIN options and does not provide any particular way to regulate 
them:
https://www.postgresql.org/message-id/57ee0cf0cf76b742b0144bd481c56b57%40oss.nttdata.com

As mentioned in that discussion, I think it would be possible to make 
this configurable
in the future, for example by adding GUCs or by adding options to 
pg_log_query_plan().
For now, however, I would like to focus on developing the mechanism for 
obtaining the
plan of a currently running query.

Thanks,

From a6b36695ddda83c4c732c5d60bf8a8e8037abf51 Mon Sep 17 00:00:00 2001
From: Atsushi Torikoshi <[email-masked]>
Date: Mon, 6 Jul 2026 14:53:29 +0900
Subject: [PATCH v58] Add function to log the plan of the currently running
 query

 doc/src/sgml/func/func-admin.sgml             |  37 ++++
 src/backend/commands/explain.c                |  43 +++-
 .../test_misc/t/015_pg_log_query_plan.pl      | 101 ++++++++++
 24 files changed, 666 insertions(+), 37 deletions(-)
 create mode 100644 src/test/modules/test_misc/t/015_pg_log_query_plan.pl

 
 
 			/*
index 0eae1c1f616..15e68edc41b 100644
@@ -184,6 +184,43 @@
        </para></entry>
       </row>
 
+      <row>
+        If the target backend is executing a nested query when it
+        processes the request, only the plan of the innermost
+        executing query is logged.
index 3a89149016f..404898b97ab 100644

 /*
 
+/*
+ * SetCurrentQueryDesc
+ */
+void
 
 /*
  *	TransactionIdIsCurrentTransactionId
@@ -2955,6 +2978,9 @@ AbortTransaction(void)
 	/* Reset snapshot export state. */
 	SnapBuildResetExportedSnapshotState();
 
@@ -5355,6 +5381,13 @@ AbortSubTransaction(void)
 
+	/*
index a40d03d35f3..d2cf3c940a7 100644
 
+/*
@@ -1827,7 +1864,9 @@ ExplainNode(PlanState *planstate, List *ancestors,
 
 	/*
 	 * We have to forcibly clean up the instrumentation state because we
-	 * haven't done ExecutorEnd yet.  This is pretty grotty ...
+	 * haven't done ExecutorEnd yet.  This is pretty grotty ... This cleanup
+	 * should not be done when explaining a running query, as the target query
+	 * may use instrumentation and clean itself up.
 	 *
 	 * Note: contrib/auto_explain could cause instrumentation to be set up
 	 * even though we didn't ask for it here.  Be careful not to print any
@@ -1835,7 +1874,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 	 * InstrEndLoop call anyway, if possible, to reduce the number of cases
 	 * auto_explain has to contend with.
 	 */
-	if (planstate->instrument)
+	if (planstate->instrument && !es->running)
 		InstrEndLoop(planstate->instrument);
 
 	if (es->analyze &&
index 00000000000..43204dc349f
index fde502efd38..dc4177216cf 100644
 
 	/* sanity checks */
 
+	/*

 

 /*
 
+	/*
 
+/*

 
 
index ce18df820cd..0e4483def5d 100644
@@ -3704,6 +3705,9 @@ ProcessInterrupts(void)

 
 
 /* ----------------
  *		extern definitions
index 3cb84359adf..741f04191bb 100644
@@ -8740,6 +8740,13 @@

 #endif							/* EXPLAIN_H */
index 97bc7ed49f6..4b90f5eaa0a 100644
+	bool		running;		/* whether target query is running */
index 1798e6027d4..7e99fb2afed 100644
 
index ee290698b31..8e8c8658120 100644
@@ -23,6 +23,7 @@ tests += {
       't/014_log_statement_max_length.pl',
+      't/015_pg_log_query_plan.pl',
diff --git a/src/test/modules/test_misc/t/015_pg_log_query_plan.pl b/src/test/modules/test_misc/t/015_pg_log_query_plan.pl
+++ b/src/test/modules/test_misc/t/015_pg_log_query_plan.pl
index c3261bff209..cc1f5103fef 100644
+-- src/test/modules/test_misc/t/015_pg_log_query_plan.pl.
index 946ee5726cd..9c508175575 100644

+--
+-- pg_log_query_plan()
+--
+-- src/test/modules/test_misc/t/015_pg_log_query_plan.pl.

---

From: Andrei Lepikhov <[email-masked]>
Date: Mon, 6 Jul 2026 15:05:33 +0200

On 06/07/2026 08:21, torikoshia wrote:
> On Wed, Jul 1, 2026 at 7:42 PM Andrei Lepikhov <[email-masked]> wrote:
> 
>> I’d

I doubt the performance impact, but generally agree: having
ExecSetExecProcNode/ExecProcNodeFirst trick with a one-time callback inside
(right now it is the only hard-wired LogQueryPlan call) may be enough for the
implementation.

So, the extendable part here may be an ExecProcNodeFirst callback that an
extension module can register once (for example, before the start of an EXPLAIN
ANALYZE) or re-arm on each call of such 'execution state probes'.

> even though the query continues executing.

That's the problem. It makes tests unstable as well as user experience
unpredictable. How to script (automatise) this feature if you have no
guarantees? Is anywhere in Postgres any other examples of such behaviour? To
close the gap, ExecutorStart routine could check a global 'pending report' flag
and wrap planstate nodes if needed.
Also, there are kinds of query feedback that might be, reporting the ‘pending
reports’ like queryid to the pg_stat_activity. That solves the automation
problem for the async feature.

In addition, I wonder why this code doesn't use standard planstate walker. It
seems to me that something like the following should work:

static bool
rearm_execprocnode_walker(PlanState *node, void *context)
{
    if (node == NULL)
        return false;
    ExecSetExecProcNode(node, node->ExecProcNodeReal);
    return planstate_tree_walker(node, rearm_execprocnode_walker, context);
}
/* trigger point */
(void) rearm_execprocnode_walker(queryDesc->planstate, NULL);

---

From: torikoshia <[email-masked]>
Date: Tue, 07 Jul 2026 23:46:25 +0900

On 2026-07-06 22:05, Andrei Lepikhov wrote:
> That's the problem.

Sorry, I misremembered the logic.
In this case, even if execution moves to an external or deeper
QueryDesc, LogQueryPlanPending flag remains set. Therefore, the plan
tree for that external or deeper QueryDesc will be wrapped, and
the plan will be logged.

For example, with the following query, ExecProcNode() is called four 
times:

CREATE OR REPLACE FUNCTION f1() RETURNS void AS $$
BEGIN
     PERFORM 1;
END;
$$ LANGUAGE plpgsql;

SELECT f1();

If pg_log_query_plan() is called just after the first
ExecProcNode() call reaches ExecProcNodeFirst(), the plan for PERFORM 1
is logged.

Thanks,