Re: Enhance pg_stat_wal_receiver view to display connected host

On Mon, Jan 29, 2018 at 7:06 PM, Michael Paquier <michael(dot)paquier(at)gmail(dot)com>
wrote:

> On Tue, Jan 16, 2018 at 05:56:22PM +1100, Haribabu Kommi wrote:
> > Without PQhostaddr() function, for the connections where the host is not
> > specified, it will be difficult to find out to remote server.
>
> That's true as well, but hostaddr should be used with host only to save
> IP lookups... There are recent threads on the matter, like this one:
> https://www.postgresql.org/message-id/15728.1493654814%40sss.pgh.pa.us
> See particularly the commits cited in this message. PQhostaddr has been
> already introduced, and reverted in the tree.
>

Thanks for the link, I checked it. The main reason for the revert was
because
of using host to return the hostaddr even though the host is not specified.
But it may be fine to return the hostaddr with a different function.
Following
are sequence of commits that has changed the behavior.

commit 274bb2b3 introduced the support of returning PQhost() function to
return the connected host. (internally hostaddr also)

commit 11003eb5 added a check in the PQhost() function not to return the
host if the connection type is CHT_HOST_ADDRESS.

This is because an earlier change in connectOptions2() function that changes
the host with hostaddr, because of this reason, PQhost() function returns
the hostaddr and it is not expected from PQhost() function when the host is
not specified.

if (conn->pghostaddr != NULL && conn->pghostaddr[0] != '\0')
{
conn->connhost[0].host = strdup(conn->pghostaddr);
if (conn->connhost[0].host == NULL)
goto oom_error;
conn->connhost[0].type = CHT_HOST_ADDRESS;
}

But the above part of the code is changed in commit 7b02ba62
to support "Allow multiple hostaddrs to go with multiple hostnames"
With this commit, the commit 11003eb5 that blocks the host names
that are of CHT_HOST_ADDRESS connection type is not be required.

> This may lead to some confusion as well. Take for example this
> connection string:
> 'port=6666,5432 hostaddr=127.0.0.1,127.0.0.1 host=/tmp,/tmp'
> =# select remote_hostname, remote_hostaddr, remote_port from
> pg_stat_wal_receiver;
> remote_hostname | remote_hostaddr | remote_port
> -----------------+-----------------+-------------
> /tmp | 127.0.0.1 | 5432
> (1 row)
> The documentation states that in this case the IP connection is used,
> though this can be confusing for users to show both. I'll bet that we
> would get complains about that, without at least proper documentation.
>

Ok, understood. As the libpq gives preference to hostaddr connection
parameter than host while connecting. How about going with one column
"remote_host" that displays either hostaddr(if exists) or hostname. So that
one column that displays the actual remote host to where it connected?

Note : The one column approach for both host and hostaddr will depend on
how we go with PQhostaddr() function.

> So my take would be to really just use PQhost and PQport, as this does
> not remove any usefulness of this feature. If you want to use IP
> addresses, there is nothing preventing you to use them in host as well,
> and those would show up properly. The commit fest is coming to an end,
> so my recommendation would be to move it on the next CF and get feedback
> on https://www.postgresql.org/message-id/CAJrrPGdrC4JTJQ4d7PT1B
> i7K8nW91XPMPQ5kJ3GWK3ts%2BW-35g%40mail.gmail.com
> before concluding on this feature. The problem with PQhost and multiple
> hosts is quite different than the 1st thread I am referring in this
> email, so let's wait and see for Robert's input.
>

OK. I will move the patch to next commitfest.

Regards,
Hari Babu
Fujitsu Australia