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 7C82911F6031 for ; Sat, 29 Apr 2006 11:34:34 -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 53832-06 for ; Sat, 29 Apr 2006 11:34:23 -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 96B7B11F602F for ; Sat, 29 Apr 2006 11:34:20 -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 ; Sat, 29 Apr 2006 14:34:18 +0000 Received: from wjlnotebook by exchange.meridiascapital.com; 29 Apr 2006 08:34:18 -0600 Subject: Re: questions on toast tables From: Warren Little To: Tom Lane Cc: pgsql-admin@postgresql.org In-Reply-To: <1146315175.4995.15.camel@wjlnotebook> References: <1146240597.9528.17.camel@wlittle.meridias.local> <15655.1146254635@sss.pgh.pa.us> <1146315175.4995.15.camel@wjlnotebook> Content-Type: text/plain Content-Transfer-Encoding: 7bit Date: Sat, 29 Apr 2006 08:34:18 -0600 Message-Id: <1146321258.4995.22.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/351 X-Sequence-Number: 21520 selecting pg_attributes for the casedocument table shows there is column with the following attributes 24216115|"........pg.dropped.2........"|0|0|-1|2|0|-1|-1|f|"x"|"i"|f|f| t|t| I also noticed a few other tables have a similar "dropped" column. 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. On Sat, 2006-04-29 at 06:52 -0600, Warren Little wrote: > I am now a little confused. > > I ran the following with all but localhost connections disabled > > vacuumdb --full --verbose -t casedocument -d tigris > vacfull.log 2>&1 > > which produced the following output: > > INFO: vacuuming "public.casedocument" > INFO: "casedocument": found 0 removable, 39663 nonremovable row > versions in 852 pages > DETAIL: 0 dead row versions cannot be removed yet. > Nonremovable row versions range from 138 to 1953 bytes long. > There were 1855 unused item pointers. > Total free space (including removable row versions) is 65168 bytes. > 0 pages are or will become empty, including 0 at the end of the table. > 90 pages containing 14644 free bytes are potential move destinations. > CPU 0.02s/0.00u sec elapsed 0.24 sec. > INFO: index "copycasedoc_pkey" now contains 39663 row versions in 387 > pages > DETAIL: 0 index row versions were removed. > 0 index pages have been deleted, 0 are currently reusable. > CPU 0.01s/0.00u sec elapsed 0.56 sec. > INFO: "casedocument": moved 0 row versions, truncated 852 to 852 pages > DETAIL: CPU 0.00s/0.01u sec elapsed 0.03 sec. > INFO: vacuuming "pg_toast.pg_toast_24216115" > INFO: "pg_toast_24216115": found 0 removable, 23125332 nonremovable row > versions in 5781284 pages > DETAIL: 0 dead row versions cannot be removed yet. > Nonremovable row versions range from 45 to 2030 bytes long. > There were 1 unused item pointers. > Total free space (including removable row versions) is 185874460 bytes. > 0 pages are or will become empty, including 0 at the end of the table. > 24019 pages containing 24668944 free bytes are potential move > destinations. > CPU 217.21s/16.97u sec elapsed 1745.04 sec. > INFO: index "pg_toast_24216115_index" now contains 23125332 row > versions in 94046 pages > DETAIL: 0 index row versions were removed. > 1427 index pages have been deleted, 1427 are currently reusable. > CPU 4.60s/1.05u sec elapsed 49.34 sec. > INFO: "pg_toast_24216115": moved 0 row versions, truncated 5781284 to > 5781284 pages > DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. > VACUUM > > Not sure if I'm reading the output correctly, but it appears no rows in > the toast table were removed. > What else could be holding onto the data in "pg_toast_24216115" ??? > > > > > > > > On Fri, 2006-04-28 at 16:03 -0400, Tom Lane wrote: > > Warren Little writes: > > > 3) I know that once upon a time the table had a bytea column, but that > > > was dropped. Do I need to do a full vacuum on that table to get rid of > > > the related toast data? > > > > Yup. (I take it it was a pretty darn bulky bytea column, too) > > > > regards, tom lane -- Warren Little Chief Technology Officer Meridias Capital Inc 1018 W Atherton Dr SLC, UT 84123 ph 866.369.7763