Re: [BUG?] check_exclusion_or_unique_constraint false negative

Hello, Amit!

> 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.

Peter answered in [0]:
> I don’t remember. I was just the committer.

I’ve attached a new version of the proposed solution.
The first commit includes tests, some README updates, and an
additional pgbench test that reproduces the issue without explicitly
simulating the wait/resume race. This last test is heavy and isn't
intended to be committed.

Instead of adding extra locking in btree, a more lightweight approach
is used: since we already call GetLatestSnapshot before
table_tuple_lock, we can simply call it before each scan attempt and
use that snapshot for the scan.

As a result:
* MVCC scan will not miss updated tuples, while DirtyScan may
* in both cases, table_tuple_lock will wait for the updating
transaction to commit before retrying
* MVCC scan cannot see not-yet-committed new rows, while DirtyScan
can. However, this does not provide any stronger guarantee: in the
case of INSERT vs INSERT, two parallel inserts are still possible.
DirtyScan only slightly reduces the probability, but if the scan does
not find the row, there is still no guarantee that it won’t be
inserted immediately afterward.

Therefore, the MVCC version appears to provide the same guarantees,
without missing tuples, and with the same performance.

Best regards,
Mikhail.

[0]:
https://discord.com/channels/1258108670710124574/1407753138991009913/1411303541900841090