From 5e5dd3c6a4056b1ef821fa5d7dccf5044f1fa48c Mon Sep 17 00:00:00 2001 From: Dilip Kumar 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, + pg_stat_get_backend_subxact + + pg_stat_get_backend_subxact ( integer ) + record + + + Returns a record of information about the backend's subtransactions. + The fields returned are subxact_count identifies + number of active subtransaction and subxact_overflow + shows whether the backend's subtransaction cache is + overflowed or not. + + + + + + + pg_stat_get_backend_userid pg_stat_get_backend_userid ( integer ) 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