Re: Microvacuum support for Hash Index

Hi,

Attached is the v6 patch for microvacuum in hash index rebased on top
of 'v10 patch for WAL in hash index - [1]' and 'v1 patch for WAL
consistency check for hash index - [2]'.

[1] - https://www.postgresql.org/message-id/CAA4eK1%2Bk5wR4-kAjPqLoKemuHayQd6RkQQT9gheTfpn%2B72o1UA%40mail.gmail.com
[2] - https://www.postgresql.org/message-id/flat/CAGz5QCJLERUn_zoO0eDv6_Y_d0o4tNTMPeR7ivTLBg4rUrJdwg(at)mail(dot)gmail(dot)com#CAGz5QCJLERUn_zoO0eDv6_Y_d0o4tNTMPeR7ivTLBg4rUrJdwg@mail.gmail.com

Also, the patch (mask_hint_bit_LH_PAGE_HAS_DEAD_TUPLES.patch) to mask
'LH_PAGE_HAS_DEAD_TUPLES' flag which got added as a part of
Microvacuum patch is attached with this mail.

--
With Regards,
Ashutosh Sharma
EnterpriseDB:http://www.enterprisedb.com

On Wed, Feb 1, 2017 at 10:30 AM, Michael Paquier
<michael(dot)paquier(at)gmail(dot)com> wrote:
> On Sat, Jan 28, 2017 at 8:02 PM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>> On Fri, Jan 27, 2017 at 5:15 PM, Ashutosh Sharma <ashu(dot)coek88(at)gmail(dot)com> wrote:
>>>>
>>>> Don't you think we should try to identify the reason of the deadlock
>>>> error reported by you up thread [1]? I know that you and Ashutosh are
>>>> not able to reproduce it, but still I feel some investigation is
>>>> required to find the reason. It is quite possible that the test case
>>>> is such that the deadlock is expected in rare cases, if that is the
>>>> case then it is okay. I have not spent enough time on that to comment
>>>> whether it is a test or code issue.
>>>
>>> I am finally able to reproduce the issue using the attached script
>>> file (deadlock_report). Basically, once I was able to reproduce the
>>> issue with hash index I also thought of checking it with a non unique
>>> B-Tree index and was able to reproduce it with B-Tree index as well.
>>> This certainly tells us that there is nothing wrong at the code level
>>> rather there is something wrong with the test script which is causing
>>> this deadlock issue. Well, I have ran pgbench with two different
>>> configurations and my observations are as follows:
>>>
>>> 1) With Primary keys i.e. uinque values: I have never encountered
>>> deadlock issue with this configuration.
>>>
>>> 2) With non unique indexes (be it hash or B-Tree): I have seen
>>> deadlock many a times with this configuration. Basically when we have
>>> non-unique values associated with a column then there is a high
>>> probability that multiple records will get updated with a single
>>> 'UPDATE' statement and when there are huge number of backends trying
>>> to do so there is high chance of getting deadlock which i assume is
>>> expected behaviour in database.
>>>
>>
>> I agree with your analysis, surely trying to update multiple rows with
>> same values from different backends can lead to deadlock.
>
> Moved that to CF 2017-03.
> --
> Michael