Error 'read beyond eof in table' when running SELECT query on partitioned table

Products

VMware Tanzu Greenplum VMware Tanzu Greenplum / Gemfire VMware Tanzu Data Suite VMware Tanzu Data Suite

Issue/Introduction

Running a SELECT on a partitioned table, the error "read beyond eof in table XXXXX" is reported. Example:

2023-10-11 16:57:15.191445 PDT,"gpadmin","gpadmin",p3241626,th-1132627584,"[local]",,2023-10-11 16:57:14 PDT,0,con86,cmd2,seg-1,,,,sx1,"ERROR","XX000","read beyond eof in table ""test_vacuum_prt_1_prt_3"" file ""base/219425/118627.1"", read position 0 (small offset 0), actual read length 0 (large rea     d length 65536) (cdbbufferedread.c:202)  (seg0 slice1 127.0.1.1:6002 pid=3241628) (cdbbufferedread.c:202)",,,,,,"select count(*) from test_vacuum_prt",0,,"cdbbufferedread.c",202,"Stack trace:
1    0x55ac7eb62b0d postgres errstart + 0x473
2    0x55ac7ec376a5 postgres cdbdisp_get_PQerror + 0x12b
3    0x55ac7ec374b5 postgres cdbdisp_dumpDispatchResult + 0x5b
4    0x55ac7ec3794d postgres cdbdisp_dumpDispatchResults + 0x104
5    0x55ac7ec332cb postgres cdbdisp_getDispatchResults + 0xc2
6    0x55ac7ec3348b postgres CdbDispatchHandleError + 0x1ab
7    0x55ac7e775382 postgres mppExecutorCleanup + 0xef
8    0x55ac7e7532f5 postgres standard_ExecutorRun + 0x5b5
9    0x55ac7e752d3d postgres ExecutorRun + 0x54
10   0x55ac7e9d5986 postgres <symbol not found> + 0x7e9d5986
11   0x55ac7e9d5576 postgres PortalRun + 0x30a
12   0x55ac7e9cc756 postgres <symbol not found> + 0x7e9cc756
13   0x55ac7e9d2376 postgres PostgresMain + 0xe31
14   0x55ac7e92a641 postgres <symbol not found> + 0x7e92a641
15   0x55ac7e929b6e postgres <symbol not found> + 0x7e929b6e
16   0x55ac7e925279 postgres <symbol not found> + 0x7e925279
17   0x55ac7e924575 postgres PostmasterMain + 0x1568
18   0x55ac7e8052a1 postgres <symbol not found> + 0x7e8052a1
19   0x7fc3bf395d90 libc.so.6 <symbol not found> + 0xbf395d90
20   0x7fc3bf395e40 libc.so.6 __libc_start_main + 0x80
21   0x55ac7e449205 postgres _start + 0x25

This error occurs when a vacuum on one of the partitions is concurrently running during the time of the SELECT statement.

Environment

Greenplum Database 6.x

Greenplum Database 7.x

Cause

Greenplum 7.x

The root cause is that, VACUUM FULL will attempt to drop append-optimized table files more aggressively than VACUUM. As a result, VACUUM FULL could drop append-optimized table files which may still be visible to some concurrent transaction snapshots. This is an expected change since 7.4.1

Resolution

Greenplum 6.x

Workaround:

Avoid vacuum of partition tables during select

Enclose select in a begin/end block, including a table lock beforehand:

begin;
lock table foo in access share mode;
select count(*) from foo;
end;

Fix

See Resolved Issue 365700 in the Greenplum 6.x Release Notes

Setting the GUC gp_keep_partition_children_locks to true will stop the error from happening. The default value for the GUC is false.

NOTE: The GUC is only available in Greenplum Database 6.27.0 and above. It is not currently available in Greenplum 7.x.

Greenplum 7.x

To avoid such issue in future, please consider these options:

Perform VACUUM FULL only in maintenance windows in order to avoid impact to concurrent workloads.
Perform normal (lazy) VACUUM in other times. This vacuum mode will skip dropping append-optimized table files that are still visible to concurrent transactions. A potential downside is that, long-running transaction might prevent VACUUM from free'ing up space occupied by unused append-optimized table files. So still consider performing VACUUM FULL when it is possible.