On Tue, Dec 7, 2021 at 11:11 AM Justin Pryzby <pry...@telsasoft.com> wrote:
>
> You added this to pg_stat_activity, which already has a lot of fields.
> We talked a few months ago about not adding more fields that weren't commonly
> used.
> https://www.postgresql.org/message-id/flat/20210426191811.sp3o77doinphyjhu%40alap3.anarazel.de#d96d0a116f0344301eead2676ea65b2e
>
> Since I think this field is usually not interesting to most users of
> pg_stat_activity, maybe this should instead be implemented as a function like
> pg_backend_get_subxact_status(pid).
>
> People who want to could use it like:
> SELECT * FROM pg_stat_activity psa, pg_backend_get_subxact_status(pid) sub;
I have provided two function, one for subtransaction counts and other
whether subtransaction cache is overflowed or not, we can use like
this, if we think this is better way to do it then we can also add
another function for the lastOverflowedXid
postgres[43994]=# select id, pg_stat_get_backend_pid(id) as pid,
pg_stat_get_backend_subxact_count(id) as nsubxact,
pg_stat_get_backend_subxact_overflow(id) as overflowed from
pg_stat_get_backend_idset() as id;
id | pid | nsubxact | overflowed
----+-------+----------+------------
1 | 43806 | 0 | f
2 | 43983 | 64 | t
3 | 43994 | 0 | f
4 | 44323 | 22 | f
5 | 43802 | 0 | f
6 | 43801 | 0 | f
7 | 43804 | 0 | f
(7 rows)
--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com
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
