ERROR: Could not read block 4707 of relationship 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 maybe I need to restore the table from backup. But first I need to find out where this corrupted data exists. Does anyone know how to find the ratio 1663/16384/16564? Or block 4707?

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 the tag or index.

To find bad data, perhaps you can query pg_class views;

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

just look at the result!

IF the result is an index, just recreate the damaged index,

IF the result is a table, than this means that some of the data in the table is damaged, you can set the "zero_damaged_pages" parameter to transfer this damaged data or restore the table from your last backup set!

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