pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedcorrupted rows in pg_attribute deleted
9+ messages / 3 participants
[nested] [flat]
* corrupted rows in pg_attribute deleted
@ 2011-09-06 19:57 marvin.deoliveira <marvin.deoliveira@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: marvin.deoliveira @ 2011-09-06 19:57 UTC (permalink / raw)
To: pgsql-admin
--------------------------------------
| 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.htm...
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: corrupted rows in pg_attribute deleted
@ 2011-09-06 20:17 Lukasz Brodziak <lukasz.brodziak@gmail.com>
parent: marvin.deoliveira <marvin.deoliveira@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Lukasz Brodziak @ 2011-09-06 20:17 UTC (permalink / raw)
To: marvin.deoliveira <marvin.deoliveira@gmail.com>; +Cc: pgsql-admin
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
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.htm...
> 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"
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: corrupted rows in pg_attribute deleted
@ 2011-09-06 21:12 marvin.deoliveira <marvin.deoliveira@gmail.com>
parent: Lukasz Brodziak <lukasz.brodziak@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: marvin.deoliveira @ 2011-09-06 21:12 UTC (permalink / raw)
To: pgsql-admin
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.htm...
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: corrupted rows in pg_attribute deleted
@ 2011-09-06 21:43 Lukasz Brodziak <lukasz.brodziak@gmail.com>
parent: marvin.deoliveira <marvin.deoliveira@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Lukasz Brodziak @ 2011-09-06 21:43 UTC (permalink / raw)
To: marvin.deoliveira <marvin.deoliveira@gmail.com>; +Cc: pgsql-admin
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ł:
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: corrupted rows in pg_attribute deleted
@ 2011-09-08 13:28 marvin.deoliveira <marvin.deoliveira@gmail.com>
parent: Lukasz Brodziak <lukasz.brodziak@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: marvin.deoliveira @ 2011-09-08 13:28 UTC (permalink / raw)
To: pgsql-admin
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.htm...
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: corrupted rows in pg_attribute deleted
@ 2011-09-09 12:27 marvin.deoliveira <marvin.deoliveira@gmail.com>
parent: marvin.deoliveira <marvin.deoliveira@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: marvin.deoliveira @ 2011-09-09 12:27 UTC (permalink / raw)
To: pgsql-admin
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.htm...
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: corrupted rows in pg_attribute deleted
@ 2011-09-09 12:49 Lukasz Brodziak <lukasz.brodziak@gmail.com>
parent: marvin.deoliveira <marvin.deoliveira@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Lukasz Brodziak @ 2011-09-09 12:49 UTC (permalink / raw)
To: marvin.deoliveira <marvin.deoliveira@gmail.com>; +Cc: pgsql-admin
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.
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.htm...
> 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"
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: corrupted rows in pg_attribute deleted
@ 2011-09-09 12:57 marvin.deoliveira <marvin.deoliveira@gmail.com>
parent: Lukasz Brodziak <lukasz.brodziak@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: marvin.deoliveira @ 2011-09-09 12:57 UTC (permalink / raw)
To: pgsql-admin
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.htm...
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: corrupted rows in pg_attribute deleted
@ 2011-09-09 13:35 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: marvin.deoliveira <marvin.deoliveira@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Kevin Grittner @ 2011-09-09 13:35 UTC (permalink / raw)
To: marvin.deoliveira <marvin.deoliveira@gmail.com>; pgsql-admin
"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
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2011-09-09 13:35 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-09-06 19:57 corrupted rows in pg_attribute deleted marvin.deoliveira <marvin.deoliveira@gmail.com>
2011-09-06 20:17 ` Lukasz Brodziak <lukasz.brodziak@gmail.com>
2011-09-06 21:12 ` marvin.deoliveira <marvin.deoliveira@gmail.com>
2011-09-06 21:43 ` Lukasz Brodziak <lukasz.brodziak@gmail.com>
2011-09-08 13:28 ` marvin.deoliveira <marvin.deoliveira@gmail.com>
2011-09-09 12:27 ` marvin.deoliveira <marvin.deoliveira@gmail.com>
2011-09-09 12:49 ` Lukasz Brodziak <lukasz.brodziak@gmail.com>
2011-09-09 12:57 ` marvin.deoliveira <marvin.deoliveira@gmail.com>
2011-09-09 13:35 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox