v2-0001-Add-functions-to-show-subtransaction-count-and-ov.patch

From 8a9aa7ba753f8ae77651861629266b86b276bf11 Mon Sep 17 00:00:00 2001
From: Dilip Kumar <dilipkumar@localhost.localdomain>
Date: Sun, 12 Dec 2021 17:10:55 +0530
Subject: [PATCH v2] Add functions to show subtransaction count and overflow
 status

If there are some backends having a lot of nested subtransaction
or the subtransaction cache is overflowed there is a no way to
detect that.  So this patch is making that easy by providing
functions to get that information.
---
 doc/src/sgml/monitoring.sgml                | 27 +++++++++++++++++++++++++++
 src/backend/storage/ipc/sinvaladt.c         | 13 +++++++++----
 src/backend/utils/activity/backend_status.c |  4 +++-
 src/backend/utils/adt/pgstatfuncs.c         | 23 +++++++++++++++++++++++
 src/include/catalog/pg_proc.dat             |  8 ++++++++
 src/include/storage/sinvaladt.h             |  4 +++-
 src/include/utils/backend_status.h          | 11 +++++++++++
 7 files changed, 84 insertions(+), 6 deletions(-)

diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 62f2a33..77c6cfc 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5482,6 +5482,33 @@ SELECT pg_stat_get_backend_pid(s.backendid) AS pid,
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
+         <primary>pg_stat_get_backend_subxact_count</primary>
+        </indexterm>
+        <function>pg_stat_get_backend_subxact_count</function> ( <type>integer</type> )
+        <returnvalue>integer</returnvalue>
+       </para>
+       <para>
+        Returns the number of cached subtransactions of this backend.
+       </para></entry>
+      </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_stat_get_backend_subxact_overflow</primary>
+        </indexterm>
+        <function>pg_stat_get_backend_subxact_overflow</function> ( <type>integer</type> )
+        <returnvalue>boolean</returnvalue>
+       </para>
+       <para>
+        Returns true if the number of active subtransactions crossed per
+        backend subtransaction cache limit, false otherwise.
+       </para></entry>
+      </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
          <primary>pg_stat_get_backend_userid</primary>
         </indexterm>
         <function>pg_stat_get_backend_userid</function> ( <type>integer</type> )
diff --git a/src/backend/storage/ipc/sinvaladt.c b/src/backend/storage/ipc/sinvaladt.c
index 946bd8e..876d7fe 100644
--- a/src/backend/storage/ipc/sinvaladt.c
+++ b/src/backend/storage/ipc/sinvaladt.c
@@ -395,17 +395,20 @@ BackendIdGetProc(int backendID)
 
 /*
  * BackendIdGetTransactionIds
- *		Get the xid and xmin of the backend. The result may be out of date
- *		arbitrarily quickly, so the caller must be careful about how this
- *		information is used.
+ *		Get the xid and xmin, nsubxid and overflow status of the backend. The
+ *		result may be out of date arbitrarily quickly, so the caller must be
+ *		careful about how this information is used.
  */
 void
-BackendIdGetTransactionIds(int backendID, TransactionId *xid, TransactionId *xmin)
+BackendIdGetTransactionIds(int backendID, TransactionId *xid,
+						   TransactionId *xmin, int *nsubxid, bool *overflowed)
 {
 	SISeg	   *segP = shmInvalBuffer;
 
 	*xid = InvalidTransactionId;
 	*xmin = InvalidTransactionId;
+	*nsubxid = 0;
+	*overflowed = false;
 
 	/* Need to lock out additions/removals of backends */
 	LWLockAcquire(SInvalWriteLock, LW_SHARED);
@@ -419,6 +422,8 @@ BackendIdGetTransactionIds(int backendID, TransactionId *xid, TransactionId *xmi
 		{
 			*xid = proc->xid;
 			*xmin = proc->xmin;
+			*nsubxid = proc->subxidStatus.count;
+			*overflowed = proc->subxidStatus.overflowed;
 		}
 	}
 
diff --git a/src/backend/utils/activity/backend_status.c b/src/backend/utils/activity/backend_status.c
index 7229598..9c904be 100644
--- a/src/backend/utils/activity/backend_status.c
+++ b/src/backend/utils/activity/backend_status.c
@@ -848,7 +848,9 @@ pgstat_read_current_status(void)
 		{
 			BackendIdGetTransactionIds(i,
 									   &localentry->backend_xid,
-									   &localentry->backend_xmin);
+									   &localentry->backend_xmin,
+									   &localentry->subxact_count,
+									   &localentry->subxact_overflowed);
 
 			localentry++;
 			localappname += NAMEDATALEN;
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index f529c15..0c8bf4a 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1005,6 +1005,29 @@ pg_stat_get_backend_userid(PG_FUNCTION_ARGS)
 	PG_RETURN_OID(beentry->st_userid);
 }
 
+Datum
+pg_stat_get_backend_subxact_count(PG_FUNCTION_ARGS)
+{
+	int32		beid = PG_GETARG_INT32(0);
+	LocalPgBackendStatus *local_beentry;
+
+	if ((local_beentry = pgstat_fetch_stat_local_beentry(beid)) == NULL)
+		PG_RETURN_NULL();
+
+	PG_RETURN_INT32(local_beentry->subxact_count);
+}
+
+Datum
+pg_stat_get_backend_subxact_overflow(PG_FUNCTION_ARGS)
+{
+	int32		beid = PG_GETARG_INT32(0);
+	LocalPgBackendStatus *local_beentry;
+
+	if ((local_beentry = pgstat_fetch_stat_local_beentry(beid)) == NULL)
+		PG_RETURN_NULL();
+
+	PG_RETURN_INT32(local_beentry->subxact_overflowed);
+}
 
 Datum
 pg_stat_get_backend_activity(PG_FUNCTION_ARGS)
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 79d787c..4da1bca 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5416,6 +5416,14 @@
   proname => 'pg_stat_get_backend_dbid', provolatile => 's', proparallel => 'r',
   prorettype => 'oid', proargtypes => 'int4',
   prosrc => 'pg_stat_get_backend_dbid' },
+{ oid => '6107', descr => 'statistics: cached subtransaction count of backend',
+  proname => 'pg_stat_get_backend_subxact_count', provolatile => 's', proparallel => 'r',
+  prorettype => 'int4', proargtypes => 'int4',
+  prosrc => 'pg_stat_get_backend_subxact_count' },
+{ oid => '6108', descr => 'statistics: subtransaction cache of backend overflowed',
+  proname => 'pg_stat_get_backend_subxact_overflow', provolatile => 's', proparallel => 'r',
+  prorettype => 'bool', proargtypes => 'int4',
+  prosrc => 'pg_stat_get_backend_subxact_overflow' },
 { oid => '1939', descr => 'statistics: user ID of backend',
   proname => 'pg_stat_get_backend_userid', provolatile => 's',
   proparallel => 'r', prorettype => 'oid', proargtypes => 'int4',
diff --git a/src/include/storage/sinvaladt.h b/src/include/storage/sinvaladt.h
index 14148bf..a342da8 100644
--- a/src/include/storage/sinvaladt.h
+++ b/src/include/storage/sinvaladt.h
@@ -32,7 +32,9 @@ extern Size SInvalShmemSize(void);
 extern void CreateSharedInvalidationState(void);
 extern void SharedInvalBackendInit(bool sendOnly);
 extern PGPROC *BackendIdGetProc(int backendID);
-extern void BackendIdGetTransactionIds(int backendID, TransactionId *xid, TransactionId *xmin);
+extern void BackendIdGetTransactionIds(int backendID, TransactionId *xid,
+									   TransactionId *xmin, int *nsubxid,
+									   bool *overflowed);
 
 extern void SIInsertDataEntries(const SharedInvalidationMessage *data, int n);
 extern int	SIGetDataEntries(SharedInvalidationMessage *data, int datasize);
diff --git a/src/include/utils/backend_status.h b/src/include/utils/backend_status.h
index 8042b81..36ecf33 100644
--- a/src/include/utils/backend_status.h
+++ b/src/include/utils/backend_status.h
@@ -258,6 +258,17 @@ typedef struct LocalPgBackendStatus
 	 * not.
 	 */
 	TransactionId backend_xmin;
+
+	/*
+	 * Number of cached subtransactions in the current session.
+	 */
+	int	subxact_count;
+
+	/*
+	 * The number of subtransactions in the current session exceeded the cached
+	 * subtransaction limit.
+	 */
+	bool subxact_overflowed;
 } LocalPgBackendStatus;
 
 
-- 
1.8.3.1