Re: Error when clustering a table

Strange - seems to have sorted itself out.

I turned off a scheduled vacuum of the table and within a few minutes I
sorted itself out.

I have reduced the frequency of the vacuuming task which is being
carried out via pgagent.

From: Benjamin Krajmalnik
Sent: Monday, December 14, 2009 12:28 PM
To: Benjamin Krajmalnik; 'pgsql-admin(at)postgresql(dot)org'
Subject: RE: Error when clustering a table

Additional info:

When I perform a vacuum full, this is the information I am getting:

INFO: vacuuming "public.tblksaura"

INFO: "tblksaura": found 89 removable, 238907 nonremovable row versions
in 59808 pages

DETAIL: 200792 dead row versions cannot be removed yet.

Nonremovable row versions range from 1776 to 2032 bytes long.

There were 391587 unused item pointers.

Total free space (including removable row versions) is 19992676 bytes.

3 pages are or will become empty, including 0 at the end of the table.

616 pages containing 945300 free bytes are potential move destinations.

CPU 0.00s/0.22u sec elapsed 0.23 sec.

INFO: index "tblksaura_kstestssysid_key" now contains 58562 row
versions in 211 pages

DETAIL: 13 index row versions were removed.

0 index pages have been deleted, 0 are currently reusable.

CPU 0.00s/0.01u sec elapsed 0.01 sec.

WARNING: index "tblksaura_kstestssysid_key" contains 58562 row
versions, but table contains 229083 row versions

HINT: Rebuild the index with REINDEX.

INFO: index "tblksaura_pkey" now contains 58562 row versions in 211
pages

DETAIL: 13 index row versions were removed.

0 index pages have been deleted, 0 are currently reusable.

CPU 0.00s/0.01u sec elapsed 0.01 sec.

WARNING: index "tblksaura_pkey" contains 58562 row versions, but table
contains 229083 row versions

HINT: Rebuild the index with REINDEX.

INFO: index "tblksaura_idx_time" now contains 58562 row versions in 183
pages

DETAIL: 13 index row versions were removed.

0 index pages have been deleted, 0 are currently reusable.

CPU 0.00s/0.01u sec elapsed 0.01 sec.

WARNING: index "tblksaura_idx_time" contains 58562 row versions, but
table contains 229083 row versions

HINT: Rebuild the index with REINDEX.

INFO: index "tblksaura_idx_kstestssysid" now contains 58562 row
versions in 298 pages

DETAIL: 13 index row versions were removed.

0 index pages have been deleted, 0 are currently reusable.

CPU 0.00s/0.01u sec elapsed 0.01 sec.

WARNING: index "tblksaura_idx_kstestssysid" contains 58562 row
versions, but table contains 229083 row versions

HINT: Rebuild the index with REINDEX.INFO: "tblksaura": moved 168 row
versions, truncated 59808 to 59803 pages

DETAIL: CPU 0.00s/0.13u sec elapsed 0.13 sec.INFO: index
"tblksaura_kstestssysid_key" now contains 58714 row versions in 212
pages

DETAIL: 16 index row versions were removed.

0 index pages have been deleted, 0 are currently reusable.

CPU 0.00s/0.00u sec elapsed 0.00 sec.

WARNING: index "tblksaura_kstestssysid_key" contains 58714 row
versions, but table contains 229235 row versions

HINT: Rebuild the index with REINDEX.

INFO: index "tblksaura_pkey" now contains 58714 row versions in 212
pages

DETAIL: 16 index row versions were removed.

0 index pages have been deleted, 0 are currently reusable.

CPU 0.00s/0.00u sec elapsed 0.00 sec.

WARNING: index "tblksaura_pkey" contains 58714 row versions, but table
contains 229235 row versions

HINT: Rebuild the index with REINDEX.

INFO: index "tblksaura_idx_time" now contains 58714 row versions in 183
pages

DETAIL: 16 index row versions were removed.

0 index pages have been deleted, 0 are currently reusable.

CPU 0.00s/0.00u sec elapsed 0.00 sec.

WARNING: index "tblksaura_idx_time" contains 58714 row versions, but
table contains 229235 row versions

HINT: Rebuild the index with REINDEX.

INFO: index "tblksaura_idx_kstestssysid" now contains 58714 row
versions in 299 pages

DETAIL: 16 index row versions were removed.

0 index pages have been deleted, 0 are currently reusable.

CPU 0.00s/0.00u sec elapsed 0.00 sec.

WARNING: index "tblksaura_idx_kstestssysid" contains 58714 row
versions, but table contains 229235 row versions

HINT: Rebuild the index with REINDEX.INFO: vacuuming
"pg_toast.pg_toast_145099"INFO: "pg_toast_145099": found 245 removable,
117973 nonremovable row versions in 4479 pages

DETAIL: 101666 dead row versions cannot be removed yet.

Nonremovable row versions range from 178 to 312 bytes long.

There were 31907 unused item pointers.

Total free space (including removable row versions) is 726932 bytes.

0 pages are or will become empty, including 0 at the end of the table.

1411 pages containing 481984 free bytes are potential move destinations.

CPU 0.00s/0.02u sec elapsed 0.02 sec.INFO: index
"pg_toast_145099_index" now contains 117973 row versions in 342 pages

DETAIL: 245 index row versions were removed.

0 index pages have been deleted, 0 are currently reusable.

CPU 0.00s/0.01u sec elapsed 0.01 sec.INFO: "pg_toast_145099": moved 495
row versions, truncated 4479 to 4457 pages

DETAIL: CPU 0.00s/0.02u sec elapsed 0.02 sec.

INFO: index "pg_toast_145099_index" now contains 117973 row versions in
343 pages

DETAIL: 495 index row versions were removed.

0 index pages have been deleted, 0 are currently reusable.

CPU 0.00s/0.00u sec elapsed 0.00 sec.Total query runtime: 719 ms.

What can be causing such a high number of rows to be nonremovable?

From: Benjamin Krajmalnik
Sent: Monday, December 14, 2009 12:17 PM
To: pgsql-admin(at)postgresql(dot)org
Subject: Error when clustering a table

I am trying to cluster a table for which vacuum full is not reducing its
size.

When I do so, I am getting the following error:

ERROR: missing chunk number 0 for toast value 207869115 in
pg_toast_145099

********** Error **********

ERROR: missing chunk number 0 for toast value 207869115 in
pg_toast_145099

SQL state: XX000

Any idea how this can be remediated?