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

Commit4744c1a

Browse files
author
Neil Conway
committed
Complete the following TODO items:
* Add session start time to pg_stat_activity* Add the client IP address and port to pg_stat_activityOriginal patch from Magnus Hagander, code review by Neil Conway. Catalogversion bumped. This patch sends the client IP address and port number inevery statistics message; that's not ideal, but will be fixed up shortly.
1 parentd8c2118 commit4744c1a

File tree

7 files changed

+189
-24
lines changed

7 files changed

+189
-24
lines changed

‎doc/src/sgml/monitoring.sgml‎

Lines changed: 44 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/monitoring.sgml,v 1.27 2004/12/28 19:08:58 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/monitoring.sgml,v 1.28 2005/05/09 11:31:32 neilc Exp $
33
-->
44

55
<chapter id="monitoring">
@@ -221,15 +221,16 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
221221
<row>
222222
<entry><structname>pg_stat_activity</></entry>
223223
<entry>One row per server process, showing process
224-
<acronym>ID</>, database, user, current query, and the time at
225-
which the current query began execution. The columns that report
226-
data on the current query are only available if the parameter
227-
<varname>stats_command_string</varname> has been turned on.
228-
Furthermore, these columns read as null unless the user examining
229-
the view is a superuser or the same as the user owning the process
230-
being reported on. (Note that because of the
231-
collector's reporting delay, current query will only be up-to-date for
232-
long-running queries.)</entry>
224+
<acronym>ID</>, database, user, current query, the time at which
225+
the current query began execution, the time at which the backend
226+
was started and the client address and port number. The columns
227+
that report data on the current query are only available if the
228+
parameter <varname>stats_command_string</varname> has been
229+
turned on. Furthermore, these columns read as null unless the
230+
user examining the view is a superuser or the same as the user
231+
owning the process being reported on. (Note that because of the
232+
collector's reporting delay, the current query will only be
233+
up-to-date for long-running queries.)</entry>
233234
</row>
234235

235236
<row>
@@ -509,7 +510,7 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
509510
<entry><type>set of integer</type></entry>
510511
<entry>
511512
Set of currently active backend process IDs (from 1 to the
512-
number of active backend processes). See usage example in the text.
513+
number of active backend processes). See usage example in the text
513514
</entry>
514515
</row>
515516

@@ -568,6 +569,38 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
568569
</entry>
569570
</row>
570571

572+
<row>
573+
<entry><literal><function>pg_stat_get_backend_start</function>(<type>integer</type>)</literal></entry>
574+
<entry><type>timestamp with time zone</type></entry>
575+
<entry>
576+
The time at which the given backend process was started, or
577+
null if the current user is not a superuser nor the same user
578+
as that of the session being queried
579+
</entry>
580+
</row>
581+
582+
<row>
583+
<entry><literal><function>pg_stat_get_backend_client_addr</function>(<type>integer</type>)</literal></entry>
584+
<entry><type>inet</type></entry>
585+
<entry>
586+
The IP address of the client connected to the given
587+
backend. Null if the connection is over a Unix domain
588+
socket. Also null if the current user is not a superuser nor
589+
the same user as that of the session being queried
590+
</entry>
591+
</row>
592+
593+
<row>
594+
<entry><literal><function>pg_stat_get_backend_client_port</function>(<type>integer</type>)</literal></entry>
595+
<entry><type>integer</type></entry>
596+
<entry>
597+
The IP port number of the client connected to the given
598+
backend. -1 if the connection is over a Unix domain
599+
socket. Null if the current user is not a superuser nor the
600+
same user as that of the session being queried
601+
</entry>
602+
</row>
603+
571604
<row>
572605
<entry><literal><function>pg_stat_reset</function>()</literal></entry>
573606
<entry><type>boolean</type></entry>

‎src/backend/catalog/system_views.sql‎

Lines changed: 5 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
*
44
* Copyright (c) 1996-2005, PostgreSQL Global Development Group
55
*
6-
* $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.11 2005/01/01 20:44:14 tgl Exp $
6+
* $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.12 2005/05/09 11:31:32 neilc Exp $
77
*/
88

99
CREATEVIEWpg_userAS
@@ -237,7 +237,10 @@ CREATE VIEW pg_stat_activity AS
237237
pg_stat_get_backend_userid(S.backendid)AS usesysid,
238238
U.usenameAS usename,
239239
pg_stat_get_backend_activity(S.backendid)AS current_query,
240-
pg_stat_get_backend_activity_start(S.backendid)AS query_start
240+
pg_stat_get_backend_activity_start(S.backendid)AS query_start,
241+
pg_stat_get_backend_start(S.backendid)AS backend_start,
242+
pg_stat_get_backend_client_addr(S.backendid)AS client_addr,
243+
pg_stat_get_backend_client_port(S.backendid)AS client_port
241244
FROM pg_database D,
242245
(SELECT pg_stat_get_backend_idset()AS backendid)AS S,
243246
pg_shadow U

‎src/backend/postmaster/pgstat.c‎

Lines changed: 7 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -13,7 +13,7 @@
1313
*
1414
*Copyright (c) 2001-2005, PostgreSQL Global Development Group
1515
*
16-
*$PostgreSQL: pgsql/src/backend/postmaster/pgstat.c,v 1.92 2005/04/14 20:32:42 tgl Exp $
16+
*$PostgreSQL: pgsql/src/backend/postmaster/pgstat.c,v 1.93 2005/05/09 11:31:33 neilc Exp $
1717
* ----------
1818
*/
1919
#include"postgres.h"
@@ -1300,6 +1300,7 @@ pgstat_setheader(PgStat_MsgHdr *hdr, int mtype)
13001300
hdr->m_procpid=MyProcPid;
13011301
hdr->m_databaseid=MyDatabaseId;
13021302
hdr->m_userid=GetSessionUserId();
1303+
memcpy(&hdr->m_clientaddr,&MyProcPort->raddr,sizeof(hdr->m_clientaddr));
13031304
}
13041305

13051306

@@ -2032,12 +2033,15 @@ pgstat_add_backend(PgStat_MsgHdr *msg)
20322033
beentry->databaseid=msg->m_databaseid;
20332034
beentry->procpid=msg->m_procpid;
20342035
beentry->userid=msg->m_userid;
2036+
beentry->start_sec=
2037+
GetCurrentAbsoluteTimeUsec(&beentry->start_usec);
20352038
beentry->activity_start_sec=0;
20362039
beentry->activity_start_usec=0;
2040+
memcpy(&beentry->clientaddr,&msg->m_clientaddr,sizeof(beentry->clientaddr));
20372041
MemSet(beentry->activity,0,PGSTAT_ACTIVITY_SIZE);
20382042

20392043
/*
2040-
* Lookup or create the database entry for thisbackends DB.
2044+
* Lookup or create the database entry for thisbackend's DB.
20412045
*/
20422046
dbentry= (PgStat_StatDBEntry*)hash_search(pgStatDBHash,
20432047
(void*)&(msg->m_databaseid),
@@ -2072,9 +2076,7 @@ pgstat_add_backend(PgStat_MsgHdr *msg)
20722076
HASH_ELEM |HASH_FUNCTION);
20732077
}
20742078

2075-
/*
2076-
* Count number of connects to the database
2077-
*/
2079+
/* Count the number of connects to the database */
20782080
dbentry->n_connects++;
20792081

20802082
return0;

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

Lines changed: 118 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.20 2004/12/31 22:01:22 pgsql Exp $
11+
* $PostgreSQL: pgsql/src/backend/utils/adt/pgstatfuncs.c,v 1.21 2005/05/09 11:31:33 neilc Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -22,6 +22,9 @@
2222
#include"nodes/execnodes.h"
2323
#include"pgstat.h"
2424
#include"utils/hsearch.h"
25+
#include"utils/inet.h"
26+
#include"utils/builtins.h"
27+
#include"libpq/ip.h"
2528

2629
/* bogus ... these externs should be in a header file */
2730
externDatumpg_stat_get_numscans(PG_FUNCTION_ARGS);
@@ -41,6 +44,9 @@ extern Datum pg_stat_get_backend_dbid(PG_FUNCTION_ARGS);
4144
externDatumpg_stat_get_backend_userid(PG_FUNCTION_ARGS);
4245
externDatumpg_stat_get_backend_activity(PG_FUNCTION_ARGS);
4346
externDatumpg_stat_get_backend_activity_start(PG_FUNCTION_ARGS);
47+
externDatumpg_stat_get_backend_start(PG_FUNCTION_ARGS);
48+
externDatumpg_stat_get_backend_client_addr(PG_FUNCTION_ARGS);
49+
externDatumpg_stat_get_backend_client_port(PG_FUNCTION_ARGS);
4450

4551
externDatumpg_stat_get_db_numbackends(PG_FUNCTION_ARGS);
4652
externDatumpg_stat_get_db_xact_commit(PG_FUNCTION_ARGS);
@@ -357,6 +363,117 @@ pg_stat_get_backend_activity_start(PG_FUNCTION_ARGS)
357363
PG_RETURN_TIMESTAMPTZ(result);
358364
}
359365

366+
Datum
367+
pg_stat_get_backend_start(PG_FUNCTION_ARGS)
368+
{
369+
PgStat_StatBeEntry*beentry;
370+
int32beid;
371+
AbsoluteTimesec;
372+
intusec;
373+
TimestampTzresult;
374+
375+
beid=PG_GETARG_INT32(0);
376+
377+
if ((beentry=pgstat_fetch_stat_beentry(beid))==NULL)
378+
PG_RETURN_NULL();
379+
380+
if (!superuser()&&beentry->userid!=GetUserId())
381+
PG_RETURN_NULL();
382+
383+
sec=beentry->start_sec;
384+
usec=beentry->start_usec;
385+
386+
if (sec==0&&usec==0)
387+
PG_RETURN_NULL();
388+
389+
result=AbsoluteTimeUsecToTimestampTz(sec,usec);
390+
391+
PG_RETURN_TIMESTAMPTZ(result);
392+
}
393+
394+
395+
Datum
396+
pg_stat_get_backend_client_addr(PG_FUNCTION_ARGS)
397+
{
398+
PgStat_StatBeEntry*beentry;
399+
int32beid;
400+
charremote_host[NI_MAXHOST];
401+
intret;
402+
403+
beid=PG_GETARG_INT32(0);
404+
405+
if ((beentry=pgstat_fetch_stat_beentry(beid))==NULL)
406+
PG_RETURN_NULL();
407+
408+
if (!superuser()&&beentry->userid!=GetUserId())
409+
PG_RETURN_NULL();
410+
411+
switch (beentry->clientaddr.addr.ss_family)
412+
{
413+
caseAF_INET:
414+
#ifdefHAVE_IPV6
415+
caseAF_INET6:
416+
#endif
417+
break;
418+
default:
419+
PG_RETURN_NULL();
420+
}
421+
422+
remote_host[0]='\0';
423+
424+
ret=getnameinfo_all(&beentry->clientaddr.addr,beentry->clientaddr.salen,
425+
remote_host,sizeof(remote_host),
426+
NULL,0,
427+
NI_NUMERICHOST |NI_NUMERICSERV);
428+
if (ret)
429+
PG_RETURN_NULL();
430+
431+
PG_RETURN_INET_P(DirectFunctionCall1(inet_in,
432+
CStringGetDatum(remote_host)));
433+
}
434+
435+
Datum
436+
pg_stat_get_backend_client_port(PG_FUNCTION_ARGS)
437+
{
438+
PgStat_StatBeEntry*beentry;
439+
int32beid;
440+
charremote_port[NI_MAXSERV];
441+
intret;
442+
443+
beid=PG_GETARG_INT32(0);
444+
445+
if ((beentry=pgstat_fetch_stat_beentry(beid))==NULL)
446+
PG_RETURN_NULL();
447+
448+
if (!superuser()&&beentry->userid!=GetUserId())
449+
PG_RETURN_NULL();
450+
451+
switch (beentry->clientaddr.addr.ss_family)
452+
{
453+
caseAF_INET:
454+
#ifdefHAVE_IPV6
455+
caseAF_INET6:
456+
#endif
457+
break;
458+
caseAF_UNIX:
459+
PG_RETURN_INT32(-1);
460+
default:
461+
PG_RETURN_NULL();
462+
}
463+
464+
remote_port[0]='\0';
465+
466+
ret=getnameinfo_all(&beentry->clientaddr.addr,
467+
beentry->clientaddr.salen,
468+
NULL,0,
469+
remote_port,sizeof(remote_port),
470+
NI_NUMERICHOST |NI_NUMERICSERV);
471+
if (ret)
472+
PG_RETURN_NULL();
473+
474+
PG_RETURN_DATUM(DirectFunctionCall1(int4in,CStringGetDatum(remote_port)));
475+
}
476+
360477

361478
Datum
362479
pg_stat_get_db_numbackends(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-2005, 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.266 2005/04/30 20:31:37 tgl Exp $
40+
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.267 2005/05/09 11:31:34 neilc Exp $
4141
*
4242
*-------------------------------------------------------------------------
4343
*/
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO200504301
56+
#defineCATALOG_VERSION_NO200505091
5757

5858
#endif

‎src/include/catalog/pg_proc.h‎

Lines changed: 7 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2005, 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.360 2005/04/30 20:31:38 tgl Exp $
10+
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.361 2005/05/09 11:31:34 neilc Exp $
1111
*
1212
* NOTES
1313
* The script catalog/genbki.sh reads this file and generates .bki
@@ -2822,6 +2822,12 @@ DATA(insert OID = 1940 ( pg_stat_get_backend_activityPGNSP PGUID 12 f f t f s
28222822
DESCR("Statistics: Current query of backend");
28232823
DATA(insertOID=2094 (pg_stat_get_backend_activity_startPGNSPPGUID12fftfs11184"23"_null__null__null_pg_stat_get_backend_activity_start-_null_));
28242824
DESCR("Statistics: Start time for current query of backend");
2825+
DATA(insertOID=1391 (pg_stat_get_backend_startPGNSPPGUID12fftfs11184"23"_null__null__null_pg_stat_get_backend_start-_null_));
2826+
DESCR("Statistics: Start time for current backend session");
2827+
DATA(insertOID=1392 (pg_stat_get_backend_client_addrPGNSPPGUID12fftfs1869"23"_null__null__null_pg_stat_get_backend_client_addr-_null_));
2828+
DESCR("Statistics: Address of client connected to backend");
2829+
DATA(insertOID=1393 (pg_stat_get_backend_client_portPGNSPPGUID12fftfs123"23"_null__null__null_pg_stat_get_backend_client_port-_null_));
2830+
DESCR("Statistics: Port number of client connected to backend");
28252831
DATA(insertOID=1941 (pg_stat_get_db_numbackendsPGNSPPGUID12fftfs123"26"_null__null__null_pg_stat_get_db_numbackends-_null_ ));
28262832
DESCR("Statistics: Number of backends in database");
28272833
DATA(insertOID=1942 (pg_stat_get_db_xact_commitPGNSPPGUID12fftfs120"26"_null__null__null_pg_stat_get_db_xact_commit-_null_ ));

‎src/include/pgstat.h‎

Lines changed: 6 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -5,17 +5,17 @@
55
*
66
*Copyright (c) 2001-2005, PostgreSQL Global Development Group
77
*
8-
*$PostgreSQL: pgsql/src/include/pgstat.h,v 1.27 2005/01/01 05:43:08 momjian Exp $
8+
*$PostgreSQL: pgsql/src/include/pgstat.h,v 1.28 2005/05/09 11:31:33 neilc Exp $
99
* ----------
1010
*/
1111
#ifndefPGSTAT_H
1212
#definePGSTAT_H
1313

14+
#include"libpq/pqcomm.h"
1415
#include"utils/hsearch.h"
1516
#include"utils/nabstime.h"
1617
#include"utils/rel.h"
1718

18-
1919
/* ----------
2020
* The types of backend/postmaster -> collector messages
2121
* ----------
@@ -54,6 +54,7 @@ typedef struct PgStat_MsgHdr
5454
intm_procpid;
5555
Oidm_databaseid;
5656
AclIdm_userid;
57+
SockAddrm_clientaddr;
5758
}PgStat_MsgHdr;
5859

5960
/* ----------
@@ -231,8 +232,11 @@ typedef struct PgStat_StatBeEntry
231232
Oiddatabaseid;
232233
Oiduserid;
233234
intprocpid;
235+
AbsoluteTimestart_sec;
236+
intstart_usec;
234237
AbsoluteTimeactivity_start_sec;
235238
intactivity_start_usec;
239+
SockAddrclientaddr;
236240
charactivity[PGSTAT_ACTIVITY_SIZE];
237241
}PgStat_StatBeEntry;
238242

0 commit comments

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