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>
In-Reply-To: <270FB587-2E83-4EB0-9FD6-07541F2A6A17@thebuild.com>
From: Phillip Diffley <phillip6402@gmail.com>
Date: Tue, 25 Mar 2025 22:56:38 -0500
Message-ID: <CAGAwPgQ1MgX2s4UgEspCwfKN_3okudzwh7NzpkoJZSA1HnGqYQ@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="000000000000c1d722063136d3f5"
Archived-At: <https://www.postgresql.org/message-id/CAGAwPgQ1MgX2s4UgEspCwfKN_3okudzwh7NzpkoJZSA1HnGqYQ%40mail.gmail.com>
Precedence: bulk

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

> You shouldn't need to manually advance the replication slot.
> The client is also expected to send back regular messages letting the
publisher / primary know that it has successfully consumed up to a
particular point

I was thinking of these as the same thing, but it sounds like they are
different. At the moment, the only method I know for letting the
publisher/primary know what has been successfully consumed is
pg_replication_slot_advance. I looked at the message formats
<https://www.postgresql.org/docs/current/protocol-message-formats.html#PROT=
OCOL-MESSAGE-FORMATS>
and logical replication message formats
<https://www.postgresql.org/docs/current/protocol-logicalrep-message-format=
s.html>
pages, but I did not see a message type for updating confirmed_flush_lsn or
otherwise letting the publisher/primary know what logs have been
successfully consumed. There is the flush
<https://www.postgresql.org/docs/current/protocol-message-formats.html#PROT=
OCOL-MESSAGE-FORMATS-FLUSH>
message, but it looks like it only passes a 4 byte int instead of the 8
bytes required for an LSN. Is there a message type that is used to confirm
what logs have been successfully consumed?

> You know that any WAL generated after `confirmed_flush_lsn` is available
for replay.

The part I am uncertain about is what "after" means here, since LSNs are
not presented in order, and the order of data streamed over the replication
slot does not match the order of the data in the WAL.

I initially (and incorrectly) thought the confirmation order was based on
LSN. So if you confirmed an LSN "x" then all logs with LSN less than "x"
could be released by the publisher/primary. That can't work though since
LSNs are not presented in order by the replication slot. Is there a
monotonically increasing identifier that can be used to identify which logs
come "after" another? Or do you just keep track of the order the
replication slot delivers logs in and not confirm a log until it and all
the logs received before it are processed to the point of being crash-proof=
?

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

>
>
> > On Mar 25, 2025, at 13:58, Phillip Diffley <phillip6402@gmail.com>
> wrote:
> >
> > Oh I see! I was conflating the data I see coming out of a replication
> slot with the internal organization of the WAL. I think the more specific
> question I am trying to answer is, as a consumer of a replication slot, h=
ow
> do I reason about what replication records will be made unavailable when =
I
> confirm an LSN? Here I am worried about situations where the replication
> connection is interrupted or the program processing the records crashes,
> and we need to replay records that may have been previously sent but were
> not fully processed.
>
> It's up to the consuming client to keep track of where it is in the WAL
> (using an LSN).  When the client connects, it specifies what LSN to start
> streaming at.  If that LSN is no longer available, the publisher / primar=
y
> returns an error.
>
> The client shouldn't confirm the flush of an LSN unless it is crash-proof
> to that point, since any WAL before that should be assumed to be
> unavailable.
>
> > For example, are the records sent by a replication slot always sent in
> the same order such that if I advance the confirmed_flush_lsn of a slot t=
o
> the LSN of record "A", I will know that any records that had been streame=
d
> after record "A" will be replayable?
>
> You know that any WAL generated after `confirmed_flush_lsn` is available
> for replay.  That's the oldest LSN that the client can specify on
> connection (although it can specify a later one, if it exists).  You
> shouldn't need to manually advance the replication slot.  Instead, the
> client specifies where it wants to start when it connects.  The client is
> also expected to send back regular messages letting the publisher / prima=
ry
> know that it has successfully consumed up to a particular point in the WA=
L,
> so the publisher / primary knows it can release that WAL information.

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

<div dir=3D"ltr"><div dir=3D"ltr">&gt; You shouldn&#39;t need to manually a=
dvance the replication slot.</div><div>&gt; The client is also expected to =
send back regular messages letting the=20
publisher / primary know that it has successfully consumed up to a=20
particular point</div><div><br></div><div>I was thinking of these as the sa=
me thing, but it sounds like they are different. At the moment, the only me=
thod I know for letting the publisher/primary know what has been successful=
ly consumed is pg_replication_slot_advance. I looked at the <a href=3D"http=
s://www.postgresql.org/docs/current/protocol-message-formats.html#PROTOCOL-=
MESSAGE-FORMATS">message formats</a> and <a href=3D"https://www.postgresql.=
org/docs/current/protocol-logicalrep-message-formats.html">logical replicat=
ion message formats</a> pages, but I did not see a message type for updatin=
g confirmed_flush_lsn or otherwise letting the publisher/primary know what =
logs have been successfully consumed. There is the <a href=3D"https://www.p=
ostgresql.org/docs/current/protocol-message-formats.html#PROTOCOL-MESSAGE-F=
ORMATS-FLUSH">flush</a> message, but it looks like it only passes a 4 byte =
int instead of the 8 bytes required for an LSN. Is there a message type tha=
t is used to confirm what logs have been successfully consumed?</div><div><=
br></div><div>&gt; You know that any WAL generated after `confirmed_flush_l=
sn` is available for replay.</div><div><br></div><div>The part I am uncerta=
in about  is what &quot;after&quot; means here, since LSNs are not presente=
d in order, and the order of data streamed over the replication slot does n=
ot match the order of the data in the WAL.=C2=A0</div><div><br></div><div>I=
 initially (and incorrectly) thought the confirmation order was based on LS=
N. So if you confirmed an LSN &quot;x&quot; then all logs with LSN less tha=
n &quot;x&quot; could be released by the publisher/primary. That can&#39;t =
work though since LSNs are not presented in order by the replication slot. =
Is there a monotonically increasing identifier that can be used to identify=
 which logs come &quot;after&quot; another? Or do you just keep track of th=
e order  the replication slot delivers logs in and not confirm a log until =
it and all the logs received before it are processed to the point of being =
crash-proof?</div><div><br></div><div class=3D"gmail_quote gmail_quote_cont=
ainer"><div dir=3D"ltr" class=3D"gmail_attr">On Tue, Mar 25, 2025 at 4:32=
=E2=80=AFPM Christophe Pettus &lt;<a href=3D"mailto:xof@thebuild.com">xof@t=
hebuild.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=
=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding=
-left:1ex"><br>
<br>
&gt; On Mar 25, 2025, at 13:58, Phillip Diffley &lt;<a href=3D"mailto:phill=
ip6402@gmail.com" target=3D"_blank">phillip6402@gmail.com</a>&gt; wrote:<br=
>
&gt; <br>
&gt; Oh I see! I was conflating the data I see coming out of a replication =
slot with the internal organization of the WAL. I think the more specific q=
uestion I am trying to answer is, as a consumer of a replication slot, how =
do I reason about what replication records will be made unavailable when I =
confirm an LSN? Here I am worried about situations where the replication co=
nnection is interrupted or the program processing the records crashes, and =
we need to replay records that may have been previously sent but were not f=
ully processed.<br>
<br>
It&#39;s up to the consuming client to keep track of where it is in the WAL=
 (using an LSN).=C2=A0 When the client connects, it specifies what LSN to s=
tart streaming at.=C2=A0 If that LSN is no longer available, the publisher =
/ primary returns an error.<br>
<br>
The client shouldn&#39;t confirm the flush of an LSN unless it is crash-pro=
of to that point, since any WAL before that should be assumed to be unavail=
able.<br>
<br>
&gt; For example, are the records sent by a replication slot always sent in=
 the same order such that if I advance the confirmed_flush_lsn of a slot to=
 the LSN of record &quot;A&quot;, I will know that any records that had bee=
n streamed after record &quot;A&quot; will be replayable?<br>
<br>
You know that any WAL generated after `confirmed_flush_lsn` is available fo=
r replay.=C2=A0 That&#39;s the oldest LSN that the client can specify on co=
nnection (although it can specify a later one, if it exists).=C2=A0 You sho=
uldn&#39;t need to manually advance the replication slot.=C2=A0 Instead, th=
e client specifies where it wants to start when it connects.=C2=A0 The clie=
nt is also expected to send back regular messages letting the publisher / p=
rimary know that it has successfully consumed up to a particular point in t=
he WAL, so the publisher / primary knows it can release that WAL informatio=
n.</blockquote></div></div>

--000000000000c1d722063136d3f5--