Skip to content

Commit 0423de4

Browse files
mhagandermhagander
committed
Make the pg_stat_activity view call a SRF (pg_stat_get_activity())
instead of calling a bunch of individual functions. This function can also be called directly, taking a PID as an argument, to return only the data for a single PID.
1 parent 8008988 commit 0423de4

File tree

6 files changed

+270
-30
lines changed

6 files changed

+270
-30
lines changed

‎doc/src/sgml/monitoring.sgml

Lines changed: 22 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/monitoring.sgml,v 1.57 2008/04/10 13:34:33 alvherre Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/monitoring.sgml,v 1.58 2008/05/07 14:41:55 mha Exp $ -->
22

33
<chapter id="monitoring">
44
<title>Monitoring Database Activity</title>
@@ -655,20 +655,31 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
655655
</row>
656656

657657
<row>
658-
<entry><literal><function>pg_stat_get_backend_idset</function>()</literal></entry>
659-
<entry><type>setof integer</type></entry>
658+
<!-- See also the entry for this in func.sgml -->
659+
<entry><literal><function>pg_backend_pid</function>()</literal></entry>
660+
<entry><type>integer</type></entry>
660661
<entry>
661-
Set of currently active server process numbers (from 1 to the
662-
number of active server processes). See usage example in the text
662+
Process ID of the server process attached to the current session
663663
</entry>
664664
</row>
665665

666666
<row>
667-
<!-- See also the entry for this in func.sgml -->
668-
<entry><literal><function>pg_backend_pid</function>()</literal></entry>
669-
<entry><type>integer</type></entry>
667+
<entry><literal><function>pg_stat_get_activity</function>(<type>integer</type>)</literal></entry>
668+
<entry><type>setof record</type></entry>
670669
<entry>
671-
Process ID of the server process attached to the current session
670+
Returns a record of information about the backend with the specified pid, or
671+
one record for each active backend in the system if <symbol>NULL</symbol> is
672+
specified. The fields returned are the same as in the
673+
<structname>pg_stat_activity</structname> view
674+
</entry>
675+
</row>
676+
677+
<row>
678+
<entry><literal><function>pg_stat_get_backend_idset</function>()</literal></entry>
679+
<entry><type>setof integer</type></entry>
680+
<entry>
681+
Set of currently active server process numbers (from 1 to the
682+
number of active server processes). See usage example in the text
672683
</entry>
673684
</row>
674685

@@ -869,6 +880,8 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
869880
</note>
870881

871882
<para>
883+
All functions to access information about backends are indexed by backend id
884+
number, except <function>pg_stat_get_activity</function> which is indexed by PID.
872885
The function <function>pg_stat_get_backend_idset</function> provides
873886
a convenient way to generate one row for each active server process. For
874887
example, to show the <acronym>PID</>s and current queries of all server processes:

‎src/backend/catalog/system_views.sql

Lines changed: 19 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
*
44
* Copyright (c) 1996-2008, PostgreSQL Global Development Group
55
*
6-
* $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.49 2008/03/10 12:55:13 mha Exp $
6+
* $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.50 2008/05/07 14:41:55 mha Exp $
77
*/
88

99
CREATE VIEW pg_roles AS
@@ -341,23 +341,26 @@ CREATE VIEW pg_statio_user_sequences AS
341341

342342
CREATE VIEW pg_stat_activity AS
343343
SELECT
344-
D.oid AS datid,
345-
D.datname AS datname,
346-
pg_stat_get_backend_pid(S.backendid) AS procpid,
347-
pg_stat_get_backend_userid(S.backendid) AS usesysid,
348-
U.rolname AS usename,
349-
pg_stat_get_backend_activity(S.backendid) AS current_query,
350-
pg_stat_get_backend_waiting(S.backendid) AS waiting,
351-
pg_stat_get_backend_xact_start(S.backendid) AS xact_start,
352-
pg_stat_get_backend_activity_start(S.backendid) AS query_start,
353-
pg_stat_get_backend_start(S.backendid) AS backend_start,
354-
pg_stat_get_backend_client_addr(S.backendid) AS client_addr,
355-
pg_stat_get_backend_client_port(S.backendid) AS client_port
344+
S.datid AS datid,
345+
D.datname AS datname,
346+
S.procpid,
347+
S.usesysid,
348+
U.rolname AS usename,
349+
S.current_query,
350+
S.waiting,
351+
S.xact_start,
352+
S.query_start,
353+
S.backend_start,
354+
S.client_addr,
355+
S.client_port
356356
FROM pg_database D,
357-
(SELECT pg_stat_get_backend_idset() AS backendid) AS S,
357+
pg_stat_get_activity(NULL) AS S(datid oid, procpid int,
358+
usesysid oid, current_query text, waiting boolean,
359+
xact_start timestamptz, query_start timestamptz,
360+
backend_start timestamptz, client_addr inet, client_port int),
358361
pg_authid U
359-
WHERE pg_stat_get_backend_dbid(S.backendid) = D.oid AND
360-
pg_stat_get_backend_userid(S.backendid) = U.oid;
362+
WHERE S.datid = D.oid AND
363+
S.usesysid = U.oid;
361364

362365
CREATE VIEW pg_stat_database AS
363366
SELECT

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

Lines changed: 223 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/utils/adt/pgstatfuncs.c,v 1.49 2008/03/25 22:42:44 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/utils/adt/pgstatfuncs.c,v 1.50 2008/05/07 14:41:55 mha Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -17,6 +17,8 @@
1717
#include "funcapi.h"
1818
#include "miscadmin.h"
1919
#include "pgstat.h"
20+
#include "catalog/pg_type.h"
21+
#include "access/heapam.h"
2022
#include "utils/builtins.h"
2123
#include "utils/inet.h"
2224
#include "libpq/ip.h"
@@ -39,6 +41,7 @@ extern Datum pg_stat_get_last_analyze_time(PG_FUNCTION_ARGS);
3941
extern Datum pg_stat_get_last_autoanalyze_time(PG_FUNCTION_ARGS);
4042

4143
extern Datum pg_stat_get_backend_idset(PG_FUNCTION_ARGS);
44+
extern Datum pg_stat_get_activity(PG_FUNCTION_ARGS);
4245
extern Datum pg_backend_pid(PG_FUNCTION_ARGS);
4346
extern Datum pg_stat_get_backend_pid(PG_FUNCTION_ARGS);
4447
extern Datum pg_stat_get_backend_dbid(PG_FUNCTION_ARGS);
@@ -363,6 +366,225 @@ pg_stat_get_backend_idset(PG_FUNCTION_ARGS)
363366
}
364367
}
365368

369+
Datum
370+
pg_stat_get_activity(PG_FUNCTION_ARGS)
371+
{
372+
FuncCallContext *funcctx;
373+
374+
if (SRF_IS_FIRSTCALL())
375+
{
376+
MemoryContext oldcontext;
377+
TupleDesc tupdesc;
378+
379+
funcctx = SRF_FIRSTCALL_INIT();
380+
381+
oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
382+
383+
tupdesc = CreateTemplateTupleDesc(10, false);
384+
TupleDescInitEntry(tupdesc, (AttrNumber) 1, "datid", OIDOID, -1, 0);
385+
TupleDescInitEntry(tupdesc, (AttrNumber) 2, "procpid", INT4OID, -1, 0);
386+
TupleDescInitEntry(tupdesc, (AttrNumber) 3, "usesysid", OIDOID, -1, 0);
387+
TupleDescInitEntry(tupdesc, (AttrNumber) 4, "current_query", TEXTOID, -1, 0);
388+
TupleDescInitEntry(tupdesc, (AttrNumber) 5, "waiting", BOOLOID, -1, 0);
389+
TupleDescInitEntry(tupdesc, (AttrNumber) 6, "act_start", TIMESTAMPTZOID, -1, 0);
390+
TupleDescInitEntry(tupdesc, (AttrNumber) 7, "query_start", TIMESTAMPTZOID, -1, 0);
391+
TupleDescInitEntry(tupdesc, (AttrNumber) 8, "backend_start", TIMESTAMPTZOID, -1, 0);
392+
TupleDescInitEntry(tupdesc, (AttrNumber) 9, "client_addr", INETOID, -1, 0);
393+
TupleDescInitEntry(tupdesc, (AttrNumber) 10, "client_port", INT4OID, -1, 0);
394+
395+
funcctx->tuple_desc = BlessTupleDesc(tupdesc);
396+
397+
funcctx->user_fctx = palloc0(sizeof(int));
398+
if (PG_ARGISNULL(0))
399+
{
400+
/* Get all backends */
401+
funcctx->max_calls = pgstat_fetch_stat_numbackends();
402+
}
403+
else
404+
{
405+
/*
406+
* Get one backend - locate by pid.
407+
*
408+
* We lookup the backend early, so we can return zero rows if it doesn't
409+
* exist, instead of returning a single row full of NULLs.
410+
*/
411+
int pid = PG_GETARG_INT32(0);
412+
int i;
413+
int n = pgstat_fetch_stat_numbackends();
414+
415+
for (i = 1; i <= n; i++)
416+
{
417+
PgBackendStatus *be = pgstat_fetch_stat_beentry(i);
418+
if (be)
419+
{
420+
if (be->st_procpid == pid)
421+
{
422+
*(int *)(funcctx->user_fctx) = i;
423+
break;
424+
}
425+
}
426+
}
427+
428+
if (*(int *)(funcctx->user_fctx) == 0)
429+
/* Pid not found, return zero rows */
430+
funcctx->max_calls = 0;
431+
else
432+
funcctx->max_calls = 1;
433+
}
434+
435+
MemoryContextSwitchTo(oldcontext);
436+
}
437+
438+
/* stuff done on every call of the function */
439+
funcctx = SRF_PERCALL_SETUP();
440+
441+
if (funcctx->call_cntr < funcctx->max_calls)
442+
{
443+
/* for each row */
444+
Datum values[10];
445+
bool nulls[10];
446+
HeapTuple tuple;
447+
PgBackendStatus *beentry;
448+
SockAddr zero_clientaddr;
449+
450+
MemSet(values, 0, sizeof(values));
451+
MemSet(nulls, 0, sizeof(nulls));
452+
453+
if (*(int *)(funcctx->user_fctx) > 0)
454+
/* Get specific pid slot */
455+
beentry = pgstat_fetch_stat_beentry(*(int *)(funcctx->user_fctx));
456+
else
457+
/* Get the next one in the list */
458+
beentry = pgstat_fetch_stat_beentry(funcctx->call_cntr+1); /* 1-based index */
459+
if (!beentry)
460+
{
461+
int i;
462+
463+
for (i = 0; i < sizeof(nulls)/sizeof(nulls[0]); i++)
464+
nulls[i] = true;
465+
466+
nulls[3] = false;
467+
values[3] = CStringGetTextDatum("<backend information not available>");
468+
469+
tuple = heap_form_tuple(funcctx->tuple_desc, values, nulls);
470+
SRF_RETURN_NEXT(funcctx, HeapTupleGetDatum(tuple));
471+
}
472+
473+
/* Values available to all callers */
474+
values[0] = ObjectIdGetDatum(beentry->st_databaseid);
475+
values[1] = Int32GetDatum(beentry->st_procpid);
476+
values[2] = ObjectIdGetDatum(beentry->st_userid);
477+
478+
/* Values only available to same user or superuser */
479+
if (superuser() || beentry->st_userid == GetUserId())
480+
{
481+
if (*(beentry->st_activity) == '\0')
482+
{
483+
values[3] = CStringGetTextDatum("<command string not enabled>");
484+
}
485+
else
486+
{
487+
values[3] = CStringGetTextDatum(beentry->st_activity);
488+
}
489+
490+
values[4] = BoolGetDatum(beentry->st_waiting);
491+
492+
if (beentry->st_xact_start_timestamp != 0)
493+
values[5] = TimestampTzGetDatum(beentry->st_xact_start_timestamp);
494+
else
495+
nulls[5] = true;
496+
497+
if (beentry->st_activity_start_timestamp != 0)
498+
values[6] = TimestampTzGetDatum(beentry->st_activity_start_timestamp);
499+
else
500+
nulls[6] = true;
501+
502+
if (beentry->st_proc_start_timestamp != 0)
503+
values[7] = TimestampTzGetDatum(beentry->st_proc_start_timestamp);
504+
else
505+
nulls[7] = true;
506+
507+
/* A zeroed client addr means we don't know */
508+
memset(&zero_clientaddr, 0, sizeof(zero_clientaddr));
509+
if (memcmp(&(beentry->st_clientaddr), &zero_clientaddr,
510+
sizeof(zero_clientaddr) == 0))
511+
{
512+
nulls[8] = true;
513+
nulls[9] = true;
514+
}
515+
else
516+
{
517+
if (beentry->st_clientaddr.addr.ss_family == AF_INET
518+
#ifdef HAVE_IPV6
519+
|| beentry->st_clientaddr.addr.ss_family == AF_INET6
520+
#endif
521+
)
522+
{
523+
char remote_host[NI_MAXHOST];
524+
char remote_port[NI_MAXSERV];
525+
int ret;
526+
527+
remote_host[0] = '\0';
528+
remote_port[0] = '\0';
529+
ret = pg_getnameinfo_all(&beentry->st_clientaddr.addr,
530+
beentry->st_clientaddr.salen,
531+
remote_host, sizeof(remote_host),
532+
remote_port, sizeof(remote_port),
533+
NI_NUMERICHOST | NI_NUMERICSERV);
534+
if (ret)
535+
{
536+
nulls[8] = true;
537+
nulls[9] = true;
538+
}
539+
else
540+
{
541+
clean_ipv6_addr(beentry->st_clientaddr.addr.ss_family, remote_host);
542+
values[8] = DirectFunctionCall1(inet_in,
543+
CStringGetDatum(remote_host));
544+
values[9] = Int32GetDatum(atoi(remote_port));
545+
}
546+
}
547+
else if (beentry->st_clientaddr.addr.ss_family == AF_UNIX)
548+
{
549+
/*
550+
* Unix sockets always reports NULL for host and -1 for port, so it's
551+
* possible to tell the difference to connections we have no
552+
* permissions to view, or with errors.
553+
*/
554+
nulls[8] = true;
555+
values[9] = DatumGetInt32(-1);
556+
}
557+
else
558+
{
559+
/* Unknown address type, should never happen */
560+
nulls[8] = true;
561+
nulls[9] = true;
562+
}
563+
}
564+
}
565+
else
566+
{
567+
/* No permissions to view data about this session */
568+
values[3] = CStringGetTextDatum("<insufficient privilege>");
569+
nulls[4] = true;
570+
nulls[5] = true;
571+
nulls[6] = true;
572+
nulls[7] = true;
573+
nulls[8] = true;
574+
nulls[9] = true;
575+
}
576+
577+
tuple = heap_form_tuple(funcctx->tuple_desc, values, nulls);
578+
579+
SRF_RETURN_NEXT(funcctx, HeapTupleGetDatum(tuple));
580+
}
581+
else
582+
{
583+
/* nothing left */
584+
SRF_RETURN_DONE(funcctx);
585+
}
586+
}
587+
366588

367589
Datum
368590
pg_backend_pid(PG_FUNCTION_ARGS)

‎src/include/catalog/catversion.h

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -37,7 +37,7 @@
3737
* Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
3838
* Portions Copyright (c) 1994, Regents of the University of California
3939
*
40-
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.455 2008/05/04 23:19:23 tgl Exp $
40+
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.456 2008/05/07 14:41:55 mha Exp $
4141
*
4242
*-------------------------------------------------------------------------
4343
*/
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 200805042
56+
#define CATALOG_VERSION_NO 200805071
5757

5858
#endif

‎src/include/catalog/pg_proc.h

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.496 2008/05/04 23:19:23 tgl Exp $
10+
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.497 2008/05/07 14:41:55 mha Exp $
1111
*
1212
* NOTES
1313
* The script catalog/genbki.sh reads this file and generates .bki
@@ -2904,6 +2904,8 @@ DATA(insert OID = 2784 ( pg_stat_get_last_autoanalyze_time PGNSP PGUID 12 1 0 f
29042904
DESCR("statistics: last auto analyze time for a table");
29052905
DATA(insert OID = 1936 ( pg_stat_get_backend_idset PGNSP PGUID 12 1 100 f f t t s 0 23 "" _null_ _null_ _null_ pg_stat_get_backend_idset - _null_ _null_ ));
29062906
DESCR("statistics: currently active backend IDs");
2907+
DATA(insert OID = 2022 ( pg_stat_get_activity PGNSP PGUID 12 1 100 f f f t s 1 2249 "23" _null_ _null_ _null_ pg_stat_get_activity - _null_ _null_ ));
2908+
DESCR("statistics: information about currently active backends");
29072909
DATA(insert OID = 2026 ( pg_backend_pid PGNSP PGUID 12 1 0 f f t f s 0 23 "" _null_ _null_ _null_ pg_backend_pid - _null_ _null_ ));
29082910
DESCR("statistics: current backend PID");
29092911
DATA(insert OID = 1937 ( pg_stat_get_backend_pid PGNSP PGUID 12 1 0 f f t f s 1 23 "23" _null_ _null_ _null_ pg_stat_get_backend_pid - _null_ _null_ ));

0 commit comments

Comments
 (0)