Re: Hash Indexes

On Fri, Nov 18, 2016 at 12:11 PM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> On Thu, Nov 17, 2016 at 10:54 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> On Thu, Nov 17, 2016 at 12:05 PM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>>
>>>> I think this comment is saying that we'll release the pin on the
>>>> primary bucket page for now, and then reacquire it later if the user
>>>> reverses the scan direction. But that doesn't sound very safe,
>>>> because the bucket could be split in the meantime and the order in
>>>> which tuples are returned could change. I think we want that to
>>>> remain stable within a single query execution.
>>>
>>> Isn't that possible even without the patch? Basically, after reaching
>>> end of forward scan and for doing backward *all* scan, we need to
>>> perform portal rewind which will in turn call hashrescan where we will
>>> drop the lock on bucket and then again when we try to move cursor
>>> forward we acquire lock in _hash_first(), so in between when we don't
>>> have the lock, the split could happen and next scan results could
>>> differ.
>>
>> Well, the existing code doesn't drop the heavyweight lock at that
>> location, but your patch does drop the pin that serves the same
>> function, so I feel like there must be some difference.
>>
>
> Yes, but I am not sure if existing code is right. Consider below scenario,
>
> Session-1
>
> Begin;
> Declare c cursor for select * from t4 where c1=1;
> Fetch forward all from c; --here shared heavy-weight lock count becomes 1
> Fetch prior from c; --here shared heavy-weight lock count becomes 2
> close c; -- here, lock release will reduce the lock count and shared
> heavy-weight lock count becomes 1
>
> Now, if we try to insert from another session, such that it leads to
> bucket-split of the bucket for which session-1 had used a cursor, it
> will wait for session-1.
>

It will not wait, but just skip the split because we are using try
lock, however, the point remains that select should not hold bucket
level locks even after the cursor is closed.

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com