Thread: A bug with unique indicies

Hello. Hopefully, this is the right mailing list to send
this type of question too.

System:
I am running the newest 7.03 build on a dual 866 Pentium III
with a 128M raid card.

I have found an error that is quite odd.
I have a table that is supposed to keep a map between
urls and ids. Each url in the table should be unique.
Thus I have

Create table "urlmap" (   "url" text not NULL,   "id" int4 not NULL,   PRIMARY KEY ("url"),   UNIQUE ("id","url") );

After inserting a number of urls (via spidering) i did the following.

I vacuumed the db : vacuum verbose analyze.

First:
select * from urlmap where url='blah blah';

Here I got back only one row. Good.

Then i went ahead reindexed the table: I recieved the error:
Cannot create unique index. Table contains non-unique values.

Same problem occurs if I drop the indicies and try to recreate them.

I then :
select * from urlmap u1,urlmap u2 where u1.url=u2.url and u1.oid!=u2.oid

I then got back two rows where the url was indeed the same and the
associated id
different. Why, would this ever occur?

-Aditya

Aditya Damle <adamle@2028.com> writes:
> Then i went ahead reindexed the table: I recieved the error:
> Cannot create unique index. Table contains non-unique values.

Curious.  When you say "reindexed" do you mean you used the REINDEX
command?  That's new in 7.0.* and possibly not fully debugged.  It
would be useful to see a complete script for reproducing the problem,
if you think you can regenerate it from scratch ...
        regards, tom lane