Re: [HACKERS] [9.1] pg_stat_get_backend_server_addr
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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [9.1] pg_stat_get_backend_server_addr
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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [9.1] pg_stat_get_backend_server_addr
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [9.1] pg_stat_get_backend_server_addr
Peter Eisentraut wrote: > On fre, 2010-05-28 at 10:21 -0400, Bruce Momjian wrote: > > Tom Lane wrote: > > > Bruce Momjian 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 http://momjian.us EnterpriseDB http://enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [9.1] pg_stat_get_backend_server_addr
On fre, 2010-05-28 at 10:21 -0400, Bruce Momjian wrote: > Tom Lane wrote: > > Bruce Momjian 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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [9.1] pg_stat_get_backend_server_addr
Tom Lane wrote: > Bruce Momjian 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 http://momjian.us EnterpriseDB http://enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [9.1] pg_stat_get_backend_server_addr
Bruce Momjian 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [9.1] pg_stat_get_backend_server_addr
Tom Lane wrote: > Peter Eisentraut 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 http://momjian.us EnterpriseDB http://enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [9.1] pg_stat_get_backend_server_addr
Peter Eisentraut 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [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?
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: user database host
+ pg_stat_get_backend_server_addr(integer)
+ inet
+
+ 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
+
+
+
+
+ pg_stat_get_backend_server_port(integer)
+ integer
+
+ 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
+
+
+
+
pg_stat_get_bgwriter_timed_checkpoints()
bigint
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_
