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>
 <CAA4eK1JUvx4nMgoTVtxPWAD3nhwnuBdfXLxSi3Lc+xhk57XwOg@mail.gmail.com>
In-Reply-To: 
 <CAA4eK1JUvx4nMgoTVtxPWAD3nhwnuBdfXLxSi3Lc+xhk57XwOg@mail.gmail.com>
From: Mihail Nikalayeu <mihailnikalayeu@gmail.com>
Date: Fri, 22 Aug 2025 14:47:57 +0200
Message-ID: 
 <CADzfLwXGhH_qD6RGqPyEeKdmHgr-HpA-tASYdi5onP+RyP5TCw@mail.gmail.com>
Subject: Re: [BUG?] check_exclusion_or_unique_constraint false negative
To: Amit Kapila <amit.kapila16@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/CADzfLwXGhH_qD6RGqPyEeKdmHgr-HpA-tASYdi5onP%2BRyP5TCw%40mail.gmail.com>
Precedence: bulk

Hello, Amit,

> IIUC, the problem you are worried about can happen with DELETE+INSERT
It seems there was some misunderstanding due to my bad explanation and word=
ing.
I wrote "A concurrent transaction deletes a tuple and inserts a new
one with a different TID" - but I mean logical UPDATE causing new TID
in index page appear because HOT was applied...

Lets try again, I hope that explanation is better:

At the start, we have a table with a primary key and one extra index
(to disable HOT), and a tuple with i=3D13:

CREATE TABLE table (i int PRIMARY KEY, data text);
CREATE INDEX no_more_hot_data_index ON table (data);
INSERT INTO table (i, data) VALUES (13, 'data');

A btree scan using SnapshotDirty can miss tuples because of internal
locking logic. Here=E2=80=99s how the bug shows up:

1) we have a tuple in the index (i=3D13), committed long ago
2) transaction A starts an index search for that tuple using
SnapshotDirty (WHERE i =3D 13)
3) in parallel, transaction B updates that tuple (SET data=3D'updated'
WHERE i=3D13) and commits (creating a new index entry because HOT is not
applied)
4) the scan from step 2 returns nothing at all - as if the tuple never exis=
ted

In other words, if you start a SnapshotDirty btree scan for i=3D13 and
update that row i=3D13 at the same physical moment, the scan may:
* return the TID of the pre=E2=80=91update version - correct behavior
* return the TID of the post=E2=80=91update version - also correct
* return nothing - this is the broken case

More broadly: any SnapshotDirty scan may completely miss existing data
when there are concurrent updates.

SnapshotDirty usage in Postgres is limited, so the impact isn=E2=80=99t hug=
e,
but every case I found is reproducible with the tests from the first
commit from v10 in my previous email.

* check_exclusion_or_unique_constraint: only a minor performance
impact, handled by retry logic
* logical replication TAP tests: multiple scenarios fail because
RelationFindReplTupleByIndex cannot find existing committed tuples

These scenarios look like:

1) logical replication tries to apply a change for tuple X received
from the publisher
2) meanwhile, the subscriber updates the same tuple X and commits in
parallel transaction
3) due to the bug, RelationFindReplTupleByIndex concludes the tuple X
does not exist at all, leading to bad outcomes, including:
     * incorrect conflict=E2=80=91type messages (and, in the future,
potentially wrong conflict=E2=80=91resolution choices)
     * lost updates (see scenario 2 from [0])

If you look at the tests and play with the $simulate_race_condition
flag, you can see the behavior directly. The second commit (a possible
fix) in v10 also includes documentation updates that try to explain
the issue in a more appropriate context.

I=E2=80=99m happy to provide additional reproducers or explanations if that=
 would help.

[0]: https://www.postgresql.org/message-id/flat/CADzfLwWC49oanFSGPTf%3D6FJo=
Tw-kAnpPZV8nVqAyR5KL68LrHQ%40mail.gmail.com#5f6b3be849f8d95c166decfae541df0=
9

Best regards,
Mikhail.