On 2015.08.19 at 09:21:50 +0000, Albe Laurenz wrote: > > > Yes, but that will only work reliably if the (read-only) standby does not > > > allow connections before it is promoted. > > > > It would just take a bit more time for client and a bit more load for > > server - to make sure that this connection is read-write by > > issuing > > > > show transaction_read_only > > > > statement before considering connection useful. > > That's not very comfortable, and a lot of middleware software won't easily > learn the trick.
It shouldn't be left to middleware. It should be hidden into PQConnectPoll. This function already handle very complicated state transition diagram, including authentication, SSL negotiation and so on. If we just add couple of new states such as CONNECTION_ASK_RW_STATUS and CONNECTION_RW_STATUS_OK it should be fine. Application would just call PQConnectPoll repeatedly (either via PQconnectdb or explicitely when readable/writable condition detected on the socket integrated into app even loop) until success or unrecoverable error would be achieved. How many interaction with server it would take, it is not middleware problem. > > It seems to me that in most cases last host in the connect string would > > be only host which accepts connections, so it wins anyway > > I'm not saying that it is particularly wide-spread and useful; it could > happen through careless editing of connection strings or by using a > connection service file entry > (http://www.postgresql.org/docs/current/static/libpq-pgservice.html) > and overriding the host parameter on the command line. I don't think that host definition from all possible sources (service file, environment, command line) should be collected together. Although it is essential to be clear when host list is appended and when - replaced. If we implement sequential trial of all hosts, then we can start with last one, to provide compatibility with existing behavior. In this case if last host is online, no change occur. Another idea - is to enable multiple host connection only if special option (loadbalance or failover) present in the connect string. > > Other idea - allow to specify host-port pair as argument of host > > parameter. > > > > host=db1.myorg.com:5432 > > > > It is consistent with URL syntax and system administrators are used to > > it. And with long list of hosts there is less chances to made an error > > as host and corresponding port come together. > > I don't think that is very attactive as it confuses the distinction between > "host" and "port". What would you do with > > host=db1.myorg.com:2345 port=1234 I don't think that it does add any more confusion than simultaneous existence of host and hostaddr, or ability to specify host and port both in host part of URL and query parameters postgresql://user@host:5432/dbname?host=otherhost&port=2345 Bot really, you've convinced me that syntax with two or three (host, port and hostaddr) parallel lists is the best. Although we'll need to allow empty entries in the lists such as host=master.db.com,standby1.db.com.standby2.db.com port=,2345, hostaddr=,192.168.0.4,192.160.1.8 with evident semantic: get port, host and hostaddr elements with same number, and if some of them are empty, behave as it was only host and corresponding parameter not specified at all. -- Victor Wagner <vi...@wagner.pp.ru> -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers