Pivotal Knowledge Base

Follow

gpmigrator failed with error psql:appendonly_upgrade.sql:277: ERROR: syntax error at or near "."

Environment

Product Version
Pivotal Greenplum (GPDB)  4.2.x to 4.3.x

Symptom

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

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 script:

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

Change the line:

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';

Note: A permanent fix will be included in a future release.

Additional Information

Related Articles:

Comments

Powered by Zendesk