Re: Libpq support to connect to standby server as priority

Tsunakawa, Takayuki wrote:
> From: Laurenz Albe [mailto:laurenz(dot)albe(at)cybertec(dot)at]
> > 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.
>
> As Tatsuo-san said, setting default_transaction_read_only leads to a misjudgement of the primary.

Yes, you can have a false negative, i.e. fail to recognize a primary server.

> > 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.
>
> I wonder what default_transaction_read_only exists for. For maing the database by default
> and allowing only specific users to write to the database with "CREATE/ALTER USER SET
> default_transaction_read_only = false"?

I'd guess that the main use of default_transaction_read_only is to make sure an
application (that isn't smart enough to change the parameter) won't modify any data.

> I'm sorry to repeat myself, but anyway, I think we need a method to connect to a standby
> as the original desire, because the primary instance may be read only by default while
> only limited users update data. That's for reducing the burdon on the primary and
> minimizing the impact on users who update data. For example,
>
> * run data reporting on the standby
> * backup the database from the standby
> * cascade replication from the standby

I see.

But then the new value should not be called "prefer-read", because that would be
misleading. It would also not be related to the existing "read-write".

For what you have in mind, there should be the options "primary-required" and
"standby-preferred", however we implement them.

Have there been a lot of complaints that the existing "read-write" is not good
enough to detect replication primaries?

> > 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.
>
> What use case do you think of? If you want to load balance the workload between
> the primary and standbys, we can follow PgJDBC -- targetServerType=any.

One use case I can think of is logical replication (or other replication methods like
Slony). You can use the feature by setting default_transaction_read_only = on
on the standby.

Yours,
Laurenz Albe