Re: PG upgrade 14->15 fails - database contains our own extension

On Thu, Oct 13, 2022 at 9:57 AM David Turoň <david(dot)turon(at)linuxbox(dot)cz> wrote:
> pg_restore: creating TYPE "public.lbuid"
> pg_restore: creating CAST "CAST (integer AS "public"."lbuid")"
> pg_restore: while PROCESSING TOC:
> pg_restore: from TOC entry 3751; 2605 16393 CAST CAST (integer AS "public"."lbuid") (no owner)
> pg_restore: error: could not execute query: ERROR: return data type of cast function must match or be binary-coercible to target data type
> Command was: CREATE CAST (integer AS "public"."lbuid") WITH FUNCTION "pg_catalog"."int8"(integer) AS IMPLICIT;

I think the error is complaining that the return type of
int8(integer), which is bigint, needs to coercible WITHOUT FUNCTION to
lbuid. Your extension contains such a cast, but at the point when the
error occurs, it hasn't been restored yet. That suggests that either
the cast didn't get included in the dump file, or it got included in
the wrong order. A quick test suggest the latter. If I execute your
SQL file and the dump the database, I get:

CREATE CAST (integer AS public.lbuid) WITH FUNCTION
pg_catalog.int8(integer) AS IMPLICIT;
CREATE CAST (bigint AS public.lbuid) WITHOUT FUNCTION AS IMPLICIT;
CREATE CAST (public.lbuid AS bigint) WITHOUT FUNCTION AS IMPLICIT;

That's not a valid dump ordering, and if I drop the casts and try to
recreate them that way, it fails in the same way you saw.

My guess is that this is a bug in Tom's commit
b55f2b6926556115155930c4b2d006c173f45e65, "Adjust pg_dump's priority
ordering for casts."

--
Robert Haas
EDB: http://www.enterprisedb.com