gpmigrator fails with: syntax error at or near "."

Products

• VMware Tanzu Greenplum

Issue/Introduction

Symptoms:

When upgrading from 4.2.x to 4.3.x, the gpmigrator script fails if owners of Append Only (AO) tables have a "." (period) or uppercase letters in their role name.

For example:

• john.doe
• JohnDoe
• John.Doe

The error message in gpmigrator for AO table owners with a "." (period) in their role name is:

HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
psql:/usr/local/greenplum-db-4.3.6.2/share/postgresql/upgrade/upg2_catupgrade_43.sql:426: NOTICE:  schema "__pg_catalog_gpmigrator_uao" does not exist, skipping
psql:appendonly_upgrade.sql:277: ERROR:  syntax error at or near "."
LINE 1: ...BLE pg_aoseg.pg_aovisimap_191281284 OWNER TO john.doe, AL...
                                                             ^
QUERY:  ALTER TABLE pg_aoseg.pg_aovisimap_191281284 OWNER TO john.doe, ALTER visimap SET STORAGE PLAIN
CONTEXT:  PL/pgSQL function "upgrade_appendonly_aux" line 20 at execute statement

The script will fail at the same point with the following error if role names have capital letters:

ERROR:  role "johndoe" does not exist

Environment

Cause

The gpmigrator fails due to the code in the procedure in the file:

$GPHOME/share/postgresql/upgrade/upg2_catupgrade_43.sql

The function pg_catalog.upgrade_appendonly_aux(oid) created in the script has the lines: 

EXECUTE 'ALTER TABLE pg_aoseg.pg_aovisimap_' || aoRelFileId  ||
    ' OWNER TO ' || aoRelOwner || ', ALTER visimap SET STORAGE PLAIN';

The aoRelOwner needs to be enclosed by the double quotes.

Resolution

The workaround is to manually edit the following script:

$GPHOME/share/postgresql/upgrade/upg2_catupgrade_43.sql

Change the line from:

EXECUTE 'ALTER TABLE pg_aoseg.pg_aovisimap_' || aoRelFileId  ||
    ' OWNER TO ' || aoRelOwner || ', ALTER visimap SET STORAGE PLAIN';

To:

EXECUTE 'ALTER TABLE pg_aoseg.pg_aovisimap_' || aoRelFileId  ||
    ' OWNER TO "' || aoRelOwner || '", ALTER visimap SET STORAGE PLAIN';