ERROR: there are more external files (URLs) than primary segments that can read them in Greenplum

Products

• VMware Tanzu Greenplum

Issue/Introduction

You may encounter either of the following errors reported by Greenplum Database (GPDB):

ERROR: there are more external files (URLs) than primary segments that can read them

or

ERROR: There are more external files than primary segment databases on host my_host_01

The definition of external tables includes a LOCATION. The LOCATION can specify many files or URLs to read from or write to.

The number of individual locations specified needs to be less than or equal to the number of primary segments in the cluster.

Note: This is generally only an issue with small test clusters.

For example, running SELECT on the following table definition results in an error if there are fewer than 6 primary segments in the cluster.

CREATE EXTERNAL TABLE ext_tab01 ( a int, b int) LOCATION (
   'gpfdist://etl01:8080/data.csv',
   'gpfdist://etl01:8081/data.csv',
   'gpfdist://etl02:8080/data.csv',
   'gpfdist://etl02:8081/data.csv',
   'gpfdist://etl03:8080/data.csv',
   'gpfdist://etl03:8081/data.csv' 
) FORMAT 'CSV';

Environment

Product Version: 6.11

Resolution

Workaround

This is functioning as designed. To avoid an error do either of the following:

• Increase the number of primary segments in the cluster.
• Reduce the number of URLs specified in the LOCATION of the external table.