[HACKERS] UUID datatype GiST index support
Hi,
Hopefully someone can help me and point me in right direction :)
I have been looking for GiST support extension for UUID datatype... since I
could not find it... I wanted to write it myself.
I need it more for EXCLUSION constraint - than to use GIST index just on
UUID column...
i.e:
CREATE TABLE test_exclude
(
id serial NOT NULL,
guid uuid NOT NULL,
valid_period period NOT NULL,
CONSTRAINT test_excludepk PRIMARY KEY (id),
EXCLUDE USING gist (guid WITH =, valid_period WITH ) --for the same guid,
period must not overlap...
)
Has taken a look on btree_gist contrib source code... there are Gist support
functions for many datatypes, so I wanted to take the same pattern and
make it...
however, problem happend in first line of code :) (tough I am comming from
totally different world - .Net)
pattern is:
typedef struct
{
ADTdataType lower;
ADTdataType upper;
} datatypeKEY;
i.e. for Date:
typedef struct
{
DateADT lower;
DateADT upper;
} dateKEY;
So I guessed for uuid would be:
typedef struct
{
pg_uuid_t lower;
pg_uuid_t upper;
} uuidKEY;
because of in pg uuid.h says:
*In C, we use the name pg_uuid_t,
* to avoid conflicts with any uuid_t type that might be defined by the
system headers...
and there is:
/* opaque struct; defined in uuid.c */
typedef struct pg_uuid_t pg_uuid_t;
But compiler shows error: Field lower (and upper) has incopmplete
datatype
Succeded to avoid error with adding:
struct pg_uuid_t
{
unsigned char data[UUID_LEN];
}
but then getting errors in compare functions:
i.e.
static int
m4_uuidkey_cmp(const void *a, const void *b)
{
uuidKEY*ia = (uuidKEY *) (((Usrt *) a)-t);
uuidKEY*ib = (uuidKEY *) (((Usrt *) b)-t);
int res;
res = DatumGetInt32(DirectFunctionCall2(uuid_cmp, UUIDPGetDatum(ia-upper),
UUIDPGetDatum(ia-upper)));
if (res == 0)
return DatumGetInt32(DirectFunctionCall2(uuid_cmp, UUIDPGetDatum(ia-upper),
UUIDPGetDatum(ib-upper)));
return res;
}
Getting error: aggregate error used where an integer was expected!
It would be a lot appreciated if anyone could help me and suggest the best
way to make Gist support for UUID datatype...
Many thanks,
Misa
Re: [HACKERS] UUID datatype GiST index support
Hi!
On Mon, Aug 22, 2011 at 2:54 PM, Misa Simic misa.si...@gmail.com wrote:
static int
m4_uuidkey_cmp(const void *a, const void *b)
{
uuidKEY*ia = (uuidKEY *) (((Usrt *) a)-t);
uuidKEY*ib = (uuidKEY *) (((Usrt *) b)-t);
int res;
res = DatumGetInt32(DirectFunctionCall2(uuid_cmp, UUIDPGetDatum(ia-upper),
UUIDPGetDatum(ia-upper)));
if (res == 0)
return DatumGetInt32(DirectFunctionCall2(uuid_cmp,
UUIDPGetDatum(ia-upper), UUIDPGetDatum(ib-upper)));
return res;
}
Getting error: aggregate error used where an integer was expected!
Seems that you need the address-of operator before ia-upper and ia-lower
(likely one of operands should be ia-lower). UUIDPGetDatum except pointer
as an argument, i.e. UUIDPGetDatum(ia-upper).
It would be a lot appreciated if anyone could help me and suggest the best
way to make Gist support for UUID datatype...
I think you're on the right way. btree_gist is an extension which provides
GiST indexing of scalar datatype. UUID is one of them. So, the module you
are writing should be quite similar.
--
With best regards,
Alexander Korotkov.
Re: [HACKERS] UUID datatype GiST index support
Thanks Alexander,
'Scalar datatype' - given me a hint...
Looking further in btree_gist source, for inet datatype, which seems a bit
complexier then uuid... (char, char, char[16]) structure for inet, compared
to jut char[16] for uuid.
GiST pattern works with double datatype... and there is method
convert_network_to_scalar(Datum, Oid), whick converts an inet value - to
scalar value... and then all index compare functions are based on the
doubles which leads me to conclusion (maybe is wrong) if I can convert
UUID value to double it would make a job a lot easier... and pretty
straight forward...
Any suggestion, how to convert UUID (char[16]) to scalar?
looking into convert inet to scalar, what confuses me, even there is
char[16] for an ip address... code is:
if (ip_family
http://doxygen.postgresql.org/network_8c.html#a7dc77a7bc93b675d36eca352d589b314(ip)
== PGSQL_AF_INET
http://doxygen.postgresql.org/utils_2inet_8h.html#a8ba3e5fe500d587d3eb8699968450b18)
len = 4;
else
len = 5;
res = ip_family
http://doxygen.postgresql.org/network_8c.html#a7dc77a7bc93b675d36eca352d589b314(ip);
for (i = 0; i len; i++)
{
res *= 256;
res += ip_addr
http://doxygen.postgresql.org/network_8c.html#a54558e944989cddebdb93f2f6cd965a4(ip)[i];
}
return res;
takes just first 4, or 5 values from ipaddres even there is 16 - (decalred
as char[16])
Many thanks,
Misa
2011/8/22 Alexander Korotkov aekorot...@gmail.com
Hi!
On Mon, Aug 22, 2011 at 2:54 PM, Misa Simic misa.si...@gmail.com wrote:
static int
m4_uuidkey_cmp(const void *a, const void *b)
{
uuidKEY*ia = (uuidKEY *) (((Usrt *) a)-t);
uuidKEY*ib = (uuidKEY *) (((Usrt *) b)-t);
int res;
res = DatumGetInt32(DirectFunctionCall2(uuid_cmp,
UUIDPGetDatum(ia-upper), UUIDPGetDatum(ia-upper)));
if (res == 0)
return DatumGetInt32(DirectFunctionCall2(uuid_cmp,
UUIDPGetDatum(ia-upper), UUIDPGetDatum(ib-upper)));
return res;
}
Getting error: aggregate error used where an integer was expected!
Seems that you need the address-of operator before ia-upper and ia-lower
(likely one of operands should be ia-lower). UUIDPGetDatum except pointer
as an argument, i.e. UUIDPGetDatum(ia-upper).
It would be a lot appreciated if anyone could help me and suggest the best
way to make Gist support for UUID datatype...
I think you're on the right way. btree_gist is an extension which provides
GiST indexing of scalar datatype. UUID is one of them. So, the module you
are writing should be quite similar.
--
With best regards,
Alexander Korotkov.
