Re: delete/vacuum not freeing disk space

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"