Feedback-ID: i76984098:Fastmail
Message-ID: <654ded11-6223-462c-ab3f-ee3a17975fd6@aklaver.com>
Date: Thu, 9 May 2024 09:45:13 -0700
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>,
 tomas.vondra@enterprisedb.com
Cc: pgsql-general@postgresql.org
References: <CACKLY6hwMnSjjipZ57TTz_FC9aWBkg7OpNLJ1tu+GvfEFc4hJA@mail.gmail.com>
 <8a534c5f-e400-4bb5-b39e-2017d259ff06@aklaver.com>
 <CACKLY6hYp+9W9xijXFh_UEpDuoo7bxg-A3deBKcEzEub3K+Kfg@mail.gmail.com>
 <10bcc03d-fa3e-40b6-bdd2-cac0acd046f4@aklaver.com>
 <CACKLY6hNLT8qQWjmMChFnALr3UFEgzCxbTFbckfWd6GjHyn7Sw@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CACKLY6hNLT8qQWjmMChFnALr3UFEgzCxbTFbckfWd6GjHyn7Sw@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/654ded11-6223-462c-ab3f-ee3a17975fd6%40aklaver.com>
Precedence: bulk

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

On broad scale I'm going to say that over 'for years' there has been an 
increase in load on the Postgres server as well as the I/0 system of the 
machine it is running on. What you are seeing now is the canary in the 
mine giving you the heads up that more trouble lies ahead as the 
hardware and software is reaching load limits.

On finer scale my guess is that the following is happening when 
synchronous_commit = on:

1) Postgres session #1 does data change.

2) This is replicated out and picked up by wal2json, which sees the new 
data.

3) The Postgres server waits for the confirmation that the WAL record 
has been written out to disk. Upon confirmation it commits on the 
server. This is the part that I am not sure of in relation to wal2json.

4) Postgres session #2 queries the database for the record. In the case 
where 3) has not completed it sees the old values as the data change in 
session #1 has not committed and therefore the new values are not seen 
by other sessions.

> 
> Daniel McKenzie
> Software Developer

-- 
Adrian Klaver
adrian.klaver@aklaver.com