pg_upgrade fails due to lack of dependencies.

[Laurenz Albe]

On Mon, 2025-06-30 at 13:50 -0400, Regina Obe wrote:
> > So your concern is that even with a dep between st_transform and
> > spatial_ref_sys, it’s entirely possible that pg_restore will just restore the empty
> > table, and the problem will still occur?
> 
> YES.

And that is a very valid concern.

I don't know if you are following the discussion on the pgsql-admin list:
https://postgr.es/m/CAFpL5Vw%2BEK4UN6mHXRqZU%2BWLyFiSLqPiDAZnA3VX65tNFpyPyA%40mail.gmail.com

The core problem is that st_transform(geometry, text, integer) is declared IMMUTABLE,
when really it depends on the contents of "spatial_ref_sys".  And as the documentation
says in https://postgis.net/docs/manual-3.5/using_postgis_dbmanagement.html#user-spatial-ref-sys,
you can add definitions to the table.  So the problem is not limited to "spatial_ref_sys"
not being populated yet during restore.

Now in real life, people probably won't change the table a lot, so you can get away
with that.  It is not like PostgreSQL itself is totally free of such problems: collations
definitions are considered immutable, yet change with OS upgrades, and modifications
to the files underlying a full-text search dictionary can lead to index corruption too.

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.

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.

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.

Yours,
Laurenz Albe