Need help with dropping a view please

From: CauBa <cauba_no_1(at)yahoo(dot)ca>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Need help with dropping a view please
Date: 2011-08-26 22:50:47
Message-ID: 1314399047.43104.YahooMailNeo@web114210.mail.gq1.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I am running postgresql 8.3.3 on solaris
My server has a number of databases.  I have a problem with only one of them.
That particular database got corrupted and I do not yet know the cause.  No hardware problems as far as I can see.
I am not able to search on a view that I was able to do before.

The view is called "clients" and is made up of tables "clients_ca", "clients_us", "clients_uk", etc.
Whenever I do a SELECT * from clients  I get 

ERROR:  missing chunk number 0 for toast value 16538

I went through each of the tables "clients_ca", "clients_us", "clients_uk", etc.  and found five records that have problems with one of the fields.
I had to empty out those fields of the problem records (I can rebuild those later) and now all records can be queried.

However, the view "clients" continues to exhibit the same problem behaviour as if nothing has changed.

I found one of the many Tom Lane's posts and went through and reindex all the pg_toast.pg_toast_*_index; 

That did not work.  I ran analyze verbose on the database and it was successful.

I created a new view called "clients2" with the same member tables as clients and it works I can query it.

But due to the many hardcoded places that use "clients" view,  I have to have "clients" view.  So I tried to drop clients view but cannot

#DROP view clients;
ERROR:  missing chunk number 0 for toast value 16538

Can someone help point me to the right direction as to how I can drop a view when it encounters a missing chunk error ?
Much appreciated.

Cauba

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andy Chambers 2011-08-26 22:53:45 Array syntax in the copier
Previous Message Bobby Dewitt 2011-08-26 22:09:17 Re: Setting a psql variable from SQL output