Re: Libpq support to connect to standby server as priority

Tsunakawa, Takayuki wrote:
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> > The problem here of course is that whoever invented target_session_attrs
> > was unconcerned with following that precedent, so what we have is
> > "target_session_attrs=(any | read-write)".
> > Are we prepared to add some aliases in service of unifying these names?
>
> I think "yes".
>
> > 2. Whether or not you want to follow pgJDBC's naming, it seems like we
> > ought to have both "require read only" and "prefer read only" behaviors
> > in this patch, and maybe likewise "require read write" versus "prefer
> > read write".
>
> Agreed, although I don't see a use case for "prefer read write". I don't think
> there's an app like "I want to write, but I'm OK if I cannot."

I don't think so either, although of course I cannot prove it.

My opinion is that we shouldn't add options like "prefer read write"
just out of a fuzzy desire for symmetry. It would probably make the code
even more complicated, and more choice means that it becomes harder for
the user to pick the right one (the latter may be a weak argument).

The motivation behind all this is to load balance reading and writing
sessions among a group of replicating servers where you don't know for sure
who is what at the moment, so "preferably read-only", "must be able to write"
and "don't care" are choice enough.

There is nothing that bars future patches from adding additional modes
if the need really arises.

> > 4. Given that other discussion, it's not quite clear what we should
> > even be checking. The existing logic devolves to checking that
> > transaction_read_only is true, but that's not really the same thing as
> > "is a master server", eg you might have connected to a master server
> > under a role that has SET ROLE default_transaction_read_only = false.
>
> PgJDBC uses transaction_read_only like this: [...]
>
> But as some people said, I don't think this is the right way. I suppose what's leading
> to the current somewhat complicated situation is that there was no easy way for the
> client to know whether the server is the master. That ended up in using
> "SHOW transaction_read_only" instead, and people supported that compromise by saying
> "read only status is more useful than whether the server is standby or not," I'm afraid.
>
> The original desire should have been the ability to connect to a primary or a standby.
> So, I think we should go back to the original thinking (and not complicate the feature),
> and create a read only GUC_REPORT variable, say, server_role, that identifies whether
> the server is a primary or a standby.

I think that transaction_read_only is good.

If it is set to false, we are sure to be on a replication primary or
stand-alone server, which is enough to know for the load balancing use case.

I deem it unlikely that someone will set default_transaction_read_only to
FALSE and then complain that the feature is not working as expected, but again
I cannot prove that claim.

As Robert said, transaction_read_only might even give you the option to
use the feature for more than just load balancing between replication master and standby.

Yours,
Laurenz Albe