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: Mon, 25 Aug 2025 12:49:10 +0200
Message-ID: <CADzfLwUXDo2y-OAhrgraQY-drYHaoPmTYf=Z-92-QXS7RROpCw@mail.gmail.com> (raw)
In-Reply-To: <CAA4eK1KKh1VyBSkipxRZp=2QWj6U6McyUOMmqqmofGLUGDh4QQ@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>
Hello!
> 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.
> 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.
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
* 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.
> 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.
Best regards,
Mikhail.
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: <CADzfLwUXDo2y-OAhrgraQY-drYHaoPmTYf=Z-92-QXS7RROpCw@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