MIME-Version: 1.0
From: Daniel McKenzie <daniel.mckenzie@curvedental.com>
Date: Wed, 8 May 2024 10:17:40 +0100
Message-ID: <CACKLY6hwMnSjjipZ57TTz_FC9aWBkg7OpNLJ1tu+GvfEFc4hJA@mail.gmail.com>
Subject: Unexpected data when subscribing to logical replication slot
To: pgsql-general@postgresql.org
Content-Type: multipart/alternative; boundary="000000000000d6f74e0617edc69f"
Archived-At: <https://www.postgresql.org/message-id/CACKLY6hwMnSjjipZ57TTz_FC9aWBkg7OpNLJ1tu%2BGvfEFc4hJA%40mail.gmail.com>
Precedence: bulk

--000000000000d6f74e0617edc69f
Content-Type: text/plain; charset="UTF-8"

We have a .NET application which subscribes to a logical replication slot
using wal2json. The purpose of the application is to publish events to AWS
SQS. We are required to first "enrich" these events by querying the
database.

We have found that these queries will often find old data (i.e. the data
that existed prior to the update) which is unexpected.

For example, when I use a psql terminal to update a user's last name from
"Jones" to "Smith" then I would expect the enrichment query to find "Smith"
but it will sometimes still find "Jones". It finds the old data perhaps 1
in 50 times.

To reproduce this I use a psql terminal to execute an update statement
which changes a user's last name with \watch 1.5 and monitor our
application logs for cases where the wal2json output and the enrichment
query output have different last names.

We have compared transaction ids by adding include-xids to pg_recvlogical
and adding txid_current() to to the enrich query and the txid_current() is
always the xid + 1.

We have found two things that appear to resolve the problem -

   - Using a more powerful EC2 instance. We can reproduce the issue with a
   r7a.medium instance but not with a r7a.large EC2 instance.
   - Changing the Postgres synchronous_commit parameter from "on" to "off".
   We cannot reproduce the issue with synchronous_commit set to "off".

We need help to understand this unexpected behaviour.

We are using Postgres 14.10.

Thanks,
Daniel McKenzie

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

<div dir=3D"ltr"><div>We have a .NET application which subscribes to a logi=
cal replication slot using wal2json. The purpose of the application is to p=
ublish events to AWS SQS. We are required to first &quot;enrich&quot; these=
 events by querying the database.</div><div><br></div><div>We have found th=
at these queries will often find old data (i.e. the data that existed prior=
 to the update) which is unexpected.</div><div><br></div><div>For example, =
when I use a psql terminal to update a user&#39;s last name from &quot;Jone=
s&quot; to &quot;Smith&quot; then I would expect the enrichment query to fi=
nd &quot;Smith&quot; but it will sometimes still find &quot;Jones&quot;. It=
 finds the old data perhaps 1 in 50 times.</div><div><br></div><div>To repr=
oduce this I use a psql terminal to execute an update statement which chang=
es a user&#39;s last name with \watch 1.5 and monitor our application logs =
for cases where the wal2json output and the enrichment query output have di=
fferent last names.</div><div><br></div><div>We have compared transaction i=
ds by adding=C2=A0include-xids to pg_recvlogical and adding txid_current() =
to to the enrich query and the txid_current() is always the xid=C2=A0+ 1.</=
div><div><br></div><div>We have found two things that appear to resolve the=
 problem -</div><div><ul><li>Using a more powerful EC2 instance. We can rep=
roduce the issue with a r7a.medium instance but not with a r7a.large EC2 in=
stance.</li><li>Changing the Postgres=C2=A0synchronous_commit parameter fro=
m &quot;on&quot; to &quot;off&quot;. We cannot reproduce the issue with syn=
chronous_commit set to &quot;off&quot;.</li></ul><div>We need help to under=
stand this unexpected behaviour.</div></div><div><br></div><div>We are usin=
g Postgres 14.10.</div><div><br></div><div>Thanks,</div><div><div dir=3D"lt=
r" class=3D"gmail_signature" data-smartmail=3D"gmail_signature"><div dir=3D=
"ltr"><div><font color=3D"#000000">Daniel McKenzie</font></div></div></div>=
</div></div>

--000000000000d6f74e0617edc69f--