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>
 <CADzfLwUuWmb9EVuQb6QN20cpOHtUm6NxYdq=yvPZF1OVuwq6sg@mail.gmail.com>
 <CAA4eK1KKh1VyBSkipxRZp=2QWj6U6McyUOMmqqmofGLUGDh4QQ@mail.gmail.com>
 <CADzfLwUXDo2y-OAhrgraQY-drYHaoPmTYf=Z-92-QXS7RROpCw@mail.gmail.com>
 <CAA4eK1+SCb3aiMdkznTo84Rw+t1824QETRM_J4rK=ddRsDvzhQ@mail.gmail.com>
 <CADzfLwUqZ_s9FzonE-z0REOm8Q0aDVJh_W8S1r9vTYSwBLHo+g@mail.gmail.com>
 <CAA4eK1+7bNcjF5gRnfACgLMq1VPKQdD8fR43Rd93wdMzLqCbdA@mail.gmail.com>
In-Reply-To: 
 <CAA4eK1+7bNcjF5gRnfACgLMq1VPKQdD8fR43Rd93wdMzLqCbdA@mail.gmail.com>
From: Mihail Nikalayeu <mihailnikalayeu@gmail.com>
Date: Wed, 27 Aug 2025 10:53:57 +0200
Message-ID: 
 <CADzfLwWiTiT_LSMfPi+_NAp21aeO5m=09K2VuiF2pB4PJ6Ee2A@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>, peter@eisentraut.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CADzfLwWiTiT_LSMfPi%2B_NAp21aeO5m%3D09K2VuiF2pB4PJ6Ee2A%40mail.gmail.com>
Precedence: bulk

Hello, Amit!

Amit Kapila <amit.kapila16@gmail.com>:

> Now, I
> would like to know the opinion of others who were involved in the
> initial commit, so added Peter E. to see what he thinks of the same.

Seems like you added another Peter in [0] - I added Peter Eisentraut :)

> > Hmm.... Yes - if the TID lands to the page left of the current
> > position, we=E2=80=99ll miss it as well.
> > A lock=E2=80=91based solution (version in the v10) would require keepin=
g all
> > pages with the same key under a read lock, which feels too expensive.
> Right.

I think it is possible to achieve the same guarantees and logic using
GetLatestSnapshot + HeapTupleSatisfiesDirty, but without the "tuple
not found" case - I'll try to experiment with it.
GetLatestSnapshot is called before tuple lock anyway.

> I think it is better to document this race somewhere in a logical
> replication document for now unless we have a consensus on a way to
> move forward.

Yes, it is an option, but what documentation is going to be strange:

* there is delete_missing type of conflict stats\logs, but be aware it
may be wrong (actually it delete_missing)
* the same for update_missing vs update_origin_differs
* the same for update_deleted vs update_origin_differs
* also DELETE or UPDATE from publisher may be missed in case of update
on subscriber even if update touches subscriber-only columns

It looks like "if something is updating on subscriber - no
guarantees". And the worst thing - it is the actual state.

[0]: https://www.postgresql.org/message-id/flat/CAA4eK1LZxzORgAoDhix9MWrOqY=
OsNZuZLW2sTfGsJFM99yRgrg%40mail.gmail.com#02be86f7e2d24a038878f03ac1b93e95

Best regards,
MIkhail.