Hi folks...sorry for sending this to this list, but the others
are not getting me any where. I've run into a problem trying to
recover data from a table that has some how been corrupted (I
do not know how). Although this is no longer critical (I have
been able to recreate the data from other sources), I would like
to know, if possible, how to get around this in the future.
The problem is a 1.6 million row table to which any command
such as select, pg_dump, copy, vacuum fails to go anywhere.
Specifically, no error is generated, but the backend just sits
and spins eating up CPU cycles. The table initially had two
indices associated with it. In trying to identify the problem,
I've dropped the indices and attempted to work with the raw
underlying table.
Specifically: 1. The table is visible to clients - i.e. you can _attempt_ a select, pg_dump, etc. 2. If a pg_dump
isattempted on the table, only the first 761 rows are dumped. Thereafter, the server task spins forever chewing
upCPU cycles and never dumps an additional record. In one case (prior to me killing the task) I witnessed it
consuming4 hours of CPU time. (P200, 128Meg Ram, 90 Meg swap, but never used swap) 3. vacuum does the same...If I
vacuumthe db, it vacuums almost everything but this table (i.e. it gets stuck on what I think is this table).
IfI vacuum the table directly, the server task spins endlessly. 4. Select statements hang forever (same effect)
In all cases, the memory used by the back-end never grows in
terms of memory usage once it starts spinning on CPU cycles.
All other tables in the db behave "normally".
Any tools other than vacuum that detect and correct inconsistencies,
or allow for some form of data recovery on the table?
Thomas
------------------------------------------------------------
Thomas Reinke Tel: (416) 460-7021
Director of Technology Fax: (416) 598-2319
E-Soft Inc. http://www.e-softinc.com
