PG upgrade 14->15 fails - database contains our own extension - Mailing list pgsql-hackers

Hi community,

I have problem with pg_upgrade. Tested from 14.5 to 15.0 rc2 when database contains our extension with one new type. Using pg_dump & restore works well.

We made workaround extension for some usage in javascript library that contains new type that represents bigint as text. So something like auto conversion from SELECT (2^32)::text -> bigint when data is stored and (2^32) -> text when data is retrieved.

Im not sure if this is postgresql bug or we have something wrong in our extension with cast. So becouse this im writing there.

Here is output of pg_upgrade:
(our extension name is lbuid, our type public.lbuid)

command: "/usr/pgsql-15/bin/pg_dump" --host /tmp/pg_upgrade_log --port 50432 --username postgres --schema-only --quote-all-identifiers --binary-upgrade --format=custom  --file="/home/pgsql/data_new/pg_upgrade_output.d/20221013T104924.054/
dump/pg_upgrade_dump_16385.custom" 'dbname=lbstat' >> "/home/pgsql/data_new/pg_upgrade_output.d/20221013T104924.054/log/pg_upgrade_dump_16385.log" 2>&1


command: "/usr/pgsql-15/bin/pg_restore" --host /tmp/pg_upgrade_log --port 50432 --username postgres --create --exit-on-error --verbose --dbname template1 "/home/pgsql/data_new/pg_upgrade_output.d/20221013T104924.054/dump/pg_upgrade_dump_1
6385.custom" >> "/home/pgsql/data_new/pg_upgrade_output.d/20221013T104924.054/log/pg_upgrade_dump_16385.log" 2>&1
pg_restore: connecting to database for restore
pg_restore: creating DATABASE "lbstat"
pg_restore: connecting to new database "lbstat"
pg_restore: creating DATABASE PROPERTIES "lbstat"
pg_restore: connecting to new database "lbstat"
pg_restore: creating pg_largeobject "pg_largeobject"
pg_restore: creating SCHEMA "public"
pg_restore: creating COMMENT "SCHEMA "public""
pg_restore: creating EXTENSION "lbuid"
pg_restore: creating COMMENT "EXTENSION "lbuid""
pg_restore: creating SHELL TYPE "public.lbuid"
pg_restore: creating FUNCTION "public.lbuid_in("cstring")"
pg_restore: creating FUNCTION "public.lbuid_out("public"."lbuid")"
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;

-- For binary upgrade, handle extension membership the hard way
ALTER EXTENSION "lbuid" ADD CAST (integer AS "public"."lbuid");


Hint is good, but this cast is already member of our extension:

lbstat=# ALTER EXTENSION lbuid ADD CAST (integer AS public.lbuid);                                                                                                                                                                      
ERROR:  cast from integer to lbuid is already a member of extension "lbuid"


Database contains this:

CREATE EXTENSION lbuid ;
CREATE TABLE test(id lbuild);
INSERT INTO test VALUES ('1344456644646645456');

Tested on our distribution based on centos7.

When i drop this cast from extension manualy a then manualy restore after pg_upgrade, then operation is without failure.

In attachment are extension files.

Thanks.

Best regards. David T.
(See attached file: lbuid.control)(See attached file: lbuid--0.1.0.sql)

--
-------------------------------------
Ing. David TUROŇ
LinuxBox.cz, s.r.o.
28. rijna 168, 709 01 Ostrava

tel.:    +420 591 166 224
fax:    +420 596 621 273
mobil:  +420 732 589 152
www.linuxbox.cz

mobil servis: +420 737 238 656
email servis: servis@linuxbox.cz
-------------------------------------