public inbox for [email protected]
help / color / mirror / Atom feedFrom: Amit Kapila <[email protected]>
To: Mihail Nikalayeu <[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: Mon, 25 Aug 2025 17:21:27 +0530
Message-ID: <CAA4eK1+SCb3aiMdkznTo84Rw+t1824QETRM_J4rK=ddRsDvzhQ@mail.gmail.com> (raw)
In-Reply-To: <CADzfLwUXDo2y-OAhrgraQY-drYHaoPmTYf=Z-92-QXS7RROpCw@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>
<CADzfLwUuWmb9EVuQb6QN20cpOHtUm6NxYdq=yvPZF1OVuwq6sg@mail.gmail.com>
<CAA4eK1KKh1VyBSkipxRZp=2QWj6U6McyUOMmqqmofGLUGDh4QQ@mail.gmail.com>
<CADzfLwUXDo2y-OAhrgraQY-drYHaoPmTYf=Z-92-QXS7RROpCw@mail.gmail.com>
On Mon, Aug 25, 2025 at 4:19 PM Mihail Nikalayeu
<[email protected]> wrote:
>
> > Why only by luck?
>
> I mean last_write_win provides the same results in the following cases:
> * we found the tuple, detected a conflict, and decided to ignore the
> update coming from the publisher
> * we were unable to find the tuple, logged an error about it, and
> ignored the update coming from the publisher
>
> In both cases, the result is the same: the subscriber version remains
> in the table.
>
Right, so we can say that it will be consistent.
> > Then these may not lead to eventual consistency for such cases. So,
> > not sure one should anyway rely on these.
>
> But with the fixed snapshot dirty scan, it becomes possible to
> implement such strategies.
> Also, some strategies require some kind of merge function for tuples.
> In my understanding, even last_write_win should probably compare
> timestamps to determine which version is "newer" because time in
> distributed systems can be tricky.
> Therefore, we have to find the tuple if it exists.
>
> > BTW, then isn't it possible that INSERT happens on a different page?
>
> Yes, it is possible - in that case, the bug does not occur. It only
> happens if a new TID of some logical tuple is added to the same page.
>
What if the new insert happens in a page prior to the current page? I
mean that the scan won't encounter the page where Insert happens.
> Just to clarify, this is about B-tree pages, not the heap.
>
> > I think this questions whether we consider the SnapshotDirty results
> > correct or not.
>
> In my understanding, this is clearly wrong:
> * such behavior is not documented anywhere
>
I agree. This is where we need inputs.
> * usage patterns assume that such things cannot happen
> * new features struggle with it. For example, the new update_deleted
> logging may fail to behave correctly
> (038_update_missing_with_retain.pl in the patch) - so how should it be
> used? It might be correct, but it also might not be...
>
> Another option is to document the behavior and rename it to SnapshotMaybe :)
> By the way, SnapshotSelf is also affected.
>
BTW, do we know the reason behind using SnapshotDirty in the first
place? I don't see any comments in the nearby code unless I am missing
something.
> > The case of logical replication giving wrong results
> > [0] is the behavior from the beginning of logical replication.
>
> Logical replication was mainly focused on replication without any
> concurrent updates on the subscriber side. So, I think this is why the
> issue was overlooked.
>
The other possibility is that as this is a rare scenario so we didn't
consider it.
--
With Regards,
Amit Kapila.
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: <CAA4eK1+SCb3aiMdkznTo84Rw+t1824QETRM_J4rK=ddRsDvzhQ@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