Thread: vacuumdb: vacuuming of database "Validation" failed: ERROR: failed to re-find parent key in index...
vacuumdb: vacuuming of database "Validation" failed: ERROR: failed to re-find parent key in index...
From
Carl Anderson
Date:
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
Re: vacuumdb: vacuuming of database "Validation" failed: ERROR: failed to re-find parent key in index...
From
Tom Lane
Date:
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