Pivotal Knowledge Base

Follow

Creating external table keeps getting created as an append-only table

Environment

Product Version
GPDB 4.3 and higher

Symptom

The external table gets created as an append-only table.

gpadmin=# create external table cars (tag int, make varchar(12), model varchar (12), year int, color varchar (12)) location ('gpfdist://mdw:5432/cars.txt') format 'csv' ;
CREATE EXTERNAL TABLE

gpadmin=# \d
List of relations
Schema | Name | Type | Owner | Storage 
---------+------------------------------------------------------+-------+---------+----------------------
gpadmin | cars | table | gpadmin | append only

Cause

The default storage type was set to "true" for the append-only tables.

gpadmin=# SHOW gp_default_storage_options

gp_default_storage_options 
---------------------------------------------------------------------------------
appendonly=true,blocksize=32768,compresstype=none,checksum=true,orientation=row
(1 row)

Resolution

Set the append-only value to "false".

gpadmin=# SET gp_default_storage_options='appendonly=false';
SET
gpadmin=# SHOW gp_default_storage_options ;
gp_default_storage_options 
----------------------------------------------------------------------------------
appendonly=false,blocksize=32768,compresstype=none,checksum=true,orientation=row
(1 row)

Retry creating the external table, and it should be created as an external table instead of an append-only tables.

Comments

  • Avatar
    Sowjanya

    NOTE: We have to set the gp_default_storage_options to false in all the segments as well.

  • Avatar
    Gurupreet Singh Bhatia

    What will be the difference if external table is created as heap or append only. performance wise is there any issue?

Powered by Zendesk