Tom>Yes, we need either session open or reconnect it approach to find out
Tom>the whether server is read-write or read-only.
Just in case, pgjdbc has that feature for quite a while, and the behavior there is to keep the connection until it fails or application decides to close it.
pgjdbc uses three parameters (since 2014):
1) targetServerType=(any | master | secondary | preferSecondary). Default is "any". When set to "master" it will look for "read-write" server. If set to "preferSecondary" it would search for "read-only" server first, then fall back to master, and so on.
2) loadBalanceHosts=(true | false). pgjdbc enables to load-balance across servers provided in the connection URL. When set to "false", pgjdbc tries connections in order, otherwise it shuffles the connections.
3) hostRecheckSeconds=int. pgjdbc caches "read/write" status of a host:port combination, so it don't re-check the status if multiple connections are created within hostRecheckSeconds timeframe.
It is sad that essentially the same feature is re-implemented in core with different name/semantics.
Does it make sense to align parameter names/semantics?