On Fri, Dec 17, 2021 at 9:32 AM Justin Pryzby <pry...@telsasoft.com> wrote:
> On Fri, Dec 17, 2021 at 09:00:04AM +0530, Dilip Kumar wrote:
> > On Tue, Dec 14, 2021 at 3:57 AM Bossart, Nathan <bossa...@amazon.com>
> wrote:
> > >
> > > On 12/13/21, 6:30 AM, "Dilip Kumar" <dilipbal...@gmail.com> wrote:
> > > > On Tue, Dec 7, 2021 at 11:11 AM Justin Pryzby <pry...@telsasoft.com>
> wrote:
> > > >> 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
> > >
> > > The general approach looks good to me. I think we could have just one
> > > function for all three values, though.
> >
> > If we create just one function then the output type will be a tuple
> > then we might have to add another view on top of that. Is there any
> > better way to do that?
>
> I don't think you'd need to add a view on top of it.
>
> Compare:
>
> postgres=# SELECT 1, pg_config() LIMIT 1;
> ?column? | pg_config
> ----------+----------------------------
> 1 | (BINDIR,/usr/pgsql-14/bin)
>
> postgres=# SELECT 1, c FROM pg_config() c LIMIT 1;
> ?column? | c
> ----------+----------------------------
> 1 | (BINDIR,/usr/pgsql-14/bin)
>
> postgres=# SELECT 1, c.* FROM pg_config() c LIMIT 1;
> ?column? | name | setting
> ----------+--------+-------------------
> 1 | BINDIR | /usr/pgsql-14/bin
>
Okay, that makes sense, I have modified it to make a single function.
--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com
From 5e5dd3c6a4056b1ef821fa5d7dccf5044f1fa48c Mon Sep 17 00:00:00 2001
From: Dilip Kumar <dilipkumar@localhost.localdomain>
Date: Sun, 12 Dec 2021 17:10:55 +0530
Subject: [PATCH v3] 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..8713c88 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 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
