Обсуждение: Repairing bad table?

Поиск
Список
Период
Сортировка

Repairing bad table?

От
Steve Wampler
Дата:
I need help - I've had a system crash that has left a table with some
invalid OIDs.  How do I clean this up?  If I try a vacuum analyze,
I get:

configdb=# vacuum analyze;
NOTICE:  Rel attributes_table: TID 3304/23: OID IS INVALID. TUPGONE 1.
NOTICE:  Rel attributes_table: TID 3304/28: OID IS INVALID. TUPGONE 1.
NOTICE:  Rel attributes_table: TID 3304/31: OID IS INVALID. TUPGONE 1.
NOTICE:  Rel attributes_table: TID 3304/33: OID IS INVALID. TUPGONE 1.
NOTICE:  Rel attributes_table: TID 3304/40: OID IS INVALID. TUPGONE 1.
NOTICE:  Rel attributes_table: TID 3304/41: OID IS INVALID. TUPGONE 1.
NOTICE:  Rel attributes_table: TID 3304/77: OID IS INVALID. TUPGONE 1.
FATAL 1:  Memory exhausted in AllocSetAlloc()
pqReadData() -- backend closed the channel unexpectedly.
        This probably means the backend terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
configdb=#

Note: I have a Linux box with 1GB ram and another 480MB swap, process
   resources are:

->ulimit -a
cpu time (seconds)         unlimited
file size (blocks)         unlimited
data seg size (kbytes)     unlimited
stack size (kbytes)        8192
core file size (blocks)    1000000
resident set size (kbytes) unlimited
processes                  32766
file descriptors           1024
locked-in-memory size (kb) unlimited
virtual memory size (kb)   unlimited

   and shared memory is 128MB, so I'm reasonably confident that the FAQ on
   "Memory exhausted in AllocSetAlloc()" doesn't apply.  Table only has 257000
   (more or less) rows.

   Any guidelines on how to repair the problem are *most* welcome!

   Thanks,
   Steve
--
Steve Wampler-  SOLIS Project, National Solar Observatory
swampler@noao.edu

Re: Repairing bad table?

От
Tom Lane
Дата:
Steve Wampler <swampler@noao.edu> writes:
> I need help - I've had a system crash that has left a table with some
> invalid OIDs.  How do I clean this up?  If I try a vacuum analyze,
> I get:

> configdb=# vacuum analyze;
> NOTICE:  Rel attributes_table: TID 3304/23: OID IS INVALID. TUPGONE 1.
> NOTICE:  Rel attributes_table: TID 3304/28: OID IS INVALID. TUPGONE 1.
> NOTICE:  Rel attributes_table: TID 3304/31: OID IS INVALID. TUPGONE 1.
> NOTICE:  Rel attributes_table: TID 3304/33: OID IS INVALID. TUPGONE 1.
> NOTICE:  Rel attributes_table: TID 3304/40: OID IS INVALID. TUPGONE 1.
> NOTICE:  Rel attributes_table: TID 3304/41: OID IS INVALID. TUPGONE 1.
> NOTICE:  Rel attributes_table: TID 3304/77: OID IS INVALID. TUPGONE 1.
> FATAL 1:  Memory exhausted in AllocSetAlloc()

Try not doing "analyze" --- the most likely cause of the FATAL message is
that the system is trying to copy some damaged column value that
claims to be ridiculously wide.

If nothing else helps, you could manually zero block 3304 of the table,
sacrificing those rows to get back the rest of your data.  dd(1) should
work for this, I think, but be sure to shut down the postmaster while
hacking directly on data files.  [NB: these are 8K blocks (unless you
changed BLCKSZ) and the file starts with block zero.]

            regards, tom lane