A bug with unique indicies

From: Aditya Damle <adamle(at)2028(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: A bug with unique indicies
Date: 2001-01-18 03:20:52
Message-ID: 3A666114.D9F4B2C5@2028.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message The Hermit Hacker 2001-01-18 04:55:42 Nothing larger then int8?
Previous Message Tom Lane 2001-01-18 01:44:27 Re: $PGDATA/base/???