Re: Patch: Global Unique Index

Hi!

Sorry to bother - but is this patch used in IvorySQL?
Here:
https://www.ivorysql.org/docs/Global%20Unique%20Index/create_global_unique_index
According to syntax it definitely looks like this patch.
Thank you!

On Sat, Dec 3, 2022 at 3:05 AM David Zhang <david(dot)zhang(at)highgo(dot)ca> wrote:

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

--
Regards,
Nikita Malakhov
Postgres Professional
https://postgrespro.ru/