Skip to content

Commit 4744c1a

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_activity Original patch from Magnus Hagander, code review by Neil Conway. Catalog version bumped. This patch sends the client IP address and port number in every statistics message; that's not ideal, but will be fixed up shortly.
1 parent d8c2118 commit 4744c1a

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
CREATE VIEW pg_user AS
@@ -237,7 +237,10 @@ CREATE VIEW pg_stat_activity AS
237237
pg_stat_get_backend_userid(S.backendid) AS usesysid,
238238
U.usename AS 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 this backends DB.
2044+
* Lookup or create the database entry for this backend'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
return 0;

‎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
extern Datum pg_stat_get_numscans(PG_FUNCTION_ARGS);
@@ -41,6 +44,9 @@ extern Datum pg_stat_get_backend_dbid(PG_FUNCTION_ARGS);
4144
extern Datum pg_stat_get_backend_userid(PG_FUNCTION_ARGS);
4245
extern Datum pg_stat_get_backend_activity(PG_FUNCTION_ARGS);
4346
extern Datum pg_stat_get_backend_activity_start(PG_FUNCTION_ARGS);
47+
extern Datum pg_stat_get_backend_start(PG_FUNCTION_ARGS);
48+
extern Datum pg_stat_get_backend_client_addr(PG_FUNCTION_ARGS);
49+
extern Datum pg_stat_get_backend_client_port(PG_FUNCTION_ARGS);
4450

4551
extern Datum pg_stat_get_db_numbackends(PG_FUNCTION_ARGS);
4652
extern Datum pg_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+
int32 beid;
371+
AbsoluteTime sec;
372+
int usec;
373+
TimestampTz result;
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+
int32 beid;
400+
char remote_host[NI_MAXHOST];
401+
int ret;
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+
case AF_INET:
414+
#ifdef HAVE_IPV6
415+
case AF_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+
int32 beid;
440+
char remote_port[NI_MAXSERV];
441+
int ret;
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+
case AF_INET:
454+
#ifdef HAVE_IPV6
455+
case AF_INET6:
456+
#endif
457+
break;
458+
case AF_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-
#define CATALOG_VERSION_NO 200504301
56+
#define CATALOG_VERSION_NO 200505091
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_activity PGNSP PGUID 12 f f t f s
28222822
DESCR("Statistics: Current query of backend");
28232823
DATA(insert OID = 2094 ( pg_stat_get_backend_activity_start PGNSP PGUID 12 f f t f s 1 1184 "23" _null_ _null_ _null_ pg_stat_get_backend_activity_start - _null_));
28242824
DESCR("Statistics: Start time for current query of backend");
2825+
DATA(insert OID = 1391 ( pg_stat_get_backend_start PGNSP PGUID 12 f f t f s 1 1184 "23" _null_ _null_ _null_ pg_stat_get_backend_start - _null_));
2826+
DESCR("Statistics: Start time for current backend session");
2827+
DATA(insert OID = 1392 ( pg_stat_get_backend_client_addr PGNSP PGUID 12 f f t f s 1 869 "23" _null_ _null_ _null_ pg_stat_get_backend_client_addr - _null_));
2828+
DESCR("Statistics: Address of client connected to backend");
2829+
DATA(insert OID = 1393 ( pg_stat_get_backend_client_port PGNSP PGUID 12 f f t f s 1 23 "23" _null_ _null_ _null_ pg_stat_get_backend_client_port - _null_));
2830+
DESCR("Statistics: Port number of client connected to backend");
28252831
DATA(insert OID = 1941 ( pg_stat_get_db_numbackends PGNSP PGUID 12 f f t f s 1 23 "26" _null_ _null_ _null_ pg_stat_get_db_numbackends - _null_ ));
28262832
DESCR("Statistics: Number of backends in database");
28272833
DATA(insert OID = 1942 ( pg_stat_get_db_xact_commit PGNSP PGUID 12 f f t f s 1 20 "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
#ifndef PGSTAT_H
1212
#define PGSTAT_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
int m_procpid;
5555
Oid m_databaseid;
5656
AclId m_userid;
57+
SockAddr m_clientaddr;
5758
} PgStat_MsgHdr;
5859

5960
/* ----------
@@ -231,8 +232,11 @@ typedef struct PgStat_StatBeEntry
231232
Oid databaseid;
232233
Oid userid;
233234
int procpid;
235+
AbsoluteTime start_sec;
236+
int start_usec;
234237
AbsoluteTime activity_start_sec;
235238
int activity_start_usec;
239+
SockAddr clientaddr;
236240
char activity[PGSTAT_ACTIVITY_SIZE];
237241
} PgStat_StatBeEntry;
238242

0 commit comments

Comments
 (0)