Message-ID: <6b69efba-5f4f-04a5-46b4-b92e38cbb69b@highgo.ca>
Date: Fri, 2 Dec 2022 16:05:08 -0800
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:102.0)
 Gecko/20100101 Thunderbird/102.5.1
Subject: Re: Patch: Global Unique Index
Content-Language: en-US
To: Tom Lane <tgl@sss.pgh.pa.us>, Bruce Momjian <bruce@momjian.us>
Cc: Greg Stark <stark@mit.edu>, Simon Riggs <simon.riggs@enterprisedb.com>,
 Cary Huang <cary.huang@highgo.ca>,
 Pgsql Hackers <pgsql-hackers@lists.postgresql.org>
References: <184879c5306.12490ea581628934.7312528450011769010@highgo.ca>
 <CANbhV-Eo4D2Njpa=YiBr7BxOMDi5B54wenjgoamRKjzj-9gBGQ@mail.gmail.com>
 <Y4D/3EbEibKkJW2C@momjian.us>
 <46a0f6fb-1c0b-bbd8-ef3e-366216a89501@highgo.ca>
 <CAM-w4HNFyX2upz6pyeWXew-p2sPXiSy2q3tn_Zfa4Xvtu+SLhw@mail.gmail.com>
 <1239139.1669763636@sss.pgh.pa.us> <Y4a5FRbhRpbJIeo5@momjian.us>
 <1257837.1669774583@sss.pgh.pa.us>
From: David Zhang <david.zhang@highgo.ca>
In-Reply-To: <1257837.1669774583@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/6b69efba-5f4f-04a5-46b4-b92e38cbb69b%40highgo.ca>
Precedence: bulk

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.