Re: Corrupt view in PostgreSQL 9.0.9

=?ISO-8859-1?Q?Mikael_Kjellstr=F6m?= <mikael(dot)kjellstrom(at)mksoft(dot)nu> writes:
> On 2012-09-28 16:35, Tom Lane wrote:
>> That's pretty odd --- it implies something nasty has happened to the
>> contents of pg_attribute. It would be interesting to have a look at
>> select * from pg_attribute where attrelid = 'communitytaxiexceptions'::regclass;

> Here is the result of the above query, I hope it's readable and not mangled:

Well, you definitely lost a few rows there, and some of the ones that
survived are partially incorrect. But it could be worse --- I was
afraid that query wouldn't work at all.

>>> Is there anything thing I could do to manually remove the view or fix
>>> the problem?

>> Well, you could manually remove its pg_class and pg_type rows, and that
>> should be close enough to being "gone". But what I'm worried about is
>> what other damage there is.

> Yes, that is what I am afraid of also. We had a nasty power spike and
> that caused the machine to reboot. The raid controller is equipped with
> a BBU though so there shouldn't be any lost disk writes. But you never
> know.

Looks like you had some data corruption from the spike. If you're
lucky, it's just this one block of pg_attribute and you can reconstruct
things. If not, there may be more problems ...

> Any point of running a vacuum full on the database or is that a bad idea?

I wouldn't try that. It might be worth trying a REINDEX on
pg_attribute. That will not fix the table damage but it will at least
make sure the indexes are consistent with what's now in the table.
After that, I'd suggest manually removing the broken view's pg_class
row and then seeing if you can pg_dump with sane-looking results.
If you can, a dump and restore of at least this database would be
prudent.

regards, tom lane