public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tomas Vondra <[email protected]>
To: Daniel McKenzie <[email protected]>
To: [email protected]
Subject: Re: Unexpected data when subscribing to logical replication slot
Date: Wed, 8 May 2024 13:32:45 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CACKLY6hwMnSjjipZ57TTz_FC9aWBkg7OpNLJ1tu+GvfEFc4hJA@mail.gmail.com>
References: <CACKLY6hwMnSjjipZ57TTz_FC9aWBkg7OpNLJ1tu+GvfEFc4hJA@mail.gmail.com>

Hi,

On 5/8/24 11:17, Daniel McKenzie wrote:
> 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.
> 

Where/how does the enrichment query run? How does the whole process look
like? I guess an application is receiving decoded changes as JSON, and
then querying the database?

> 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.
> 

Would be good to have some sort of reproducer - ideally a script that
sets up an instance + replication, and demonstrates the issue. Or at
least a sufficiently detailed steps to reproduce it without having to
guess what exactly you did.

regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Unexpected data when subscribing to logical replication slot
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox