Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uWdjO-00Gm2k-12 for pgsql-admin@arkaria.postgresql.org; Tue, 01 Jul 2025 16:21:14 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1uWdjM-006mLK-5I for pgsql-admin@arkaria.postgresql.org; Tue, 01 Jul 2025 16:21:12 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uWdjL-006mLA-Md for pgsql-admin@lists.postgresql.org; Tue, 01 Jul 2025 16:21:12 +0000 Received: from mail-ed1-x535.google.com ([2a00:1450:4864:20::535]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uWdjK-004yl0-0a for pgsql-admin@lists.postgresql.org; Tue, 01 Jul 2025 16:21:11 +0000 Received: by mail-ed1-x535.google.com with SMTP id 4fb4d7f45d1cf-607ec30df2bso12172242a12.1 for ; Tue, 01 Jul 2025 09:21:10 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1751386869; x=1751991669; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=hy7zMBqbcOECqGRiDjHRfgEZfo0lTnKIqMl/AOvXNp8=; b=NidSzdpH6nVlGbxog2+ml+b7nGjUiQRjbK963ieXz8cCCQmyPdBXRq/VcSz60Nm23i 9UiwrmB/0yhZNWEvmaJ+g64Xs6eyktIQtUSSXy/zL89ZPfGoFdNZa8kMhLS0HlmuQB07 cT89CtnekIyL4wuC967tPt/zNfHtESt14rWOO4tWWBIIsKnfwzMZyw4K2CzSJ8LyuME7 OURP/I0bAsaT8B1RaboGzPAWmFsEeBqNQwtTH5JJ6KeJGeXN/ZEY3M/B4pcX/CV81cBe iOt7G8fXLbV6BKW9C+WDHPrxs/wOEI/LdTojVsgGc50LAV+KyqQOHdLM9/zat70u+43g G78w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1751386869; x=1751991669; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=hy7zMBqbcOECqGRiDjHRfgEZfo0lTnKIqMl/AOvXNp8=; b=um8K7oTSGbo3ATsilB9JSO7AUgQrfl5Q2B6pnV1mwGLiK3z0FIGkLZ+T/AK0vUVIWv /TU0zAGqLw+13s7akZ92L+CwHnHyHhIwseZK3hGKRZmIQZmL7WM9cMlEbzRObaFRQL2C 5u7uEDLjzxn4zCAQK0d/LEYZeUssyvWXXarTXP1iKUCJ0m5Pwe1Mp2ktNGXK5pjQk7eW HJ6Lyqmquli0pMSNrU2eqwjL1ZWIvl/Vx9NrdkfDk0bfCR/tKAQyh06hjBPbT0/u+ALq xzX37jntpvd/SyDu9unr1SbRL1menuLvRd66EO+q2HGxKFyJEZXZeDyFfVlY+79C+fYT dCcw== X-Gm-Message-State: AOJu0YwEKfgsjWZVY32H9IbzAZBndMTbW3g2IOK0g1BIypDRJbe/lYrM SRpNybdH/ymDrg4lwEEVjjEtouuiHU/hHTma6vMfoWfzIv5OcGTZha4G41pGHrwqLPQSKf3hyAd 7A4LbbM32zDIXMCwd6Rrej07GPnFt0FU= X-Gm-Gg: ASbGncufS1o6pLJ5Igdx71dL27lQoTh/o/vb+frrOyM1SAUgFLA97IO67SxUqnTgy/D JL51R64hYgtdNK36wH92gYbFgsUYNJP18i56m5inNn6L+Q2MYt7sB7StowEhp0J9g/EmfZkIsOs BBm9+Dndf+hTwiouRfqsrFNOISUiY+rUWayFFgnpY+5S2XrC7+4u8= X-Google-Smtp-Source: AGHT+IHXmQrPYJlySyQB59nGJV8ydU8b5FNgFm7V+sEC3epYVj9KegAPwnNOWRSVo6zlL6CSQY7/9yf0qiTxgHJXu90= X-Received: by 2002:a17:907:1c23:b0:ae3:a240:7ad4 with SMTP id a640c23a62f3a-ae3a240d9d0mr735167366b.4.1751386868367; Tue, 01 Jul 2025 09:21:08 -0700 (PDT) MIME-Version: 1.0 References: <396f2fb636248f329c152c6eca189f4348fec726.camel@cybertec.at> In-Reply-To: <396f2fb636248f329c152c6eca189f4348fec726.camel@cybertec.at> From: Nikhil Shetty Date: Tue, 1 Jul 2025 21:50:56 +0530 X-Gm-Features: Ac12FXy3SfRjLBobEY0TukRi4yv58EeLApomVggNcEUT6kKbrLUUX1GLo-IIneg Message-ID: Subject: Re: pg_upgrade failure due to dependencies To: Laurenz Albe Cc: Pgsql-admin Content-Type: multipart/alternative; boundary="0000000000002745b20638e088ce" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000002745b20638e088ce Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=E2=80=AFPM Laurenz Albe 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 =3D 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 creatio= n > for table1 fails > > > > I checked with the PostGIS community and they suggested this needs to b= e > 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 thi= s > 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=3D$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 > --0000000000002745b20638e088ce Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
We have not reported a bug but we have asked this in postg= is mailing list, since they suggested checking if this can be fixed in pg_u= pgrade, I posted here.

On Tue, Jul 1, 2025 at 3:07=E2= =80=AFPM Laurenz Albe <laure= nz.albe@cybertec.at> wrote:
On Tue, 2025-0= 7-01 at 11:23 +0530, Nikhil Shetty wrote:
> I was trying an upgrade from=C2=A0PostgreSQL 13 and PostGIS 3.1.2 TO P= ostgreSQL 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:=C2=A0 relation &quo= t;public.spatial_ref_sys" does not exist
> LINE 1: ...LECT proj4text, auth_name, auth_srid, srtext FROM public.sp= a...
> =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:=C2=A0 SELECT proj4text, auth_name, auth_srid, srtext FROM publi= c.spatial_ref_sys WHERE srid =3D 3857 LIMIT 1
> Command was:
>
>
> Table Structure
>
> 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"."geom= etry"(Geometry,4326),
> =C2=A0=C2=A0=C2=A0=C2=A0"geom_3857" "public"."= ;geometry"(Geometry,3857) GENERATED ALWAYS AS ("public".&quo= t;st_transform"("public"."st_intersection"("g= eom", "public"."st_transform"("public".&= quot;st_tileenvelope"(0, 0, 0), 4326)), 3857)) STORED
> );
>
> The st_transform function used in above table definition uses=C2=A0pub= lic.spatial_ref_sys to check the SRID
> but since public.spatial_ref_sys is not restored yet, the table creati= on 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 i= t 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 th= is 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:

=C2=A0 \sf st_transform(geometry, text, integer)

=C2=A0 CREATE OR REPLACE FUNCTION public.st_transform(geom geometry, from_p= roj text, to_srid integer)
=C2=A0 =C2=A0RETURNS geometry
=C2=A0 =C2=A0LANGUAGE sql
=C2=A0 =C2=A0IMMUTABLE PARALLEL SAFE STRICT COST 5000
=C2=A0 AS $function$SELECT public.postgis_transform_geometry($1, $2, proj4t= ext, $3)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 FROM public.spatial_ref_sys WHERE srid=3D$3;$fu= nction$

Anything that selects from a table may not be marked IMMUTABLE, since the c= ontents of the table
can change.=C2=A0 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.=C2=A0 If= they had used the standard
conforming new style, PostgreSQL would try to trach dependencies.=C2=A0 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
--0000000000002745b20638e088ce--