Re: 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