Re: Unexpected data when subscribing to logical replication slot

Thank you all for your input. We have solved the problem by - 1. Configuring wal2json to include xids . 2. Updating our enrichment queries to return the xmin . 3.

Re: Unexpected data when subscribing to logical replication slot

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 t

Re: Unexpected data when subscribing to logical replication slot

Sorry, to correct myself. The pg_xact bit is written with the next checkpoint. But the COMMIT record in the WAL is there. On Thu, May 9, 2024 at 5:14 PM Torsten Förtsch wrote: > I would not find this behavior surprising in particular if you have a > synchronous replica. According to the document

Re: Unexpected data when subscribing to logical replication slot

I would not find this behavior surprising in particular if you have a synchronous replica. According to the documentation of synchronous_commit: The local behavior of all non-off modes is to wait for local flush of WAL to disk. This is when the logical decoder sees the item. But that does not

Re: Unexpected data when subscribing to logical replication slot

On 5/9/24 00:32, Daniel McKenzie wrote: Asynchronous commit introduces the risk of data loss. There is a short time window between the report of transaction completion to the client and the time that the transaction is truly committed. To get anywhere with this issue you will need

Re: Unexpected data when subscribing to logical replication slot

> > Asynchronous commit introduces the risk of data loss. There is a short > time window between the report of transaction completion to the client > and the time that the transaction is truly committed. The documentation speaks about synchronous_commit changing how transactions change behaviour

Re: Unexpected data when subscribing to logical replication slot

On 5/8/24 08:24, Daniel McKenzie wrote: It's running both (in docker containers) and also quite a few more docker containers running various .NET applications. I think what you found is that the r7a.medium instance is not capable enough to do all that it is asked without introducing lag under

Re: Unexpected data when subscribing to logical replication slot

It's running both (in docker containers) and also quite a few more docker containers running various .NET applications. 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 inte

Re: Unexpected data when subscribing to logical replication slot

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 repro

Re: Unexpected data when subscribing to logical replication slot

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

Unexpected data when subscribing to logical replication slot

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 dat