Message-ID: <448c5c84-52db-470f-a01c-57776a9914aa@enterprisedb.com>
Date: Wed, 8 May 2024 13:32:45 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Unexpected data when subscribing to logical replication slot
To: Daniel McKenzie <daniel.mckenzie@curvedental.com>,
 pgsql-general@postgresql.org
References: <CACKLY6hwMnSjjipZ57TTz_FC9aWBkg7OpNLJ1tu+GvfEFc4hJA@mail.gmail.com>
Content-Language: en-US
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
In-Reply-To: <CACKLY6hwMnSjjipZ57TTz_FC9aWBkg7OpNLJ1tu+GvfEFc4hJA@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/448c5c84-52db-470f-a01c-57776a9914aa%40enterprisedb.com>
Precedence: bulk

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