Message-ID: <396f2fb636248f329c152c6eca189f4348fec726.camel@cybertec.at>
Subject: Re: pg_upgrade failure due to dependencies
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Nikhil Shetty <nikhil.dba04@gmail.com>, Pgsql-admin
	 <pgsql-admin@lists.postgresql.org>
Date: Tue, 01 Jul 2025 11:37:06 +0200
In-Reply-To: 
 <CAFpL5Vw+EK4UN6mHXRqZU+WLyFiSLqPiDAZnA3VX65tNFpyPyA@mail.gmail.com>
References: 
	<CAFpL5Vw+EK4UN6mHXRqZU+WLyFiSLqPiDAZnA3VX65tNFpyPyA@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2 (3.56.2-1.fc42) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/396f2fb636248f329c152c6eca189f4348fec726.camel%40cybertec.at>
Precedence: bulk

On Tue, 2025-07-01 at 11:23 +0530, Nikhil Shetty wrote:
> I was trying an upgrade from=C2=A0PostgreSQL 13 and PostGIS 3.1.2 TO Post=
greSQL 15 and PostGIS 3.4.2 and got below error
>=20
> 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.spat=
ial_ref_sys" does not exist
> LINE 1: ...LECT proj4text, auth_name, auth_srid, srtext FROM public.spa..=
.
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0^
> QUERY:  SELECT proj4text, auth_name, auth_srid, srtext FROM public.spatia=
l_ref_sys WHERE srid =3D 3857 LIMIT 1
> Command was:
>=20
>=20
> Table Structure=20
>=20
> CREATE TABLE table1 (
> =C2=A0=C2=A0=C2=A0=C2=A0"id1" numeric NOT NULL,
> =C2=A0=C2=A0=C2=A0=C2=A0"geom" "public"."geometry"(Geometry,4326),
> =C2=A0=C2=A0=C2=A0=C2=A0"geom_3857" "public"."geometry"(Geometry,3857) GE=
NERATED ALWAYS AS ("public"."st_transform"("public"."st_intersection"("geom=
", "public"."st_transform"("public"."st_tileenvelope"(0, 0, 0), 4326)), 385=
7)) STORED
> );
>=20
> The st_transform function used in above table definition uses=C2=A0public=
.spatial_ref_sys to check the SRID
> but since public.spatial_ref_sys is not restored yet, the table creation =
for table1 fails
>=20
> 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 r=
estores public.spatial_ref_sys
> first, another problem here is that it is checking for a record in the 'p=
ublic.spatial_ref_sys' table
> which does not exist and would be populated during the upgrade link.
>=20
> 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 t=
ext, 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=3D$3;$function$

Anything that selects from a table may not be marked IMMUTABLE, since the c=
ontents of the table
can change.  In your case, the table did not even exist.
Moreover, PostgreSQL cannot check dependencies, since the function was defi=
ned 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