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>
 <CADzfLwXGhH_qD6RGqPyEeKdmHgr-HpA-tASYdi5onP+RyP5TCw@mail.gmail.com>
 <CADzfLwXRuDKeEpfD9DvCKTmFycbObU1S1nZQvR3T-SW4YhEQAw@mail.gmail.com>
In-Reply-To: 
 <CADzfLwXRuDKeEpfD9DvCKTmFycbObU1S1nZQvR3T-SW4YhEQAw@mail.gmail.com>
From: Mihail Nikalayeu <mihailnikalayeu@gmail.com>
Date: Fri, 22 Aug 2025 17:41:25 +0200
Message-ID: 
 <CADzfLwUuWmb9EVuQb6QN20cpOHtUm6NxYdq=yvPZF1OVuwq6sg@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/CADzfLwUuWmb9EVuQb6QN20cpOHtUm6NxYdq%3DyvPZF1OVuwq6sg%40mail.gmail.com>
Precedence: bulk

Amit, a few more explanations related to your message.

> IIUC, the problem you are worried about can happen with DELETE+INSERT
> in the same transaction on the subscriber, right?

Technically, yes - this can occur during a single UPDATE, as well as a
DELETE followed by an INSERT of the same key within the same
transaction (which is effectively equivalent to an UPDATE). However,
it should NOT occur, because at no point in the timeline does a row
with that key fail to exist; therefore, no scan should return =E2=80=9Cther=
e
is no such row in the index.=E2=80=9D

> If so, this should
> happen with DELETE and INSERT in a separate transaction as well.

Yes, it may happen - and in that case, it is correct. This is because
there is a moment between the DELETE and the INSERT when the row does
not exist. Therefore, it is acceptable for a scan to check the index
at that particular moment and find nothing.

> 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.

For the last_write_win and UPDATE vs UPDATE case - yes, probably, but
only by luck.

However, there are many scenarios that cannot be implemented
correctly, for example:
* DELETE always wins
* UPDATE with a higher version (column value) wins
* first_write_win
* etc.

Also, the cases from [0] are clearly wrong without any conflict
resolution. In particular, case 2 - there are no real conflicts at all
(since different sets of columns are involved), but an incorrect
result may still be produced.

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