Re: Microvacuum support for Hash Index

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.
>>
>>
>> [1] - https://www.postgresql.org/message-id/dc6d7247-050f-4014-8c80-a4ee676eb384%40redhat.com
>>
>
> 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.

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