Re: Broken index?

On 2001.09.07 15:10 Tom Lane wrote:
> Paul Green <traktion@webleicester.co.uk> writes:
> > I am having a problem with duplicates appearing in a unique index and I
> > have no idea why.
>
> Hmm.  What PG version is this, on what platform?

Redhat 7.1, (Linux 2.4.2-2) on a K6-2 300 with 128 mb ram running PG 7.0
using jdbc7.1-1.2.jar for databas access.

>
> Have you tried dropping and recreating the index?

I've tried creating a new table and selecting * (create table blah as
select * from player) and then creating an index on this, but it compains
about duplicates and fails. What *is* wierd is that if I do, say, "select *
from player where name='bob'" I get one result where as if I do a "select *
from player where name like '%bob', I get many bobs (about 10) returned.
Unless psql is removing white space and this is somehow creeping in
(although I 'trim()' all values before insertion) I can't understand how
this can be so. Also, if I do a "select count(id) from player" I get the
same number as "select distinct count(id) from player" even though they
should clearly be different.

Also, I've just tried to do a query to update all rows by "update player
set flatname=lower(name)" and that failed complaining about duplicates, yet
if you specify the id, the row is altered. Crazy huh?

>
> > I can't say how often these duplicates slip through, but it seems to
> only
> > happen *very* rarely, but is obviously completely mangling my data. Not
> > only that, but VACUUM ANALYZE is taking *days* to complete and I can
> only
> > assume this is due to these duplicates. The 'player' table contains
> around
> > 180000 records, so if this fall of in performance when running vacuum
> can
> > be associated with this, I'd appreciate some feedback.
>
> Clearly something broken here; that's not an especially large table...
>
>             regards, tom lane
>

Yeah, something is definately going a bit odd. I could recrease the
database starting from scratch (importing everything again etc), but if the
same situation occurs, I'll just have wasted a lot of time, so if you have
any ideas, I'd appreciate the feedback.

Thanks in advance,
Paul