Re: GiST support for UUIDs

Hello,

This patch adds UUID support to the `btree_gist` extension. I've also
submitted it to the September commitfest (hopefully correctly!)

Many people are looking for this feature, e.g.:

    http://dba.stackexchange.com/questions/83604/optimizing-postgres-row-overlap-constraints-involving-uuids-and-gist
    http://stackoverflow.com/questions/22720130/how-to-use-uuid-with-postgresql-gist-index-type
    http://www.postgresql.org/message-id/CAH3i69njJ9AX1fzHwt6uoUzCMBqnaDBwhmAPhRQzzLWifb2WOA@mail.gmail.com
    http://www.postgresql.org/message-id/C59F2565-4753-4C83-BDCD-A0F9430B1638@datafax.com

As seen from those links, indexing UUIDs in a GiST is necessary when
including a UUID as part of an exclusion constraint. Here is my note
to the HACKERS list proposing this addition:

    http://www.postgresql.org/message-id/CA+renyVepHxTO1c7dFbVjP1GYMUc0-3qDNWPN30-noo5MPyaVQ@mail.gmail.com

As discussed there, my changes are restricted to `contrib/btree_gist`.
I pretty much did a copy/paste of the code for intervals, since like
UUIDs they are 16 bytes. In some cases I could simplify, since
sometimes intervals can be less than 16 bytes, but not UUIDs.

This patch also includes tests and new files to bump the extension
version from 1.1 to 1.2.

One possible wart is that because the pg_uuid_t struct is defined in
uuid.c and hence invisible here, I had to repeat its definition (so
that I could include it in the GiST key struct). Alternately I could
move the definition in the core code into uuid.h, but my goal was to
touch only code in contrib. Let me know if you'd rather I made the
struct definition public and I can make that change.

This is my first patch, so my apologies if anything is missing. I went
the guidelines and I think I have everything covered. :-)

Thanks!
Paul Jungwirth

On Thu, Jun 25, 2015 at 9:50 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Paul A Jungwirth <pj@illuminatedcomputing.com> writes:
>> On Thu, Jun 25, 2015 at 8:06 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> Paul A Jungwirth <pj@illuminatedcomputing.com> writes:
>>>> I'm interested in adding GiST support for the UUID column type
>>>> . . . . So I'm curious where this change would go?
>
>>> btree_gist, I'd think
>
>> Okay, thank you for your answer! I was worried about the effects of
>> having btree_gist depend on uuid-ossp. People won't have to say
>> `CREATE EXTENSION "uuid-ossp"` if they only want `btree_gist`, right?
>
> No, the uuid type exists in core.  It's only some creation functions that
> are in that extension.
>
>>> the overhead of an extension version bump will probably
>>> exceed the useful payload :-(
>
>> Sorry to put more work on your plate. :-) I'm trying to pick something
>> easy to get my feet wet.
>
> That work will be on your plate actually ;-).  Read the docs concerning
> creation of new extension versions, and perhaps look in our git history
> for previous commits that have upgraded contrib extensions.
>
>                         regards, tom lane