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

From: torikoshia <[email-masked]>
Date: Mon, 16 May 2022 17:02:40 +0900

On 2022-03-09 19:04, torikoshia wrote:
> On 2022-02-08 01:13, Fujii Masao wrote:
>> AbortSubTransaction() should reset ActiveQueryDesc to
>> save_ActiveQueryDesc that ExecutorRun() set, instead of NULL?
>> Otherwise ActiveQueryDesc of top-level statement will be unavailable
>> after subtransaction is aborted in the nested statements.
> 
> I once agreed above suggestion and made v20 patch making
> save_ActiveQueryDesc a global variable, but it caused segfault when
> calling pg_log_query_plan() after FreeQueryDesc().
> 
> OTOH, doing some kind of reset of ActiveQueryDesc seems necessary
> since it also caused segfault when running pg_log_query_plan() during
> installcheck.
> 
> There may be a better way, but resetting ActiveQueryDesc to NULL seems
> safe and simple.
> Of course it makes pg_log_query_plan() useless after a subtransaction
> is aborted.
> However, if it does not often happen that people want to know the
> running query's plan whose subtransaction is aborted, resetting
> ActiveQueryDesc to NULL would be acceptable.
> 
> Attached is a patch that sets ActiveQueryDesc to NULL when a
> subtransaction is aborted.
> 
> How do you think?

Attached new patch to fix patch apply failures.

-- 
Regards,

--
Atsushi Torikoshi
NTT DATA CORPORATION

From 5be784278e8e7aeeeadf60a772afccda7b59e6e4 Mon Sep 17 00:00:00 2001
From: Atsushi Torikoshi <[email-masked]>
Date: Mon, 16 May 2022 12:02:16 +0900
Subject: [PATCH v22] Add function to log the plan of the query currently
 running on the backend.

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.

Reviewed-by: Bharath Rupireddy, Fujii Masao, Dilip Kumar,
Masahiro Ikeda, Ekaterina Sokolova, Justin Pryzby, Kyotaro
Horiguchi, Robert Treat
---
 doc/src/sgml/func.sgml                       |  49 +++++++
 src/backend/access/transam/xact.c            |  13 ++
 src/backend/catalog/system_functions.sql     |   2 +
 src/backend/commands/explain.c               | 140 ++++++++++++++++++-
 src/backend/executor/execMain.c              |  10 ++
 src/backend/storage/ipc/procsignal.c         |   4 +
 src/backend/storage/lmgr/lock.c              |   9 +-
 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               |   3 +
 src/include/miscadmin.h                      |   1 +
 src/include/storage/lock.h                   |   2 -
 src/include/storage/procsignal.h             |   1 +
 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, 315 insertions(+), 28 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 85ecc639fd..47f54b978b 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -28027,6 +28027,25 @@ 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.
+        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>
@@ -28141,6 +28160,36 @@ 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'
+</screen>
+    Note that when statements are executed inside a function, only the
+    plan of the most deeply nested query 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 47d80b0d25..13c9d727f7 100644
--- a/src/backend/access/transam/xact.c
+++ b/src/backend/access/transam/xact.c
@@ -60,6 +60,7 @@
 #include "storage/procarray.h"
 #include "storage/sinvaladt.h"
 #include "storage/smgr.h"
+#include "tcop/pquery.h"
 #include "utils/builtins.h"
 #include "utils/catcache.h"
 #include "utils/combocid.h"
@@ -2739,6 +2740,12 @@ AbortTransaction(void)
 	 */
 	PG_SETMASK(&UnBlockSig);
 
+	/*
+	 * When ActiveQueryDesc is referenced after abort, some of its elements
+	 * are freed. To avoid accessing them, reset ActiveQueryDesc here.
+	 */
+	ActiveQueryDesc = NULL;
+
 	/*
 	 * check the current transaction state
 	 */
@@ -5060,6 +5067,12 @@ AbortSubTransaction(void)
 	 */
 	PG_SETMASK(&UnBlockSig);
 
+	/*
+	 * When ActiveQueryDesc is referenced after abort, some of its elements
+	 * are freed. To avoid accessing them, reset ActiveQueryDesc here.
+	 */
+	ActiveQueryDesc = NULL;
+
 	/*
 	 * check the current transaction state
 	 */
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 73da687d5d..59e010ea95 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -709,6 +709,8 @@ REVOKE EXECUTE ON FUNCTION pg_ls_logicalmapdir() FROM PUBLIC;
 
 REVOKE EXECUTE ON FUNCTION pg_ls_replslotdir(text) 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 c461061fe9..339f8810cc 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,8 @@
 #include "parser/parsetree.h"
 #include "rewrite/rewriteHandler.h"
 #include "storage/bufmgr.h"
+#include "storage/procarray.h"
+#include "tcop/pquery.h"
 #include "tcop/tcopprot.h"
 #include "utils/builtins.h"
 #include "utils/guc_tables.h"
@@ -40,7 +43,6 @@
 #include "utils/typcache.h"
 #include "utils/xml.h"
 
-
 /* Hook for plugins to get control in ExplainOneQuery() */
 ExplainOneQuery_hook_type ExplainOneQuery_hook = NULL;
 
@@ -1603,6 +1605,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 singal, 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
@@ -1610,7 +1615,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 &&
@@ -5006,3 +5011,134 @@ escape_yaml(StringInfo buf, const char *str)
 {
 	escape_json(buf, str);
 }
+
+/*
+ * 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 */
+}
+
+/*
+ * ProcessLogQueryPlanInterrupt
+ * 		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 LogQueryPlanPending set.
+ * It is called from CHECK_FOR_INTERRUPTS(), which is enough because the target
+ * process for logging plan is a backend.
+ */
+void
+ProcessLogQueryPlanInterrupt(void)
+{
+	ExplainState *es;
+	HASH_SEQ_STATUS status;
+	LOCALLOCK  *locallock;
+	LogQueryPlanPending = false;
+
+	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));
+		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("ignored request for logging query plan due to lock confilcts"),
+				errdetail("You can try again in a moment."));
+			hash_seq_term(&status);
+			return;
+		}
+	}
+
+	es = NewExplainState();
+
+	es->format = EXPLAIN_FORMAT_TEXT;
+	es->settings = true;
+	es->verbose = true;
+	es->signaled = true;
+
+	ExplainBeginOutput(es);
+	ExplainQueryText(es, ActiveQueryDesc);
+	ExplainPrintPlan(es, ActiveQueryDesc);
+	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);
+	PGPROC	   *proc;
+
+	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);
+	}
+
+	if (SendProcSignal(pid, PROCSIG_LOG_QUERY_PLAN, 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);
+}
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index ef2fd46092..a82ac87457 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -77,6 +77,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);
@@ -301,10 +304,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 21a9fc0fdd..0b4a591ee7 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"
@@ -657,6 +658,9 @@ procsignal_sigusr1_handler(SIGNAL_ARGS)
 	if (CheckProcSignal(PROCSIG_LOG_MEMORY_CONTEXT))
 		HandleLogMemoryContextInterrupt();
 
+	if (CheckProcSignal(PROCSIG_LOG_QUERY_PLAN))
+		HandleLogQueryPlanInterrupt();
+
 	if (CheckProcSignal(PROCSIG_RECOVERY_CONFLICT_DATABASE))
 		RecoveryConflictInterrupt(PROCSIG_RECOVERY_CONFLICT_DATABASE);
 
diff --git a/src/backend/storage/lmgr/lock.c b/src/backend/storage/lmgr/lock.c
index 5f5803f681..9beb456f78 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 8b6b5bbaaa..0afa690c53 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 "common/pg_prng.h"
 #include "jit/jit.h"
@@ -3387,6 +3388,9 @@ ProcessInterrupts(void)
 
 	if (LogMemoryContextPending)
 		ProcessLogMemoryContextInterrupt();
+
+	if (LogQueryPlanPending)
+		ProcessLogQueryPlanInterrupt();
 }

diff --git a/src/backend/utils/init/globals.c b/src/backend/utils/init/globals.c
index 1a5d29ac9b..ab2fdf80fd 100644
--- a/src/backend/utils/init/globals.c
+++ b/src/backend/utils/init/globals.c
@@ -37,6 +37,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 babe16f00a..45ba793681 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8093,6 +8093,12 @@
   prorettype => 'bool', proargtypes => 'int4',
   prosrc => 'pg_log_backend_memory_contexts' },
 
+# logging plan of the running query on the specified backend
+{ oid => '4550', 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 666977fb1f..a59abbf53d 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		signaled;		/* whether explain is called by signal */
 } 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 HandleLogQueryPlanInterrupt(void);
+extern void ProcessLogQueryPlanInterrupt(void);
 #endif							/* EXPLAIN_H */
diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h
index 0af130fbc5..0ab8a14fee 100644
--- a/src/include/miscadmin.h
+++ b/src/include/miscadmin.h
@@ -95,6 +95,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/lock.h b/src/include/storage/lock.h
index e4e1495b24..ae89a104ef 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 ee636900f3..dc8fd66466 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_QUERY_PLAN, /* ask backend to log plan of the current query */
 
 	/* Recovery conflict reasons */
 	PROCSIG_RECOVERY_CONFLICT_DATABASE,
diff --git a/src/include/tcop/pquery.h b/src/include/tcop/pquery.h
index f9a6882ecb..227d24b9d6 100644
--- a/src/include/tcop/pquery.h
+++ b/src/include/tcop/pquery.h
@@ -21,6 +21,8 @@ struct PlannedStmt;				/* avoid including plannodes.h here */

 extern PGDLLIMPORT Portal ActivePortal;
+extern PGDLLIMPORT QueryDesc *ActiveQueryDesc;
+extern PGDLLIMPORT QueryDesc *save_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 01d1ad0b9a..c726c119b2 100644
--- a/src/test/regress/expected/misc_functions.out
+++ b/src/test/regress/expected/misc_functions.out
@@ -276,14 +276,15 @@ SELECT test_canonicalize_path('./abc/./def/.././ghi/../../../jkl/mno');
  ../jkl/mno
 (1 row)
 
+-- Test logging functions
 --
--- pg_log_backend_memory_contexts()
---
--- 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()
 SELECT pg_log_backend_memory_contexts(pg_backend_pid());
  pg_log_backend_memory_contexts 
 --------------------------------
@@ -297,8 +298,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 
 ------------------------
@@ -306,15 +307,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 
 --------------------------------
@@ -323,8 +324,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 072fc36a1f..9f0bb8f813 100644
--- a/src/test/regress/sql/misc_functions.sql
+++ b/src/test/regress/sql/misc_functions.sql
@@ -68,39 +68,60 @@ SELECT test_canonicalize_path('./abc/./def/.');
 SELECT test_canonicalize_path('./abc/././def/.');
 SELECT test_canonicalize_path('./abc/./def/.././ghi/../../../jkl/mno');
 
+-- Test logging functions
 --
--- pg_log_backend_memory_contexts()
---
--- 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()
+
 SELECT pg_log_backend_memory_contexts(pg_backend_pid());
 
 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;
 
 --
 -- Test some built-in SRFs

base-commit: 5bcc4d09332844ae369bcf99f18ace1c982b7301
-- 
2.27.0

---

From: torikoshia <[email-masked]>
Date: Thu, 08 Sep 2022 19:19:09 +0900

>> subtransaction is aborted.
Attached new patch to fix patch apply failures again.

-- 
Regards,

--
Atsushi Torikoshi
NTT DATA CORPORATION

From 5be784278e8e7aeeeadf60a772afccda7b59e6e4 Mon Sep 17 00:00:00 2001
From: Atsushi Torikoshi <[email-masked]>
Date: Mon, 5 Sep 2022 12:02:16 +0900
Subject: [PATCH v23] Add function to log the plan of the query currently
 running on the backend.

---

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 67eb380632..0e12f73b9c 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -25535,6 +25535,25 @@ SELECT collation for ('foo' COLLATE "de_DE");
        </para></entry>
       </row>
 
+      <row>
@@ -25649,6 +25668,36 @@ LOG:  Grand total: 1651920 bytes in 201 blocks; 622360 free (88 chunks); 1029560
 
+   <para>
 
index 50f092d7eb..8a286f7559 100644
 
+	/*
@@ -5072,6 +5079,12 @@ AbortSubTransaction(void)
 	 */
 	PG_SETMASK(&UnBlockSig);
 
+	/*
+	 * When ActiveQueryDesc is referenced after abort, some of its elements
+	 * are freed. To avoid accessing them, reset ActiveQueryDesc here.
+	 */
+	ActiveQueryDesc = NULL;
+
 	/*
 	 * check the current transaction state
 	 */
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 30a048f6b0..e347e6be90 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -713,6 +713,8 @@ REVOKE EXECUTE ON FUNCTION pg_ls_logicalmapdir() FROM PUBLIC;

index 053d2ca5ae..9a361e87ac 100644
 
-
 
@@ -1625,6 +1627,9 @@ ExplainNode(PlanState *planstate, List *ancestors,
 	/*
@@ -1632,7 +1637,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 
 	if (es->analyze &&
@@ -5041,3 +5046,134 @@ escape_yaml(StringInfo buf, const char *str)
 {
 	escape_json(buf, str);
 }
+
+/*

 void

 
 
 /*
index 7bec4e4ff5..cb704d3e40 100644
@@ -33,6 +33,7 @@
@@ -3377,6 +3378,9 @@ ProcessInterrupts(void)

 /*
index a07e737a33..9a09d77f93 100644
@@ -8101,6 +8101,12 @@
 
index 9ebde089ae..fc9f9f8e3f 100644
 
 /* Hook for plugins to get control in ExplainOneQuery() */
@@ -125,4 +126,6 @@ extern void ExplainOpenGroup(const char *objtype, const char *labelname,
 extern void ExplainCloseGroup(const char *objtype, const char *labelname,
 							  bool labeled, ExplainState *es);
 
index 65cf4ba50f..fb57da0d19 100644

index 9f106c2a10..62196d0a39 100644

 

 RESET ROLE;
index 639e9b352c..43b78a23fc 100644

 

 

 

base-commit: 6bcda4a72123c3aa29fa3f03d952095675ad4468
-- 
2.27.0

---

From: Алена Рыбакина<[email-masked]>
Date: Mon, 19 Sep 2022 11:47:21 +0300

Hi,

I'm sorry,if this message is duplicated previous this one, but I'm not sure that the previous message is sent correctly. I sent it from email address [email-masked] and I couldn't send this one email from those address.

I like idea to create patch for logging query plan. After reviewing this code and notice some moments and I'd rather ask you some questions.

Firstly, I suggest some editing in the comment of commit. I think, it is turned out the more laconic and the same clear. I wrote it below since I can't think of any other way to add it.

```
Currently, we have to wait for finishing of the query execution to check its plan.
This is not so convenient in investigation long-running queries on production
environments where we cannot use debuggers.

To improve this situation there is proposed the patch containing the pg_log_query_plan()
function which request to log plan of the specified backend process.

By default, only superusers are allowed to request log of the plan otherwise
allowing any users to issue this request could create cause lots of log messages
and it can lead to denial of service.

At the next requesting CHECK_FOR_INTERRUPTS(), the target backend logs its plan at
LOG_SERVER_ONLY level and therefore this plan will appear in the server log only,
not to be sent to the client.
```

Secondly, I have question about deleting USE_ASSERT_CHECKING in lock.h?
It supposed to have been checked in another placed of the code by matching values. I am worry about skipping errors due to untesting with assert option in the places where it (GetLockMethodLocalHash) participates and we won't able to get core file in segfault cases. I might not understand something, then can you please explain to me?

Thirdly, I have incomprehension of the point why save_ActiveQueryDesc is declared in the pquery.h? I am seemed to save_ActiveQueryDesc to be used in an once time in the ExecutorRun function and  its declaration superfluous. I added it in the attached patch.

Fourthly, it seems to me there are not enough explanatory comments in the code. I also added them in the attached patch.

Lastly, I have incomprehension about handling signals since have been unused it before. Could another signal disabled calling this signal to log query plan? I noticed this signal to be checked the latest in procsignal_sigusr1_handler function.

--

Regards,

Alena Rybakina
Postgres Professional

19.09.2022, 11:01, "torikoshia" <[email-masked]>:

How do you think?

--
Regards,

--
Atsushi Torikoshi
NTT DATA CORPORATION

---

From: torikoshia <[email-masked]>
Date: Tue, 20 Sep 2022 23:41:57 +0900

On 2022-09-19 17:47, Алена Рыбакина wrote:
Thanks for your review and comments!

> Hi,
> 
> I'm sorry,if this message is duplicated previous this one, but I'm not
> sure that the previous message is sent correctly. I sent it from email
> address [email-masked] and I couldn't send this one email
> from those address.

I've successfully received your mail from both [email-masked] 
and [email-masked].

> I like idea to create patch for logging query plan. After reviewing
> this code and notice some moments and I'd rather ask you some
> questions.
> 
> Firstly, I suggest some editing in the comment of commit. I think, it 
> is
> turned out the more laconic and the same clear. I wrote it below since 
> I
> can't think of any other way to add it.

> ```
> Currently, we have to wait for finishing of the query execution to 
> check
> its plan.

> not to be sent to the client.
Thanks, I have incorporated your comments.
Since the latter part of the original message comes from the commit 
message of pg_log_backend_memory_contexts(43620e328617c), so I left it 
as it was for consistency.

> It supposed to have been checked in another placed of the code by
> matching values. I am worry about skipping errors due to untesting with
> assert option in the places where it (GetLockMethodLocalHash)
> participates and we won't able to get core file in segfault cases. I
> might not understand something, then can you please explain to me?
Since GetLockMethodLocalHash() is only used for assertions, this is only 
defined when USE_ASSERT_CHECKING is enabled.
This patch uses GetLockMethodLocalHash() not only for the assertion 
purpose, so I removed "ifdef USE_ASSERT_CHECKING" for this function.
I belive it does not lead to skip errors.

> Thirdly, I have incomprehension of the point why save_ActiveQueryDesc 
> is
> declared in the pquery.h? I am seemed to save_ActiveQueryDesc to be 
> used
> in an once time in the ExecutorRun function and  its declaration
> superfluous. I added it in the attached patch.
Exactly.

> Fourthly, it seems to me there are not enough explanatory comments in
> the code. I also added them in the attached patch.
Thanks!

| +   /*
| +    * Save value of ActiveQueryDesc before having called
| +    * ExecutorRun_hook function due to having reset by
| +    * AbortSubTransaction.
| +    */
| +
|     save_ActiveQueryDesc = ActiveQueryDesc;
|     ActiveQueryDesc = queryDesc;
|
| @@ -314,6 +320,7 @@ ExecutorRun(QueryDesc *queryDesc,
|     else
|         standard_ExecutorRun(queryDesc, direction, count, 
execute_once);
|
| +   /* We set the actual value of ActiveQueryDesc */
|     ActiveQueryDesc = save_ActiveQueryDesc;

Since these processes are needed for nested queries, not only for
AbortSubTransaction[1], added comments on it.

| +/* Function to handle the signal to output the query plan. */
|  extern void HandleLogQueryPlanInterrupt(void);

I feel this comment is unnecessary since the explanation of 
HandleLogQueryPlanInterrupt() is written in explain.c and no functions 
in explain.h have comments in it.

> Lastly, I have incomprehension about handling signals since have been
> unused it before. Could another signal disabled calling this signal to
> log query plan? I noticed this signal to be checked the latest in
> procsignal_sigusr1_handler function.
Are you concerned that one signal will not be processed when multiple 
signals are sent in succession?
AFAIU both of them are processed since SendProcSignal flags 
ps_signalFlags for each signal.

```
  SendProcSignal(pid_t pid, ProcSignalReason reason, BackendId backendId)
  {
      volatile ProcSignalSlot *slot;
...(snip)...
278         if (slot->pss_pid == pid)
279         {
280             /* Atomically set the proper flag */
281             slot->pss_signalFlags[reason] = true;
282             /* Send signal */
283             return kill(pid, SIGUSR1);
```

Comments of ProcSignalReason also say 'We can cope with concurrent 
signals for different reasons'.

```C
  /*
   * Reasons for signaling a Postgres child process (a backend or an 
auxiliary
   * process, like checkpointer).  We can cope with concurrent signals 
for different
   * reasons.  However, if the same reason is signaled multiple times in 
quick
   * succession, the process is likely to observe only one notification 
of it.
   * This is okay for the present uses.
   ...
  typedef enum
  {
      PROCSIG_CATCHUP_INTERRUPT,  /* sinval catchup interrupt */
      PROCSIG_NOTIFY_INTERRUPT,   /* listen/notify interrupt */
      PROCSIG_PARALLEL_MESSAGE,   /* message from cooperating parallel 
backend */
      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 */
...
  } ProcSignalReason;
```

[1] 
https://www.postgresql.org/message-id/8b53b32f-26cc-0531-4ac0-27310e0bef4b%40oss.nttdata.com

-- 
Regards,

--
Atsushi Torikoshi
NTT DATA CORPORATION

From a0d2179826a0fa224eaf37ca00d14954b76fde6b Mon Sep 17 00:00:00 2001
From: Atsushi Torikoshi <[email-masked]>
Date: Tue, 20 Sep 2022 21:52:41 +0900
Subject: [PATCH v24] Add function to log the plan of the query
currently running on the backend.

Horiguchi, Robert Treat, Alena Rybakina
 src/backend/executor/execMain.c              |  14 ++
 src/include/tcop/pquery.h                    |   2 +-
 17 files changed, 318 insertions(+), 29 deletions(-)

index e1fe4fec57..bd80d062c1 100644
 
+      <row>
 
+   <para>
 
index 2bb975943c..fb698ac007 100644
 
+	/*
 
+	/*

 
-
 
+	 * executed and explain has been called by signal, as the target query
 
 	if (es->analyze &&
index d78862e660..1ce107a069 100644
 
@@ -301,10 +304,21 @@ ExecutorRun(QueryDesc *queryDesc,
+	 * Update ActiveQueryDesc here to enable retrieval of the currently
+	 * running queryDesc for nested queries.
 
 void

 
 
 /*
index 35eff28bd3..053d460ab2 100644

 /*

 
index ee48e392ed..960366bf93 100644

 	/* Recovery conflict reasons */
 	PROCSIG_RECOVERY_CONFLICT_DATABASE,
diff --git a/src/include/tcop/pquery.h b/src/include/tcop/pquery.h
index f9a6882ecb..4a0e94ab1a 100644
--- a/src/include/tcop/pquery.h
+++ b/src/include/tcop/pquery.h
@@ -21,7 +21,7 @@ struct PlannedStmt;				/* avoid including plannodes.h here */

 

 

 RESET ROLE;

---

From: Alena Rybakina <[email-masked]>
Date: Wed, 21 Sep 2022 11:30:25 +0300

Ok, I get it.

> Since GetLockMethodLocalHash() is only used for assertions, this is 
> only defined when USE_ASSERT_CHECKING is enabled. This patch uses 
> GetLockMethodLocalHash() not only for the assertion purpose, so I 
> removed "ifdef USE_ASSERT_CHECKING" for this function. I belive it 
> does not lead to skip errors. 
Agree.
> AbortSubTransaction[1], added comments on it.

I also noticed it. However I also discovered that above function 
declarations to be aplied for explain command and used to be printed 
details of the executed query.

We have a similar task to print the plan of an interrupted process 
making a request for a specific pid.

In short, I think, this task is different and for separating these parts 
I added this comment.

> in explain.h have comments in it. 

Yes, I was worried about it. I understood it, thank for explaining.

> * process, like checkpointer). We can cope with concurrent signals for 
> different
> PROCSIG_PARALLEL_MESSAGE, /* message from cooperating parallel backend */

---

From: torikoshia <[email-masked]>
Date: Thu, 22 Sep 2022 23:12:16 +0900

On 2022-09-21 17:30, Alena Rybakina wrote:

Thanks for your reply!

> In short, I think, this task is different and for separating these
> parts I added this comment.

I'm not sure I understand your comment correctly, do you mean 
HandleLogQueryPlanInterrupt() should not be placed in explain.c?

It may be so.
However, given that ProcesLogMemoryContextInterrupt(), which similarly 
handles interrupts for pg_log_backend_memory_contexts(), is located in 
mcxt.c, I also think current location might be acceptable.

-- 
Regards,

--
Atsushi Torikoshi
NTT DATA CORPORATION

---

From: Alena Rybakina <[email-masked]>
Date: Fri, 23 Sep 2022 12:43:19 +0300

Sorry, I wrote confusingly at that time.

No, I suggested adding comment about the explanation of 
HandleLogQueryPlanInterrupt() only in the explain.h and not removing 
from the explain.c.

I seemed to be necessary separating declaration function for 'explaining 
feature' of executed query from our logging plan of the running query 
interrupts function. But now, I doubt it.

> HandleLogQueryPlanInterrupt() should not be placed in explain.c? 

Thank you for having reminded about this function and I looked at 
ProcessLogMemoryContextInterrupt() declaration. I'm noticed comments in 
the memutils.h are missed tooю

Description of this function is written only in mcxt.c.
> mcxt.c, I also think current location might be acceptable. 

So I think you are right and the comment about the explanation of 
HandleLogQueryPlanInterrupt() written in explain.h is redundant.

Regards,

-- 
Alena Rybakina
Postgres Professional

---

From: Andres Freund <[email-masked]>
Date: Tue, 6 Dec 2022 10:41:01 -0800

Hi,

This patch does not currently build, due to a conflicting oid:

https://cirrus-ci.com/task/4638460594618368?logs=build#L108
[17:26:44.602] Duplicate OIDs detected:
[17:26:44.602] 4550
[17:26:44.602] found 1 duplicate OID(s) in catalog data

I suggest you choose a random oid out of the "development purposes" range:

 *		OIDs 1-9999 are reserved for manual assignment (see .dat files in
 *		src/include/catalog/).  Of these, 8000-9999 are reserved for
 *		development purposes (such as in-progress patches and forks);
 *		they should not appear in released versions.

Greetings,

Andres Freund

---

From: torikoshia <[email-masked]>
Date: Thu, 08 Dec 2022 14:10:32 +0900

> I suggest you choose a random oid out of the "development purposes" 

Thanks for your advice!
Attached updated patch.

BTW, since this patch depends on ProcessInterrupts() and EXPLAIN codes 
which is used in auto_explain, I'm feeling that the following discussion 
also applies to this patch.

> -- 
> https://www.postgresql.org/message-id/CA%2BTgmoYW_rSOW4JMQ9_0Df9PKQ%3DsQDOKUGA4Gc9D8w4wui8fSA%40mail.gmail.com
> 
> explaining a query is a pretty
> complicated operation that involves catalog lookups and lots of
> complicated stuff, and I don't think that it would be safe to do all
> of that at any arbitrary point in the code where ProcessInterrupts()
> happened to fire.

If I can't come up with some workaround during the next Commitfest, I'm 
going to withdraw this proposal.

-- 
Regards,

--
Atsushi Torikoshi
NTT DATA CORPORATION

From a0d2179826a0fa224eaf37ca00d14954b76fde6b Mon Sep 17 00:00:00 2001
From: Atsushi Torikoshi <[email-masked]>
Date: Thu, 8 Dec 2022 12:42:22 +0900
Subject: [PATCH v25] Add function to log the plan of the query
currently running on the backend.

---
 src/backend/commands/explain.c               | 139 ++++++++++++++++++-
 17 files changed, 318 insertions(+), 28 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index e57ffce971..d39a4e50a0 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -25536,6 +25536,25 @@ SELECT collation for ('foo' COLLATE "de_DE");
        </para></entry>
       </row>
 
+      <row>
@@ -25756,6 +25775,36 @@ LOG:  Grand total: 1651920 bytes in 201 blocks; 622360 free (88 chunks); 1029560
 
+   <para>
 
index 8086b857b9..08cb48ece1 100644
 
+	/*
@@ -5090,6 +5097,12 @@ AbortSubTransaction(void)
 	 */
 	PG_SETMASK(&UnBlockSig);
 
+	/*
+	 * When ActiveQueryDesc is referenced after abort, some of its elements
+	 * are freed. To avoid accessing them, reset ActiveQueryDesc here.
+	 */
+	ActiveQueryDesc = NULL;
+
 	/*
 	 * check the current transaction state
 	 */
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 52517a6531..5ada291ce6 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -739,6 +739,8 @@ REVOKE EXECUTE ON FUNCTION pg_ls_logicalmapdir() FROM PUBLIC;

index f86983c660..ff46433f05 100644
@@ -1625,6 +1628,9 @@ ExplainNode(PlanState *planstate, List *ancestors,
@@ -1632,7 +1638,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 
 	if (es->analyze &&
@@ -5041,3 +5047,134 @@ escape_yaml(StringInfo buf, const char *str)
 {
 	escape_json(buf, str);
 }
+
+/*
index 872b879387..01d3f22c07 100644
@@ -78,6 +78,9 @@ ExecutorEnd_hook_type ExecutorEnd_hook = NULL;
 /* Hook for plugin to get control in ExecCheckPermissions() */
 
@@ -302,10 +305,21 @@ ExecutorRun(QueryDesc *queryDesc,
 
 void
index 7767657f27..aabb2c3fb6 100644

index 3d1049cf75..35b8891063 100644

 /*
index 3082093d1e..e99b02ca81 100644
@@ -3379,6 +3380,9 @@ ProcessInterrupts(void)

 /*
index 00bceec8fa..87d50de9f8 100644
index f9301b2627..14cc65bb6b 100644
@@ -8135,6 +8135,12 @@
 
+{ oid => '8000', descr => 'log plan of the running query on the specified backend',

index 795182fa51..814d15d1d8 100644

 
 
index 88bb696ded..e43c0f7252 100644

 

 RESET ROLE;
index b07e9e8dbb..3aa6cea941 100644

 

 

 

base-commit: bf07ab492c461460b4a69279abb2ef996b4f67ec
-- 
2.27.0