Thread: [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?
Attachment
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.