Re: Patch: Implement failover on libpq connect level.

From: pgsql-hackers-owner(at)postgresql(dot)org
> [mailto:pgsql-hackers-owner(at)postgresql(dot)org] On Behalf Of Robert Haas
> I am very strict about regressing the performance of things that we already
> have, but I try not to make a policy that a new feature must be as fast
> as it could ever be. That could result in us having very few new features.

I see. I like your attitude toward new features. But I don't think now is the time to compromise this feature and rush to the commit.

> Also, I am not saying that we should not change this in time for v10.
> I'm saying that I don't think it should be a requirement for the next patch
> to be committed in this area to introduce a whole new mechanism for
> determining whether something is a master or a standby. Love it or hate
> it, pgsql-jdbc has already implemented something in this area and it does
> something useful -- without requiring a wire protocol change. Now you and
> Kevin are trying to say that what they did is all wrong, but I don't agree.
> There are very many users for whom the pgsql-jdbc approach will do exactly
> what they need, and no doubt some for whom it won't. Getting a patch that
> mimics that approach committed is *progress*. Improving it afterwards,
> whether for v10 or some later release, is also good.

transaction_read_only=on does not mean the standby. As the manual article on hot standby says, they are different.

https://www.postgresql.org/docs/devel/static/hot-standby.html

[Excerpt]
--------------------------------------------------
In normal operation, “read-only” transactions are allowed to update sequences and to use LISTEN, UNLISTEN, and NOTIFY, so Hot Standby sessions operate under slightly tighter restrictions than ordinary read-only sessions. It is possible that some of these restrictions might be loosened in a future release.
...
Users will be able to tell whether their session is read-only by issuing SHOW transaction_read_only. In addition, a set of functions (Table 9.79, “Recovery Information Functions”) allow users to access information about the standby server. These allow you to write programs that are aware of the current state of the database. These can be used to monitor the progress of recovery, or to allow you to write complex programs that restore the database to particular states.
--------------------------------------------------

I'm afraid that if the current patch is committed, you will lose interest in the ideal solution. Then if the current patch is out as v10, there would be a concern about incompatibility when we pursue the ideal solution in a later release. That is, "should we continue to report that this server is standby even if it's actually a primary with transaction_read_only is on, to maintain compatibility with the older release."

If you want to connect to a server where the transaction is read-only, then shouldn't the connection parameter be something like "target_session_attrs=readonly"? That represents exactly what the code does.

> There is a saying that one should not let the perfect be the enemy of the
> good. I believe that saying applies here.

True, so I suggested not including the support for older servers for a while. Shall we find the real enemy -- what's preventing the ideal solution? I know my knowledge is still far less than you, so I may be missing something difficult. So, I'd like Mithun to share the difficulty.

Regards
Takayuki Tsunakawa