index help for uuid datatype

Started by Gevik Babakhaniover 19 years ago5 messages
#1Gevik Babakhani
pgdev@xs4all.nl

I am testing the uuid datatype with unique indexing.
I have the following script to generate a table with uuid types:

create table guid(
pk uuid primary key default new_guid(),
f1 varchar(38)
);

insert into guid(f1) values('bla bla');
insert into guid(f1) values('bla bla');

insert into guid(f1) select f1 from guid;

-- I repeat the insert above 10 times to get may records.

-- then save the generated guid into the varchar field
update guid set f1=pk;

-- check for duplicates in the varchar field
select f1,count(*) from guid group by f1 having(count(f1) > 1);

-- no duplicates there:

f1 | count
----+-------
(0 rows)

-- and here it comes.........
dev=# create unique index idx1 on guid using btree (pk);
ERROR: could not create unique index
DETAIL: Table contains duplicated values.

I must be doing something very wrong.....
Does anyone ever seen such a thing?

Regards,
Gevik.

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Gevik Babakhani (#1)
Re: index help for uuid datatype

Gevik Babakhani <pgdev@xs4all.nl> writes:

I must be doing something very wrong.....
Does anyone ever seen such a thing?

Your comparison functions for uuid are inconsistent.

regards, tom lane

#3Gevik Babakhani
pgdev@xs4all.nl
In reply to: Tom Lane (#2)
Re: index help for uuid datatype

Thank you Tom :)

I was doing strncmp at some point but it did not work because
of the '\0'. I have created a custom comparison function and it seems to
work. I am now inserting 6 million records to see if it will break again
and start the other tests from scratch.
Thank you for your help.

Show quoted text

On Sat, 2006-09-16 at 12:43 -0400, Tom Lane wrote:

Gevik Babakhani <pgdev@xs4all.nl> writes:

I must be doing something very wrong.....
Does anyone ever seen such a thing?

Your comparison functions for uuid are inconsistent.

regards, tom lane

#4Tom Lane
tgl@sss.pgh.pa.us
In reply to: Gevik Babakhani (#3)
Re: index help for uuid datatype

Gevik Babakhani <pgdev@xs4all.nl> writes:

I was doing strncmp at some point but it did not work because
of the '\0'. I have created a custom comparison function and it seems to
work.

Perhaps you just need memcmp instead of strncmp?

regards, tom lane

#5Gevik Babakhani
pgdev@xs4all.nl
In reply to: Tom Lane (#4)
Re: index help for uuid datatype

I followed your advice.... 6 million records are inserted without any
problems :)

Thank you.

Show quoted text

On Sat, 2006-09-16 at 14:03 -0400, Tom Lane wrote:

Gevik Babakhani <pgdev@xs4all.nl> writes:

I was doing strncmp at some point but it did not work because
of the '\0'. I have created a custom comparison function and it seems to
work.

Perhaps you just need memcmp instead of strncmp?

regards, tom lane