Re: Broken index?

Paul Green <traktion(at)webleicester(dot)co(dot)uk> writes:
>> 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

> 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.

This is entirely consistent with the theory of a broken index. The %bob
query does a sequential scan and looks at every row, but the other will
do an index lookup and return only the rows it finds via the index.

Did you compile 7.0 with locale support? If so, a plausible theory is
that you've started the postmaster with different locale settings at
different times --- a change in locale would alter the effective sort
order, rendering what had been a valid index corrupt. The btree search
and update algorithms fail completely if the data in the index is not
really in the correct order.

Unfortunately it is *very* easy to start the postmaster with
inconsistent locale environment, since what you have in your login
profile is often different from the environment that system boot scripts
run in.

7.1 contains fixes that force PG always to use the initdb-time locale
setting, so as to eliminate this class of problems. I'd suggest an
upgrade. Meanwhile, you need to take steps to eliminate the duplicate
rows from your table.

> 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.

Eh? Looks like the same thing to me. Perhaps you are thinking of
"select count(distinct id) from player"

> 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?

Once the index becomes corrupt, the behavior will be awfully
unpredictable...

regards, tom lane