Re: Patch: Global Unique Index - Mailing list pgsql-hackers

On 2022-11-29 6:16 p.m., Tom Lane wrote:
> Assuming that you are inserting into index X, and you've checked
> index Y to find that it has no conflicts, what prevents another
> backend from inserting a conflict into index Y just after you look?
> AIUI the idea is to prevent that by continuing to hold an exclusive
> lock on the whole index Y until you've completed the insertion.
> Perhaps there's a better way to do that, but it's not what was
> described.
Another main change in patch
`0004-support-global-unique-index-insert-and-update.patch`,
+                search_global:
+                        stack = _bt_search(iRel, insertstate.itup_key,
+                                           &insertstate.buf, BT_READ,
NULL);
+                        xwait = _bt_check_unique_gi(iRel, &insertstate,
+                                                    hRel, checkUnique,
&is_unique,
+ &speculativeToken, heapRel);
+                        if (unlikely(TransactionIdIsValid(xwait)))
+                        {
... ...
+                            goto search_global;
+                        }

Here, I am trying to use `BT_READ` to require a LW_SHARED lock on the
buffer block if a match found using `itup_key` search key. The
cross-partition uniqueness checking will wait if the index tuple
insertion on this buffer block has not done yet, otherwise runs the
uniqueness check to see if there is an ongoing transaction which may
insert a conflict value. Once the ongoing insertion is done, it will go
back and check again (I think it can also handle the case that a
potential conflict index tuple was later marked as dead in the same
transaction). Based on this change, my test results are:

1) a select-only query will not be blocked by the ongoing insertion on
index X

2) insertion happening on index Y may wait for the buffer block lock
when inserting a different value but it does not wait for the
transaction lock held by insertion on index X.

3) when an insertion inserting a conflict value on index Y,
     3.1) it waits for buffer block lock if the lock has been held by
the insertion on index X.
     3.2) then, it waits for transaction lock until the insertion on
index X is done.