Re: pg_upgrade failure due to dependencies

We have not reported a bug but we have asked this in postgis mailing list,
since they suggested checking if this can be fixed in pg_upgrade, I posted
here.

On Tue, Jul 1, 2025 at 3:07 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
wrote:

> On Tue, 2025-07-01 at 11:23 +0530, Nikhil Shetty wrote:
> > I was trying an upgrade from PostgreSQL 13 and PostGIS 3.1.2 TO
> PostgreSQL 15 and PostGIS 3.4.2 and got below error
> >
> > pg_restore: creating TABLE "table1"
> > pg_restore: while PROCESSING TOC:
> > pg_restore: from TOC entry 551; 1259 39789310 TABLE table1 db1
> > pg_restore: error: could not execute query: ERROR: relation
> "public.spatial_ref_sys" does not exist
> > LINE 1: ...LECT proj4text, auth_name, auth_srid, srtext FROM
> public.spa...
> > ^
> > QUERY: SELECT proj4text, auth_name, auth_srid, srtext FROM
> public.spatial_ref_sys WHERE srid = 3857 LIMIT 1
> > Command was:
> >
> >
> > Table Structure
> >
> > CREATE TABLE table1 (
> > "id1" numeric NOT NULL,
> > "geom" "public"."geometry"(Geometry,4326),
> > "geom_3857" "public"."geometry"(Geometry,3857) GENERATED ALWAYS AS
> ("public"."st_transform"("public"."st_intersection"("geom",
> "public"."st_transform"("public"."st_tileenvelope"(0, 0, 0), 4326)), 3857))
> STORED
> > );
> >
> > The st_transform function used in above table definition
> uses public.spatial_ref_sys to check the SRID
> > but since public.spatial_ref_sys is not restored yet, the table creation
> for table1 fails
> >
> > I checked with the PostGIS community and they suggested this needs to be
> fixed in pg_upgrade because
> > it is not checking the dependencies in this particular case. Even if it
> restores public.spatial_ref_sys
> > first, another problem here is that it is checking for a record in the
> 'public.spatial_ref_sys' table
> > which does not exist and would be populated during the upgrade link.
> >
> > I am adding this in the community to check if there is a way to fix this
> kind of problem during upgrade?
>
> The PostGIS people must be misinformed.
> The fault is clearly theirs for marking the function
> st_transform(geometry, text, integer) as IMMUTABLE:
>
> \sf st_transform(geometry, text, integer)
>
> CREATE OR REPLACE FUNCTION public.st_transform(geom geometry, from_proj
> text, to_srid integer)
> RETURNS geometry
> LANGUAGE sql
> IMMUTABLE PARALLEL SAFE STRICT COST 5000
> AS $function$SELECT public.postgis_transform_geometry($1, $2, proj4text,
> $3)
> FROM public.spatial_ref_sys WHERE srid=$3;$function$
>
> Anything that selects from a table may not be marked IMMUTABLE, since the
> contents of the table
> can change. In your case, the table did not even exist.
> Moreover, PostgreSQL cannot check dependencies, since the function was
> defined using the "old"
> style for SQL functions, where the function body is just a string. If
> they had used the standard
> conforming new style, PostgreSQL would try to trach dependencies. Not
> sure if that would have been
> enough to avoid the problem, but clearly better.
>
> Did you open a bug report on https://trac.osgeo.org/postgis ?
>
> Yours,
> Laurenz Albe
>