From: Warren Little <warren.little@meridiascapital.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-admin@postgresql.org
Subject: Re: questions on toast tables
Date: Sun, 30 Apr 2006 10:55:58 -0600
Message-ID: <1146416158.2425.0.camel@wjlnotebook> (raw)
In-Reply-To: <25284.1146336505@sss.pgh.pa.us>
References: <1146240597.9528.17.camel@wlittle.meridias.local>
<15655.1146254635@sss.pgh.pa.us>
<1146315175.4995.15.camel@wjlnotebook>
<1146321258.4995.22.camel@wjlnotebook>
<25284.1146336505@sss.pgh.pa.us>
Tom,
thanks much for your help, the cluster command did the trick.
fyi running 8.1.2
On Sat, 2006-04-29 at 14:48 -0400, Tom Lane wrote:
> Warren Little <warren.little@meridiascapital.com> writes:
> > Could this be the reference to the toast table that is preventing the
> > vacuum from deleting the toast data? And what purges "dropped" columns
> > if not a full vacuum.
>
> Actually, the way that toast references work is that they'll go away at
> the next update of the row containing the reference. The reason you've
> still got a pile of unremovable toast data is evidently that a lot of
> the parent table's rows have remained untouched since the wide bytea
> column existed. (We choose not to do this housekeeping immediately
> during DROP COLUMN, but to defer it until the next row update.)
>
> One way to clean up the junk would be to do a trivial full-table update
> ("UPDATE foo SET f1 = f1") and then VACUUM FULL, but there are other
> ways that are more efficient. If you're using a PG version released
> within the last year, CLUSTER will do the job nicely.
>
> regards, tom lane
--
Warren Little
Chief Technology Officer
Meridias Capital Inc
1018 W Atherton Dr
SLC, UT 84123
ph 866.369.7763
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: warren.little@meridiascapital.com, tgl@sss.pgh.pa.us
Subject: Re: questions on toast tables
In-Reply-To: <1146416158.2425.0.camel@wjlnotebook>
References: <1146240597.9528.17.camel@wlittle.meridias.local> <15655.1146254635@sss.pgh.pa.us> <1146315175.4995.15.camel@wjlnotebook> <1146321258.4995.22.camel@wjlnotebook> <25284.1146336505@sss.pgh.pa.us> <1146416158.2425.0.camel@wjlnotebook>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox