Skip to content

Commit dd1a3bc

Browse files
robertmhaasrobertmhaas
committed
Show xid and xmin in pg_stat_activity and pg_stat_replication.
Christian Kruse, reviewed by Andres Freund and myself, with further minor adjustments by me.
1 parent 278c942 commit dd1a3bc

File tree

10 files changed

+149
-20
lines changed

10 files changed

+149
-20
lines changed

‎doc/src/sgml/monitoring.sgml

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -628,6 +628,16 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
628628
</itemizedlist>
629629
</entry>
630630
</row>
631+
<row>
632+
<entry><structfield>backend_xid</structfield></entry>
633+
<entry><type>xid</type></entry>
634+
<entry>Toplevel transaction identifier of this backend, if any.</entry>
635+
</row>
636+
<row>
637+
<entry><structfield>backend_xmin</structfield></entry>
638+
<entry><type>xid</type></entry>
639+
<entry>The current backend's <literal>xmin</> horizon.</entry>
640+
</row>
631641
<row>
632642
<entry><structfield>query</></entry>
633643
<entry><type>text</></entry>
@@ -1483,6 +1493,12 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
14831493
client connected to this WAL sender
14841494
</entry>
14851495
</row>
1496+
<row>
1497+
<entry><structfield>backend_xid</structfield></entry>
1498+
<entry><type>xid</type></entry>
1499+
<entry>This standby's <literal>xmin</> horizon reported
1500+
by <xref linkend="guc-hot-standby-feedback">.</entry>
1501+
</row>
14861502
<row>
14871503
<entry><structfield>state</></entry>
14881504
<entry><type>text</></entry>

‎src/backend/catalog/system_views.sql

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -586,6 +586,8 @@ CREATE VIEW pg_stat_activity AS
586586
S.state_change,
587587
S.waiting,
588588
S.state,
589+
S.backend_xid,
590+
s.backend_xmin,
589591
S.query
590592
FROM pg_database D, pg_stat_get_activity(NULL) AS S, pg_authid U
591593
WHERE S.datid = D.oid AND
@@ -601,6 +603,7 @@ CREATE VIEW pg_stat_replication AS
601603
S.client_hostname,
602604
S.client_port,
603605
S.backend_start,
606+
S.backend_xmin,
604607
W.state,
605608
W.sent_location,
606609
W.write_location,

‎src/backend/postmaster/pgstat.c

Lines changed: 39 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -48,12 +48,14 @@
4848
#include "postmaster/autovacuum.h"
4949
#include "postmaster/fork_process.h"
5050
#include "postmaster/postmaster.h"
51+
#include "storage/proc.h"
5152
#include "storage/backendid.h"
5253
#include "storage/fd.h"
5354
#include "storage/ipc.h"
5455
#include "storage/latch.h"
5556
#include "storage/pg_shmem.h"
5657
#include "storage/procsignal.h"
58+
#include "storage/sinvaladt.h"
5759
#include "utils/ascii.h"
5860
#include "utils/guc.h"
5961
#include "utils/memutils.h"
@@ -213,7 +215,7 @@ typedef struct TwoPhasePgStatRecord
213215
*/
214216
static MemoryContext pgStatLocalContext = NULL;
215217
static HTAB *pgStatDBHash = NULL;
216-
static PgBackendStatus *localBackendStatusTable = NULL;
218+
static LocalPgBackendStatus *localBackendStatusTable = NULL;
217219
static int localNumBackends = 0;
218220

219221
/*
@@ -2303,6 +2305,28 @@ pgstat_fetch_stat_beentry(int beid)
23032305
{
23042306
pgstat_read_current_status();
23052307

2308+
if (beid < 1 || beid > localNumBackends)
2309+
return NULL;
2310+
2311+
return &localBackendStatusTable[beid - 1].backendStatus;
2312+
}
2313+
2314+
2315+
/* ----------
2316+
* pgstat_fetch_stat_local_beentry() -
2317+
*
2318+
* Like pgstat_fetch_stat_beentry() but with locally computed addtions (like
2319+
* xid and xmin values of the backend)
2320+
*
2321+
* NB: caller is responsible for a check if the user is permitted to see
2322+
* this info (especially the querystring).
2323+
* ----------
2324+
*/
2325+
LocalPgBackendStatus *
2326+
pgstat_fetch_stat_local_beentry(int beid)
2327+
{
2328+
pgstat_read_current_status();
2329+
23062330
if (beid < 1 || beid > localNumBackends)
23072331
return NULL;
23082332

@@ -2783,8 +2807,8 @@ static void
27832807
pgstat_read_current_status(void)
27842808
{
27852809
volatile PgBackendStatus *beentry;
2786-
PgBackendStatus *localtable;
2787-
PgBackendStatus *localentry;
2810+
LocalPgBackendStatus *localtable;
2811+
LocalPgBackendStatus *localentry;
27882812
char *localappname,
27892813
*localactivity;
27902814
int i;
@@ -2795,9 +2819,9 @@ pgstat_read_current_status(void)
27952819

27962820
pgstat_setup_memcxt();
27972821

2798-
localtable = (PgBackendStatus *)
2822+
localtable = (LocalPgBackendStatus *)
27992823
MemoryContextAlloc(pgStatLocalContext,
2800-
sizeof(PgBackendStatus) * MaxBackends);
2824+
sizeof(LocalPgBackendStatus) * MaxBackends);
28012825
localappname = (char *)
28022826
MemoryContextAlloc(pgStatLocalContext,
28032827
NAMEDATALEN * MaxBackends);
@@ -2821,19 +2845,19 @@ pgstat_read_current_status(void)
28212845
{
28222846
int save_changecount = beentry->st_changecount;
28232847

2824-
localentry->st_procpid = beentry->st_procpid;
2825-
if (localentry->st_procpid > 0)
2848+
localentry->backendStatus.st_procpid = beentry->st_procpid;
2849+
if (localentry->backendStatus.st_procpid > 0)
28262850
{
2827-
memcpy(localentry, (char *) beentry, sizeof(PgBackendStatus));
2851+
memcpy(&localentry->backendStatus, (char *) beentry, sizeof(PgBackendStatus));
28282852

28292853
/*
28302854
* strcpy is safe even if the string is modified concurrently,
28312855
* because there's always a \0 at the end of the buffer.
28322856
*/
28332857
strcpy(localappname, (char *) beentry->st_appname);
2834-
localentry->st_appname = localappname;
2858+
localentry->backendStatus.st_appname = localappname;
28352859
strcpy(localactivity, (char *) beentry->st_activity);
2836-
localentry->st_activity = localactivity;
2860+
localentry->backendStatus.st_activity = localactivity;
28372861
}
28382862

28392863
if (save_changecount == beentry->st_changecount &&
@@ -2846,8 +2870,12 @@ pgstat_read_current_status(void)
28462870

28472871
beentry++;
28482872
/* Only valid entries get included into the local array */
2849-
if (localentry->st_procpid > 0)
2873+
if (localentry->backendStatus.st_procpid > 0)
28502874
{
2875+
BackendIdGetTransactionIds(i,
2876+
&localentry->backend_xid,
2877+
&localentry->backend_xmin);
2878+
28512879
localentry++;
28522880
localappname += NAMEDATALEN;
28532881
localactivity += pgstat_track_activity_query_size;

‎src/backend/storage/ipc/sinvaladt.c

Lines changed: 32 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -25,6 +25,7 @@
2525
#include "storage/shmem.h"
2626
#include "storage/sinvaladt.h"
2727
#include "storage/spin.h"
28+
#include "access/transam.h"
2829

2930

3031
/*
@@ -400,6 +401,37 @@ BackendIdGetProc(int backendID)
400401
return result;
401402
}
402403

404+
/*
405+
* BackendIdGetTransactionIds
406+
* Get the xid and xmin of the backend. The result may be out of date
407+
* arbitrarily quickly, so the caller must be careful about how this
408+
* information is used.
409+
*/
410+
void
411+
BackendIdGetTransactionIds(int backendID, TransactionId *xid, TransactionId *xmin)
412+
{
413+
ProcState *stateP;
414+
SISeg *segP = shmInvalBuffer;
415+
PGXACT *xact;
416+
417+
*xid = InvalidTransactionId;
418+
*xmin = InvalidTransactionId;
419+
420+
/* Need to lock out additions/removals of backends */
421+
LWLockAcquire(SInvalWriteLock, LW_SHARED);
422+
423+
if (backendID > 0 && backendID <= segP->lastBackend)
424+
{
425+
stateP = &segP->procState[backendID - 1];
426+
xact = &ProcGlobal->allPgXact[stateP->proc->pgprocno];
427+
428+
*xid = xact->xid;
429+
*xmin = xact->xmin;
430+
}
431+
432+
LWLockRelease(SInvalWriteLock);
433+
}
434+
403435
/*
404436
* SIInsertDataEntries
405437
* Add new invalidation message(s) to the buffer.

‎src/backend/utils/adt/pgstatfuncs.c

Lines changed: 22 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -536,7 +536,7 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
536536

537537
oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
538538

539-
tupdesc = CreateTemplateTupleDesc(14, false);
539+
tupdesc = CreateTemplateTupleDesc(16, false);
540540
TupleDescInitEntry(tupdesc, (AttrNumber) 1, "datid",
541541
OIDOID, -1, 0);
542542
TupleDescInitEntry(tupdesc, (AttrNumber) 2, "pid",
@@ -565,6 +565,10 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
565565
TEXTOID, -1, 0);
566566
TupleDescInitEntry(tupdesc, (AttrNumber) 14, "client_port",
567567
INT4OID, -1, 0);
568+
TupleDescInitEntry(tupdesc, (AttrNumber) 15, "backend_xid",
569+
XIDOID, -1, 0);
570+
TupleDescInitEntry(tupdesc, (AttrNumber) 16, "backend_xmin",
571+
XIDOID, -1, 0);
568572

569573
funcctx->tuple_desc = BlessTupleDesc(tupdesc);
570574

@@ -616,9 +620,10 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
616620
if (funcctx->call_cntr < funcctx->max_calls)
617621
{
618622
/* for each row */
619-
Datum values[14];
620-
bool nulls[14];
623+
Datum values[16];
624+
bool nulls[16];
621625
HeapTuple tuple;
626+
LocalPgBackendStatus *local_beentry;
622627
PgBackendStatus *beentry;
623628

624629
MemSet(values, 0, sizeof(values));
@@ -627,12 +632,14 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
627632
if (*(int *) (funcctx->user_fctx) > 0)
628633
{
629634
/* Get specific pid slot */
630-
beentry = pgstat_fetch_stat_beentry(*(int *) (funcctx->user_fctx));
635+
local_beentry = pgstat_fetch_stat_local_beentry(*(int *) (funcctx->user_fctx));
636+
beentry = &local_beentry->backendStatus;
631637
}
632638
else
633639
{
634640
/* Get the next one in the list */
635-
beentry = pgstat_fetch_stat_beentry(funcctx->call_cntr + 1); /* 1-based index */
641+
local_beentry = pgstat_fetch_stat_local_beentry(funcctx->call_cntr + 1); /* 1-based index */
642+
beentry = &local_beentry->backendStatus;
636643
}
637644
if (!beentry)
638645
{
@@ -657,6 +664,16 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
657664
else
658665
nulls[3] = true;
659666

667+
if (TransactionIdIsValid(local_beentry->backend_xid))
668+
values[14] = TransactionIdGetDatum(local_beentry->backend_xid);
669+
else
670+
nulls[14] = true;
671+
672+
if (TransactionIdIsValid(local_beentry->backend_xmin))
673+
values[15] = TransactionIdGetDatum(local_beentry->backend_xmin);
674+
else
675+
nulls[15] = true;
676+
660677
/* Values only available to same user or superuser */
661678
if (superuser() || beentry->st_userid == GetUserId())
662679
{

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 201402241
56+
#define CATALOG_VERSION_NO 201402251
5757

5858
#endif

‎src/include/catalog/pg_proc.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2632,7 +2632,7 @@ DATA(insert OID = 3057 ( pg_stat_get_autoanalyze_count PGNSP PGUID 12 1 0 0 0 f
26322632
DESCR("statistics: number of auto analyzes for a table");
26332633
DATA(insert OID = 1936 ( pg_stat_get_backend_idset PGNSP PGUID 12 1 100 0 0 f f f f t t s 0 0 23 "" _null_ _null_ _null_ _null_ pg_stat_get_backend_idset _null_ _null_ _null_ ));
26342634
DESCR("statistics: currently active backend IDs");
2635-
DATA(insert OID = 2022 ( pg_stat_get_activity PGNSP PGUID 12 1 100 0 0 f f f f f t s 1 0 2249 "23" "{23,26,23,26,25,25,25,16,1184,1184,1184,1184,869,25,23}" "{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o}" "{pid,datid,pid,usesysid,application_name,state,query,waiting,xact_start,query_start,backend_start,state_change,client_addr,client_hostname,client_port}" _null_ pg_stat_get_activity _null_ _null_ _null_ ));
2635+
DATA(insert OID = 2022 ( pg_stat_get_activity PGNSP PGUID 12 1 100 0 0 f f f f f t s 1 0 2249 "23" "{23,26,23,26,25,25,25,16,1184,1184,1184,1184,869,25,23,28,28}" "{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}" "{pid,datid,pid,usesysid,application_name,state,query,waiting,xact_start,query_start,backend_start,state_change,client_addr,client_hostname,client_port,backend_xid,backend_xmin}" _null_ pg_stat_get_activity _null_ _null_ _null_ ));
26362636
DESCR("statistics: information about currently active backends");
26372637
DATA(insert OID = 3099 ( pg_stat_get_wal_senders PGNSP PGUID 12 1 10 0 0 f f f f f t s 0 0 2249 "" "{23,25,3220,3220,3220,3220,23,25}" "{o,o,o,o,o,o,o,o}" "{pid,state,sent_location,write_location,flush_location,replay_location,sync_priority,sync_state}" _null_ pg_stat_get_wal_senders _null_ _null_ _null_ ));
26382638
DESCR("statistics: information about currently active replication");

‎src/include/pgstat.h

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -735,6 +735,34 @@ typedef struct PgBackendStatus
735735
char *st_activity;
736736
} PgBackendStatus;
737737

738+
/* ----------
739+
* LocalPgBackendStatus
740+
*
741+
* When we build the backend status array, we use LocalPgBackendStatus to be
742+
* able to add new values to the struct when needed without adding new fields
743+
* to the shared memory. It contains the backend status as a first member.
744+
* ----------
745+
*/
746+
typedef struct LocalPgBackendStatus
747+
{
748+
/*
749+
* Local version of the backend status entry.
750+
*/
751+
PgBackendStatus backendStatus;
752+
753+
/*
754+
* The xid of the current transaction if available, InvalidTransactionId
755+
* if not.
756+
*/
757+
TransactionId backend_xid;
758+
759+
/*
760+
* The xmin of the current session if available, InvalidTransactionId
761+
* if not.
762+
*/
763+
TransactionId backend_xmin;
764+
} LocalPgBackendStatus;
765+
738766
/*
739767
* Working state needed to accumulate per-function-call timing statistics.
740768
*/
@@ -907,6 +935,7 @@ extern void pgstat_send_bgwriter(void);
907935
extern PgStat_StatDBEntry *pgstat_fetch_stat_dbentry(Oid dbid);
908936
extern PgStat_StatTabEntry *pgstat_fetch_stat_tabentry(Oid relid);
909937
extern PgBackendStatus *pgstat_fetch_stat_beentry(int beid);
938+
extern LocalPgBackendStatus *pgstat_fetch_stat_local_beentry(int beid);
910939
extern PgStat_StatFuncEntry *pgstat_fetch_stat_funcentry(Oid funcid);
911940
extern int pgstat_fetch_stat_numbackends(void);
912941
extern PgStat_ArchiverStats *pgstat_fetch_stat_archiver(void);

‎src/include/storage/sinvaladt.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -32,6 +32,7 @@ extern Size SInvalShmemSize(void);
3232
extern void CreateSharedInvalidationState(void);
3333
extern void SharedInvalBackendInit(bool sendOnly);
3434
extern PGPROC *BackendIdGetProc(int backendID);
35+
extern void BackendIdGetTransactionIds(int backendID, TransactionId *xid, TransactionId *xmin);
3536

3637
extern void SIInsertDataEntries(const SharedInvalidationMessage *data, int n);
3738
extern int SIGetDataEntries(SharedInvalidationMessage *data, int datasize);

‎src/test/regress/expected/rules.out

Lines changed: 5 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1604,9 +1604,11 @@ pg_stat_activity| SELECT s.datid,
16041604
s.state_change,
16051605
s.waiting,
16061606
s.state,
1607+
s.backend_xid,
1608+
s.backend_xmin,
16071609
s.query
16081610
FROM pg_database d,
1609-
pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, waiting, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port),
1611+
pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, waiting, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin),
16101612
pg_authid u
16111613
WHERE ((s.datid = d.oid) AND (s.usesysid = u.oid));
16121614
pg_stat_all_indexes| SELECT c.oid AS relid,
@@ -1704,14 +1706,15 @@ pg_stat_replication| SELECT s.pid,
17041706
s.client_hostname,
17051707
s.client_port,
17061708
s.backend_start,
1709+
s.backend_xmin,
17071710
w.state,
17081711
w.sent_location,
17091712
w.write_location,
17101713
w.flush_location,
17111714
w.replay_location,
17121715
w.sync_priority,
17131716
w.sync_state
1714-
FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, waiting, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port),
1717+
FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, waiting, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin),
17151718
pg_authid u,
17161719
pg_stat_get_wal_senders() w(pid, state, sent_location, write_location, flush_location, replay_location, sync_priority, sync_state)
17171720
WHERE ((s.usesysid = u.oid) AND (s.pid = w.pid));

0 commit comments

Comments
 (0)