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 for the client. So in this case, my psql terminal is the
client, and I would expect a faster commit (from its perspective) and then
a period of risk (as a process usually done as part of the commit is now
being done in the background) but it's not clear how that affects a
replication slot subscriber.

What we're struggling to understand is: why are we seeing any updates in
the replication slot before they have been "truly committed"?

There appears to be a state of limbo between updating data and that data
being available to query (and our subscriber is picking up changes in this
period of time) but I can't pin down any documentation which describes it.

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 to replication slot changes.

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 intended exclusively for the individual or entity to which
it is addressed. This communication may contain information that is
proprietary, privileged, confidential, or otherwise legally exempt from
disclosure. If you are not the named addressee, you are not authorized to
read, print, retain, copy, or disseminate this message or any part of it.
If you have received this message in error, please notify the sender
immediately by replying to this e-mail and delete all copies of this
message.

On Wed, May 8, 2024 at 5:28 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> 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 load.
> Answering the questions posed by Tomas Vondra would help get to the
> actual cause of the lag.
>
> In meantime my suspicion is this part:
>
> "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."
>
> If this is being run against the Postgres server my guess is that
> synchronous_commit=on is causing the commit on the server to wait for
> the WAL records to be flushed to disk and this is not happening in a
> timely manner in the '... 1 in 50 times' you mention. In that case you
> see the old values not the new committed values. This seems to be
> confirmed when you set synchronous_commit=off and don't see old values.
> For completeness per:
>
> https://www.postgresql.org/docs/current/wal-async-commit.html
>
> "However, for short transactions this delay is a major component of the
> total transaction time. Selecting asynchronous commit mode means that
> the server returns success as soon as the transaction is logically
> completed, before the WAL records it generated have actually made their
> way to disk. This can provide a significant boost in throughput for
> small transactions.
>
> 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 (that is, it is
> guaranteed not to be lost if the server crashes). ...
> "
>
> >
> > Daniel McKenzie
> > Software Developer
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>
>