MIME-Version: 1.0
References: <CAGAwPgR=B9VTDwKkHZRHjYTmQzvJ+30mK-uyc7MMw6Tf9s9Fsw@mail.gmail.com>
 <E08677AC-AE92-40A1-9C46-9C8BA4529FE7@thebuild.com> <CAGAwPgT2zsO5XYH1_uZuVyOxf7vUUBvPGjXwwBW1ySXicFNQ=Q@mail.gmail.com>
 <270FB587-2E83-4EB0-9FD6-07541F2A6A17@thebuild.com> <CAGAwPgQ1MgX2s4UgEspCwfKN_3okudzwh7NzpkoJZSA1HnGqYQ@mail.gmail.com>
 <3CBAE3E1-4ECB-4936-908E-3F03B79886F8@thebuild.com>
In-Reply-To: <3CBAE3E1-4ECB-4936-908E-3F03B79886F8@thebuild.com>
From: Phillip Diffley <phillip6402@gmail.com>
Date: Wed, 26 Mar 2025 09:55:06 -0500
Message-ID: <CAGAwPgRoGtUvkX=s4pCQd2Fhy9XqMit6y2eM6=kQyDAN9v71qQ@mail.gmail.com>
Subject: Re: Replication slot WAL reservation
To: Christophe Pettus <xof@thebuild.com>
Cc: pgsql-general@postgresql.org
Content-Type: multipart/alternative; boundary="000000000000959cd206314006f8"
Archived-At: <https://www.postgresql.org/message-id/CAGAwPgRoGtUvkX%3Ds4pCQd2Fhy9XqMit6y2eM6%3DkQyDAN9v71qQ%40mail.gmail.com>
Precedence: bulk

--000000000000959cd206314006f8
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

> You're looking for Standby Status Update

Awesome! I completely missed that.

> Transactions are always presented to the output plugin in commit order

Ok great. I think that is what I needed to know.

Just to confirm, it sounds like the order messages are sent from the output
plugin is what matters here. When you update confirmed_flush_lsn to LSN
"A", any messages that were sent by the output plugin after the message
with LSN "A" will be replayable. Any messages sent by the output plugin
before the message with LSN "A" will most likely not be replayed, since
their data is freed for deletion. Is that correct?

On Tue, Mar 25, 2025 at 11:32=E2=80=AFPM Christophe Pettus <xof@thebuild.co=
m> wrote:

>
>
> > On Mar 25, 2025, at 20:56, Phillip Diffley <phillip6402@gmail.com>
> wrote:
> >
> > Is there a message type that is used to confirm what logs have been
> successfully consumed?
>
> You're looking for Standby Status Update:
>
>
> https://www.postgresql.org/docs/current/protocol-replication.html#PROTOCO=
L-REPLICATION-STANDBY-STATUS-UPDATE
>
> The logical replication protocol is a superset of the binary replication
> protocol, so many of the same messages are used.
>
> > The part I am uncertain about is what "after" means here, since LSNs ar=
e
> not presented in order, and the order of data streamed over the replicati=
on
> slot does not match the order of the data in the WAL.
>
> I think there's a misunderstanding here (possibly my fault).  Transaction=
s
> are always presented to the output plugin in commit order, and LSNs can b=
e
> reliably used to determine the time ordering of commits.  LSNs are exactl=
y
> what is used to determine how far into the WAL the replication slot has
> gotten.

--000000000000959cd206314006f8
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>&gt; You&#39;re looking for Standby Status Update</di=
v><div><br></div><div>Awesome! I completely missed that. <br></div><div><br=
></div><div>&gt; Transactions are always presented to the output plugin in =
commit order</div><div><br></div><div>Ok great. I think that is what I need=
ed to know.=C2=A0</div><div><br></div><div>Just to confirm, it sounds like =
the order messages are sent from the output plugin is what matters here. Wh=
en you update confirmed_flush_lsn to LSN &quot;A&quot;, any messages that w=
ere sent by the output plugin after the message with LSN &quot;A&quot; will=
 be replayable. Any messages sent by the output plugin before the message w=
ith LSN &quot;A&quot; will most likely not be replayed, since their data is=
 freed for deletion. Is that correct?</div></div><br><div class=3D"gmail_qu=
ote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Tue, Ma=
r 25, 2025 at 11:32=E2=80=AFPM Christophe Pettus &lt;<a href=3D"mailto:xof@=
thebuild.com">xof@thebuild.com</a>&gt; wrote:<br></div><blockquote class=3D=
"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(2=
04,204,204);padding-left:1ex"><br>
<br>
&gt; On Mar 25, 2025, at 20:56, Phillip Diffley &lt;<a href=3D"mailto:phill=
ip6402@gmail.com" target=3D"_blank">phillip6402@gmail.com</a>&gt; wrote:<br=
>
&gt; <br>
&gt; Is there a message type that is used to confirm what logs have been su=
ccessfully consumed?<br>
<br>
You&#39;re looking for Standby Status Update:<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 <a href=3D"https://www.postgresql.org/docs/curr=
ent/protocol-replication.html#PROTOCOL-REPLICATION-STANDBY-STATUS-UPDATE" r=
el=3D"noreferrer" target=3D"_blank">https://www.postgresql.org/docs/current=
/protocol-replication.html#PROTOCOL-REPLICATION-STANDBY-STATUS-UPDATE</a><b=
r>
<br>
The logical replication protocol is a superset of the binary replication pr=
otocol, so many of the same messages are used.<br>
<br>
&gt; The part I am uncertain about is what &quot;after&quot; means here, si=
nce LSNs are not presented in order, and the order of data streamed over th=
e replication slot does not match the order of the data in the WAL. <br>
<br>
I think there&#39;s a misunderstanding here (possibly my fault).=C2=A0 Tran=
sactions are always presented to the output plugin in commit order, and LSN=
s can be reliably used to determine the time ordering of commits.=C2=A0 LSN=
s are exactly what is used to determine how far into the WAL the replicatio=
n slot has gotten.</blockquote></div>

--000000000000959cd206314006f8--