ERROR: could not read block 4707 of relation 1663/16384/16564: Success

Question

I am using psql 8.1.18 on Glassfishserver. I have a query like this:

select ip,round((select sum(t1.size) from table t1)) 
from table 
where date > '2011.07.29' 
and date < '2011.07.30' 
and ip = '255.255.255.255' 
group by ip;

When I run this query I got this error:

ERROR:  could not read block 4707 of relation 1663/16384/16564: Success

However this query works fine:

select ip,round(sum(size)/175) 
from table 
where date > '2011.07.29' 
and l_date < '2011.07.30' 
and ip = '255.255.255.255' 
group by ip;

I think it might be a database error and I need to restore the table from the backup, maybe. But first I need to learn where this corrupted data exist. Does anyone know how to find 1663/16384/16564 relation? Or 4707 block?

EDIT: I tried this code:

select relname , relfilenode from pg_class where  relname in ('1663','16384','16564');

but it returns:

relname | relfilenode 
---------+-------------
(0 rows)

Answer 1

It looks like there are bad blocks in a table or an index.

To find the bad data, Maybe you can query pg_class views ;

select oid,relname from pg_class where oid =1663 or oid=16564;

just see what's the result!

IF the result is an index, just recreate the corrupted index;

IF the result is a table , than it means that there are some data of the table is damaged, you can set the parameter "zero_damaged_pages" to on to by pass those corrupted data or restore the table from your recently backup set !

more information about the parameter "zero_damaged_pages" http://www.postgresql.org/docs/9.0/static/runtime-config-developer.html