Moving from postgres 13 to 15 i bumped into an issue using target_session_attrs=read-write, where default_transaction_read_only would be off on standbys, resulting in clients connecting to the standby.
I have worked around it by switching target_session_attrs to primary instead of read-write which works for our case.
Now the weird thing is that show transaction_read_only returns on, but show default_transaction_read_only returns off. default_transaction_read_only also reports off in the connection startup parameters.
The reason it worked in postgres 13 is because postgres doesn't send default_transaction_read_only on startup so the client falls back to checking by show transaction_read_only.
If I call alter system set default_transaction_read_only to on and select pg_reload_conf() myself on the standbys they return on for default_transaction_read_only as expected.
Moving from postgres 13 to 15 i bumped into an issue using
target_session_attrs=read-write
, wheredefault_transaction_read_only
would beoff
on standbys, resulting in clients connecting to the standby.I have worked around it by switching
target_session_attrs
toprimary
instead ofread-write
which works for our case.Now the weird thing is that
show transaction_read_only
returnson
, butshow default_transaction_read_only
returnsoff
.default_transaction_read_only
also reports off in the connection startup parameters.The reason it worked in postgres 13 is because postgres doesn't send
default_transaction_read_only
on startup so the client falls back to checking byshow transaction_read_only
.If I call
alter system set default_transaction_read_only to on
andselect pg_reload_conf()
myself on the standbys they returnon
fordefault_transaction_read_only
as expected.