BUG #18663: synchronous_standby_names vs synchronous_commit vs pg_stat_replication

On 2024/10/18 23:48, PG Bug reporting form wrote:

The following bug has been logged on the website:

Bug reference: 18663
Logged by: Asphator
Email address: asphator@gmail.com
PostgreSQL version: 15.7
Operating system: RHEL7+
Description:

Hello

I would like to raise 2 issues:
- documentation issue
- pg_stat_replication view issue in a very specific use case

1. Documentation
Regarding parameter synchronous_standby_names, I did not manage to find
anything in documentation preventing from setting value 0. The only thing
I've found is a comment in a commit of 2016. Would be nice to mention in
documentation that value must be a "positive" integer.

Agreed. I've attached the patch.

2. Synchronous_mode
Seems to me that quorum mode is only effective when both conditions below
are met:
- synchronous_commit is set either to "on / remote_write", "remote_flush" or
"remote_apply"
- synchronous_standby_names is set to "ANY N (....)", with N >= 1 and (...)
a list of slaves

According to the documentation, setting synchronous_commit value either to
local or off, meaning we do not care at all about any kind of ACK from any
slave), should thus be enough to disable synchronous mode.

So if you do so, event if you let (or better say forget, because we agree
it's non consistent) parameter synchronous_standby_names with value "ANY N
(...)", we are supposed to be in asynchronous mode.
Still, view pg_stat_replication shows up "quorum" which is (worse of) a
non-sense. Should show up "async" to me.
Or am i wrong here ?

Even if synchronous_commit is set to off or local in postgresql.conf,
if synchronous_commit is set to on, remote_write, or remote_apply for
some transactions (for example, via the SET command), those transactions
will still use synchronous replication. Displaying "async" in such cases
would be misleading.

From what I understand, replication mode can be set both per-transaction
(using synchronous_commit) and per-standby (using synchronous_standby_names).
Transactions with synchronous mode (e.g., synchronous_commit = on) should
wait for replies from standbys that are also set to sync mode (i.e.,
standbys listed in synchronous_standby_names), which is how sync replication works.
On the other hand, if either the transaction or standby is set to async,
it results in async replication. Based on my understanding,
pg_stat_replication.sync_state shows the replication mode per standby,
so it makes sense to display "quorum" when that's the case.

Regards,

--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION

On 2024/10/21 18:43, Fujii Masao wrote:

On 2024/10/18 23:48, PG Bug reporting form wrote:

The following bug has been logged on the website:

Bug reference:      18663
Logged by:          Asphator
Email address:      asphator@gmail.com
PostgreSQL version: 15.7
Operating system:   RHEL7+
Description:

Hello

I would like to raise 2 issues:
- documentation issue
- pg_stat_replication view issue in a very specific use case

1. Documentation
Regarding parameter synchronous_standby_names, I did not manage to find
anything in documentation preventing from setting value 0. The only thing
I've found is a comment in a commit of 2016. Would be nice to mention in
documentation that value must be a "positive" integer.

Agreed. I've attached the patch.

I made a slight wording adjustment.
Unless there are objections, I'll commit the attached patch.

Regards,

--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION

On 2025/01/09 9:35, Fujii Masao wrote:

On 2024/10/21 18:43, Fujii Masao wrote:

On 2024/10/18 23:48, PG Bug reporting form wrote:

The following bug has been logged on the website:

Bug reference:      18663
Logged by:          Asphator
Email address:      asphator@gmail.com
PostgreSQL version: 15.7
Operating system:   RHEL7+
Description:

Hello

I would like to raise 2 issues:
- documentation issue
- pg_stat_replication view issue in a very specific use case

1. Documentation
Regarding parameter synchronous_standby_names, I did not manage to find
anything in documentation preventing from setting value 0. The only thing
I've found is a comment in a commit of 2016. Would be nice to mention in
documentation that value must be a "positive" integer.

Agreed. I've attached the patch.

I made a slight wording adjustment.
Unless there are objections, I'll commit the attached patch.

Pushed.

Regards,

--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION

Hello

I've just made the test and now, indeed, i understand the choice and agree
with it.
So basically:

Thx for the push.

Le jeu. 9 janv. 2025 à 13:11, Fujii Masao <masao.fujii@oss.nttdata.com> a
écrit :

On 2025/01/09 9:35, Fujii Masao wrote:

On 2024/10/21 18:43, Fujii Masao wrote:

On 2024/10/18 23:48, PG Bug reporting form wrote:

The following bug has been logged on the website:

Bug reference: 18663
Logged by: Asphator
Email address: asphator@gmail.com
PostgreSQL version: 15.7
Operating system: RHEL7+
Description:

Hello

I would like to raise 2 issues:
- documentation issue
- pg_stat_replication view issue in a very specific use case

1. Documentation
Regarding parameter synchronous_standby_names, I did not manage to find
anything in documentation preventing from setting value 0. The only

thing

I've found is a comment in a commit of 2016. Would be nice to mention

in

documentation that value must be a "positive" integer.

Agreed. I've attached the patch.

I made a slight wording adjustment.
Unless there are objections, I'll commit the attached patch.

Pushed.

Regards,

--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION

--

____________________
S'instruire pour vaincre...