Re: [PATCH] libpq: Allow specifying multiple host names to try to connect to

Steve Singer <steve@ssinger.info> writes:

> On 04/19/2015 11:18 AM, Mikko Tiihonen wrote:
>>
>> Hi,
>>
>>
>> I would like allow specifying multiple host names for libpq to try to
>> connecting to. This is currently only supported if the host name
>> resolves to multiple addresses. Having the support for it without
>> complex dns setup would be much easier.
>>
>>
>> Example:
>>
>> psql -h dbslave,dbmaster -p 5432 dbname
>>
>> psql 'postgresql://dbslave,dbmaster:5432/dbname'
>>
>>
>> Here the idea is that without any added complexity of pgbouncer or
>> similar tool I can get any libpq client to try connecting to multiple
>> nodes until one answers. I have added the similar functionality to the
>> jdbc driver few years ago.
>>
>>
>> Because libpq almost supported the feature already the patch is very
>> simple. I just split the given host name and do a dns lookup on each
>> separately, and link the results.
>>
>>
>> If you configure a port that does not exist you can see the libpq
>> trying to connect to multiple hosts.
>>
>>
>> psql -h 127.0.0.2,127.0.0.3, -p 5555
>>
>> psql: could not connect to server: Connection refused
>>     Is the server running on host "127.0.0.2,127.0.0.3" (127.0.0.2)
>> and accepting
>>     TCP/IP connections on port 5555?
>> could not connect to server: Connection refused
>>     Is the server running on host "127.0.0.2,127.0.0.3" (127.0.0.3)
>> and accepting
>>     TCP/IP connections on port 5555?
>>
>> Further improvement would be to add a connection parameter to limit
>> connection only to master (writable) or to slave (read only).
>
> Another concern I have is that the server needs to be listening on the
> same port against all hosts this means that in a development environment
> we can't fully test this feature using just a single server.  I can't
> think of anything else we have in core that couldn't be tested on a
> single server (all the replication stuff works fine if you setup two
> separate clusters on different ports on one server)
>
> You update the documentation just for  psql but your change effects any
> libpq application if we go forward with this patch we should update the
> documentation for libpq as well.
>
> This approach seems to work with the url style of conninfo
>
> For example
>   postgres://some-down-host.info,some-other-host.org:5435/test1
>
> seems to work as expected but I don't like that syntax I would rather see
> postgres://some-down-host.info:5435/test1,postgres://some-other-host.org:5435/test1
>
> This would be a more invasive change but I think the syntax is more usable.

I agree with this; it seems to me that it's more powerful to be able to
specify complete urls for when they may differ.

For the non-url case though, I don't see a clean way of doing this.  We
could always, e.g., locally bind port specification to the closest host
specification, but that seems nasty, and is still less powerful than
passing urls (or we could just do the same for all parameters, but
that's just a mess).

Might it be reasonable to only allow the multi-host syntax in the
url-style and not otherwise?