From: Laurenz Albe [mailto:laurenz.albe@cybertec.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.
> 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"?
default_transaction_read_only is a user settable parameter, even if it set as true by default,
any user can change it later. Deciding server type based on this whether it supports read-write
or read-only can go wrong, as the user can change it later.
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
IMO, if we try to use only pg_is_in_recovery() only to decide to connect, we may not
support all the target_session_attrs that are possible. how about using both to decide?
Master/read-write -- recovery = false and default_transaction_read_only = false
Standby/read-only -- recovery = true
prefer-standby/prefer-read -- recovery = true or default_transaction_read_only = true
any -- Nothing to be verified
I feel above verifications can cover for both physical and logical replication.
we can decide what type of options that we can support? and also if we
don't want to rely on default_transaction_read_only user settable parameter,
we can add a new parameter that cannot be changed only with server restart?