Greenplum ERROR: could not read block 0 of relation XXXX: read only 0 of YYYY bytes

Products

• VMware Tanzu Greenplum

Issue/Introduction

When accessing a table you get the following error message: 

20200217:01:41:37:177557 analyzedb:mdw1:gpadmin-[WARNING]:-ERROR: could not read block 0 of relation 1663/45528/11925710: read only 0 of 32768 bytes (seg6 slice1 192.168.10.28:1025 pid=189575)

Environment

Product Version: 5.7

Resolution

To resolve this issue, follow the steps below.

1. Identify the database you have an issue with:

template1=# SELECT oid,datname from pg_database where oid = 45528; 
   oid | datname 
-------+--------- 
 45528 | prod 
(1 row)

2. The error message provides details about the segment information that you can use to connect directly. Connect to the correct database on a correct segment and look for the problematic object. 

Execute the following on a master bash prompt:

PGOPTIONS='-c gp_session_role=utility' psql -h 192.168.10.28 -p 1025 -c "SELECT relname,relkind,relfilenode from pg_class where relfilenode = '11925710'; " -d prod
         relname          | relkind | relfilenode 
--------------------------+---------+-------------  
 pg_aovisimap_16431_index | i       |    11925710 
(1 row) 

You can see this is an AO index. If you want to know what the table name is, you can use the oid:

PGOPTIONS='-c gp_session_role=utility' psql -h 192.168.10.28 -p 1025 -c "SELECT relname from pg_class where oid = '16431'" 
relname 
--------- 
t1 
(1 row)

3. Connect to the segment and re-index it.

PGOPTIONS='-c gp_session_role=utility' psql -h 192.168.10.28 -p 1025 -c "REINDEX INDEX pg_aoseg.pg_aovisimap_16431_index " -d prod

Once re-indexed, try the load again to verify the table doesn't cause any more issues.