Content-Type: multipart/mixed; boundary="------------0js0fSvgZmkgC43ULm30f0V0"
Message-ID: <a8794ab2-7fba-4541-98fa-528b1e03852c@oss.nttdata.com>
Date: Mon, 21 Oct 2024 18:43:49 +0900
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: BUG #18663: synchronous_standby_names vs synchronous_commit vs
 pg_stat_replication
To: asphator@gmail.com, pgsql-bugs@lists.postgresql.org
References: <18663-b02f75cb919f1b60@postgresql.org>
Content-Language: en-US
From: Fujii Masao <masao.fujii@oss.nttdata.com>
In-Reply-To: <18663-b02f75cb919f1b60@postgresql.org>
Archived-At: 
 <https://www.postgresql.org/message-id/a8794ab2-7fba-4541-98fa-528b1e03852c%40oss.nttdata.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------0js0fSvgZmkgC43ULm30f0V0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit


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

--------------0js0fSvgZmkgC43ULm30f0V0
Content-Type: text/plain; charset=UTF-8;
 name="v1-0001-doc-Clarify-synchronous_standby_names-parameter.patch"
Content-Disposition: attachment;
 filename*0="v1-0001-doc-Clarify-synchronous_standby_names-parameter.patc";
 filename*1="h"
Content-Transfer-Encoding: base64

RnJvbSA1MzZkM2E2MjZiN2Q2MWRlZTU1ZmU0MDQzOTc3YTQ3YmU4NzViOGE2IE1vbiBTZXAg
MTcgMDA6MDA6MDAgMjAwMQpGcm9tOiBGdWppaSBNYXNhbyA8ZnVqaWlAcG9zdGdyZXNxbC5v
cmc+CkRhdGU6IE1vbiwgMjEgT2N0IDIwMjQgMTg6MDQ6MDcgKzA5MDAKU3ViamVjdDogW1BB
VENIIHYxXSBkb2M6IENsYXJpZnkgc3luY2hyb25vdXNfc3RhbmRieV9uYW1lcyBwYXJhbWV0
ZXIuCgpUaGUgc3luY2hyb25vdXNfc3RhbmRieV9uYW1lcyBHVUMgYWxsb3dzIHNwZWNpZnlp
bmcgbnVtX3N5bmMsCnRoZSBudW1iZXIgb2Ygc3luY2hyb25vdXMgc3RhbmRieXMgdHJhbnNh
Y3Rpb25zIG11c3Qgd2FpdCBmb3IKcmVwbGllcyBmcm9tLiBUaGlzIHZhbHVlIG11c3QgYmUg
YW4gaW50ZWdlciBncmVhdGVyIHRoYW4gemVyby4KVGhpcyBjb21taXQgdXBkYXRlcyB0aGUg
ZG9jdW1lbnRhdGlvbiB0byBjbGFyaWZ5IHRoaXMgcmVxdWlyZW1lbnQuCgpSZXBvcnRlZC1i
eTogQXNwaGF0b3IgPGFzcGhhdG9yQGdtYWlsLmNvbT4KRGlzY3Vzc2lvbjogaHR0cHM6Ly9w
b3N0Z3IuZXMvbS8xODY2My1iMDJmNzVjYjkxOWYxYjYwQHBvc3RncmVzcWwub3JnCi0tLQog
ZG9jL3NyYy9zZ21sL2NvbmZpZy5zZ21sIHwgMSArCiAxIGZpbGUgY2hhbmdlZCwgMSBpbnNl
cnRpb24oKykKCmRpZmYgLS1naXQgYS9kb2Mvc3JjL3NnbWwvY29uZmlnLnNnbWwgYi9kb2Mv
c3JjL3NnbWwvY29uZmlnLnNnbWwKaW5kZXggOTM0ZWY1ZTQ2OS4uNWNiZTVhNjFhNyAxMDA2
NDQKLS0tIGEvZG9jL3NyYy9zZ21sL2NvbmZpZy5zZ21sCisrKyBiL2RvYy9zcmMvc2dtbC9j
b25maWcuc2dtbApAQCAtNDY3OCw2ICs0Njc4LDcgQEAgQU5ZIDxyZXBsYWNlYWJsZSBjbGFz
cz0icGFyYW1ldGVyIj5udW1fc3luYzwvcmVwbGFjZWFibGU+ICggPHJlcGxhY2VhYmxlIGNs
YXNzPSIKICAgICAgICAgd2FpdCBmb3IgcmVwbGllcyBmcm9tLAogICAgICAgICBhbmQgPHJl
cGxhY2VhYmxlIGNsYXNzPSJwYXJhbWV0ZXIiPnN0YW5kYnlfbmFtZTwvcmVwbGFjZWFibGU+
CiAgICAgICAgIGlzIHRoZSBuYW1lIG9mIGEgc3RhbmRieSBzZXJ2ZXIuCisgICAgICAgIFRo
aXMgbnVtYmVyIG11c3QgYmUgYW4gaW50ZWdlciB2YWx1ZSBncmVhdGVyIHRoYW4gemVyby4K
ICAgICAgICAgPGxpdGVyYWw+RklSU1Q8L2xpdGVyYWw+IGFuZCA8bGl0ZXJhbD5BTlk8L2xp
dGVyYWw+IHNwZWNpZnkgdGhlIG1ldGhvZCB0byBjaG9vc2UKICAgICAgICAgc3luY2hyb25v
dXMgc3RhbmRieXMgZnJvbSB0aGUgbGlzdGVkIHNlcnZlcnMuCiAgICAgICAgPC9wYXJhPgot
LSAKMi40Ni4yCgo=

--------------0js0fSvgZmkgC43ULm30f0V0--