cancel
Showing results for 
Search instead for 
Did you mean: 

routine Database Maintenance - reindexdb fail

Adventurer

routine Database Maintenance - reindexdb fail

I am using PostgreSQL version 9.4.4 

During reindexdb , facing  this ERROR : could not open relation with OID [oid_no]

but this oid is not present in any ( pg_class , pg_index) system catalog tables.

 

 

7 REPLIES
EDB Team Member

Re: routine Database Maintenance - reindexdb fail

Hi avinashpatil,

 

This issue indicates towards system catalouge corruption. You can create the particluar index freshly, i.e drop current index and again create it with create index command. 

 

Also, if you can share the output of following query before dropping the index :

select * from pg_rewrite where oid = <OID giving error>

 

Adventurer

Re: routine Database Maintenance - reindexdb fail

Thank you for reply 

I have check this System Catalogs table/view but no rows found for respective <OID giving error>

 

 

EDB Team Member

Re: routine Database Maintenance - reindexdb fail

Execute an ANALYZE on the database in the non-peak hours and then try executing reindex you are trying.

EDB Team Member

Re: routine Database Maintenance - reindexdb fail

Hi  avinashpatil,

 

If we see your specific scenario of "reindex" then the error   "ERROR: could not open relation with OID <oid>" occurs when the table entry is not present in the pg_class table. This means the oid is of the table of which the index is created.

 

If the index entry was not present then you would have got error "ERROR:  relation "<index_name>" does not exist.

If the base file i.e data file was deleted then you would have got error like  "ERROR:  could not open file "<location to data file>": No such file or directory".

 

Just verify whether the oid file is present on your system using locate or find command.

If this is the case, then you will have to restore that table from backup.

 

Hope this helps.

 

 

Adventurer

Re: routine Database Maintenance - reindexdb fail

Okay I will check this during non peak hours

EDB Team Member

Re: routine Database Maintenance - reindexdb fail

Hi  avinashpatil,

 

If we see your specific scenario of "reindex" then the error   "ERROR: could not open relation with OID <oid>" occurs when the table entry is not present in the pg_class table. This means the oid is of the table of which the index is created.

 

If the index entry was not present then you would have got error "ERROR:  relation "<index_name>" does not exist.

If the base file i.e data file was deleted then you would have got error like  "ERROR:  could not open file "<location to data file>": No such file or directory".

 

Just verify whether the oid file is present on your system using locate or find command.

If this is the case, then you will have to restore that table from backup.

 

Hope this helps.

 
Adventurer

Re: routine Database Maintenance - reindexdb fail

I have checked on system using find command in base directory of that database but no file is present for that oid. Actually , i had setup reindexdb utility in daily activity and got this error 3 times in week in log. So as per your suggection, i think before reindexdb need to execute ANALYZE but its time consuming process because reindexdb already takes 4 hours daily