pg_upgrade fails due to lack of dependencies.

[Regina Obe]

> 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.

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.

> The situation would become a little better if you defined these functions in the
> "new style":
> 
>   CREATE FUNCTION public.st_transform(geom geometry, from_proj text,
> to_srid integer)
>    RETURNS geometry
>    IMMUTABLE PARALLEL SAFE STRICT COST 5000
>   BEGIN ATOMIC
>      SELECT public.postgis_transform_geometry($1, $2, proj4text, $3)
>      FROM public.spatial_ref_sys WHERE srid=$3;
>   END;
> 
> Functions defined in that way get parsed at definition time and stored in that
> form.
> The interesting aspect is that PostgreSQL tracks object dependencies for such
> functions, So pg_dump can make sure that dependent objects are dumped
> first.
> I think that would be a clear improvement.
> 

The 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.


> 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?

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.