Re: delete/vacuum not freeing disk space - Mailing list pgsql-admin

On Sun, 14 Oct 2001, Marc Spitzer wrote:

> In article <20011012165439.S71408-100000@fw.mccons.net>, Brian McCane wrote:
> >
> > Being the perpetually lazy person that I am, I have written a PERL script
> > that takes care of indexes on a live system.  You tell the script the
> > database and table to re-index.  It then finds a list of indexes for the
> > table and creates exactly the same index, with a slightly modified name.
> > After the copy is made the old index is DROP'd, and the new one is
> > ALTER'd to the old name.  Unfortunately, this means that you must have
> > enough room for the new index plus the old at the same time, but I am
> > sure that most people have lots of spare room.  I seem to remember that
> > the last time I ran this, it didn't even stop my web sites from using the
> > tables at the same time.  I assume this is because of the versioning
> > system used in PostgreSQL.
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
> would you consider posting that script or emailing it to me?
> It would realy come in handy at work.
>
> Thanks
>
> marc
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

Short answer, sure no problem.

Long answer, not yet.

I am having a couple of inconsistencies with it, and I would prefer not to
release it to anyone else until I at least feel comfortable with my work.
No tool is often better than the wrong or a defective tool.  As soon as I
am sure it is not what caused a recent problem, I will make it available
either here or via FTP.

NOTE:  The problem was a duplicated key in a unique index on a BIG (at least
35Million+ records is big to me) table, which caused my script to fail
because it couldn't re-create the index.  I don't know how my script could
have caused it, but it was a table which I had previously run my script
on, so I am testing.  I did, however, write the script to "failsafe",
ie. failure to create the index just left the original index in place.

An interesting point was that the duplicated key was actually in the
table, but the existing unique index was happy as a clam.  I have often
encountered this with MySQL, but this is the first time with PostgreSQL.
I had to delete the record by OID, once I finally located it, then my
script worked fine.

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