Pivotal Knowledge Base

Follow

Create external table error: type "xxx" already exists (segxx sdwx:xxx pid=xxxx)

Environment

Pivotal Greenplum: 4.3.x

OS: RHEL 6.x

Symptom

You are trying to create an external table after dropping it, but it fails returning the following error:

error : type "ext_mss_cdr" already exists (seg38 sdw5.gphd.local:1031 pid=507708) 
********** Error **********
ERROR: type "ext_mss_cdr" already exists (seg38 sdw5.gphd.local:1031 pid=507708) 
SQL state: 42710

Cause

When you create an external table, it will insert a record in pg_type whose typename is same as the name of the external table. In this case, the customer attempts to create the external table which dropped before. But the record of the dropped table remains in the pg_type on some segments, thus causing the error. 

Example

CREATE EXTERNAL TABLE ext.ext_mss_cdr
(
record_length integer ,
record_type integer ,
record_subtype character varying,
record_number integer ,
record_status integer ,
check_sum integer ,
file_name character varying
)
LOCATION (
'gpfdist://etl1:14000/OTHERS/MSS_CDR/BIEDW_MSC_MSC*.dat'
)
FORMAT 'text' (delimiter '|' null '' escape '\\')
ENCODING 'UTF8'
LOG ERRORS INTO err_mss_cdr SEGMENT REJECT LIMIT 1000 ROWS;
CREATE EXTERNAL TABLE
gpadmin=# select gp_segment_id, typname from gp_dist_random('pg_type') where typname='ext_mss_cdr';
gp_segment_id | typname
---------------+------------- 
0 | ext_mss_cdr 
1 | ext_mss_cdr
(2 rows) 

Resolution

Step 1. Use the query below to check if there is any existing record of that dropped external table on either segment in pg_type

select gp_segment_id, typname from gp_dist_random('pg_type') where typname='<external_table_name>';

In this case, the record in pg_type is only extracted on seg38

edwhprod=# select gp_segment_id, typname from gp_dist_random('pg_type') where typname='ext_mss_cdr';
 gp_segment_id |   typname
---------------+-------------
            38 | ext_mss_cdr
(1 row)

Step 2. Delete the extracted row in pg_type on those segments via Utility mode

[gpadmin@BOTBIOTHO1000 ~]$ PGOPTIONS='-c gp_session_role=utility' psql -h sdw5 -p 1031
edwhprod=# select oid,* from pg_type where typname like '%ext_mss_cdr%';
    oid    |   typname    | typnamespace | typowner | typlen | typbyval | typtype | typisdefined | typdelim | typrelid  | typelem | typinput  | typoutput  | typreceive
 |   typsend   | typanalyze | typalign | typstorage | typnotnull | typbasetype | typtypmod | typndims | typdefaultbin | typdefault
-----------+--------------+--------------+----------+--------+----------+---------+--------------+----------+-----------+---------+-----------+------------+------------
-+-------------+------------+----------+------------+------------+-------------+-----------+----------+---------------+------------
  89701735 | ext_mss_cdr  |        30483 |       10 |     -1 | f        | c       | t            | ,        |  89701734 |       0 | record_in | record_out | record_recv
 | record_send | -          | d        | x          | f          |         0 |        -1 |        0 |               |
(1 row)
copy (select oid,* from pg_type where oid=89701735) to '/home/gpadmin/pg_type_89701735.txt';
set allow_system_table_mods=dml; 
begin; 
Delete from pg_type where oid=89701735; 
edwhprod=# select * from pg_class where relname like '%ext_mss_cdr%'; relname | relnamespace | reltype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid | relaosegrelid | relao segidxid | relhasindex | relisshared | relkind | relstorage | relnatts | relchecks | reltriggers | relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules | relhassubclass | relfrozenxid | relacl | reloptions --------------+--------------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+---------------+------ ---------+-------------+-------------+---------+------------+----------+-----------+-------------+----------+----------+---------+------------+------------+------------ -+----------------+--------------+--------+------------ 
(0 row) 
commit; 

Step 3. Try to run "create DDL" again

Important! We suggest that the customer runs a gpcheckcat to make sure there are no other catalog errors. If there are some existing catalog issues, please contact Pivotal Support for further assistant. 

Comments

Powered by Zendesk