Fetching Data from External Table Ends Up with "ERROR: <port> substring size must not exceed 8 characters"

Products

• VMware Tanzu Greenplum

Issue/Introduction

Symptoms:

Any DML operation on an external table encounters an error.

Error Message:

ERROR: <port> substring size must not exceed 8 characters  (seg5 slice1 sdw2:46001 pid=9343)

The above error was a result of the "SELECT" operation. Here sdw2 is the segment host that has encountered error while trying to communicate to gpfdist host mentioned in LOCATION clause of EXTERNAL TABLE during creation as shown below:

template1=# \d+ ext_expenses                                                               
      External table "public.ext_expenses"
  Column  | Type | Modifiers | Storage  | Description 
----------+------+-----------+----------+-------------
 name     | text |           | extended | 
 category | text |           | extended | 
Type: readable
Encoding: UTF8
Format type: text
Format options: delimiter '|' null ' ' escape '\'
External location: gpfdist://:etlhost-1:8081/*.txt

Environment

Cause

This issue is happening due to an error in the syntax with the definition of the external table. As you can see the External location above shows an extra colon (:) just before the hostname (etlhost-1).

There should not be any colon (:) before the hostname(OR IP address if used). Since there is a colon in front of the hostname, it is considering to be the port name, and port name cannot exceed 8 characters. 

 

Resolution

Drop and Create the external table again with proper syntax. Below is the example for both the cases(hostname and IP address)

-- Wrong syntax for LOCATION

LOCATION (
    'gpfdist://:etlhost-1:8081/*.txt' 
)
LOCATION (
    'gpfdist://:172.28.8.20:8081/*.txt' 
)

-- Correct syntax for LOCATION

LOCATION (
    'gpfdist://etlhost-1:8081/*.txt' 
)
LOCATION (
    'gpfdist://172.28.8.20:8081/*.txt' 
)

Complete correct DDL should be like:

CREATE EXTERNAL TABLE ext_expenses ( name text, category text ) 
LOCATION ('gpfdist://etlhost-1:8081/*.txt')
FORMAT 'TEXT' ( DELIMITER '|' NULL ' ') ;