Re: questions on toast tables - Mailing list pgsql-admin

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 Office
Meridias Capital Inc
1018 W Atherton Dr
SLC, UT 84123
ph 866.369.7763