[9.1] pg_stat_get_backend_server_addr
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?
Attachments:
get-server-addr.patchtext/x-patch; charset=UTF-8; name=get-server-addr.patchDownload
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;
Peter Eisentraut <peter_e@gmx.net> writes:
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,
... indeed. Is it worth burdening the pg_stats mechanism with this?
The use case seems vanishingly thin.
regards, tom lane
Tom Lane wrote:
Peter Eisentraut <peter_e@gmx.net> writes:
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,... indeed. Is it worth burdening the pg_stats mechanism with this?
The use case seems vanishingly thin.
I am confused how this is different from inet_server_addr() and
inet_server_port().
Also, these functions return nothing for unix domain connections.
Should they, particularly for the port number which we do use to map to
a socket name?
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
Bruce Momjian <bruce@momjian.us> writes:
Tom Lane wrote:
... indeed. Is it worth burdening the pg_stats mechanism with this?
The use case seems vanishingly thin.
I am confused how this is different from inet_server_addr() and
inet_server_port().
I think the point is to let someone find out *from another session*
which server port number a particular session is using. I fail to see
a significant use case for that, though.
regards, tom lane
Tom Lane wrote:
Bruce Momjian <bruce@momjian.us> writes:
Tom Lane wrote:
... indeed. Is it worth burdening the pg_stats mechanism with this?
The use case seems vanishingly thin.I am confused how this is different from inet_server_addr() and
inet_server_port().I think the point is to let someone find out *from another session*
which server port number a particular session is using. I fail to see
a significant use case for that, though.
Uh, aren't they all using the same server port number, e.g. 5432? Is
the issue different IP addresses for the same server?
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
On fre, 2010-05-28 at 10:21 -0400, Bruce Momjian wrote:
Tom Lane wrote:
Bruce Momjian <bruce@momjian.us> writes:
Tom Lane wrote:
... indeed. Is it worth burdening the pg_stats mechanism with this?
The use case seems vanishingly thin.I am confused how this is different from inet_server_addr() and
inet_server_port().I think the point is to let someone find out *from another session*
which server port number a particular session is using. I fail to see
a significant use case for that, though.Uh, aren't they all using the same server port number, e.g. 5432? Is
the issue different IP addresses for the same server?
Yes, I would like to know who is connecting to what IP address. It's
useful if you have HA setups and you need to check which way your
connections are going.
Peter Eisentraut wrote:
On fre, 2010-05-28 at 10:21 -0400, Bruce Momjian wrote:
Tom Lane wrote:
Bruce Momjian <bruce@momjian.us> writes:
Tom Lane wrote:
... indeed. Is it worth burdening the pg_stats mechanism with this?
The use case seems vanishingly thin.I am confused how this is different from inet_server_addr() and
inet_server_port().I think the point is to let someone find out *from another session*
which server port number a particular session is using. I fail to see
a significant use case for that, though.Uh, aren't they all using the same server port number, e.g. 5432? Is
the issue different IP addresses for the same server?Yes, I would like to know who is connecting to what IP address. It's
useful if you have HA setups and you need to check which way your
connections are going.
OK, at least now I understand the goal.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
On Fri, 2010-05-28 at 18:01 +0300, Peter Eisentraut wrote:
Yes, I would like to know who is connecting to what IP address. It's
useful if you have HA setups and you need to check which way your
connections are going.
A few comments on this patch:
The two functions aren't perfectly symmetric, because
pg_stat_get_backend_server_port() returns -1 if it's a unix socket, and
pg_stat_get_backend_server_addr() returns NULL (which is also overloaded
to mean that you don't have permissions). So, perhaps it's better to
just have pg_stat_get_backend_server_addr(), which is the one you want,
anyway.
Also, for the permission check I'm inclined to throw an error rather
than return NULL. If the function is being called from a view, it's
understandable that we don't want to throw an error; but this function
isn't being called from a view. Based on your use-case, I'm more worried
about the HA system getting confused with a NULL result, and then
failing mysteriously with no error message.
Regards,
Jeff Davis
On ons, 2010-07-21 at 22:12 -0700, Jeff Davis wrote:
The two functions aren't perfectly symmetric, because
pg_stat_get_backend_server_port() returns -1 if it's a unix socket,
and
pg_stat_get_backend_server_addr() returns NULL (which is also
overloaded
to mean that you don't have permissions). So, perhaps it's better to
just have pg_stat_get_backend_server_addr(), which is the one you
want,
anyway.
This mirrors exactly the pg_stat_get_backend_client_* behaviors. I
don't much like them either, but I think it'd be worse to make it
inconsistent.
On tor, 2010-05-27 at 22:32 +0300, Peter Eisentraut wrote:
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.)
I think I'm not going to pursue this patch anymore. There hasn't been
any enthusiasm from anyone else, and if necessary the information can be
carved out of netstat.