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: Sat, 29 Apr 2006 08:34:18 -0600
Message-ID: <1146321258.4995.22.camel@wjlnotebook> (raw)
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>
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 <warren.little@meridiascapital.com> 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
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: <1146321258.4995.22.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>
* 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