ERROR: could not find segment file to use for inserting into relation schema.tablename

Products

• VMware Tanzu Greenplum

Issue/Introduction

When inserting or delete an AO/AOCO table Greenplum Database can report the error:

ERROR: could not find segment file to use for inserting into relation <schema>.<tablename> (appendonlywriter.c:1226)

Environment

Product Version: 6.x - 6.27.5

Cause

This is due to in certain situation, when running insert/delete at the same time as vacuum, it will cause certain conflict for segment file status update. 

Resolution

To avoid this error, gpdb v6.28.0 introduced a new parameter. So upgrade to gpdb v6.28.0 or later release is the best solution.

VMware Greenplum 6.28.0 introduces a new server configuration parameter — gp_appendonly_compaction_segfile_limit. This parameter sets the minimum number of segment files required for inserts before the next compaction.

Besides upgrade, another way to temporarily avoid this error, is reorganize the related table. But as time goes, it's still possible to meet this error.