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
