External tables with serial columns created as default and do not restore successfully in Greenplum

Products

• VMware Tanzu Greenplum

Issue/Introduction

When creating an external table with a sequence, we see that it gets a default data type appended to it:

In psql session on postgres database...

CREATE WRITABLE EXTERNAL TABLE foobar (a int, b bigserial)
LOCATION ( 'gpfdist://localhost:8081/myfile' )
FORMAT 'csv';
! gpbackup --dbname postgres --metadata-only
! gprestore --timestamp <generated timestamp> // use --redirect-db and --createdb as well
```

gpbackup will dump this:
```
CREATE SEQUENCE public.foobar_b_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
SELECT pg_catalog.setval('public.foobar_b_seq', 11, true);

CREATE WRITABLE EXTERNAL TABLE public.foobar (
a integer,
b bigint DEFAULT nextval('public.foobar_b_seq'::regclass) NOT NULL
) LOCATION (
'gpfdist://localhost:8081/myfile'
)
FORMAT 'CSV' (delimiter E',' null E'' escape E'"' quote E'"')
ENCODING 'UTF8'
DISTRIBUTED RANDOMLY;

// results in this:
ERROR:  syntax error at or near "DEFAULT"
LINE 3:         b bigint DEFAULT nextval('public.foobar_b_seq'::regc...
```

pg_dump/gpcrondump will dump this:
```
CREATE WRITABLE EXTERNAL TABLE foobar (
a integer,
b bigint
) LOCATION (
'gpfdist://localhost:8081/myfile'
)
FORMAT 'csv' (delimiter E',' null E'' escape E'"' quote E'"')
OPTIONS (

)
ENCODING 'UTF8' DISTRIBUTED RANDOMLY;

CREATE SEQUENCE foobar_b_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
SELECT pg_catalog.setval('foobar_b_seq', 11, true);
ALTER TABLE foobar ALTER COLUMN b SET DEFAULT nextval('foobar_b_seq'::regclass);

// results in this:
WARNING:  "foobar" is an external table. ALTER TABLE for external tables is deprecated.
HINT:  Use ALTER EXTERNAL TABLE instead
ERROR:  Unsupported ALTER command for table type external

Here is a snippet from the log of gprestore:

20200629:09:40:06 gprestore:gpadmin:Server-Name:123456-[DEBUG]:-Error encountered when executing statement: CREATE READ
ABLE EXTERNAL TABLE schema.test_alert (
        columnA character varying(30),
        columnB character varying(30),
        columnC character varying(1),
        columnD timestamp without time zone,
        columnE character varying(30),
        columnF bigint DEFAULT nextval('schema.uid_seq'::regclass) NOT NULL,
        columnG character varying(9),
     
) LOCATION (
        'gpfdist://server-name:8080/pathToDirectory/*'
)
FORMAT 'TEXT' (delimiter E'' null E'' escape E'OFF')
ENCODING 'UTF8'
LOG ERRORS
SEGMENT REJECT LIMIT 100 PERCENT; Error was: ERROR: syntax error at or near "DEFAULT" (SQLSTATE 42601)

Resolution

Workaround

To workaorund this issue, you have to use the `--on-error-continue` flag on a metadata restore and recreate the external table manually by following this procedure:

1. Drop the sequence table referenced for the external table.

2. Create the table, but replace the column type from bigint to bigserial.

3. Run the `pg_catalog.setval()` with the autogenerated sequence table name and the correct sequence value. This should output the same metadata as in the metadata SQL dump.

4. Since these are external tables, the data doesn't get restored.