Re: [BUG?] check_exclusion_or_unique_constraint false negative

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’ll miss it as well.
> > A lock‑based solution (version in the v10) would require keeping 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/CAA4eK1LZxzORgAoDhix9MWrOqYOsNZuZLW2sTfGsJFM99yRgrg%40mail.gmail.com#02be86f7e2d24a038878f03ac1b93e95

Best regards,
MIkhail.