Re: Broken index?

On 2001.09.07 15:53 Tom Lane wrote:
> 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.

Ahh yes, that seems to make sense.

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

I didn't actually compile it at all - I used the default RPMs provided by
Redhat. I'm unsure whether they have compiled in support or not, but I know
I always start/stop postgres using the init.d scripts, which always start
the database under the user postgres. As far as I'm aware, I've never
changed the locale of the system/db either. I just checked using 'locale'
to see how the system was set up under the usernames I use to log in to the
machine and they are all en_GB anyway. I also checked /etc/sysconfig/i18n
and that too was set to en_GB, so I think I'm safe on this front.

Any ideas?

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

If you are convinced that the above is not the problem, I will try to delay
the upgrade for now. If you think that it may still be a locale setting,
then I guess I will have to though :)

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

Yes, sorry, that is what I meant.

>
> > 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
>
--
Paul Green
Programmer

Jippii Midlands
85 London Road,
Leicester,
England,
LE2 0PF.
tel: 0116 2230662
fax: 0116 2221305

Please visit www.businessjippii.co.uk to view our latest Internet and
Telecommunication Products and Services. We offer it all!

Want to order a free dial up account online? Go to www.dialjippii.co.uk.

Want a quick and easy way to get a domain name? Order online at
www.domainsjippii.co.uk.

For the latest ringtones, logos and fun games go to the Jippii portal at
www.jippii.co.uk.

NOTICE: This e-mail is strictly confidential and is intended solely for
the person or organisation to whom it is addressed. It may contain
privileged and confidential information and if you are not the intended
recipient, you must not copy, distribute or take any action in reliance
on it. If you have received this communication in error, please advise
us by e-mail and delete the file from your system. Whilst all efforts
are made to safeguard Inbound and Outbound e-mails, Webleicester Ltd.,
its subsidiaries or associates cannot guarantee that attachments are
Virus-free or compatible with your system and does not accept any
liability in respect of viruses or computer problems experienced. Any
views expressed in this message are those of the individual sender,
except where specifically stated to be the view of Webleicester Ltd.,
its subsidiaries or associates.