On Thu, 18 Oct 2001, Tom Lane wrote:
> Brian McCane <bmccane@mccons.net> writes:
> > An interesting point was that the duplicated key was actually in the
> > table, but the existing unique index was happy as a clam.
>
> Hmm. What datatype is the key, and what PG version are you running?
>
> The only known cause of such problems at the moment is that if you
> have LOCALE support compiled in, then the correct sort ordering of
> textual datatypes depends on locale. Change the locale, and presto
> your index is out of order --- and therefore corrupt. (The btree
> algorithms do not cope at all well with out-of-order index data.
> Failing to find entries that are there would be a very common result.)
>
> Prior to 7.1 you could easily shoot yourself in the foot this way
> by starting the postmaster with different locale environment variables
> at different times. As of 7.1, we save the locale seen at initdb time
> and adopt that at every postmaster startup, so in theory this class of
> problems is gone in 7.1.
>
> If you've got an example that doesn't fit into this case then I'd
> like to know about it, especially if you still have the broken index
> available for examination ...
>
> regards, tom lane
>
Datatype was an int4, and I am running 7.1.3. However, I have been slowly
banging this thing along since 7.0.?, so it might have gotten there at any
point. Come to think of it though, I had to pg_dump to move up to 7.1, so
it must have happened since then. I do a nightly 'vacuumdb -z -a', and
the error never showed up there. I only speak locale "C" ;), and I don't
even build the database support for multi-byte.
Of course, since I use mirrored RAIDs (and a nightly offsite) for backups,
I no longer have an copy of the database that was in question. Plus, it
was app 2.1GB without it's indexes, so I wouldn't want to mail it to you
anyway :). My guess would be that it got screwed up by a system crash. I
had a couple of those a few weeks ago when I had a runaway program take my
machine down (not really a crash, after about 30seconds your keypress
would appear). I have since executed the programmer, so hopefully this
won't happen again.
- brian
Wm. Brian McCane | Life is full of doors that won't open
Search http://recall.maxbaud.net/ | when you knock, equally spaced amid those
Usenet http://freenews.maxbaud.net/ | that open when you don't want them to.
Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of Amber"