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?

Thanks!