Re: Microvacuum support for Hash Index

>>> I have done some more testing with this, and have moved to the patch
>>> back to 'Needs Review' pending Amit's comments.
>>>
>>
>> Moved to "Ready for Committer".
>>
>
> 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.

Also, Attached are pgbench_bt.patch and pgbench_hash.patch files that
has changes done to create btree and hash index.

pgbench settings:
Scale Factor = 300
Shared Buffer= 1GB
Client counts = 64
run time duration = 30 mins
read-write workload.

./pgbench -c $threads -j $threads -T $time_for_reading -M prepared
postgres -f /home/ashu/deadlock_report

I hope this makes the things clear now and if there is no more
concerns it can be moved to 'Ready for committer' state. Thank you.

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