Re: delete/vacuum not freeing disk space

From: Brian McCane <bmccane(at)mccons(dot)net>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-admin(at)postgresql(dot)org>
Subject: Re: delete/vacuum not freeing disk space
Date: 2001-10-19 00:50:07
Message-ID: 20011018194153.Q15228-100000@fw.mccons.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

On Thu, 18 Oct 2001, Tom Lane wrote:

> Brian McCane <bmccane(at)mccons(dot)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"

In response to

Browse pgsql-admin by date

  From Date Subject
Next Message Brian McCane 2001-10-19 01:07:32 Re: Please help - tks
Previous Message Josh Berkus 2001-10-18 22:09:54 Re: Doing a regexp-based search/replace?