Re: Problems with missing OIDs

Hi Tom,

> Alexander Lohse <al@humantouch.de> writes:
>> after a disk-crash some weeks ago and a successful recovery, I now
>> find some strange issues in postgres.
>> I am not sure they are related to the crash, but I figure chances
>> are ...
>
> That really shouldn't have happened in the first place.  Are you
> running
> an up-to-date Postgres version?  Are you sure your disk drives don't
> lie
> about write completion?

the server is running PostgreSQL 7.4. The disks where replaced by our
hosting partners, so I do not know anything about their internal state.

What else could produce such a mess?

>> One of them is: When trying to vacuum one database I get the
>> following
>> message:
>> "ERROR:  could not open relation with OID 36893600"
>
>> Now I searched and found a row in pg_index with
>> indexrelid => 36893600
>> Is it save to just drop this row? Or how do I find out which index is
>> affected?
>
> Well, if the pg_class row with that OID is gone, then there's no
> direct
> way to know.  But you should be able to look at the row referenced by
> indrelid, to find out which table it was an index *of*.  Hopefully you
> know your schema well enough to figure out which of its indexes is
> missing.

Yes, that was easy to find.

The index for a primary key contstraint on that table is missing.

I also cannot drop and recreate that constraint!

Can I just go ahead and drop the corresponding rows from pg_contraint
and pg_index?

> I would recommend a dump/initdb/reload cycle to try to detect and
> clean
> up any other corruption.  Loss of just one pg_class row doesn't seem
> like a very probable failure, so I'm afraid you may have more
> problems.

Currently I cannot create any dump because postgres just stops at the
error. ;-(

So, our backup scripts are all broke ... :-(

Thank you,

Alex
______________________________________________________________

Alexander Lohse • Entwicklungsleitung & Projektmanagement
Tel +49 38374 752 11 • Fax +49 38374 752 23
http://www.humantouch.de

Human Touch Medienproduktion GmbH
Am See 1 • 17440 Klein Jasedow • Deutschland

Geschäftsführung:
Lara Mallien, Nele Hybsier, Alexander Lohse, Johannes Heimrath (Senior)
Handelsregister Stralsund • HRB 4192 • USt-IdNr. DE128367684