public inbox for [email protected]
help / color / mirror / Atom feedFrom: Mihail Nikalayeu <[email protected]>
To: Amit Kapila <[email protected]>
Cc: Zhijie Hou (Fujitsu) <[email protected]>
Cc: Peter Geoghegan <[email protected]>
Cc: Hayato Kuroda (Fujitsu) <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: Andres Freund <[email protected]>
Subject: Re: [BUG?] check_exclusion_or_unique_constraint false negative
Date: Fri, 22 Aug 2025 17:41:25 +0200
Message-ID: <CADzfLwUuWmb9EVuQb6QN20cpOHtUm6NxYdq=yvPZF1OVuwq6sg@mail.gmail.com> (raw)
In-Reply-To: <CADzfLwXRuDKeEpfD9DvCKTmFycbObU1S1nZQvR3T-SW4YhEQAw@mail.gmail.com>
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>
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 “there
is no such row in the index.”
> 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%3D6FJoTw-kAnpPZV8nVqAyR5KL68LrHQ%40m...
view thread (37+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: [BUG?] check_exclusion_or_unique_constraint false negative
In-Reply-To: <CADzfLwUuWmb9EVuQb6QN20cpOHtUm6NxYdq=yvPZF1OVuwq6sg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox