Thread: vacuumdb: vacuuming of database "Validation" failed: ERROR: failed to re-find parent key in index...

I have a DB in constant autovacuum waiting mode:

1 S postgres 26262  4617  0  80   0 - 88927 semtim 10:19 ?        00:00:00 postgres: autovacuum worker process
Validationwaiting                                                                     

Running vacuumdb, with and without -f, I get output with final line:

vacuumdb: vacuuming of database "Validation" failed: ERROR:  failed to re-find parent key in index
"pg_shdepend_reference_index"for deletion target page 380 

I am using 8.3 on ubuntu hardy heron and in case this is relevant there are 253 schema in this DB (in case it is
possibleto identify and delete an errant schema?) 

Any insights into what caused this or advice about fixing the issue?

Thanks

Carl



The full output is:
root@datadyve1:~# vacuumdb -v Validation
INFO:  vacuuming "information_schema.sql_features"
INFO:  "sql_features": found 0 removable, 494 nonremovable row versions in 5 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
1 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.03 sec.
INFO:  vacuuming "pg_toast.pg_toast_11429"
INFO:  index "pg_toast_11429_index" now contains 0 row versions in 1 pages
DETAIL:  0 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.
INFO:  "pg_toast_11429": found 0 removable, 0 nonremovable row versions in 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.02 sec.
INFO:  vacuuming "information_schema.sql_implementation_info"
INFO:  "sql_implementation_info": found 0 removable, 12 nonremovable row versions in 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 1 unused item pointers.
1 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.02 sec.
INFO:  vacuuming "pg_toast.pg_toast_11434"
INFO:  index "pg_toast_11434_index" now contains 0 row versions in 1 pages
DETAIL:  0 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.
INFO:  "pg_toast_11434": found 0 removable, 0 nonremovable row versions in 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  vacuuming "pg_catalog.pg_statistic"
INFO:  scanned index "pg_statistic_relid_att_index" to remove 470 row versions
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.25 sec.
INFO:  "pg_statistic": removed 470 row versions in 49 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "pg_statistic_relid_att_index" now contains 22133 row versions in 248 pages
DETAIL:  470 index row versions were removed.
9 index pages have been deleted, 8 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_statistic": found 448 removable, 22133 nonremovable row versions in 1977 pages
DETAIL:  417 dead row versions cannot be removed yet.
There were 20584 unused item pointers.
1300 pages contain useful free space.
0 pages are entirely empty.
CPU 0.02s/0.03u sec elapsed 1.19 sec.
INFO:  vacuuming "pg_toast.pg_toast_2619"
INFO:  index "pg_toast_2619_index" now contains 1 row versions in 2 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  "pg_toast_2619": found 0 removable, 1 nonremovable row versions in 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 2 unused item pointers.
1 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.03 sec.
INFO:  vacuuming "information_schema.sql_languages"
INFO:  "sql_languages": found 0 removable, 4 nonremovable row versions in 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
1 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  vacuuming "pg_toast.pg_toast_11439"
INFO:  index "pg_toast_11439_index" now contains 0 row versions in 1 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  "pg_toast_11439": found 0 removable, 0 nonremovable row versions in 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.04 sec.
INFO:  vacuuming "information_schema.sql_packages"
INFO:  "sql_packages": found 0 removable, 10 nonremovable row versions in 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
1 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.02 sec.
INFO:  vacuuming "pg_toast.pg_toast_11444"
INFO:  index "pg_toast_11444_index" now contains 0 row versions in 1 pages
DETAIL:  0 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.
INFO:  "pg_toast_11444": found 0 removable, 0 nonremovable row versions in 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  vacuuming "information_schema.sql_parts"
INFO:  "sql_parts": found 0 removable, 9 nonremovable row versions in 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
1 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.02 sec.
INFO:  vacuuming "pg_toast.pg_toast_11449"
INFO:  index "pg_toast_11449_index" now contains 0 row versions in 1 pages
DETAIL:  0 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.
INFO:  "pg_toast_11449": found 0 removable, 0 nonremovable row versions in 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  vacuuming "information_schema.sql_sizing"
INFO:  "sql_sizing": found 0 removable, 23 nonremovable row versions in 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 9 unused item pointers.
1 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  vacuuming "pg_toast.pg_toast_11454"
INFO:  index "pg_toast_11454_index" now contains 0 row versions in 1 pages
DETAIL:  0 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.
INFO:  "pg_toast_11454": found 0 removable, 0 nonremovable row versions in 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  vacuuming "pg_catalog.pg_authid"
INFO:  scanned index "pg_authid_rolname_index" to remove 7 row versions
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  scanned index "pg_authid_oid_index" to remove 7 row versions
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_authid": removed 7 row versions in 1 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "pg_authid_rolname_index" now contains 44 row versions in 2 pages
DETAIL:  7 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.
INFO:  index "pg_authid_oid_index" now contains 44 row versions in 2 pages
DETAIL:  7 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.
INFO:  "pg_authid": found 3 removable, 44 nonremovable row versions in 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 1 unused item pointers.
1 pages contain useful free space.
0 pages are entirely empty.
CPU 0.01s/0.00u sec elapsed 0.00 sec.
INFO:  vacuuming "pg_toast.pg_toast_1260"
INFO:  index "pg_toast_1260_index" now contains 0 row versions in 1 pages
DETAIL:  0 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.
INFO:  "pg_toast_1260": found 0 removable, 0 nonremovable row versions in 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  vacuuming "information_schema.sql_sizing_profiles"
INFO:  "sql_sizing_profiles": found 0 removable, 0 nonremovable row versions in 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  vacuuming "pg_toast.pg_toast_11459"
INFO:  index "pg_toast_11459_index" now contains 0 row versions in 1 pages
DETAIL:  0 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.
INFO:  "pg_toast_11459": found 0 removable, 0 nonremovable row versions in 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  vacuuming "pg_catalog.pg_database"
INFO:  scanned index "pg_database_datname_index" to remove 27 row versions
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  scanned index "pg_database_oid_index" to remove 27 row versions
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_database": removed 27 row versions in 1 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "pg_database_datname_index" now contains 17 row versions in 2 pages
DETAIL:  27 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.
INFO:  index "pg_database_oid_index" now contains 17 row versions in 2 pages
DETAIL:  27 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.
INFO:  "pg_database": found 32 removable, 17 nonremovable row versions in 1 pages
DETAIL:  2 dead row versions cannot be removed yet.
There were 5 unused item pointers.
1 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  vacuuming "pg_toast.pg_toast_1262"
INFO:  index "pg_toast_1262_index" now contains 0 row versions in 1 pages
DETAIL:  0 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.
INFO:  "pg_toast_1262": found 0 removable, 0 nonremovable row versions in 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  vacuuming "pg_catalog.pg_tablespace"
INFO:  index "pg_tablespace_oid_index" now contains 2 row versions in 2 pages
DETAIL:  0 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.
INFO:  index "pg_tablespace_spcname_index" now contains 2 row versions in 2 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  "pg_tablespace": found 0 removable, 2 nonremovable row versions in 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
1 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.03 sec.
INFO:  vacuuming "pg_catalog.pg_pltemplate"
INFO:  index "pg_pltemplate_name_index" now contains 6 row versions in 2 pages
DETAIL:  0 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.
INFO:  "pg_pltemplate": found 0 removable, 6 nonremovable row versions in 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
1 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.02 sec.
INFO:  vacuuming "pg_catalog.pg_shdepend"
INFO:  scanned index "pg_shdepend_depender_index" to remove 287557 row versions
DETAIL:  CPU 0.00s/0.09u sec elapsed 0.09 sec.
vacuumdb: vacuuming of database "Validation" failed: ERROR:  failed to re-find parent key in index
"pg_shdepend_reference_index"for deletion target page 380 




Carl Anderson <carl.anderson@archimedesmodel.com> writes:
> Running vacuumdb, with and without -f, I get output with final line:

> vacuumdb: vacuuming of database "Validation" failed: ERROR:  failed to re-find parent key in index
"pg_shdepend_reference_index"for deletion target page 380 

You should be able to fix that with REINDEX.  However, since it's a
shared catalog index, you'll need to do it in standalone mode.
See the REINDEX and "postgres" reference pages.

            regards, tom lane