pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18663: synchronous_standby_names vs synchronous_commit vs pg_stat_replication
5+ messages / 3 participants
[nested] [flat]
Commits from this thread:
2025-01-09 6313a76b35 doc: Clarify synchronous_standby_names parameter.
* BUG #18663: synchronous_standby_names vs synchronous_commit vs pg_stat_replication
@ 2024-10-18 14:48 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 5+ messages in thread
From: PG Bug reporting form @ 2024-10-18 14:48 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: asphator@gmail.com
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.
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 ?
Same as for 1, could that be less ambiguous in documentation whether quorum
preempts on synchronous_mode and that would explain, or if it's not supposed
to, to fix this in the pg_stat_replication view ?
Kind Regards
Arpad
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #18663: synchronous_standby_names vs synchronous_commit vs pg_stat_replication
@ 2024-10-21 09:43 Fujii Masao <masao.fujii@oss.nttdata.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 5+ messages in thread
From: Fujii Masao @ 2024-10-21 09:43 UTC (permalink / raw)
To: asphator@gmail.com; pgsql-bugs@lists.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.sgml
index 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
* Re: BUG #18663: synchronous_standby_names vs synchronous_commit vs pg_stat_replication
@ 2025-01-09 00:35 Fujii Masao <masao.fujii@oss.nttdata.com>
parent: Fujii Masao <masao.fujii@oss.nttdata.com>
0 siblings, 1 reply; 5+ messages in thread
From: Fujii Masao @ 2025-01-09 00:35 UTC (permalink / raw)
To: asphator@gmail.com; pgsql-bugs@lists.postgresql.org
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
Attachments:
v2-0001-doc-Clarify-synchronous_standby_names-parameter.patch (text/plain, 1.3K) download
From 4bb69b55e060dc4124ba55dcd7dca164cc76c09d Mon Sep 17 00:00:00 2001
From: Fujii Masao <fujii@postgresql.org>
Date: Thu, 9 Jan 2025 09:26:36 +0900
Subject: [PATCH v2] 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 | 2 ++
1 file changed, 2 insertions(+)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 8683f0bdf5..f1ab614575 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -4715,6 +4715,8 @@ 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.
+ <replaceable class="parameter">num_sync</replaceable>
+ 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.47.0
* Re: BUG #18663: synchronous_standby_names vs synchronous_commit vs pg_stat_replication
@ 2025-01-09 12:11 Fujii Masao <masao.fujii@oss.nttdata.com>
parent: Fujii Masao <masao.fujii@oss.nttdata.com>
0 siblings, 1 reply; 5+ messages in thread
From: Fujii Masao @ 2025-01-09 12:11 UTC (permalink / raw)
To: asphator@gmail.com; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #18663: synchronous_standby_names vs synchronous_commit vs pg_stat_replication
@ 2025-01-10 11:54 Asphator <asphator@gmail.com>
parent: Fujii Masao <masao.fujii@oss.nttdata.com>
0 siblings, 0 replies; 5+ messages in thread
From: Asphator @ 2025-01-10 11:54 UTC (permalink / raw)
To: Fujii Masao <masao.fujii@oss.nttdata.com>; +Cc: pgsql-bugs@lists.postgresql.org
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...
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-01-10 11:54 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-10-18 14:48 BUG #18663: synchronous_standby_names vs synchronous_commit vs pg_stat_replication PG Bug reporting form <noreply@postgresql.org>
2024-10-21 09:43 ` Fujii Masao <masao.fujii@oss.nttdata.com>
2025-01-09 00:35 ` Fujii Masao <masao.fujii@oss.nttdata.com>
2025-01-09 12:11 ` Fujii Masao <masao.fujii@oss.nttdata.com>
2025-01-10 11:54 ` Asphator <asphator@gmail.com>
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