PG upgrade 14->15 fails - database contains our own extension
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: ser...@linuxbox.cz
-
lbuid.control
Description: Binary data
lbuid--0.1.0.sql
Description: Binary data
Re: PG upgrade 14->15 fails - database contains our own extension
Hi,
I really appreciate your help and very quick response. And WOW, write patch
for this in few hours ...that's amazing!
> Looking closer, I don't see how b55f2b692 could have changed pg_dump's
> opinion of the order to sort these three casts in; that sort ordering
> logic is old enough to vote. So I'm guessing that in fact this *never*
> worked. Perhaps this extension has never been through pg_upgrade before,
> or at least not with these casts?
Yes its new and I tested right now with upgrade from 9.6 to 15.0 rc2 with
same result. So this behavior is probably long time there, but extension is
new and not upgraded yet. And probably nobody have this "strange" idea.
>(I'm pretty skeptical about it being a good idea to have a set of
casts like this, but I don't suppose pg_dump is chartered to
editorialize on that.)
Yes, im not proud of the creation this workaround extension and I did what
frontend develepers asked me if it's possible. I don't expect a medal of
honor:)
The problem was when bigint was taken from DB as json and stored as number
JS library cast number automaticaly to integer that cause problem.
lbstat=# SELECT json_agg(test) FROM test;
json_agg
---
[{"id":"4294967296"}]
(1 row)
-- ID was represnted now as text and JS library can use it and sent back
without error. But for DB is still bigint.
This was automatic way to solve this problem without casting on all places
to text. I tested and most things works well until upgrade test didn't
pass.
Thank you all.
David T.
--
-
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: ser...@linuxbox.cz
-
Od: "Tom Lane"
Komu: "David Turoň"
Kopie: "Robert Haas" ,
pgsql-hack...@postgresql.org, "Marian Krucina"
Datum: 13.10.2022 18:06
Předmět:Re: PG upgrade 14->15 fails - database contains our own
extension
I wrote:
> Hmm ... I think it's a very ancient bug that somehow David has avoided
> tripping over up to now.
Looking closer, I don't see how b55f2b692 could have changed pg_dump's
opinion of the order to sort these three casts in; that sort ordering
logic is old enough to vote. So I'm guessing that in fact this *never*
worked. Perhaps this extension has never been through pg_upgrade before,
or at least not with these casts?
> We might be able to put in some kluge in pg_dump to make it less
> likely to fail with existing DBs, but I think the true fix lies
> in adding that dependency.
I don't see any painless way to fix this in pg_dump, and I'm inclined
not to bother trying if it's not a regression. Better to spend the
effort on the backend-side fix.
On the backend side, really anyplace that we consult IsBinaryCoercible
during DDL is at hazard. While there aren't a huge number of such
places, there's certainly more than just CreateCast. I'm trying to
decide how much trouble it's worth going to there. I could be wrong,
but I think that only the cast-vs-cast case is really likely to be
problematic for pg_dump, given that it dumps casts pretty early now.
So it might be sufficient to fix that one case.
regards, tom lane
