Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit0423de4

Browse files
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, toreturn only the data for a single PID.
1 parent8008988 commit0423de4

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
CREATEVIEWpg_rolesAS
@@ -341,23 +341,26 @@ CREATE VIEW pg_statio_user_sequences AS
341341

342342
CREATEVIEWpg_stat_activityAS
343343
SELECT
344-
D.oidAS datid,
345-
D.datnameAS datname,
346-
pg_stat_get_backend_pid(S.backendid)ASprocpid,
347-
pg_stat_get_backend_userid(S.backendid)ASusesysid,
348-
U.rolnameAS usename,
349-
pg_stat_get_backend_activity(S.backendid)AScurrent_query,
350-
pg_stat_get_backend_waiting(S.backendid)ASwaiting,
351-
pg_stat_get_backend_xact_start(S.backendid)ASxact_start,
352-
pg_stat_get_backend_activity_start(S.backendid)ASquery_start,
353-
pg_stat_get_backend_start(S.backendid)ASbackend_start,
354-
pg_stat_get_backend_client_addr(S.backendid)ASclient_addr,
355-
pg_stat_get_backend_client_port(S.backendid)ASclient_port
344+
S.datidAS datid,
345+
D.datnameAS datname,
346+
S.procpid,
347+
S.usesysid,
348+
U.rolnameAS 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(datidoid, procpidint,
358+
usesysidoid, current_querytext, waitingboolean,
359+
xact_starttimestamptz, query_starttimestamptz,
360+
backend_starttimestamptz, client_addrinet, client_portint),
358361
pg_authid U
359-
WHEREpg_stat_get_backend_dbid(S.backendid)=D.oidAND
360-
pg_stat_get_backend_userid(S.backendid)=U.oid;
362+
WHERES.datid=D.oidAND
363+
S.usesysid=U.oid;
361364

362365
CREATEVIEWpg_stat_databaseAS
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
externDatumpg_stat_get_last_autoanalyze_time(PG_FUNCTION_ARGS);
4042

4143
externDatumpg_stat_get_backend_idset(PG_FUNCTION_ARGS);
44+
externDatumpg_stat_get_activity(PG_FUNCTION_ARGS);
4245
externDatumpg_backend_pid(PG_FUNCTION_ARGS);
4346
externDatumpg_stat_get_backend_pid(PG_FUNCTION_ARGS);
4447
externDatumpg_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+
MemoryContextoldcontext;
377+
TupleDesctupdesc;
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+
intpid=PG_GETARG_INT32(0);
412+
inti;
413+
intn=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+
Datumvalues[10];
445+
boolnulls[10];
446+
HeapTupletuple;
447+
PgBackendStatus*beentry;
448+
SockAddrzero_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+
inti;
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+
#ifdefHAVE_IPV6
519+
||beentry->st_clientaddr.addr.ss_family==AF_INET6
520+
#endif
521+
)
522+
{
523+
charremote_host[NI_MAXHOST];
524+
charremote_port[NI_MAXSERV];
525+
intret;
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+
elseif (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-
#defineCATALOG_VERSION_NO200805042
56+
#defineCATALOG_VERSION_NO200805071
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(insertOID=1936 (pg_stat_get_backend_idsetPGNSPPGUID121100fftts023""_null__null__null_pg_stat_get_backend_idset-_null__null_ ));
29062906
DESCR("statistics: currently active backend IDs");
2907+
DATA(insertOID=2022 (pg_stat_get_activityPGNSPPGUID121100fffts12249"23"_null__null__null_pg_stat_get_activity-_null__null_ ));
2908+
DESCR("statistics: information about currently active backends");
29072909
DATA(insertOID=2026 (pg_backend_pidPGNSPPGUID1210fftfs023""_null__null__null_pg_backend_pid-_null__null_ ));
29082910
DESCR("statistics: current backend PID");
29092911
DATA(insertOID=1937 (pg_stat_get_backend_pidPGNSPPGUID1210fftfs123"23"_null__null__null_pg_stat_get_backend_pid-_null__null_ ));

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp