On 2026-07-14 16:07, Andrei Lepikhov wrote:
On 13/07/2026 15:35, torikoshia wrote:
On 2026-07-13 08:28, Andrei Lepikhov wrote:
So, it potentially makes sense to add a statistics field to the
activity stat to
let users know whether the logging (and how many signals, if replace
flag with a
counter) is awaiting execution.
That might be useful for some users, but personally I feel that adding
a column to pg_stat_activity solely for pg_log_query_plan() might be
excessive.
That part is just an idea. I think it might help if the activity
statistics had
a variable-length JSON field where the backend could write some rarely
used flags.
I think it would be worth trying this if we can identify some other
candidates
for rarely used flags.
Maybe it doesn't need to clean up the 'pending' flag at all if no
query has been
executed yet - the query might be under planning, and the EXPLAIN
will be logged
right at the beginning of execution.
Without this cleanup, as the comment says, the plan could instead be
logged when a different query is subsequently executed in the same
session. I imagine users don't expect this behavior.
Hmmm, asynchronous feature that explains an arbitrary part of the query
- for
example, some query inside an evaluated plpgsql function ... I think it
is
expected by design.
Even when execution moves to an outer or deeper QueryDesc in this way,
the LogQueryPlanPending flag remains set, so the plan is logged. [1]
The plan is not logged when there is no subsequent opportunity to call
ExecProcNode(), including in any outer or deeper QueryDesc, as in the
example using PREPARE that you showed in your previous message.
My concern is whether, without the pending flag, it would be
appropriate to log the plan of a different query subsequently
executed by the same backend.
For example, suppose a user calls pg_log_query_plan() expecting
the plan of query A to be logged, but there is no opportunity
to do so. If query B is then executed by the same backend one
hour later, the plan of query B would be logged at that point.
This does not seem like behavior that users would expect.
What do you think?
Personally, I still think it might be preferable to emit a log
message at the end of standard_ExecutorRun() indicating that
there was no opportunity to log the plan.
Also, this feature prints the query, so there is no room for a
mismatch. I think
it should print queryId and let people identify specific EXPLAIN more
easily -
imagine GB-scale log with multiple explains.
Attached patch has changed it to print the query ID as well.
The default 'verbose' mode could
also be helpful.
Regarding this point, the current default was chosen based on
the discussion[2].
I think the part of VERBOSE that helps identify the query is
primarily the query ID. If the query ID is included in the log
message, it may not be necessary to add VERBOSE for this purpose.
+ ereport(LOG,
+ errmsg("query plan logging was
requested but
there was no opportunity to do it"));
+ LogQueryPlanPending = false;
+ }
}
What do you think about this approach?
It reduces uncertainty but I think we can do more.
In addition, I wonder why this code doesn't use standard planstate
walker. It
seems to me that something like the following should work:
As discussed in [2], the set of nodes handled here slightly differs
from that handled by planstate_tree_walker_impl(), so I'd like
to confirm whether it is appropriate to use the standard walker here.
As correctly mentioned in [2], we don't need any special processing for
CTEScan.
So, I think the planstate walker is a correct tool to pass the tree.
I confirmed that planstate_tree_walker() also handles the case I was
concerned about correctly, so attached patch changes the code to use it.
[1]
https://www.postgresql.org/message-id/c3cbd6ae775dff9603ce0d724bb7ea1b%40oss.nttdata.com
[2]
https://www.postgresql.org/message-id/57ee0cf0cf76b742b0144bd481c56b57%40oss.nttdata.com
--
Thanks,
--
Atsushi Torikoshi
Seconded from NTT DATA CORPORATION to SRA OSS K.K.
From 9db3ddd330a6654868ce8d42b9691ff733964b77 Mon Sep 17 00:00:00 2001
From: Atsushi Torikoshi <[email protected]>
Date: Wed, 22 Jul 2026 00:42:44 +0900
Subject: [PATCH v59] 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, 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.
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.
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 | 37 +++
src/backend/access/transam/xact.c | 33 +++
src/backend/commands/Makefile | 1 +
src/backend/commands/explain.c | 43 +++-
src/backend/commands/explain_running.c | 212 ++++++++++++++++++
src/backend/commands/meson.build | 1 +
src/backend/executor/execMain.c | 23 ++
src/backend/executor/execProcnode.c | 37 ++-
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 | 3 +
src/include/commands/explain_running.h | 24 ++
src/include/commands/explain_state.h | 1 +
src/include/miscadmin.h | 1 +
src/include/storage/procsignal.h | 2 +
src/test/modules/test_misc/meson.build | 1 +
.../test_misc/t/015_pg_log_query_plan.pl | 102 +++++++++
src/test/regress/expected/misc_functions.out | 38 ++++
src/test/regress/sql/misc_functions.sql | 31 +++
23 files changed, 601 insertions(+), 38 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/015_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);
- 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);
- if (explain_per_plan_hook)
- (*explain_per_plan_hook) (queryDesc->plannedstmt,
- NULL, es,
- queryDesc->sourceText,
- queryDesc->params,
- queryDesc->estate->es_queryEnv);
- 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] = '}';
- }
+ ExplainStringAssemble(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/func-admin.sgml b/doc/src/sgml/func/func-admin.sgml
index 0eae1c1f616..15e68edc41b 100644
--- a/doc/src/sgml/func/func-admin.sgml
+++ b/doc/src/sgml/func/func-admin.sgml
@@ -184,6 +184,43 @@
</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>
+ If the target backend is executing a nested query when it
+ processes the request, only the plan of the innermost
+ executing query is 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 3a89149016f..404898b97ab 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
+SetCurrentQueryDesc(QueryDesc *queryDesc)
+{
+ TransactionState s = CurrentTransactionState;
+
+ s->queryDesc = queryDesc;
+}
+
+/*
+ * GetCurrentQueryDesc
+ */
+QueryDesc *
+GetCurrentQueryDesc(void)
+{
+ TransactionState s = CurrentTransactionState;
+
+ return s->queryDesc;
+}
/*
* TransactionIdIsCurrentTransactionId
@@ -2955,6 +2978,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.
@@ -5355,6 +5381,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().
+ */
+ SetCurrentQueryDesc(NULL);
+
/*
* No need for SnapBuildResetExportedSnapshotState() here, snapshot
* exports are not supported in subtransactions.
diff --git a/src/backend/commands/Makefile b/src/backend/commands/Makefile
index 5b9d084977e..671f036fa76 100644
--- a/src/backend/commands/Makefile
+++ b/src/backend/commands/Makefile
@@ -35,6 +35,7 @@ OBJS = \
explain.o \
explain_dr.o \
explain_format.o \
+ explain_running.o \
explain_state.o \
extension.o \
foreigncmds.o \
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index a40d03d35f3..d2cf3c940a7 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -1092,6 +1092,43 @@ ExplainQueryParameters(ExplainState *es, ParamListInfo params, int maxlen)
ExplainPropertyText("Query Parameters", str, es);
}
+/*
+ * ExplainStringAssemble -
+ * Assemble es->str for logging according to specified options and format
+ */
+
+void
+ExplainStringAssemble(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);
+ if (explain_per_plan_hook)
+ (*explain_per_plan_hook) (queryDesc->plannedstmt,
+ NULL, es,
+ queryDesc->sourceText,
+ queryDesc->params,
+ queryDesc->estate->es_queryEnv);
+ 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] = '}';
+ }
+}
+
/*
* report_triggers -
* report execution stats for a single relation's triggers
@@ -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 &&
diff --git a/src/backend/commands/explain_running.c b/src/backend/commands/explain_running.c
new file mode 100644
index 00000000000..6af2cf214a6
--- /dev/null
+++ b/src/backend/commands/explain_running.c
@@ -0,0 +1,212 @@
+/*-------------------------------------------------------------------------
+ *
+ * explain_running.c
+ * Explain query plans during execution
+ *
+ * Portions Copyright (c) 1996-2026, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994-5, Regents of the University of California
+ *
+ * IDENTIFICATION
+ * src/backend/commands/explain_running.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include "access/xact.h"
+#include "commands/explain.h"
+#include "commands/explain_format.h"
+#include "commands/explain_running.h"
+#include "commands/explain_state.h"
+#include "executor/executor.h"
+#include "miscadmin.h"
+#include "nodes/nodeFuncs.h"
+#include "storage/proc.h"
+#include "storage/procarray.h"
+#include "storage/procsignal.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;
+
+static bool wrapExecProcNode(PlanState *ps, void *context);
+
+/*
+ * Wrap all the underlying ExecProcNode with ExecProcNodeFirst.
+ */
+static bool
+wrapExecProcNode(PlanState *ps, void *context)
+{
+ if (ps == NULL)
+ return false;
+
+ ExecSetExecProcNode(ps, ps->ExecProcNodeReal);
+
+ return planstate_tree_walker(ps, wrapExecProcNode, context);
+}
+
+/*
+ * 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 */
+}
+
+/*
+ * Actual plan logging function.
+ */
+void
+LogQueryPlan(void)
+{
+ ExplainState *es;
+ MemoryContext cxt;
+ MemoryContext old_cxt;
+ QueryDesc *queryDesc;
+
+ /*
+ * Current QueryDesc is valid only during standard_ExecutorRun. However,
+ * ExecProcNode can be called afterward (i.e., ExecPostprocessPlan). To
+ * handle the case, check whether we have QueryDesc now.
+ */
+ queryDesc = GetCurrentQueryDesc();
+
+ if (queryDesc == NULL)
+ {
+ ereport(LOG_SERVER_ONLY,
+ errmsg("query plan logging was requested but there was no opportunity to do it"));
+ LogQueryPlanPending = false;
+ return;
+ }
+
+ cxt = AllocSetContextCreate(CurrentMemoryContext,
+ "log_query_plan temporary context",
+ ALLOCSET_DEFAULT_SIZES);
+ old_cxt = MemoryContextSwitchTo(cxt);
+
+ es = NewExplainState();
+ es->running = true;
+
+ PG_TRY();
+ {
+ ExplainStringAssemble(es, queryDesc, es->format, false, -1);
+
+ ereport(LOG_SERVER_ONLY,
+ errmsg("query and its plan for queryid " INT64_FORMAT " running on backend with PID %d are:\n%s",
+ queryDesc->plannedstmt->queryId, MyProcPid,
+ es->str->data));
+ }
+ PG_FINALLY();
+ {
+ MemoryContextSwitchTo(old_cxt);
+ MemoryContextDelete(cxt);
+ LogQueryPlanPending = false;
+ }
+ PG_END_TRY();
+}
+
+/*
+ * 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 query plan if requested.
+ * This way ensures that EXPLAIN-related code is executed only during
+ * ExecProcNodeFirst, where it is considered safe.
+ */
+void
+ProcessLogQueryPlanInterrupt(void)
+{
+ QueryDesc *querydesc = GetCurrentQueryDesc();
+
+ /* If current query has already finished, we can do nothing but exit */
+ if (querydesc == NULL)
+ {
+ ereport(LOG_SERVER_ONLY,
+ errmsg("query plan logging was requested but there was no opportunity to do it"));
+ LogQueryPlanPending = false;
+ return;
+ }
+
+ /*
+ * 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)
+ return;
+
+ WrapNodesInProgress = true;
+
+ PG_TRY();
+ {
+ INJECTION_POINT("log-query-interrupt", NULL);
+
+ /*
+ * Wrap ExecProcNodes with ExecProcNodeFirst, which logs query plan
+ * when LogQueryPlanPending is true.
+ */
+ (void) wrapExecProcNode(querydesc->planstate, NULL);
+ }
+ PG_FINALLY();
+ {
+ WrapNodesInProgress = false;
+ }
+ PG_END_TRY();
+}
+
+/*
+ * Signal a backend process to log the query plan of the running query.
+ *
+ * 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.
+ * 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 = NULL;
+
+ proc = BackendPidGetProc(pid);
+
+ if (proc != NULL)
+ be_status = pgstat_get_beentry_by_proc_number(proc->vxid.procNumber);
+
+ if (proc == NULL || be_status == NULL || be_status->st_procpid != pid)
+ {
+ /*
+ * 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);
+ }
+
+ 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/meson.build b/src/backend/commands/meson.build
index 9f258d566eb..d630dedd5d7 100644
--- a/src/backend/commands/meson.build
+++ b/src/backend/commands/meson.build
@@ -23,6 +23,7 @@ backend_sources += files(
'explain.c',
'explain_dr.c',
'explain_format.c',
+ 'explain_running.c',
'explain_state.c',
'extension.c',
'foreigncmds.c',
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index fde502efd38..39649b95384 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/explain_running.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,20 @@ standard_ExecutorRun(QueryDesc *queryDesc,
InstrStop(queryDesc->query_instr);
MemoryContextSwitchTo(oldcontext);
+ SetCurrentQueryDesc(oldQueryDesc);
+
+ /*
+ * Ensure LogQueryPlanPending is initialized in case there was no time for
+ * logging the plan. Otherwise plan will be logged at the next query
+ * execution on the same session.
+ */
+ if (LogQueryPlanPending)
+ {
+ ereport(LOG_SERVER_ONLY,
+ (errmsg("query plan logging was requested but there was no opportunity to do it for queryid " INT64_FORMAT,
+ queryDesc->plannedstmt->queryId)));
+ LogQueryPlanPending = false;
+ }
}
/* ----------------------------------------------------------------
diff --git a/src/backend/executor/execProcnode.c b/src/backend/executor/execProcnode.c
index 7c4c66e323f..912cdd15859 100644
--- a/src/backend/executor/execProcnode.c
+++ b/src/backend/executor/execProcnode.c
@@ -72,6 +72,7 @@
*/
#include "postgres.h"
+#include "commands/explain_running.h"
#include "executor/executor.h"
#include "executor/instrument.h"
#include "executor/nodeAgg.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,21 +443,37 @@ 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
@@ -527,7 +545,6 @@ MultiExecProcNode(PlanState *node)
return result;
}
-
/* ----------------------------------------------------------------
* ExecEndNode
*
diff --git a/src/backend/storage/ipc/procsignal.c b/src/backend/storage/ipc/procsignal.c
index 21a77f98c1d..1d551cc16bf 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/explain_running.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();
+ 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 b6bdfe213fe..c05b5f56778 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_running.h"
#include "commands/explain_state.h"
#include "commands/prepare.h"
#include "commands/repack.h"
@@ -3699,6 +3700,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 bbd28d14d99..de84fd9cfa3 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 a8cbdf247c8..0570fdd3277 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);
+extern QueryDesc *GetCurrentQueryDesc(void);
+extern void SetCurrentQueryDesc(QueryDesc *queryDesc);
extern bool TransactionIdIsCurrentTransactionId(TransactionId xid);
extern int GetTopReadOnlyTransactionNestLevel(void);
extern void CommandCounterIncrement(void);
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index f8a021987b5..3ee6d9cc7ad 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8737,6 +8737,13 @@
prorettype => 'bool', proargtypes => 'int4',
prosrc => 'pg_log_backend_memory_contexts', proacl => '{POSTGRES=X}' },
+# 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',
+ proacl => '{POSTGRES=X}' },
+
# 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 472e141bba3..633ac94ea58 100644
--- a/src/include/commands/explain.h
+++ b/src/include/commands/explain.h
@@ -81,5 +81,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 */
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
+ *
+ * Portions Copyright (c) 1996-2026, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994-5, Regents of the University of California
+ *
+ * src/include/commands/explain_running.h
+ *
+ *-------------------------------------------------------------------------
+ */
+#ifndef EXPLAIN_RUNNING_H
+#define EXPLAIN_RUNNING_H
+
+#include "executor/executor.h"
+#include "commands/explain_state.h"
+
+extern void HandleLogQueryPlanInterrupt(void);
+extern void ProcessLogQueryPlanInterrupt(void);
+extern void LogQueryPlan(void);
+extern Datum pg_log_query_plan(PG_FUNCTION_ARGS);
+
+#endif /* EXPLAIN_RUNNING_H */
diff --git a/src/include/commands/explain_state.h b/src/include/commands/explain_state.h
index 97bc7ed49f6..4b90f5eaa0a 100644
--- a/src/include/commands/explain_state.h
+++ b/src/include/commands/explain_state.h
@@ -73,6 +73,7 @@ typedef struct ExplainState
* entry */
/* state related to the current plan node */
ExplainWorkersState *workers_state; /* needed if parallel plan */
+ bool running; /* whether target query is running */
/* extensions */
void **extension_state;
int extension_state_allocated;
diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h
index 7170a4bff98..0ab775e964f 100644
--- a/src/include/miscadmin.h
+++ b/src/include/miscadmin.h
@@ -98,6 +98,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/storage/procsignal.h b/src/include/storage/procsignal.h
index aaa158bfd66..c2142043e2d 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 */
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 ee290698b31..8e8c8658120 100644
--- a/src/test/modules/test_misc/meson.build
+++ b/src/test/modules/test_misc/meson.build
@@ -23,6 +23,7 @@ tests += {
't/012_ddlutils.pl',
't/013_temp_obj_multisession.pl',
't/014_log_statement_max_length.pl',
+ 't/015_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/015_pg_log_query_plan.pl b/src/test/modules/test_misc/t/015_pg_log_query_plan.pl
new file mode 100644
index 00000000000..9e7f06f3d89
--- /dev/null
+++ b/src/test/modules/test_misc/t/015_pg_log_query_plan.pl
@@ -0,0 +1,102 @@
+
+# Copyright (c) 2024-2026, 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 coordinations:
+# 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().
+$psql_session2->query_safe("SELECT pg_log_query_plan($session1_pid);");
+
+# Ensure that the signal of pg_log_query_plan() is actually
+# received by confirming session1 is waiting on the injection point.
+$node->wait_for_event('client backend', 'log-query-interrupt');
+
+# Commit the session 2 to release the advisory lock.
+$psql_session2->query_safe("COMMIT;");
+
+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(
+ "running on backend with PID $session1_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 c3261bff209..94f4dc59b14 100644
--- a/src/test/regress/expected/misc_functions.out
+++ b/src/test/regress/expected/misc_functions.out
@@ -357,6 +357,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 verify 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/015_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 946ee5726cd..183721edb1e 100644
--- a/src/test/regress/sql/misc_functions.sql
+++ b/src/test/regress/sql/misc_functions.sql
@@ -113,6 +113,37 @@ 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.
+-- Here we just verify 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/015_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
--
--
2.48.1
