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.zh...@highgo.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/