X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 55CC29F9DCD for ; Sun, 30 Apr 2006 13:56:14 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 36439-04 for ; Sun, 30 Apr 2006 13:56:00 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from exchange.meridiascapital.com (exchange.meridiascapital.com [65.90.55.78]) by postgresql.org (Postfix) with ESMTP id 679D79F9586 for ; Sun, 30 Apr 2006 13:55:59 -0300 (ADT) Received: from 192.168.19.130 ([192.168.19.130]) by atherton-ex-01.meridias.local ([192.168.8.78]) with Microsoft Exchange Server HTTP-DAV ; Sun, 30 Apr 2006 16:55:57 +0000 Received: from wjlnotebook by exchange.meridiascapital.com; 30 Apr 2006 10:55:59 -0600 Subject: Re: questions on toast tables From: Warren Little To: Tom Lane Cc: pgsql-admin@postgresql.org 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> Content-Type: text/plain Content-Transfer-Encoding: 7bit Date: Sun, 30 Apr 2006 10:55:58 -0600 Message-Id: <1146416158.2425.0.camel@wjlnotebook> Mime-Version: 1.0 X-Mailer: Evolution 2.6.1 (2.6.1-1.fc5.2) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200604/363 X-Sequence-Number: 21532 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 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