From: Fujii Masao <masao.fujii@oss.nttdata.com>
To: asphator@gmail.com
To: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18663: synchronous_standby_names vs synchronous_commit vs pg_stat_replication
Date: Mon, 21 Oct 2024 18:43:49 +0900
Message-ID: <a8794ab2-7fba-4541-98fa-528b1e03852c@oss.nttdata.com> (raw)
In-Reply-To: <18663-b02f75cb919f1b60@postgresql.org>
References: <18663-b02f75cb919f1b60@postgresql.org>
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
Attachments:v1-0001-doc-Clarify-synchronous_standby_names-parameter.patch (text/plain, 1.3K) download
From 536d3a626b7d61dee55fe4043977a47be875b8a6 Mon Sep 17 00:00:00 2001
From: Fujii Masao <fujii@postgresql.org>
Date: Mon, 21 Oct 2024 18:04:07 +0900
Subject: [PATCH v1] doc: Clarify synchronous_standby_names parameter.
The synchronous_standby_names GUC allows specifying num_sync,
the number of synchronous standbys transactions must wait for
replies from. This value must be an integer greater than zero.
This commit updates the documentation to clarify this requirement.
Reported-by: Asphator <asphator@gmail.com>
Discussion: https://postgr.es/m/18663-b02f75cb919f1b60@postgresql.org
---
doc/src/sgml/config.sgml | 1 +
1 file changed, 1 insertion(+)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgmlindex 934ef5e469..5cbe5a61a7 100644--- a/doc/src/sgml/config.sgml+++ b/doc/src/sgml/config.sgml@@ -4678,6 +4678,7 @@ ANY <replaceable class="parameter">num_sync</replaceable> ( <replaceable class="
wait for replies from,
and <replaceable class="parameter">standby_name</replaceable>
is the name of a standby server.
+ This number must be an integer value greater than zero.
<literal>FIRST</literal> and <literal>ANY</literal> specify the method to choose
synchronous standbys from the listed servers.
</para>
--
2.46.2
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: masao.fujii@oss.nttdata.com, asphator@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18663: synchronous_standby_names vs synchronous_commit vs pg_stat_replication
In-Reply-To: <a8794ab2-7fba-4541-98fa-528b1e03852c@oss.nttdata.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox