MIME-Version: 1.0
References: 
 <CANtu0oiktqQ2pwExoXqDpByXNCJa-KE5vQRodTRnmFHN_+qwHg@mail.gmail.com>
 <CANtu0ohU2XRV9shtu14CffLPDS1x10q7ebOGf-vX0p+45_L8jw@mail.gmail.com>
 <CANtu0oh0tspW-xWzDGWP9ehz96KPt9aUP1c9JYhdBYxKsB0jpA@mail.gmail.com>
 <CANtu0ohUB9ky45iiMAYN1fGyt82+cg=+UYBom=P7drb+=97G9w@mail.gmail.com>
 <TYAPR01MB56921C9C3D21B0D62FF76330F5B22@TYAPR01MB5692.jpnprd01.prod.outlook.com>
 <CANtu0og=5v4j8onS4nyJ4zMPdh-EPFxmiEi5PLoyZrmqHA6RKw@mail.gmail.com>
 <CAA4eK1Jfb0xviXYon-_TvHNKeAY7ngAeo++Knu-0RPR6EkSBjA@mail.gmail.com>
 <CANtu0ohHmYXsK5bxU9Thcq1FbELLAk0S2Zap0r8AnU3OTmcCOA@mail.gmail.com>
 <CAA4eK1+_V1PWXrrgAM01p+CByP6JwYRxejZrcxOu83a-v_+zZg@mail.gmail.com>
 <CANtu0ogDDQnXbrv6p7Xtc2dT_MZ1fjdPgB9-0B5Lw1b4pQGd2A@mail.gmail.com>
 <OS0PR01MB5716FFD8DBBADB55E8E6935994852@OS0PR01MB5716.jpnprd01.prod.outlook.com>
 <CANtu0oiziTBM8+WDtkktMZv0rhGBroYGWwqSQW+MzOWpmk-XEw@mail.gmail.com>
 <OS0PR01MB5716E30952F542E256DD72E294802@OS0PR01MB5716.jpnprd01.prod.outlook.com>
 <CANtu0oh69b+VCiASX86dF_eY=9=A2RmMQ_+0+uxZ_Zir+oNhhw@mail.gmail.com>
 <CANtu0og_4FVsRMxXue8SXUn03MwBAT0SiZSib_wcPMLDkpn-RA@mail.gmail.com>
 <CANtu0oj8LzpjCvF1zSwdLJxPddhbdY0=uh=7-wT6vwhmju_-PQ@mail.gmail.com>
 <CANtu0ogHMahRJvLKPofE9T7Z19H3UyWeb22fZ9cKfFRG_BEV0w@mail.gmail.com>
 <CANtu0ojos4kvrrQ9YJOei2=c5vB1wJBHpR3q_X+BG1i99ut+Hw@mail.gmail.com>
 <CADzfLwWuXh8KO=OZvB71pZnQ8nH0NYXfuGbFU6FBiVZUbmuFGg@mail.gmail.com>
In-Reply-To: 
 <CADzfLwWuXh8KO=OZvB71pZnQ8nH0NYXfuGbFU6FBiVZUbmuFGg@mail.gmail.com>
From: Amit Kapila <amit.kapila16@gmail.com>
Date: Fri, 22 Aug 2025 16:20:41 +0530
Message-ID: 
 <CAA4eK1JUvx4nMgoTVtxPWAD3nhwnuBdfXLxSi3Lc+xhk57XwOg@mail.gmail.com>
Subject: Re: [BUG?] check_exclusion_or_unique_constraint false negative
To: Mihail Nikalayeu <michail.nikolaev@gmail.com>
Cc: "Zhijie Hou (Fujitsu)" <houzj.fnst@fujitsu.com>,
 Peter Geoghegan <pg@bowt.ie>,
	"Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>,
	PostgreSQL Hackers <pgsql-hackers@postgresql.org>,
 Andres Freund <andres@anarazel.de>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAA4eK1JUvx4nMgoTVtxPWAD3nhwnuBdfXLxSi3Lc%2Bxhk57XwOg%40mail.gmail.com>
Precedence: bulk

On Wed, Mar 12, 2025 at 6:36=E2=80=AFAM Mihail Nikalayeu
<michail.nikolaev@gmail.com> wrote:
>
> Hello, everyone and Peter!
>
> Peter, I have added you because you may be interested in (or already know=
 about) this btree-related issue.
>
> Short description of the problem:
>
> I noticed a concurrency issue in btree index scans that affects SnapshotD=
irty and SnapshotSelf scan types.
> When using these non-MVCC snapshot types, a scan could miss tuples if con=
current transactions delete existing tuples and insert new one with differe=
nt TIDs on the same page.
>
> The problem occurs because:
>   1. The scan reads a page and caches its tuples in backend-local storage
>   2. A concurrent transaction deletes a tuple and inserts a new one with =
a different TID
>   3. The scan misses the new tuple because it was already deleted by a co=
mmitted transaction and does not pass visibility check
>   4. But new version on the page is missed, because not in cached tuples
>

IIUC, the problem you are worried about can happen with DELETE+INSERT
in the same transaction on the subscriber, right? If so, this should
happen with DELETE and INSERT in a separate transaction as well. If
that happens then we anyway may not be able to detect such an INSERT
if it happens on a page earlier than the current page.

BTW, as the update (or DELETE+INSERT) happens at a later time than the
publisher's update/delete, so once we have the last_write_win
resolution strategy implemented, it is the subscriber operation that
will win. So, the current behavior shouldn't cause any problem.

--=20
With Regards,
Amit Kapila.