Обсуждение: 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
Hi!
On Mon, Aug 22, 2011 at 2:54 PM, Misa Simic <misa.simic@gmail.com> wrote:
------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).static intm4_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...
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.
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(ip) == PGSQL_AF_INET) len = 4; else len = 5; res = ip_family(ip); for (i = 0; i < len; i++) { res *= 256; res += ip_addr(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 <aekorotkov@gmail.com>
Hi!------On Mon, Aug 22, 2011 at 2:54 PM, Misa Simic <misa.simic@gmail.com> wrote: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).static intm4_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...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.