[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?

Peter Eisentraut <peter_e(at)gmx(dot)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(at)gmx(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

Bruce Momjian <bruce(at)momjian(dot)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(at)momjian(dot)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(at)momjian(dot)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(at)momjian(dot)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(at)momjian(dot)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(at)momjian(dot)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.