Re: Help - corruption issue?

> On Fri, Apr 22, 2011 at 8:20 PM, <tv(at)fuzzy(dot)cz> wrote:
>>> On Fri, Apr 22, 2011 at 7:07 PM,  <tv(at)fuzzy(dot)cz> wrote:
>>> In the pg_dumpall backup process, I get this error. Does this help?
>>>
>>
>> Well, not really - it's just another incarnation of the problem we've
>> already seen. PostgreSQL reads the data, and at some point it finds out
>> it
>> needs to allocate 4294967293B of memory. Which is strange, because it's
>> actually a negative number (-3 AFAIK).
>>
>> It's probably caused by data corruption (incorrect length for a field).
>>
>> There are ways to find out more about the cause, e.g. here:
>>
>> http://archives.postgresql.org/pgsql-hackers/2005-10/msg01198.php
>>
>> but you need to have a pg compiled with debug support. I guess the
>> packaged version does not support that, but maybe you can get the
>> sources
>> and compile them on your own.
>>
>> If it really is a data corruption, you might try to locate the corrupted
>> blocks like this:
>>
>> -- get number of blocks
>> SELECT relpages FROM pg_class WHERE relname = 'table_name';
>>
>> -- get items for each block (read the problematic column)
>> FOR block IN 1..relpages LOOP
>> SELECT AVG(length(colname)) FROM table_name WHERE ctid >=
>> '(block,0)'::ctid AND ctid < '(block+1,0)'::ctid;
>
>
> Thanks for this. Very useful. What is this -- a function? How should I
> execute this query?

It's a pseudocode - you need to implement that in whatever language you
like. You could do that in PL/pgSQL but don't forget it's probably going
to crash when you hit the problematic block so I'd probably implement that
in outside the DB (with a logic to continue the loop once the connection
dies).

And 'ctid' is a pseudocolumn that means '(block#, row#)' i.e. it's
something like a physical location of the row.

regards
Tomas