Hi,

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 ...

One of them is: When trying to vacuum one database I get the following
message:
"ERROR: could not open relation with OID 36893600"
After browsing the archives I found similar threads and possible
recovery methods.

So, I already reindexed and vacuumed all pg_catalog, with no effect.

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?

Any help is very much apprechiated as this seems to affect our backup-
dumping as well!

Best regards,

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



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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?

> 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.

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.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

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



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly