pg_upgrade fails due to lack of dependencies.

[Laurenz Albe]

On Tue, 2025-07-01 at 16:44 -0400, Regina Obe wrote:
> > Would downgrading functions that use "spatial_ref_sys" to STABLE be too
> > much of a compatibility break to consider?  It would certainly create a
> > headache for people who are currently using such functions in index
> > definitions, generated columns or partition key definitions - but as this report
> > shows, such people already have a problem.
> > Anyway, that would be the proper solution.
>
> Yes that's a no go for 2 reasons
> 
> 1) As you saw with the example mentioned, many people build indexes etc
>    around this function.  If it were marked stable it would prevent usage
>    in indexes etc.  The medicine would be way worse than the sickness.

Even if the sickness is index corruption and inability to upgrade and restore
a dump?

> 2) We tried making it stable like 10 years ago, and that made the function
>    super unbearably slow.  Maybe things have changed since then, but the
>    case 1 is still an issue.

Could that have been problems around inlining that git solved with optimizer
support functions in v12?

> 
> e function you have about is not a variant used much and is relatively new
> 
> The one that is the big culprit is:
> 
> CREATE OR REPLACE FUNCTION st_transform(
> 	geometry,
> 	integer)
>     RETURNS geometry
>     LANGUAGE 'c'
>     COST 5000
>     IMMUTABLE STRICT PARALLEL SAFE 
> AS '$libdir/postgis-3', 'transform'
> ;
> 
> And that one as you can see you can't even tell it relies on spatial_ref_sys
> cause it's calling is buried in the c code.

Ouch.

> > However, that dependency does not extend to the data in "spatial_ref_sys",
> > so using the function in a generated column is still a problem.
> 
> Yap my main concern.  But in case of pg_upgrade, since the data is overlaid,
> at what point does it record, there is data in spatial_ref_sys?

I didn't scrutinize the code, but the table data seem to get dumped in
alphabetical order of "schemaname.tablename".

> The other discussion we had had, which I know Paul wants to throw up everytime
> I mention it is to get rid of spatial_ref_sys and read directly from the
> proj.db sqlite db that has the definitions.
> Most of the time postgis just uses the proj.db def anyway.

How IMMUTABLE is that?

Yours,
Laurenz Albe