Обсуждение: corrupted rows in pg_attribute deleted
-------------------------------------- | Postgres Version 9.0.2 | -------------------------------------- After a Kernel Panic in Debian 5, we found corrupted rows in pg_attribute about 2 tables. I deleted the rows and updated the pg_class.relnatts with the result of: SELECT count(*) FROM pg_attribute WHERE attnum > 0 AND attrelid = 167017 -- Returned 22 rows. where 167017 is the one of the tables OID. Now I got the following messages: 1 - When PgAdmin III list the table structure for the first time: ERROR: invalid attribute number 23 for fase_laudo_apac LINE 1: Select count(*) as rows from only fase_laudo_apac 2- When I tried to drop the fase_laudo_apac table: An error has occourred: ERROR: cache lookup failed for attribute 3 of relation 167045 Any help will be really appreciated ----- Marcos Oliveira -- View this message in context: http://postgresql.1045698.n5.nabble.com/corrupted-rows-in-pg-attribute-deleted-tp4775814p4775814.html Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
Hi,
First thing I would've done is to insert the two corrupted rows into pg_attribute. The best way to get the data for the inserts is to restore a dump of the DB into empty database and retriever the rows by running select over pg_attribute in restored DB. Of You have EMS manager for postgres you can export the selected rows to sql script as inserts. After-hours running updates on corrupted DB run an update on pg_class.
If you don't have EMS write inserts yourself based on selected rows.
Since it's only 2 rows it will be quick.
Hope this helped You.
Regards
Luke
--
Łukasz Brodziak
"Do you bury me when I'm gone
Do you teach me while I'm here
Just as soon I belong
Then it's time I disappear"
First thing I would've done is to insert the two corrupted rows into pg_attribute. The best way to get the data for the inserts is to restore a dump of the DB into empty database and retriever the rows by running select over pg_attribute in restored DB. Of You have EMS manager for postgres you can export the selected rows to sql script as inserts. After-hours running updates on corrupted DB run an update on pg_class.
If you don't have EMS write inserts yourself based on selected rows.
Since it's only 2 rows it will be quick.
Hope this helped You.
Regards
Luke
2011/9/6 marvin.deoliveira <marvin.deoliveira@gmail.com>
--------------------------------------
| Postgres Version 9.0.2 |
--------------------------------------
After a Kernel Panic in Debian 5, we found corrupted rows in pg_attribute
about 2 tables.
I deleted the rows and updated the pg_class.relnatts with the result of:
SELECT count(*) FROM pg_attribute WHERE attnum > 0 AND attrelid = 167017 --
Returned 22 rows.
where 167017 is the one of the tables OID.
Now I got the following messages:
1 - When PgAdmin III list the table structure for the first time:
ERROR: invalid attribute number 23 for fase_laudo_apac
LINE 1: Select count(*) as rows from only fase_laudo_apac
2- When I tried to drop the fase_laudo_apac table:
An error has occourred:
ERROR: cache lookup failed for attribute 3 of relation 167045
Any help will be really appreciated
-----
Marcos Oliveira
--
View this message in context: http://postgresql.1045698.n5.nabble.com/corrupted-rows-in-pg-attribute-deleted-tp4775814p4775814.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Łukasz Brodziak
"Do you bury me when I'm gone
Do you teach me while I'm here
Just as soon I belong
Then it's time I disappear"
I don't have any other healthy dump of the database. That's why I'm trying to drop these tables. ----- Marcos Oliveira -- View this message in context: http://postgresql.1045698.n5.nabble.com/corrupted-rows-in-pg-attribute-deleted-tp4775814p4776113.html Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
Yet still to drop the tables you need to have complete information on them in the system tables. So I would go and 'forge' the data by inserting info into pg_attribute based on other entries for the columns of these tables. After that you should be able to drop the tables or do aby other modifications.
06-09-2011 23:12 użytkownik "marvin.deoliveira" <marvin.deoliveira@gmail.com> napisał:
I'm inserting rows in pg_attribute from a old version of the table. After that, the pg_class.relnatts field is updated with the result of: select count(*) from pg_attribute where attrelid = 167017 and attnum > 0 -- returns 24 So I try do drop the table to see if it works and I get a message like this: An error has occurred: ERROR: invalid attribute number 25 for fase_laudo_apac The curious thing is if I delete or insert another row, the number in the error message is always the actual numbers of rows +1 (as we see above). what is wrong? ----- Marcos Oliveira -- View this message in context: http://postgresql.1045698.n5.nabble.com/corrupted-rows-in-pg-attribute-deleted-tp4775814p4782410.html Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
I think I managed to restore the fields that were missing. Now I got a message that references a Pk without de column reference, but the column is in pg_attribute. I guess that solving that reference could allow me to ,finaly, drop the table. How can I fix that? ----- Marcos Oliveira And I still haven't found what I'm looking for... -- View this message in context: http://postgresql.1045698.n5.nabble.com/corrupted-rows-in-pg-attribute-deleted-tp4775814p4786156.html Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
Hi,
I guess that the Primary Key is on the table that You want to drop. Try dropping the PK first then drop the table.
--
Łukasz Brodziak
"Do you bury me when I'm gone
Do you teach me while I'm here
Just as soon I belong
Then it's time I disappear"
2011/9/9 marvin.deoliveira <marvin.deoliveira@gmail.com>
I think I managed to restore the fields that were missing.
Now I got a message that references a Pk without de column reference, but
the column is in pg_attribute.
I guess that solving that reference could allow me to ,finaly, drop the
table.
How can I fix that?
-----
Marcos Oliveira
And I still haven't found what I'm looking for...
--
View this message in context: http://postgresql.1045698.n5.nabble.com/corrupted-rows-in-pg-attribute-deleted-tp4775814p4786156.htmlSent from the PostgreSQL - admin mailing list archive at Nabble.com.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Łukasz Brodziak
"Do you bury me when I'm gone
Do you teach me while I'm here
Just as soon I belong
Then it's time I disappear"
I've tried that, but no success. ----- Marcos Oliveira And I still haven't found what I'm looking for... -- View this message in context: http://postgresql.1045698.n5.nabble.com/corrupted-rows-in-pg-attribute-deleted-tp4775814p4786262.html Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
"marvin.deoliveira" <marvin.deoliveira@gmail.com> wrote: > I've tried that, but no success. Please copy and paste the command and the results, including any related entries in the log. -Kevin