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: servis(at)linuxbox(dot)cz
-------------------------------------

Od: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Komu: "David Turoň" <david(dot)turon(at)linuxbox(dot)cz>
Kopie: "Robert Haas" <robertmhaas(at)gmail(dot)com>,
pgsql-hackers(at)postgresql(dot)org, "Marian Krucina"
<marian(dot)krucina(at)linuxbox(dot)cz>
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