hapostgres / hapostgres/pg_auto_failover

default_transaction_read_only is not set to on for standbys

Open
#970 0 comments 0 reactions 0 assignees View on GitHub
bug good first issue
Dominant language
C
Stars
1.4k
Forks
142
Avg merge
5h 8m
Merged PRs (30d)
1

Description

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.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.