Re: WIP: unique hash indexes

Attached is a patch that implements unique hash indexes. Regression
tests and docs have been updated (with the exception of
src/backend/access/hash/README).

This patches implements an improved version of the concurrency changes I
outlined on -hackers a couple days ago: the separate lock for
"right-of-insertion" into a bucket chain has been replaced by making use
of existing lmgr lockmodes to get the behavior we need.

I also took the opportunity to refactor the hash index code in a couple
places by moving duplicated code into separate functions, fixed some
typos in the README file, etc.

I've tested this a little bit (it survives 100,000 single-user pgbench
transactions, and 10 concurrent clients doing 1000 transactions each),
but I need to do more testing in the future.

As far as I know, the unique hash index code is functionally complete,
but don't add this to the patch queue (it is obviously for 7.5, anyway),
since I'm about to get started on some related hash index work, for
which I'll submit a mega-patch containing everything. I just posted this
here so that anyone who's interested can take a look at it -- comments
are welcome.

Thanks to Tom Lane, who was very helpful when I asked him code
questions, including suggesting the improved concurrency design I
mentioned above.

Cheers,

Neil

Your patch has been added to the PostgreSQL unapplied patches list at:

http://momjian.postgresql.org/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

---------------------------------------------------------------------------

Neil Conway wrote:
> Attached is a patch that implements unique hash indexes. Regression
> tests and docs have been updated (with the exception of
> src/backend/access/hash/README).
>
> This patches implements an improved version of the concurrency changes I
> outlined on -hackers a couple days ago: the separate lock for
> "right-of-insertion" into a bucket chain has been replaced by making use
> of existing lmgr lockmodes to get the behavior we need.
>
> I also took the opportunity to refactor the hash index code in a couple
> places by moving duplicated code into separate functions, fixed some
> typos in the README file, etc.
>
> I've tested this a little bit (it survives 100,000 single-user pgbench
> transactions, and 10 concurrent clients doing 1000 transactions each),
> but I need to do more testing in the future.
>
> As far as I know, the unique hash index code is functionally complete,
> but don't add this to the patch queue (it is obviously for 7.5, anyway),
> since I'm about to get started on some related hash index work, for
> which I'll submit a mega-patch containing everything. I just posted this
> here so that anyone who's interested can take a look at it -- comments
> are welcome.
>
> Thanks to Tom Lane, who was very helpful when I asked him code
> questions, including suggesting the improved concurrency design I
> mentioned above.
>
> Cheers,
>
> Neil
>

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> I will try to apply it within the next 48 hours.

My impression was that Neil didn't think this was ready yet:

>> As far as I know, the unique hash index code is functionally complete,
>> but don't add this to the patch queue (it is obviously for 7.5, anyway),
>> since I'm about to get started on some related hash index work, for
>> which I'll submit a mega-patch containing everything.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > I will try to apply it within the next 48 hours.
>
> My impression was that Neil didn't think this was ready yet:
>
> >> As far as I know, the unique hash index code is functionally complete,
> >> but don't add this to the patch queue (it is obviously for 7.5, anyway),
> >> since I'm about to get started on some related hash index work, for
> >> which I'll submit a mega-patch containing everything.

Oops, sorry.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Patch removed.

---------------------------------------------------------------------------

Neil Conway wrote:
> Attached is a patch that implements unique hash indexes. Regression
> tests and docs have been updated (with the exception of
> src/backend/access/hash/README).
>
> This patches implements an improved version of the concurrency changes I
> outlined on -hackers a couple days ago: the separate lock for
> "right-of-insertion" into a bucket chain has been replaced by making use
> of existing lmgr lockmodes to get the behavior we need.
>
> I also took the opportunity to refactor the hash index code in a couple
> places by moving duplicated code into separate functions, fixed some
> typos in the README file, etc.
>
> I've tested this a little bit (it survives 100,000 single-user pgbench
> transactions, and 10 concurrent clients doing 1000 transactions each),
> but I need to do more testing in the future.
>
> As far as I know, the unique hash index code is functionally complete,
> but don't add this to the patch queue (it is obviously for 7.5, anyway),
> since I'm about to get started on some related hash index work, for
> which I'll submit a mega-patch containing everything. I just posted this
> here so that anyone who's interested can take a look at it -- comments
> are welcome.
>
> Thanks to Tom Lane, who was very helpful when I asked him code
> questions, including suggesting the improved concurrency design I
> mentioned above.
>
> Cheers,
>
> Neil
>

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073