There are functions pg_stat_get_backend_client_addr and
pg_stat_get_backend_client_port, which are exposed through the
pg_stat_activity view, but there is no straightforward way to get the
server-side address and port of a connection. This is obviously much
less commonly needed than the client information, but it's still
sometimes useful on hosts with many IP addresses.
I suggest that we add the functions pg_stat_get_backend_server_addr and
pg_stat_get_backend_server_port, but don't expose them in
pg_stat_activity. (_server_port is really mostly for symmetry, because
you can't currently bind to multiple ports.)
Patch attached. Comments?
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index d197731..b1f7645 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -838,6 +838,28 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
</row>
<row>
+ <entry><literal><function>pg_stat_get_backend_server_addr</function>(<type>integer</type>)</literal></entry>
+ <entry><type>inet</type></entry>
+ <entry>
+ The IP address on the given server process that the client is
+ connected to. Null if the connection is over a Unix domain
+ socket. Also null if the current user is not a superuser nor
+ the same user as that of the session being queried
+ </entry>
+ </row>
+
+ <row>
+ <entry><literal><function>pg_stat_get_backend_server_port</function>(<type>integer</type>)</literal></entry>
+ <entry><type>integer</type></entry>
+ <entry>
+ The TCP port number on the given server process that the client
+ is connected to. -1 if the connection is over a Unix domain
+ socket. Null if the current user is not a superuser nor the
+ same user as that of the session being queried
+ </entry>
+ </row>
+
+ <row>
<entry><literal><function>pg_stat_get_bgwriter_timed_checkpoints</function>()</literal></entry>
<entry><type>bigint</type></entry>
<entry>
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index edb5c80..b3a7d82 100644
--- a/src/backend/postmaster/pgstat.c
+++ b/src/backend/postmaster/pgstat.c
@@ -2278,6 +2278,7 @@ pgstat_bestart(void)
TimestampTz proc_start_timestamp;
Oid userid;
SockAddr clientaddr;
+ SockAddr serveraddr;
volatile PgBackendStatus *beentry;
/*
@@ -2296,12 +2297,18 @@ pgstat_bestart(void)
/*
* We may not have a MyProcPort (eg, if this is the autovacuum process).
* If so, use all-zeroes client address, which is dealt with specially in
- * pg_stat_get_backend_client_addr and pg_stat_get_backend_client_port.
+ * pg_stat_get_backend_(client|server)_(addr|port).
*/
if (MyProcPort)
+ {
memcpy(&clientaddr, &MyProcPort->raddr, sizeof(clientaddr));
+ memcpy(&serveraddr, &MyProcPort->laddr, sizeof(serveraddr));
+ }
else
+ {
MemSet(&clientaddr, 0, sizeof(clientaddr));
+ MemSet(&serveraddr, 0, sizeof(serveraddr));
+ }
/*
* Initialize my status entry, following the protocol of bumping
@@ -2322,6 +2329,7 @@ pgstat_bestart(void)
beentry->st_databaseid = MyDatabaseId;
beentry->st_userid = userid;
beentry->st_clientaddr = clientaddr;
+ beentry->st_serveraddr = serveraddr;
beentry->st_waiting = false;
beentry->st_appname[0] = '\0';
beentry->st_activity[0] = '\0';
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 8379407..9af2b5a 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -56,6 +56,8 @@ extern Datum pg_stat_get_backend_xact_start(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_backend_start(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_backend_client_addr(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_backend_client_port(PG_FUNCTION_ARGS);
+extern Datum pg_stat_get_backend_server_addr(PG_FUNCTION_ARGS);
+extern Datum pg_stat_get_backend_server_port(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_db_numbackends(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_db_xact_commit(PG_FUNCTION_ARGS);
@@ -887,6 +889,101 @@ pg_stat_get_backend_client_port(PG_FUNCTION_ARGS)
Datum
+pg_stat_get_backend_server_addr(PG_FUNCTION_ARGS)
+{
+ int32 beid = PG_GETARG_INT32(0);
+ PgBackendStatus *beentry;
+ SockAddr zero_serveraddr;
+ char local_host[NI_MAXHOST];
+ int ret;
+
+ if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
+ PG_RETURN_NULL();
+
+ if (!superuser() && beentry->st_userid != GetUserId())
+ PG_RETURN_NULL();
+
+ /* A zeroed server addr means we don't know */
+ memset(&zero_serveraddr, 0, sizeof(zero_serveraddr));
+ if (memcmp(&(beentry->st_serveraddr), &zero_serveraddr,
+ sizeof(zero_serveraddr) == 0))
+ PG_RETURN_NULL();
+
+ switch (beentry->st_serveraddr.addr.ss_family)
+ {
+ case AF_INET:
+#ifdef HAVE_IPV6
+ case AF_INET6:
+#endif
+ break;
+ default:
+ PG_RETURN_NULL();
+ }
+
+ local_host[0] = '\0';
+ ret = pg_getnameinfo_all(&beentry->st_serveraddr.addr,
+ beentry->st_serveraddr.salen,
+ local_host, sizeof(local_host),
+ NULL, 0,
+ NI_NUMERICHOST | NI_NUMERICSERV);
+ if (ret)
+ PG_RETURN_NULL();
+
+ clean_ipv6_addr(beentry->st_serveraddr.addr.ss_family, local_host);
+
+ PG_RETURN_INET_P(DirectFunctionCall1(inet_in,
+ CStringGetDatum(local_host)));
+}
+
+Datum
+pg_stat_get_backend_server_port(PG_FUNCTION_ARGS)
+{
+ int32 beid = PG_GETARG_INT32(0);
+ PgBackendStatus *beentry;
+ SockAddr zero_serveraddr;
+ char local_port[NI_MAXSERV];
+ int ret;
+
+ if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
+ PG_RETURN_NULL();
+
+ if (!superuser() && beentry->st_userid != GetUserId())
+ PG_RETURN_NULL();
+
+ /* A zeroed server addr means we don't know */
+ memset(&zero_serveraddr, 0, sizeof(zero_serveraddr));
+ if (memcmp(&(beentry->st_serveraddr), &zero_serveraddr,
+ sizeof(zero_serveraddr) == 0))
+ PG_RETURN_NULL();
+
+ switch (beentry->st_serveraddr.addr.ss_family)
+ {
+ case AF_INET:
+#ifdef HAVE_IPV6
+ case AF_INET6:
+#endif
+ break;
+ case AF_UNIX:
+ PG_RETURN_INT32(-1);
+ default:
+ PG_RETURN_NULL();
+ }
+
+ local_port[0] = '\0';
+ ret = pg_getnameinfo_all(&beentry->st_serveraddr.addr,
+ beentry->st_serveraddr.salen,
+ NULL, 0,
+ local_port, sizeof(local_port),
+ NI_NUMERICHOST | NI_NUMERICSERV);
+ if (ret)
+ PG_RETURN_NULL();
+
+ PG_RETURN_DATUM(DirectFunctionCall1(int4in,
+ CStringGetDatum(local_port)));
+}
+
+
+Datum
pg_stat_get_db_numbackends(PG_FUNCTION_ARGS)
{
Oid dbid = PG_GETARG_OID(0);
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 4f7d920..dfb226e 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -53,6 +53,6 @@
*/
/* yyyymmddN */
-#define CATALOG_VERSION_NO 201004261
+#define CATALOG_VERSION_NO 201005271
#endif
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index c11c2fd..10b15d7 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -3049,6 +3049,10 @@ DATA(insert OID = 1392 ( pg_stat_get_backend_client_addr PGNSP PGUID 12 1 0 0 f
DESCR("statistics: address of client connected to backend");
DATA(insert OID = 1393 ( pg_stat_get_backend_client_port PGNSP PGUID 12 1 0 0 f f f t f s 1 0 23 "23" _null_ _null_ _null_ _null_ pg_stat_get_backend_client_port _null_ _null_ _null_ ));
DESCR("statistics: port number of client connected to backend");
+DATA(insert OID = 950 ( pg_stat_get_backend_server_addr PGNSP PGUID 12 1 0 0 f f f t f s 1 0 869 "23" _null_ _null_ _null_ _null_ pg_stat_get_backend_server_addr _null_ _null_ _null_ ));
+DESCR("statistics: address on server that client is connected to");
+DATA(insert OID = 951 ( pg_stat_get_backend_server_port PGNSP PGUID 12 1 0 0 f f f t f s 1 0 23 "23" _null_ _null_ _null_ _null_ pg_stat_get_backend_server_port _null_ _null_ _null_ ));
+DESCR("statistics: port number on server that client is connected to");
DATA(insert OID = 1941 ( pg_stat_get_db_numbackends PGNSP PGUID 12 1 0 0 f f f t f s 1 0 23 "26" _null_ _null_ _null_ _null_ pg_stat_get_db_numbackends _null_ _null_ _null_ ));
DESCR("statistics: number of backends in database");
DATA(insert OID = 1942 ( pg_stat_get_db_xact_commit PGNSP PGUID 12 1 0 0 f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_db_xact_commit _null_ _null_ _null_ ));
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 3dd5f45..816f86a 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -597,10 +597,11 @@ typedef struct PgBackendStatus
TimestampTz st_xact_start_timestamp;
TimestampTz st_activity_start_timestamp;
- /* Database OID, owning user's OID, connection client address */
+ /* Database OID, owning user's OID, connection client and server address */
Oid st_databaseid;
Oid st_userid;
SockAddr st_clientaddr;
+ SockAddr st_serveraddr;
/* Is backend currently waiting on an lmgr lock? */
bool st_waiting;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
