On Tue, Dec 14, 2021 at 6:23 PM Ashutosh Sharma <ashu.coe...@gmail.com>
wrote:
In the latest patch I have fixed comments given here except a few.
I have looked into the v2 patch and here are my comments:
+ PG_RETURN_INT32(local_beentry->subxact_overflowed);
+}
Should this be PG_RETURN_BOOL instead of PG_RETURN_INT32??
With the new patch this is not relevant because we are returning a tuple.
+{ 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' },
The description says that the two new functions show the statistics for
"cached subtransaction count of backend" and "subtransaction cache of
backend overflowed". But, when these functions are called it shows these
stats for the non-backend processes like checkpointer, walwriter etc as
well. Should that happen?
I am following similar description as pg_stat_get_backend_pid,
pg_stat_get_backend_idset and other relevant functioins.
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;
All the variables inside this LocalPgBackendStatus structure are prefixed
with "backend" word. Can we do the same for the newly added variables as
well?
Done
+ * Get the xid and xmin, nsubxid and overflow status of the backend.
The
Should this be put as - "xid, xmin, nsubxid and overflow" instead of "xid
and xmin, nsubxid and overflow"?
I missed to fix this one in the last patch so updated again.
--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com
From e7dd74b5171327109a3c1ecdb48c104e93630b2b Mon Sep 17 00:00:00 2001
From: Dilip Kumar <dilipkumar@localhost.localdomain>
Date: Sun, 12 Dec 2021 17:10:55 +0530
Subject: [PATCH v4] 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
function to get that information.
---
doc/src/sgml/monitoring.sgml | 18 ++++++++++++++
src/backend/storage/ipc/sinvaladt.c | 13 +++++++---
src/backend/utils/activity/backend_status.c | 4 ++-
src/backend/utils/adt/pgstatfuncs.c | 38 +++++++++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 7 ++++++
src/include/storage/sinvaladt.h | 4 ++-
src/include/utils/backend_status.h | 11 +++++++++
7 files changed, 89 insertions(+), 6 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 62f2a33..4388151 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5482,6 +5482,24 @@ 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</primary>
+ </indexterm>
+ <function>pg_stat_get_backend_subxact</function> ( <type>integer</type> )
+ <returnvalue>record</returnvalue>
+ </para>
+ <para>
+ Returns a record of information about the backend's subtransactions.
+ The fields returned are <parameter>subxact_count</parameter> identifies
+ number of active subtransaction and <parameter>subxact_overflow
+ </parameter> shows whether the backend's subtransaction cache is
+ overflowed or not.
+ </para></entry>
+ </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 cb3ee82..0b7b183 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, 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 2fecf26..5349e40 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->backend_subxact_count,
+ &localentry->backend_subxact_overflowed);
localentry++;
localappname += NAMEDATALEN;
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 15cb17a..850dc61 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1005,6 +1005,44 @@ pg_stat_get_backend_userid(PG_FUNCTION_ARGS)
PG_RETURN_OID(beentry->st_userid);
}
+Datum
+pg_stat_get_backend_subxact(PG_FUNCTION_ARGS)
+{
+#define PG_STAT_GET_SUBXACT_COLS 2
+ TupleDesc tupdesc;
+ Datum values[PG_STAT_GET_SUBXACT_COLS];
+ bool nulls[PG_STAT_GET_SUBXACT_COLS];
+ int32 beid = PG_GETARG_INT32(0);
+ LocalPgBackendStatus *local_beentry;
+
+ /* Initialise values and NULL flags arrays */
+ MemSet(values, 0, sizeof(values));
+ MemSet(nulls, 0, sizeof(nulls));
+
+ /* Initialise attributes information in the tuple descriptor */
+ tupdesc = CreateTemplateTupleDesc(PG_STAT_GET_SUBXACT_COLS);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 1, "subxact_count",
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 2, "subxact_overflow",
+ BOOLOID, -1, 0);
+
+ BlessTupleDesc(tupdesc);
+
+ if ((local_beentry = pgstat_fetch_stat_local_beentry(beid)) != NULL)
+ {
+ /* Fill values and NULLs */
+ values[0] = Int32GetDatum(local_beentry->backend_subxact_count);
+ values[1] = BoolGetDatum(local_beentry->backend_subxact_overflowed);
+ }
+ else
+ {
+ nulls[0] = true;
+ nulls[1] = true;
+ }
+
+ /* Returns the record as Datum */
+ PG_RETURN_DATUM(HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls)));
+}
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 d6bf1f3..953af32 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5398,6 +5398,13 @@
proname => 'pg_stat_get_backend_dbid', provolatile => 's', proparallel => 'r',
prorettype => 'oid', proargtypes => 'int4',
prosrc => 'pg_stat_get_backend_dbid' },
+{ oid => '6107', descr => 'statistics: get subtransaction status of backend',
+ proname => 'pg_stat_get_backend_subxact', provolatile => 's', proparallel => 'r',
+ prorettype => 'record', proargtypes => 'int4',
+ proallargtypes => '{int4,int4,bool}',
+ proargmodes => '{i,o,o}',
+ proargnames => '{bid,subxact_count,subxact_overflowed}',
+ prosrc => 'pg_stat_get_backend_subxact' },
{ 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 91e2418..bf739eb 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 8217d0c..01432ae 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 backend_subxact_count;
+
+ /*
+ * The number of subtransactions in the current session exceeded the cached
+ * subtransaction limit.
+ */
+ bool backend_subxact_overflowed;
} LocalPgBackendStatus;
--
1.8.3.1
