gpupgrade fails when trying to create a CAST

Products

• VMware Tanzu Greenplum

Issue/Introduction

When upgrading Greemplum to version 6.x. using gpupgrade 1.3 fails with the following error:

pg_restore: creating CAST CAST ("date" AS "text")
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 352133; 2605 2457911 CAST CAST ("date" AS "text")
pg_restore: [archiver (db)] could not execute query: ERROR: function pg_catalog.text(date) does not exist
Command was: CREATE CAST ("date" AS "text") WITH FUNCTION "pg_catalog"."text"("date") AS IMPLICIT;

Here it is trying to create a CAST in the target DB.

Environment

Product Version: 5.28

Resolution

Due to difference in GPDB 5.x and GPDB 6.x, there are functions that are on 5.x and not available on 6.x, therefore they can not be used and can cause a failure during the upgrade. 
Most of these are captured in the gpupgrade initialize state. 

However, CAST that are created by the user and not the system in the pg_catalog schema are not checking during this state. Therefore the gpupgrade initialize will pass but the gpupgrade execute will fail. To move forward you need to revert the upgrade and drop these CASTS from the source. 

To get a list of CASTS that need to be dropped please run this query in the DB.

SELECT c.oid AS castoid,
       c.castsource::regtype,
       c.casttarget::regtype,
       c.castfunc::regprocedure
FROM pg_cast c
JOIN pg_proc p ON (c.castfunc = p.oid)
WHERE p.pronamespace = 11  -- 11 is pg_catalog
  AND c.oid >= 16384;