MIME-Version: 1.0
References: <CACKLY6hwMnSjjipZ57TTz_FC9aWBkg7OpNLJ1tu+GvfEFc4hJA@mail.gmail.com>
 <8a534c5f-e400-4bb5-b39e-2017d259ff06@aklaver.com> <CACKLY6hYp+9W9xijXFh_UEpDuoo7bxg-A3deBKcEzEub3K+Kfg@mail.gmail.com>
 <10bcc03d-fa3e-40b6-bdd2-cac0acd046f4@aklaver.com> <CACKLY6hNLT8qQWjmMChFnALr3UFEgzCxbTFbckfWd6GjHyn7Sw@mail.gmail.com>
 <654ded11-6223-462c-ab3f-ee3a17975fd6@aklaver.com>
In-Reply-To: <654ded11-6223-462c-ab3f-ee3a17975fd6@aklaver.com>
From: Daniel McKenzie <daniel.mckenzie@curvedental.com>
Date: Fri, 10 May 2024 13:11:42 +0100
Message-ID: <CACKLY6ibsfhfFrrLbSXANcjsRFa5RUGxeY5ra-hcUnOPAtJVDg@mail.gmail.com>
Subject: Re: Unexpected data when subscribing to logical replication slot
To: Adrian Klaver <adrian.klaver@aklaver.com>, tomas.vondra@enterprisedb.com, 
	tfoertsch123@gmail.com
Cc: pgsql-general@postgresql.org
Content-Type: multipart/alternative; boundary="000000000000d4b24d06181870d4"
Archived-At: <https://www.postgresql.org/message-id/CACKLY6ibsfhfFrrLbSXANcjsRFa5RUGxeY5ra-hcUnOPAtJVDg%40mail.gmail.com>
Precedence: bulk

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

Thank you all for your input.

We have solved the problem by -

   1. Configuring wal2json to include xids
   <https://github.com/eulerto/wal2json/blob/master/README.md>.
   2. Updating our enrichment queries to return the xmin
   <https://www.postgresql.org/docs/9.1/ddl-system-columns.html>.
   3. Retrying our enrichment queries after a brief sleep if xmin < xid.

This seems to be working very well so far.

Daniel McKenzie
Software Developer

Office: +1 403.910.5927 x 251
Mobile: +44 7712 159045
Website: www.curvedental.com

*Curve Dental Confidentiality Notice*
This message is intended exclusively for the individual or entity to which
it is addressed. This communication may contain information that is
proprietary, privileged, confidential, or otherwise legally exempt from
disclosure. If you are not the named addressee, you are not authorized to
read, print, retain, copy, or disseminate this message or any part of it.
If you have received this message in error, please notify the sender
immediately by replying to this e-mail and delete all copies of this
message.


On Thu, May 9, 2024 at 5:45=E2=80=AFPM Adrian Klaver <adrian.klaver@aklaver=
.com>
wrote:

> On 5/9/24 00:32, Daniel McKenzie wrote:
>
> > We've had this running in live now for years without a hiccup so we are
> > surprised to learn that we have this massive race condition and it just
> > so happens that the hardware is fast enough to process the transaction
> > before the .NET application can react to replication slot changes.
>
> On broad scale I'm going to say that over 'for years' there has been an
> increase in load on the Postgres server as well as the I/0 system of the
> machine it is running on. What you are seeing now is the canary in the
> mine giving you the heads up that more trouble lies ahead as the
> hardware and software is reaching load limits.
>
> On finer scale my guess is that the following is happening when
> synchronous_commit =3D on:
>
> 1) Postgres session #1 does data change.
>
> 2) This is replicated out and picked up by wal2json, which sees the new
> data.
>
> 3) The Postgres server waits for the confirmation that the WAL record
> has been written out to disk. Upon confirmation it commits on the
> server. This is the part that I am not sure of in relation to wal2json.
>
> 4) Postgres session #2 queries the database for the record. In the case
> where 3) has not completed it sees the old values as the data change in
> session #1 has not committed and therefore the new values are not seen
> by other sessions.
>
> >
> > Daniel McKenzie
> > Software Developer
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
>

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

<div dir=3D"ltr"><div>Thank you all for your input.</div><div><br></div><di=
v>We have solved the problem by -</div><div><ol><li>Configuring wal2json to=
 <a href=3D"https://github.com/eulerto/wal2json/blob/master/README.md">incl=
ude xids</a>.</li><li>Updating our enrichment queries=C2=A0to return the <a=
 href=3D"https://www.postgresql.org/docs/9.1/ddl-system-columns.html">xmin<=
/a>.</li><li>Retrying our enrichment queries after a brief sleep if xmin &l=
t; xid.</li></ol><div>This seems to be working very well so far.</div></div=
><div><br></div><div><div dir=3D"ltr" class=3D"gmail_signature" data-smartm=
ail=3D"gmail_signature"><div dir=3D"ltr"><div><font color=3D"#000000">Danie=
l McKenzie</font></div><div><font color=3D"#000000">Software Developer</fon=
t></div><div><font color=3D"#000000"><br></font></div><div><div style=3D"co=
lor:rgb(34,34,34)">Office: +1 403.910.5927 x 251</div><div style=3D"color:r=
gb(34,34,34)">Mobile: +44 7712 159045</div></div><div><font color=3D"#00000=
0">Website:=C2=A0<a href=3D"http://www.curvedental.com/" target=3D"_blank">=
www.curvedental.com</a></font></div><div><div style=3D"color:rgb(34,34,34)"=
><span><img width=3D"414px;" height=3D"204px;" src=3D"https://lh4.googleuse=
rcontent.com/kP-tftTVPqEWSyAs7smdyApYfhJjzsj0rYBa6N78OWEqemCvkLQuMeq9p5msU0=
4kDWe1WC8FyvKdsmY4Cj5MSYFWpQeRNW1nO0mArl2KWrpZEd-KMRI4R9Efiq_MpDMD"></span>=
<br></div><div style=3D"color:rgb(34,34,34)"><div><u><b><font size=3D"1">Cu=
rve Dental Confidentiality Notice</font></b></u></div><div><font size=3D"1"=
>This message is intended exclusively for the individual or entity to which=
 it is addressed. This communication may=C2=A0</font><span style=3D"font-si=
ze:x-small">contain information that is proprietary, privileged, confidenti=
al, or otherwise legally exempt from disclosure. If you are=C2=A0</span><sp=
an style=3D"font-size:x-small">not the named addressee, you are not authori=
zed to read, print, retain, copy, or disseminate this message or any=C2=A0<=
/span><span style=3D"font-size:x-small">part of it. If you have received th=
is message in error, please notify the sender immediately by replying to th=
is e-mail=C2=A0</span><span style=3D"font-size:x-small">and delete all copi=
es of this message.</span></div></div></div></div></div></div><br></div><br=
><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Thu, M=
ay 9, 2024 at 5:45=E2=80=AFPM Adrian Klaver &lt;<a href=3D"mailto:adrian.kl=
aver@aklaver.com">adrian.klaver@aklaver.com</a>&gt; wrote:<br></div><blockq=
uote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1p=
x solid rgb(204,204,204);padding-left:1ex">On 5/9/24 00:32, Daniel McKenzie=
 wrote:<br>
<br>
&gt; We&#39;ve had this running in live now for years without a hiccup so w=
e are <br>
&gt; surprised to learn that we have this massive race condition and it jus=
t <br>
&gt; so happens that the hardware is fast enough to process the transaction=
 <br>
&gt; before the .NET application can react to replication slot changes.<br>
<br>
On broad scale I&#39;m going to say that over &#39;for years&#39; there has=
 been an <br>
increase in load on the Postgres server as well as the I/0 system of the <b=
r>
machine it is running on. What you are seeing now is the canary in the <br>
mine giving you the heads up that more trouble lies ahead as the <br>
hardware and software is reaching load limits.<br>
<br>
On finer scale my guess is that the following is happening when <br>
synchronous_commit =3D on:<br>
<br>
1) Postgres session #1 does data change.<br>
<br>
2) This is replicated out and picked up by wal2json, which sees the new <br=
>
data.<br>
<br>
3) The Postgres server waits for the confirmation that the WAL record <br>
has been written out to disk. Upon confirmation it commits on the <br>
server. This is the part that I am not sure of in relation to wal2json.<br>
<br>
4) Postgres session #2 queries the database for the record. In the case <br=
>
where 3) has not completed it sees the old values as the data change in <br=
>
session #1 has not committed and therefore the new values are not seen <br>
by other sessions.<br>
<br>
&gt; <br>
&gt; Daniel McKenzie<br>
&gt; Software Developer<br>
<br>
-- <br>
Adrian Klaver<br>
<a href=3D"mailto:adrian.klaver@aklaver.com" target=3D"_blank">adrian.klave=
r@aklaver.com</a><br>
<br>
</blockquote></div>

--000000000000d4b24d06181870d4--