Skip site navigation (1) Skip section navigation (2)

How to reindex when unable to open relation?

From: Darren Reed <darrenr+postgres(at)fastmail(dot)net>
To: pgsql-admin(at)postgresql(dot)org
Subject: How to reindex when unable to open relation?
Date: 2008-02-26 06:13:51
Message-ID: 47C3AE1F.2010804@fastmail.net (view raw or flat)
Thread:
Lists: pgsql-admin
Inserts into my table started generating an error message (see below),
and there was a log message that suggested I reindex.

postgres seems to start and stop ok, but no operations on the tables 
seem to work.

l# /usr/pkg/bin/pg_dump -s -U postgres
pg_dump: failed sanity check, parent table OID 16401 of pg_rewrite entry 
OID 16403 not found

Is this recoverable without using a backup?

Darren

ERROR:  could not open relation with OID 16399
STATEMENT:   INSERT INTO table (cola,colb,colc,cold,cole) VALUES 
(1,'fred',2,'john',true);
LOG:  unexpected EOF on client connection
ERROR:  could not open relation with OID 16399
STATEMENT:   SELECT * FROM table WHERE (ignore IS NOT TRUE) AND (region 
= 'usa') ORDER BY date DESC LIMIT 300 OFFSET 0;
ERROR:  index "pg_attrdef_adrelid_adnum_index" contains unexpected zero 
page at block 1
HINT:  Please REINDEX it.
STATEMENT:  LOCK TABLE public.ifl IN ACCESS SHARE MODE
FATAL:  database "posrgres" does not exist
ERROR:  could not open relation with OID 16399
STATEMENT:  SELECT COUNT(*) FROM table;
ERROR:  syntax error at or near ";" at character 9
STATEMENT:  REINDEX
       ;
ERROR:  syntax error at or near "table" at character 9
STATEMENT:  REINDEX table;
ERROR:  syntax error at or near "help" at character 1
STATEMENT:  help REINDEX;
ERROR:  could not open relation with OID 16399
STATEMENT:  REINDEX TABLE table;
ERROR:  syntax error at or near ";" at character 17
STATEMENT:  REINDEX DATABASE;
NOTICE:  table "pg_class" was reindexed
NOTICE:  table "sql_sizing" was reindexed
NOTICE:  table "sql_sizing_profiles" was reindexed
NOTICE:  table "sql_features" was reindexed
NOTICE:  table "sql_implementation_info" was reindexed
NOTICE:  table "sql_languages" was reindexed
NOTICE:  table "sql_packages" was reindexed
NOTICE:  table "sql_parts" was reindexed
NOTICE:  table "pg_statistic" was reindexed
NOTICE:  table "pg_type" was reindexed
NOTICE:  table "pg_attribute" was reindexed
NOTICE:  table "pg_proc" was reindexed
NOTICE:  table "pg_autovacuum" was reindexed
NOTICE:  table "pg_attrdef" was reindexed
NOTICE:  table "pg_constraint" was reindexed
NOTICE:  table "pg_inherits" was reindexed
ERROR:  could not open relation with OID 16389
STATEMENT:  REINDEX DATABASE postgres;
NOTICE:  table "pg_class" was reindexed
NOTICE:  table "pg_statistic" was reindexed
NOTICE:  table "pg_type" was reindexed
NOTICE:  table "pg_attribute" was reindexed
NOTICE:  table "pg_proc" was reindexed
NOTICE:  table "pg_autovacuum" was reindexed
NOTICE:  table "pg_attrdef" was reindexed
NOTICE:  table "pg_constraint" was reindexed
NOTICE:  table "pg_inherits" was reindexed
NOTICE:  table "pg_index" was reindexed
NOTICE:  table "pg_operator" was reindexed
NOTICE:  table "pg_opclass" was reindexed
NOTICE:  table "pg_am" was reindexed
NOTICE:  table "pg_amop" was reindexed
NOTICE:  table "pg_amproc" was reindexed
NOTICE:  table "pg_language" was reindexed
NOTICE:  table "pg_largeobject" was reindexed
NOTICE:  table "pg_aggregate" was reindexed
NOTICE:  table "pg_rewrite" was reindexed
NOTICE:  table "pg_trigger" was reindexed
NOTICE:  table "pg_description" was reindexed
NOTICE:  table "pg_cast" was reindexed
NOTICE:  table "pg_namespace" was reindexed
NOTICE:  table "pg_convtableion" was reindexed
NOTICE:  table "pg_depend" was reindexed
NOTICE:  table "pg_class" was reindexed
NOTICE:  table "sql_sizing" was reindexed
NOTICE:  table "sql_sizing_profiles" was reindexed
NOTICE:  table "sql_features" was reindexed
NOTICE:  table "sql_implementation_info" was reindexed
NOTICE:  table "sql_languages" was reindexed
NOTICE:  table "sql_packages" was reindexed
NOTICE:  table "sql_parts" was reindexed
NOTICE:  table "pg_statistic" was reindexed
NOTICE:  table "pg_type" was reindexed
NOTICE:  table "pg_attribute" was reindexed
NOTICE:  table "pg_proc" was reindexed
NOTICE:  table "pg_autovacuum" was reindexed
NOTICE:  table "pg_attrdef" was reindexed
NOTICE:  table "pg_constraint" was reindexed
NOTICE:  table "pg_inherits" was reindexed
ERROR:  could not open relation with OID 16389
STATEMENT:  REINDEX DATABASE postgres;
ERROR:  could not open relation with OID 16393
STATEMENT:  SELECT COUNT(*) FROM picphone;
LOG:  received smart shutdown request
LOG:  shutting down
LOG:  database system is shut down
LOG:  database system was shut down at 2008-02-25 22:03:28 PST
LOG:  checkpoint record is at 1/FB15C10
LOG:  redo record is at 1/FB15C10; undo record is at 0/0; shutdown TRUE
LOG:  next transaction ID: 0/1348953; next OID: 16544
LOG:  next MultiXactId: 1; next MultiXactOffset: 0
LOG:  database system is ready
ERROR:  could not open relation with OID 16399
STATEMENT:  SELECT COUNT(*) FROM table;



Responses

pgsql-admin by date

Next:From: Darren ReedDate: 2008-02-26 06:19:15
Subject: Retrieving data from the data files.
Previous:From: Suresh Gupta VGDate: 2008-02-26 06:08:19
Subject: Latest postgres stable version

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group