ERROR: External table has more URLs than available primary segments that can write into them

Products

• Services Suite

Issue/Introduction

Symptoms:

When attempting to INSERT data into an external writable table over gpfdist with multiple locations, it errors out all the time.

Error Message:

edw=# insert into stgextusr.ext_sec_dim (select * from capmktusr.sec_dim limit 10) ; 
ERROR: External table has more URLs then available primary segments that can write into them (seg0 hdw1.gphd.local:40000 pid=649455) 

 

Environment

Cause

HDB is allocating some virtual segments (vseg) less than the number of URLs. A single vseg can only write to a single gpfdist location; since there are fewer vsegs than URLs, the operation fails.

Resolution

Resolution

"Force" or "advise" HDB to use more vsegs. We can set "hawq_rm_stmt_nvseg" at the session level to match the number of gpfdist URLs.

edw=# create writable external table stgextusr.ext_sec_dim_bharat ( like capmktusr.sec_dim ) 
LOCATION ( 
'gpfdist://hdw7:/5180/sec_dim_1.gz', 
'gpfdist://hdw8:/5180/sec_dim_2.gz', 
'gpfdist://hdw9:/5180/sec_dim_3.gz'

) 
format 'text' ( delimiter ',' null '' ) 
encoding 'utf8' ;

edw=# set hawq_rm_stmt_nvseg=3; 
SET 
Time: 1.845 ms 

edw=# insert into stgextusr.capmktusr_bnk_dim_bh_new (select * from capmktusr.bnk_dim limit 10) ;