pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feedImprove WAIT FOR read-your-writes consistency doc
10+ messages / 4 participants
[nested] [flat]
* Improve WAIT FOR read-your-writes consistency doc
@ 2026-09-08 21:42 Sami Imseih <samimseih.pg@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Sami Imseih @ 2026-09-08 21:42 UTC (permalink / raw)
To: Postgres hackers <pgsql-hackers@lists.postgresql.org>
The WAIT FOR docs say that "The standby_replay mode waits for the LSN to be
replayed (applied to the database), which is useful to achieve read-your-writes
consistency while using an async replica for reads and the primary for writes."
I think that needs one more caveat.
WAIT FOR success does not guarantee read-your-writes if the supplied LSN
is before the transaction's COMMIT record.
Maybe the last sentence should be qualified with:
" .... the primary for writes, provided that the target LSN was
captured after COMMIT on the primary."
This may be obvious to users of this feature, but perhaps worth being
more explicit about it.
Thoughts?
--
Sami Imseih
Amazon Web Services (AWS)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Improve WAIT FOR read-your-writes consistency doc
@ 2026-09-09 07:52 Xuneng Zhou <xunengzhou@gmail.com>
parent: Sami Imseih <samimseih.pg@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Xuneng Zhou @ 2026-09-09 07:52 UTC (permalink / raw)
To: Sami Imseih <samimseih.pg@gmail.com>; +Cc: Postgres hackers <pgsql-hackers@lists.postgresql.org>
Hi Sami,
On Wed, Sep 9, 2026 at 5:42 AM Sami Imseih <samimseih.pg@gmail.com> wrote:
> The WAIT FOR docs say that "The standby_replay mode waits for the LSN to be
> replayed (applied to the database), which is useful to achieve
> read-your-writes
> consistency while using an async replica for reads and the primary for
> writes."
>
> I think that needs one more caveat.
>
> WAIT FOR success does not guarantee read-your-writes if the supplied LSN
> is before the transaction's COMMIT record.
>
> Maybe the last sentence should be qualified with:
>
> " .... the primary for writes, provided that the target LSN was
> captured after COMMIT on the primary."
>
> This may be obvious to users of this feature, but perhaps worth being
> more explicit about it.
>
> Thoughts?
Thanks for raising this! I agree this may need to be harnessed. If we
change this line, we might also want to modify the
read-your-writes-consistency chapter.
<sect2 id="read-your-writes-consistency">
<title>Read-Your-Writes Consistency</title>
I am also wondering whether a example like this is helpful, though it is
somehow duplicated with the existing one:
postgres=# BEGIN;
BEGIN
postgres=*# UPDATE movie SET genre = 'Dramatic' WHERE genre = 'Drama';
UPDATE 100
postgres=*# COMMIT;
COMMIT
postgres=# SELECT pg_current_wal_insert_lsn();
--
Regards,
Xuneng Zhou
HighGo Software Co., Ltd.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Improve WAIT FOR read-your-writes consistency doc
@ 2026-09-09 13:57 Sami Imseih <samimseih.pg@gmail.com>
parent: Xuneng Zhou <xunengzhou@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Sami Imseih @ 2026-09-09 13:57 UTC (permalink / raw)
To: Xuneng Zhou <xunengzhou@gmail.com>; +Cc: Postgres hackers <pgsql-hackers@lists.postgresql.org>
Thanks for the feedback.
> Thanks for raising this! I agree this may need to be harnessed. If we change this line, we might also want to modify the read-your-writes-consistency chapter.
> <sect2 id="read-your-writes-consistency">
> <title>Read-Your-Writes Consistency</title>
Yes, that needs to mention the COMMIT LSN explicitly.
> I am also wondering whether a example like this is helpful, though it is somehow duplicated with the existing one:
> postgres=# BEGIN;
> BEGIN
> postgres=*# UPDATE movie SET genre = 'Dramatic' WHERE genre = 'Drama';
> UPDATE 100
> postgres=*# COMMIT;
> COMMIT
> postgres=# SELECT pg_current_wal_insert_lsn();
Rather than change the example, just updated the explanation of the
example with the proper commit wording.
WDYT of the attached?
--
Sami
Attachments:
[application/octet-stream] v1-0001-Clarify-WAIT-FOR-read-your-writes-documentation.patch (4.7K, ../../CAN12+Y+RaD-Q1BugF9vboPrCQjLLBRp-FAy=CBK0v5dpQ3ux1Q@mail.gmail.com/2-v1-0001-Clarify-WAIT-FOR-read-your-writes-documentation.patch)
download | inline diff:
From 13efac74d82a57435d998811f1e1a5c37251cc91 Mon Sep 17 00:00:00 2001
From: Sami Imseih <samimseih.pg@gmail.com>
Date: Wed, 9 Sep 2026 13:48:04 +0000
Subject: [PATCH v1 1/1] Clarify WAIT FOR read-your-writes documentation
To achieve read-your-writes consistency with WAIT FOR, the target LSN
must be captured after the transaction's COMMIT record. The existing
documentation did not make that explicit, and could be read as allowing
an LSN captured after a statement but before COMMIT.
Document the COMMIT-LSN requirement explicitly in both the WAIT FOR
reference page and the read-your-writes consistency section.
---
doc/src/sgml/high-availability.sgml | 13 ++++++++-----
doc/src/sgml/ref/wait_for.sgml | 19 +++++++++++++------
2 files changed, 21 insertions(+), 11 deletions(-)
diff --git a/doc/src/sgml/high-availability.sgml b/doc/src/sgml/high-availability.sgml
index d58b16977cd..7e4fe3d3fc9 100644
--- a/doc/src/sgml/high-availability.sgml
+++ b/doc/src/sgml/high-availability.sgml
@@ -1409,9 +1409,10 @@ synchronous_standby_names = 'ANY 2 (s1, s2, s3)'
</para>
<para>
- This is achieved by the following steps. After performing write
- operations, the application retrieves the current WAL location using a
- function call like this.
+ This is achieved by the following steps. After committing the write
+ operations on the primary, the application retrieves a WAL location
+ that is at or after the transaction's <literal>COMMIT</literal> record,
+ using a function call like this.
<programlisting>
postgres=# SELECT pg_current_wal_insert_lsn();
@@ -1438,8 +1439,10 @@ postgres=# WAIT FOR LSN '0/306EE20';
(1 row)
</programlisting>
Once the command returns a status of success, it guarantees that all
- changes up to the provided <acronym>LSN</acronym> have been applied,
- ensuring that subsequent read queries will reflect the latest updates.
+ changes up to the provided <acronym>LSN</acronym> have been applied.
+ Therefore, if that <acronym>LSN</acronym> is at or after the write
+ transaction's <literal>COMMIT</literal> record on the primary,
+ subsequent read queries will reflect those updates.
</para>
</sect2>
diff --git a/doc/src/sgml/ref/wait_for.sgml b/doc/src/sgml/ref/wait_for.sgml
index 04ca9400426..e5b54d47afc 100644
--- a/doc/src/sgml/ref/wait_for.sgml
+++ b/doc/src/sgml/ref/wait_for.sgml
@@ -231,7 +231,9 @@ WAIT FOR LSN '<replaceable class="parameter">lsn</replaceable>'
<parameter>mode</parameter>. The <literal>standby_replay</literal> mode
waits for the LSN to be replayed (applied to the database), which is
useful to achieve read-your-writes consistency while using an async
- replica for reads and the primary for writes. The
+ replica for reads and the primary for writes, provided that the target
+ <acronym>LSN</acronym> is at or after the relevant write transaction's
+ <literal>COMMIT</literal> record on the primary. The
<literal>standby_flush</literal> mode waits for the WAL to be flushed
to durable storage on the replica, or to have already been replayed
from WAL present on the standby. The <literal>standby_write</literal> mode
@@ -289,9 +291,13 @@ WAIT FOR LSN '<replaceable class="parameter">lsn</replaceable>'
<para>
You can use <command>WAIT FOR</command> command to wait for
the <type>pg_lsn</type> value. For example, an application could update
- the <literal>movie</literal> table and get the <acronym>lsn</acronym> after
- changes just made. This example uses <function>pg_current_wal_insert_lsn</function>
- on primary server to get the <acronym>lsn</acronym> given that
+ the <literal>movie</literal> table and get an <acronym>lsn</acronym> that
+ is at or after the relevant write transaction's <literal>COMMIT</literal>
+ record. In the default autocommit mode shown here, the
+ <command>UPDATE</command> commits before the subsequent
+ <command>SELECT</command>. This example uses
+ <function>pg_current_wal_insert_lsn</function> on primary server to get
+ the <acronym>lsn</acronym> given that
<varname>synchronous_commit</varname> could be set to
<literal>off</literal>.
@@ -306,8 +312,9 @@ postgres=# SELECT pg_current_wal_insert_lsn();
</programlisting>
Then an application could run <command>WAIT FOR</command>
- with the <parameter>lsn</parameter> obtained from primary. After that the
- changes made on primary should be guaranteed to be visible on replica.
+ with the <parameter>lsn</parameter> obtained from primary by that
+ post-commit <command>SELECT</command>. After that the changes made on
+ primary should be guaranteed to be visible on replica.
<programlisting>
postgres=# WAIT FOR LSN '0/0306EE20';
--
2.50.1
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Improve WAIT FOR read-your-writes consistency doc
@ 2026-09-10 11:31 Xuneng Zhou <xunengzhou@gmail.com>
parent: Sami Imseih <samimseih.pg@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Xuneng Zhou @ 2026-09-10 11:31 UTC (permalink / raw)
To: Sami Imseih <samimseih.pg@gmail.com>; +Cc: Postgres hackers <pgsql-hackers@lists.postgresql.org>
On Wed, Sep 9, 2026 at 9:57 PM Sami Imseih <samimseih.pg@gmail.com> wrote:
>
> Thanks for the feedback.
>
> > Thanks for raising this! I agree this may need to be harnessed. If we change this line, we might also want to modify the read-your-writes-consistency chapter.
> > <sect2 id="read-your-writes-consistency">
> > <title>Read-Your-Writes Consistency</title>
>
> Yes, that needs to mention the COMMIT LSN explicitly.
>
> > I am also wondering whether a example like this is helpful, though it is somehow duplicated with the existing one:
> > postgres=# BEGIN;
> > BEGIN
> > postgres=*# UPDATE movie SET genre = 'Dramatic' WHERE genre = 'Drama';
> > UPDATE 100
> > postgres=*# COMMIT;
> > COMMIT
> > postgres=# SELECT pg_current_wal_insert_lsn();
>
> Rather than change the example, just updated the explanation of the
> example with the proper commit wording.
>
> WDYT of the attached?
Just a trivial comment:
+ This is achieved by the following steps. After committing the write
+ operations on the primary, the application retrieves a WAL location
+ that is at or after the transaction's <literal>COMMIT</literal> record,
+ using a function call like this.
We might need to specify the word 'end' clearly to avoid
misunderstanding the LSN position as the beginning of the commit
record.
at or after the *end* of the write transaction’s COMMIT record.
--
Regards,
Xuneng Zhou
HighGo Software Co., Ltd.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Improve WAIT FOR read-your-writes consistency doc
@ 2026-09-10 18:19 Sami Imseih <samimseih.pg@gmail.com>
parent: Xuneng Zhou <xunengzhou@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Sami Imseih @ 2026-09-10 18:19 UTC (permalink / raw)
To: Xuneng Zhou <xunengzhou@gmail.com>; +Cc: Postgres hackers <pgsql-hackers@lists.postgresql.org>
Thanks!
> > WDYT of the attached?
>
> Just a trivial comment:
>
> + This is achieved by the following steps. After committing the write
> + operations on the primary, the application retrieves a WAL location
> + that is at or after the transaction's <literal>COMMIT</literal> record,
> + using a function call like this.
>
> We might need to specify the word 'end' clearly to avoid
> misunderstanding the LSN position as the beginning of the commit
> record.
>
> at or after the *end* of the write transaction’s COMMIT record.
I rather the comment be more high-level, and we just need to convey
that the function should be called after the commit.
See the attached.
--
Sami Imseih
Amazon Web Services (AWS)
Attachments:
[application/octet-stream] v2-0001-Clarify-WAIT-FOR-read-your-writes-documentation.patch (4.5K, ../../CAN12+Y+Pod3fcibF1s7ezxAaj+3wu3XxykVBMaUghZGvt0gxHA@mail.gmail.com/2-v2-0001-Clarify-WAIT-FOR-read-your-writes-documentation.patch)
download | inline diff:
From a809939c0c7da48c715114979311dfbdecc1d261 Mon Sep 17 00:00:00 2001
From: Sami Imseih <samimseih.pg@gmail.com>
Date: Wed, 9 Sep 2026 13:48:04 +0000
Subject: [PATCH v2 1/1] Clarify WAIT FOR read-your-writes documentation
To achieve read-your-writes consistency with WAIT FOR, the target LSN
must be captured after the transaction's COMMIT record. The existing
documentation did not make that explicit, and could be read as allowing
an LSN captured after a statement but before COMMIT.
Document the COMMIT-LSN requirement explicitly in both the WAIT FOR
reference page and the read-your-writes consistency section.
---
doc/src/sgml/high-availability.sgml | 11 ++++++-----
doc/src/sgml/ref/wait_for.sgml | 19 +++++++++++++------
2 files changed, 19 insertions(+), 11 deletions(-)
diff --git a/doc/src/sgml/high-availability.sgml b/doc/src/sgml/high-availability.sgml
index d58b16977cd..1299efde51d 100644
--- a/doc/src/sgml/high-availability.sgml
+++ b/doc/src/sgml/high-availability.sgml
@@ -1409,9 +1409,9 @@ synchronous_standby_names = 'ANY 2 (s1, s2, s3)'
</para>
<para>
- This is achieved by the following steps. After performing write
- operations, the application retrieves the current WAL location using a
- function call like this.
+ This is achieved by the following steps. Once the write transaction
+ has committed, the application retrieves a WAL location from the
+ primary using a function call like this.
<programlisting>
postgres=# SELECT pg_current_wal_insert_lsn();
@@ -1438,8 +1438,9 @@ postgres=# WAIT FOR LSN '0/306EE20';
(1 row)
</programlisting>
Once the command returns a status of success, it guarantees that all
- changes up to the provided <acronym>LSN</acronym> have been applied,
- ensuring that subsequent read queries will reflect the latest updates.
+ changes up to the provided <acronym>LSN</acronym> have been applied.
+ Therefore, if that <acronym>LSN</acronym> was retrieved from the primary
+ after the commit, subsequent read queries will reflect those updates.
</para>
</sect2>
diff --git a/doc/src/sgml/ref/wait_for.sgml b/doc/src/sgml/ref/wait_for.sgml
index 04ca9400426..e5b54d47afc 100644
--- a/doc/src/sgml/ref/wait_for.sgml
+++ b/doc/src/sgml/ref/wait_for.sgml
@@ -231,7 +231,9 @@ WAIT FOR LSN '<replaceable class="parameter">lsn</replaceable>'
<parameter>mode</parameter>. The <literal>standby_replay</literal> mode
waits for the LSN to be replayed (applied to the database), which is
useful to achieve read-your-writes consistency while using an async
- replica for reads and the primary for writes. The
+ replica for reads and the primary for writes, provided that the target
+ <acronym>LSN</acronym> is at or after the relevant write transaction's
+ <literal>COMMIT</literal> record on the primary. The
<literal>standby_flush</literal> mode waits for the WAL to be flushed
to durable storage on the replica, or to have already been replayed
from WAL present on the standby. The <literal>standby_write</literal> mode
@@ -289,9 +291,13 @@ WAIT FOR LSN '<replaceable class="parameter">lsn</replaceable>'
<para>
You can use <command>WAIT FOR</command> command to wait for
the <type>pg_lsn</type> value. For example, an application could update
- the <literal>movie</literal> table and get the <acronym>lsn</acronym> after
- changes just made. This example uses <function>pg_current_wal_insert_lsn</function>
- on primary server to get the <acronym>lsn</acronym> given that
+ the <literal>movie</literal> table and get an <acronym>lsn</acronym> that
+ is at or after the relevant write transaction's <literal>COMMIT</literal>
+ record. In the default autocommit mode shown here, the
+ <command>UPDATE</command> commits before the subsequent
+ <command>SELECT</command>. This example uses
+ <function>pg_current_wal_insert_lsn</function> on primary server to get
+ the <acronym>lsn</acronym> given that
<varname>synchronous_commit</varname> could be set to
<literal>off</literal>.
@@ -306,8 +312,9 @@ postgres=# SELECT pg_current_wal_insert_lsn();
</programlisting>
Then an application could run <command>WAIT FOR</command>
- with the <parameter>lsn</parameter> obtained from primary. After that the
- changes made on primary should be guaranteed to be visible on replica.
+ with the <parameter>lsn</parameter> obtained from primary by that
+ post-commit <command>SELECT</command>. After that the changes made on
+ primary should be guaranteed to be visible on replica.
<programlisting>
postgres=# WAIT FOR LSN '0/0306EE20';
--
2.50.1
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Improve WAIT FOR read-your-writes consistency doc
@ 2026-09-15 13:26 Sami Imseih <samimseih.pg@gmail.com>
parent: Sami Imseih <samimseih.pg@gmail.com>
0 siblings, 2 replies; 10+ messages in thread
From: Sami Imseih @ 2026-09-15 13:26 UTC (permalink / raw)
To: Xuneng Zhou <xunengzhou@gmail.com>; +Cc: Postgres hackers <pgsql-hackers@lists.postgresql.org>
Hi,
Attached is a rebase.
--
Sami Imseih
Amazon Web Services (AWS)
Attachments:
[application/octet-stream] v3-0001-Clarify-WAIT-FOR-read-your-writes-documentation.patch (4.6K, ../../CAN12+YJKX_1FkQQdU8apCBvUd-jVVhQqN4n+hLRiCGK1nc7E0g@mail.gmail.com/2-v3-0001-Clarify-WAIT-FOR-read-your-writes-documentation.patch)
download | inline diff:
From cd6d78c2eb6edb39304f10e5a916af8117be02b8 Mon Sep 17 00:00:00 2001
From: Sami Imseih <samimseih.pg@gmail.com>
Date: Wed, 9 Sep 2026 13:48:04 +0000
Subject: [PATCH v3 1/1] Clarify WAIT FOR read-your-writes documentation
To achieve read-your-writes consistency with WAIT FOR, the target LSN
must be captured after the transaction's COMMIT record. The existing
documentation did not make that explicit, and could be read as allowing
an LSN captured after a statement but before COMMIT.
Document the COMMIT-LSN requirement explicitly in both the WAIT FOR
reference page and the read-your-writes consistency section.
---
doc/src/sgml/high-availability.sgml | 11 ++++++-----
doc/src/sgml/ref/wait.sgml | 21 ++++++++++++++-------
2 files changed, 20 insertions(+), 12 deletions(-)
diff --git a/doc/src/sgml/high-availability.sgml b/doc/src/sgml/high-availability.sgml
index b24bb2ec45f..7253f9d8287 100644
--- a/doc/src/sgml/high-availability.sgml
+++ b/doc/src/sgml/high-availability.sgml
@@ -1409,9 +1409,9 @@ synchronous_standby_names = 'ANY 2 (s1, s2, s3)'
</para>
<para>
- This is achieved by the following steps. After performing write
- operations, the application retrieves the current WAL location using a
- function call like this.
+ This is achieved by the following steps. Once the write transaction
+ has committed, the application retrieves a WAL location from the
+ primary using a function call like this.
<programlisting>
postgres=# SELECT pg_current_wal_insert_lsn();
@@ -1438,8 +1438,9 @@ postgres=# WAIT FOR LSN '0/306EE20';
(1 row)
</programlisting>
Once the command returns a status of success, it guarantees that all
- changes up to the provided <acronym>LSN</acronym> have been applied,
- ensuring that subsequent read queries will reflect the latest updates.
+ changes up to the provided <acronym>LSN</acronym> have been applied.
+ Therefore, if that <acronym>LSN</acronym> was retrieved from the primary
+ after the commit, subsequent read queries will reflect those updates.
</para>
</sect2>
diff --git a/doc/src/sgml/ref/wait.sgml b/doc/src/sgml/ref/wait.sgml
index 9056eb6726b..5412597b3cd 100644
--- a/doc/src/sgml/ref/wait.sgml
+++ b/doc/src/sgml/ref/wait.sgml
@@ -279,7 +279,9 @@ WAIT FOR LSN '<replaceable class="parameter">lsn</replaceable>'
<parameter>mode</parameter>. The <literal>standby_replay</literal> mode
waits for the LSN to be replayed (applied to the database), which is
useful to achieve read-your-writes consistency while using an async
- replica for reads and the primary for writes. The
+ replica for reads and the primary for writes, provided that the target
+ <acronym>LSN</acronym> is at or after the relevant write transaction's
+ <literal>COMMIT</literal> record on the primary. The
<literal>standby_flush</literal> mode waits for the WAL to be flushed
to durable storage on the replica, or to have already been replayed
from WAL present on the standby. The <literal>standby_write</literal> mode
@@ -337,9 +339,13 @@ WAIT FOR LSN '<replaceable class="parameter">lsn</replaceable>'
<para>
You can use the <command>WAIT</command> command to wait for
the <type>pg_lsn</type> value. For example, an application could update
- the <literal>movie</literal> table and get the <acronym>lsn</acronym> after
- changes just made. This example uses <function>pg_current_wal_insert_lsn</function>
- on primary server to get the <acronym>lsn</acronym> given that
+ the <literal>movie</literal> table and get an <acronym>lsn</acronym> that
+ is at or after the relevant write transaction's <literal>COMMIT</literal>
+ record. In the default autocommit mode shown here, the
+ <command>UPDATE</command> commits before the subsequent
+ <command>SELECT</command>. This example uses
+ <function>pg_current_wal_insert_lsn</function> on primary server to get
+ the <acronym>lsn</acronym> given that
<varname>synchronous_commit</varname> could be set to
<literal>off</literal>.
@@ -353,9 +359,10 @@ postgres=# SELECT pg_current_wal_insert_lsn();
(1 row)
</programlisting>
- Then an application could run <command>WAIT</command>
- with the <parameter>lsn</parameter> obtained from primary. After that the
- changes made on primary should be guaranteed to be visible on replica.
+ Then an application could run <command>WAIT FOR</command>
+ with the <parameter>lsn</parameter> obtained from the primary
+ after commit. After that, the changes made on the
+ primary should be guaranteed to be visible on the replica.
<programlisting>
postgres=# WAIT FOR LSN '0/0306EE20';
--
2.50.1
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Improve WAIT FOR read-your-writes consistency doc
@ 2026-09-16 15:51 Nathan Bossart <nathandbossart@gmail.com>
parent: Sami Imseih <samimseih.pg@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: Nathan Bossart @ 2026-09-16 15:51 UTC (permalink / raw)
To: Sami Imseih <samimseih.pg@gmail.com>; +Cc: Xuneng Zhou <xunengzhou@gmail.com>; Postgres hackers <pgsql-hackers@lists.postgresql.org>; aekorotkov@gmail.com
[RMT hat]
Please note that this one is now listed as an open item for v19.
--
nathan
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Improve WAIT FOR read-your-writes consistency doc
@ 2026-09-16 22:30 Alexander Korotkov <aekorotkov@gmail.com>
parent: Sami Imseih <samimseih.pg@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Alexander Korotkov @ 2026-09-16 22:30 UTC (permalink / raw)
To: Sami Imseih <samimseih.pg@gmail.com>; +Cc: Xuneng Zhou <xunengzhou@gmail.com>; Postgres hackers <pgsql-hackers@lists.postgresql.org>
On Tue, Sep 15, 2026 at 4:27 PM Sami Imseih <samimseih.pg@gmail.com> wrote:
> Attached is a rebase.
I've made this following changes to the patch.
1) Change <command>WAIT FOR</command> back to <command>WAIT</command>
(see a23ab4862cfe).
2) In the example, app gets lsn at or after *the end* of the relevant
write transaction's COMMIT record. If we say lsn at the COMMIT record
then it might be interpreted as at he beginning of that record (and
that is wrong).
I'm going to push this if no objections.
------
Regards,
Alexander Korotkov
Supabase
Attachments:
[application/octet-stream] v4-0001-doc-Say-which-LSN-gives-read-your-writes-with-WAI.patch (5.1K, ../../CAPpHfduds=5upQGq6zssKCYXFdm7f=QEQQBHQPSV30X=jNLT0Q@mail.gmail.com/2-v4-0001-doc-Say-which-LSN-gives-read-your-writes-with-WAI.patch)
download | inline diff:
From 700baa7e34b454b52cef07055d28cb262f9f9830 Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <akorotkov@postgresql.org>
Date: Wed, 16 Sep 2026 15:39:43 +0300
Subject: [PATCH v4] doc: Say which LSN gives read-your-writes with WAIT
The reference page and the read-your-writes section both presented
WAIT in standby_replay mode as enough for read-your-writes, without
saying which LSN to wait for. Read as written, an LSN taken after the
last statement but before COMMIT would do, and it would not: the standby
position WAIT compares against is the end of the last replayed record, so
a target that falls inside the COMMIT record is satisfied while that
record is still unapplied.
Say that the target must be at or after the end of the write
transaction's COMMIT record, and note in the example that autocommit is
what puts the UPDATE's commit before the call that reads the position.
Author: Sami Imseih <samimseih.pg@gmail.com>
Reviewed-by: Xuneng Zhou <xunengzhou@gmail.com>
Reviewed-by: Alexander Korotkov <aekorotkov@gmail.com>
Discussion: https://postgr.es/m/CAN12%2BYKN7gXb1o9bDWgfA%3D6wjDrqNEgt%2BrXJiAWXgPCXQ5TqpA%40mail.gmail.com
Backpatch-through: 19
---
doc/src/sgml/high-availability.sgml | 11 ++++++-----
doc/src/sgml/ref/wait.sgml | 19 +++++++++++++------
2 files changed, 19 insertions(+), 11 deletions(-)
diff --git a/doc/src/sgml/high-availability.sgml b/doc/src/sgml/high-availability.sgml
index b24bb2ec45f..7253f9d8287 100644
--- a/doc/src/sgml/high-availability.sgml
+++ b/doc/src/sgml/high-availability.sgml
@@ -1409,9 +1409,9 @@ synchronous_standby_names = 'ANY 2 (s1, s2, s3)'
</para>
<para>
- This is achieved by the following steps. After performing write
- operations, the application retrieves the current WAL location using a
- function call like this.
+ This is achieved by the following steps. Once the write transaction
+ has committed, the application retrieves a WAL location from the
+ primary using a function call like this.
<programlisting>
postgres=# SELECT pg_current_wal_insert_lsn();
@@ -1438,8 +1438,9 @@ postgres=# WAIT FOR LSN '0/306EE20';
(1 row)
</programlisting>
Once the command returns a status of success, it guarantees that all
- changes up to the provided <acronym>LSN</acronym> have been applied,
- ensuring that subsequent read queries will reflect the latest updates.
+ changes up to the provided <acronym>LSN</acronym> have been applied.
+ Therefore, if that <acronym>LSN</acronym> was retrieved from the primary
+ after the commit, subsequent read queries will reflect those updates.
</para>
</sect2>
diff --git a/doc/src/sgml/ref/wait.sgml b/doc/src/sgml/ref/wait.sgml
index 9056eb6726b..542fa6b0047 100644
--- a/doc/src/sgml/ref/wait.sgml
+++ b/doc/src/sgml/ref/wait.sgml
@@ -279,7 +279,9 @@ WAIT FOR LSN '<replaceable class="parameter">lsn</replaceable>'
<parameter>mode</parameter>. The <literal>standby_replay</literal> mode
waits for the LSN to be replayed (applied to the database), which is
useful to achieve read-your-writes consistency while using an async
- replica for reads and the primary for writes. The
+ replica for reads and the primary for writes, provided that the target
+ <acronym>LSN</acronym> is at or after the end of the relevant write
+ transaction's <literal>COMMIT</literal> record on the primary. The
<literal>standby_flush</literal> mode waits for the WAL to be flushed
to durable storage on the replica, or to have already been replayed
from WAL present on the standby. The <literal>standby_write</literal> mode
@@ -337,9 +339,13 @@ WAIT FOR LSN '<replaceable class="parameter">lsn</replaceable>'
<para>
You can use the <command>WAIT</command> command to wait for
the <type>pg_lsn</type> value. For example, an application could update
- the <literal>movie</literal> table and get the <acronym>lsn</acronym> after
- changes just made. This example uses <function>pg_current_wal_insert_lsn</function>
- on primary server to get the <acronym>lsn</acronym> given that
+ the <literal>movie</literal> table and get an <acronym>lsn</acronym> that
+ is at or after the end of the relevant write transaction's
+ <literal>COMMIT</literal> record. In the default autocommit mode shown
+ here, the <command>UPDATE</command> commits before the subsequent
+ <command>SELECT</command>. This example uses
+ <function>pg_current_wal_insert_lsn</function> on primary server to get
+ the <acronym>lsn</acronym> given that
<varname>synchronous_commit</varname> could be set to
<literal>off</literal>.
@@ -354,8 +360,9 @@ postgres=# SELECT pg_current_wal_insert_lsn();
</programlisting>
Then an application could run <command>WAIT</command>
- with the <parameter>lsn</parameter> obtained from primary. After that the
- changes made on primary should be guaranteed to be visible on replica.
+ with the <parameter>lsn</parameter> obtained from the primary after the
+ commit. After that, the changes made on the primary should be guaranteed
+ to be visible on the replica.
<programlisting>
postgres=# WAIT FOR LSN '0/0306EE20';
--
2.55.0
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Improve WAIT FOR read-your-writes consistency doc
@ 2026-09-16 22:41 Sami Imseih <samimseih.pg@gmail.com>
parent: Alexander Korotkov <aekorotkov@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Sami Imseih @ 2026-09-16 22:41 UTC (permalink / raw)
To: Alexander Korotkov <aekorotkov@gmail.com>; +Cc: Xuneng Zhou <xunengzhou@gmail.com>; Postgres hackers <pgsql-hackers@lists.postgresql.org>
On Wed, Sep 16, 2026 at 5:30 PM Alexander Korotkov <aekorotkov@gmail.com>
wrote:
> On Tue, Sep 15, 2026 at 4:27 PM Sami Imseih <samimseih.pg@gmail.com>
> wrote:
> > Attached is a rebase.
>
> I've made this following changes to the patch.
> 1) Change <command>WAIT FOR</command> back to <command>WAIT</command>
> (see a23ab4862cfe).
> 2) In the example, app gets lsn at or after *the end* of the relevant
> write transaction's COMMIT record. If we say lsn at the COMMIT record
> then it might be interpreted as at he beginning of that record (and
> that is wrong).
>
> I'm going to push this if no objections.
LGTM
Sami
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Improve WAIT FOR read-your-writes consistency doc
@ 2026-09-17 06:38 Alexander Korotkov <aekorotkov@gmail.com>
parent: Sami Imseih <samimseih.pg@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Alexander Korotkov @ 2026-09-17 06:38 UTC (permalink / raw)
To: Sami Imseih <samimseih.pg@gmail.com>; +Cc: Xuneng Zhou <xunengzhou@gmail.com>; Postgres hackers <pgsql-hackers@lists.postgresql.org>
On Thu, Sep 17, 2026 at 1:42 AM Sami Imseih <samimseih.pg@gmail.com> wrote:
> On Wed, Sep 16, 2026 at 5:30 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:
>>
>> On Tue, Sep 15, 2026 at 4:27 PM Sami Imseih <samimseih.pg@gmail.com> wrote:
>> > Attached is a rebase.
>>
>> I've made this following changes to the patch.
>> 1) Change <command>WAIT FOR</command> back to <command>WAIT</command>
>> (see a23ab4862cfe).
>> 2) In the example, app gets lsn at or after *the end* of the relevant
>> write transaction's COMMIT record. If we say lsn at the COMMIT record
>> then it might be interpreted as at he beginning of that record (and
>> that is wrong).
>>
>> I'm going to push this if no objections.
>
> LGTM
Thank you, pushed.
------
Regards,
Alexander Korotkov
Supabase
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2026-09-17 06:38 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-08 21:42 Improve WAIT FOR read-your-writes consistency doc Sami Imseih <samimseih.pg@gmail.com>
2026-09-09 07:52 ` Xuneng Zhou <xunengzhou@gmail.com>
2026-09-09 13:57 ` Sami Imseih <samimseih.pg@gmail.com>
2026-09-10 11:31 ` Xuneng Zhou <xunengzhou@gmail.com>
2026-09-10 18:19 ` Sami Imseih <samimseih.pg@gmail.com>
2026-09-15 13:26 ` Sami Imseih <samimseih.pg@gmail.com>
2026-09-16 15:51 ` Nathan Bossart <nathandbossart@gmail.com>
2026-09-16 22:30 ` Alexander Korotkov <aekorotkov@gmail.com>
2026-09-16 22:41 ` Sami Imseih <samimseih.pg@gmail.com>
2026-09-17 06:38 ` Alexander Korotkov <aekorotkov@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