Horton
Menu

Thread

  1. [9.1] pg_stat_get_backend_server_addr

    Peter Eisentraut <peter_e@gmx.net> — 2010-05-27T19:32:25Z

    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?

  2. Re: [9.1] pg_stat_get_backend_server_addr

    Tom Lane <tgl@sss.pgh.pa.us> — 2010-05-27T19:44:52Z

    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

  3. Re: [9.1] pg_stat_get_backend_server_addr

    Bruce Momjian <bruce@momjian.us> — 2010-05-28T13:41:24Z

    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

  4. Re: [9.1] pg_stat_get_backend_server_addr

    Tom Lane <tgl@sss.pgh.pa.us> — 2010-05-28T14:19:02Z

    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

  5. Re: [9.1] pg_stat_get_backend_server_addr

    Bruce Momjian <bruce@momjian.us> — 2010-05-28T14:21:23Z

    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

  6. Re: [9.1] pg_stat_get_backend_server_addr

    Peter Eisentraut <peter_e@gmx.net> — 2010-05-28T15:01:58Z

    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.

  7. Re: [9.1] pg_stat_get_backend_server_addr

    Bruce Momjian <bruce@momjian.us> — 2010-05-28T15:02:52Z

    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

  8. Re: [9.1] pg_stat_get_backend_server_addr

    Jeff Davis <pgsql@j-davis.com> — 2010-07-22T05:12:29Z

    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

  9. Re: [9.1] pg_stat_get_backend_server_addr

    Peter Eisentraut <peter_e@gmx.net> — 2010-07-22T20:01:26Z

    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.

  10. Re: [9.1] pg_stat_get_backend_server_addr

    Peter Eisentraut <peter_e@gmx.net> — 2010-08-23T19:16:42Z

    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.