corrupted table postgresql 8.3

Hi people!

I have a pg 8.3. Today I issued in a database that comand:
=# ALTER TABLE cliente ADD COLUMN pwd_expired boolean DEFAULT FALSE;
WARNING: unexpected attrdef record found for attr 22 of rel cliente
WARNING: unexpected attrdef record found for attr 22 of rel cliente
WARNING: unexpected attrdef record found for attr 22 of rel cliente
ALTER TABLE
Time: 1184.404 ms

After that the table was empty.

SELECT * from cliente;
0 rows ;)
Should contain about 90k records.

I checked in the logs and there are not disk/memory issues on the server.

If I try to execute a vacuum full I get this error.
ERROR: could not open relation 1663/36509/28638634: No such file or directory

At this time I saw two entries of table "cliente" in the pg_tables.

At this time I stopped to troubleshoot and tried to plan some tasks to recover the disaster situation.

I recovered the 98% of the data by copying manually the physical data file of the cluster of that table and the clog to another pg server 8.3.
after that in the server where i got that problem I did this:
- renamed the table to cliente_prova
- removed all the foreign key that are pointing to that table
- recreated the table
- populated the table with the production data recovered from the other server (the last 3-4 fields of the table was unreadable, but I don't know if the method that I used to "recover" the table was technically correct... It was a try...)
- all up and running again

Now i'm facing this:
- if I type \d cliente, I see the schema twice, one without the field "pwd_expired" and the right one that is without it (the table that I restored)
- unable to drop the table renamed cliente_prova
=# DROP TABLE cliente_prova;
WARNING: unexpected attrdef record found for attr 22 of rel cliente_prova
WARNING: unexpected attrdef record found for attr 22 of rel cliente_prova
WARNING: unexpected attrdef record found for attr 22 of rel cliente_prova
WARNING: unexpected attrdef record found for attr 22 of rel cliente_prova
NOTICE: default for table cliente column id depends on sequence cliente_id_seq
WARNING: unexpected attrdef record found for attr 22 of rel cliente_prova
WARNING: unexpected attrdef record found for attr 22 of rel cliente_prova
WARNING: unexpected attrdef record found for attr 22 of rel cliente_prova
WARNING: unexpected attrdef record found for attr 22 of rel cliente_prova
WARNING: unexpected attrdef record found for attr 22 of rel cliente_prova
WARNING: unexpected attrdef record found for attr 22 of rel cliente_prova
WARNING: unexpected attrdef record found for attr 22 of rel cliente_prova
ERROR: cannot drop table cliente_prova because other objects depend on it
HINT: Use DROP ... CASCADE to drop the dependent objects too.

I'll stop here to describe further detail... can you suppose what is happened please?
After that can we discuss how to cleanup the situation...;)

Thanks a lot!

Matteo

Hi people!

I have a pg 8.3. Today I issued in a database that comand:
=# ALTER TABLE cliente ADD COLUMN pwd_expired boolean DEFAULT FALSE;
WARNING: unexpected attrdef record found for attr 22 of rel cliente
WARNING: unexpected attrdef record found for attr 22 of rel cliente
WARNING: unexpected attrdef record found for attr 22 of rel cliente
ALTER TABLE
Time: 1184.404 ms

After that the table was empty.

SELECT * from cliente;
0 rows ;)
Should contain about 90k records.

I checked in the logs and there are not disk/memory issues on the server.

If I try to execute a vacuum full I get this error.
ERROR: could not open relation 1663/36509/28638634: No such file or directory

At this time I saw two entries of table "cliente" in the pg_tables.

At this time I stopped to troubleshoot and tried to plan some tasks to recover the disaster situation.

I recovered the 98% of the data by copying manually the physical data file of the cluster of that table and the clog to another pg server 8.3.
after that in the server where i got that problem I did this:
- renamed the table to cliente_prova
- removed all the foreign key that are pointing to that table
- recreated the table
- populated the table with the production data recovered from the other server (the last 3-4 fields of the table was unreadable, but I don't know if the method that I used to "recover" the table was technically correct... It was a try...)
- all up and running again

Now i'm facing this:
- if I type \d cliente, I see the schema twice, one without the field "pwd_expired" and the right one that is without it (the table that I restored)
- unable to drop the table renamed cliente_prova
=# DROP TABLE cliente_prova;
WARNING: unexpected attrdef record found for attr 22 of rel cliente_prova
WARNING: unexpected attrdef record found for attr 22 of rel cliente_prova
WARNING: unexpected attrdef record found for attr 22 of rel cliente_prova
WARNING: unexpected attrdef record found for attr 22 of rel cliente_prova
NOTICE: default for table cliente column id depends on sequence cliente_id_seq
WARNING: unexpected attrdef record found for attr 22 of rel cliente_prova
WARNING: unexpected attrdef record found for attr 22 of rel cliente_prova
WARNING: unexpected attrdef record found for attr 22 of rel cliente_prova
WARNING: unexpected attrdef record found for attr 22 of rel cliente_prova
WARNING: unexpected attrdef record found for attr 22 of rel cliente_prova
WARNING: unexpected attrdef record found for attr 22 of rel cliente_prova
WARNING: unexpected attrdef record found for attr 22 of rel cliente_prova
ERROR: cannot drop table cliente_prova because other objects depend on it
HINT: Use DROP ... CASCADE to drop the dependent objects too.

I'll stop here to describe further detail... can you suppose what is happened please?
After that can we discuss how to cleanup the situation...;)

Thanks a lot!

Matteo

On 6.3.2012 21:24, Matteo Sgalaberni wrote:
> Hi people!
>
> I have a pg 8.3. Today I issued in a database that comand:

Which minor version? The last one in this branch is 8.3.18 and if you're
running an old one, there might be an important bugfix ...

> =# ALTER TABLE cliente ADD COLUMN pwd_expired boolean DEFAULT FALSE;
> WARNING: unexpected attrdef record found for attr 22 of rel cliente
> WARNING: unexpected attrdef record found for attr 22 of rel cliente
> WARNING: unexpected attrdef record found for attr 22 of rel cliente
> ALTER TABLE
> Time: 1184.404 ms
>
> After that the table was empty.
>
> SELECT * from cliente;
> 0 rows ;)
> Should contain about 90k records.
>
> I checked in the logs and there are not disk/memory issues on the server.

That proves nothing. It might be a PostgreSQL bug but just as well it
might be a silent disk corruption somewhere, unspotted for a long time.

> If I try to execute a vacuum full I get this error.
> ERROR: could not open relation 1663/36509/28638634: No such file or directory
>
> At this time I saw two entries of table "cliente" in the pg_tables.
>
> At this time I stopped to troubleshoot and tried to plan some tasks to recover the disaster situation.
>
> I recovered the 98% of the data by copying manually the physical data file of the cluster of that table and the clog to another pg server 8.3.
> after that in the server where i got that problem I did this:

Not sure what you mean by 'physical data file of the cluster' but you
should do a file-level backup of the whole cluster right now. Before
trying to fix the issues (possibly damaging the data).

Then get the last 8.3.x release (if you're using an old one).

> - renamed the table to cliente_prova
> - removed all the foreign key that are pointing to that table
> - recreated the table
> - populated the table with the production data recovered from the other server (the last 3-4 fields of the table was unreadable, but I don't know if the method that I used to "recover" the table was technically correct... It was a try...)
> - all up and running again

What do you mean by 'populated the table' with the production data? How
did you do that?

kind regards
Tomas

On Tue, Mar 6, 2012 at 10:15 AM, Matteo Sgalaberni <sgala(at)sgala(dot)com> wrote:
> Hi people!
>
> I have a pg 8.3. Today I issued in a database that comand:
> =# ALTER TABLE cliente ADD COLUMN pwd_expired boolean DEFAULT FALSE;
> WARNING:  unexpected attrdef record found for attr 22 of rel cliente
> WARNING:  unexpected attrdef record found for attr 22 of rel cliente
> WARNING:  unexpected attrdef record found for attr 22 of rel cliente
> ALTER TABLE
> Time: 1184.404 ms
>
> After that the table was empty.

I suppose it's a bit late to be answering this now, but it sounds like
you've got a corrupted database, which is something that can have many
causes.

http://rhaas.blogspot.com/2012/03/why-is-my-database-corrupted.html

Generally, the way people handle problems like the one you have in
practice is to do enough surgery on the catalogs to make pg_dump work,
and then restore into a freshly created database.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company