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 1uWdhq-00GljI-7o for pgsql-admin@arkaria.postgresql.org; Tue, 01 Jul 2025 16:19:38 +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 1uWdho-006ivz-Ar for pgsql-admin@arkaria.postgresql.org; Tue, 01 Jul 2025 16:19:36 +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 1uWdhn-006ivl-Nq for pgsql-admin@lists.postgresql.org; Tue, 01 Jul 2025 16:19:36 +0000 Received: from mail-ej1-x634.google.com ([2a00:1450:4864:20::634]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uWdhm-004ykI-1d for pgsql-admin@lists.postgresql.org; Tue, 01 Jul 2025 16:19:35 +0000 Received: by mail-ej1-x634.google.com with SMTP id a640c23a62f3a-ad56cbc7b07so606683366b.0 for ; Tue, 01 Jul 2025 09:19:34 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1751386773; x=1751991573; 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=TxkfrxIkO0RnaWqSL6zZn7LIy2n3j3rM//hkzsP0FkI=; b=nlNp+umxCjHPL5wX/rlWh0Aaz5iBNyWOqoktyTBeWQjjONCbDR8AohaCVM6xeVqhw8 jAJ3ciyO3oJ90MCxdxvUiYY7VduDKcLhibbazZXfry9ppWVrV6P3tm9h7tqhUVQxAOPL ow6vMoH3nycQdm0AE0UU/GLYSYmT9wAhe/9ZlBhzuouUyhO+oMRlYiZ2uTnWk7s7z0TI mT2HZ6/d49TdClS+kr8/oqPiXAYkQO/uU72HF3BAiE3MC58XDNXNH+u1Exo3rWJK7gH/ Cc/pGk9LajtQ3lIfNe36ORNW/qvnLkBrraXnwAqnJTRLJswxhS1EVDJkmhFA4P125xi+ DFvQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1751386773; x=1751991573; 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=TxkfrxIkO0RnaWqSL6zZn7LIy2n3j3rM//hkzsP0FkI=; b=Sb30EO5/b6MA8nGmsqyvIQ2/T3Xz3kxtH4vaEbMFpy3tp2auAzBZVV+ceHW3tFaQBK hO3IMKATNKKS8b19KEa5lU+gnADH1n91RNX6d3kITyz5py7tT/LscZ7HhxTSlrVdievz 3LgzLx7FTkVvtDwhhmdw0rM/PZGHLVEUOq+NkeCwflch16ryjpGQxHviz8xab1XFo3lC R2QcMfsxMJjiKxMjXOMT0Flh1lyR5ybfHNdxCX/jiiXnTN0q9xU5LBWFG9brTkiOtHGZ ycA3EIe3uzlgT5LdYIeUQKeoPOoU6gTjB70/N0ARXE351WryvbQ7M5s5V2uM62DH3EQp mL5w== X-Forwarded-Encrypted: i=1; AJvYcCWut9I08VFYyVoLWdTP6hf6UL6GlIMZ3ZBMypVvwNn36i2NfEutG6+8bf9IbtQ/Ha8tG9CS4PKwD07hag==@lists.postgresql.org X-Gm-Message-State: AOJu0YxuZOtaMKfbQGQNr8UbZEQm0LMheDvylFAVT15iJakXJBSWBeQM Sy3gF3QblsqCmiV0Y3fXBoJxivPNlT4HUXDOVnvjUPyRVGFcRBfwDPzzxm1kV9bXimuOCIX/HCs RiM7Pvp06/T8gb85ONzg3Tphu7R4P9PSdxNy6 X-Gm-Gg: ASbGncvMerT0I39RBvT0iEliNlP2ngykk4S2YXIGmtCcJKURy9xwB6BqdSnh8xU6/J0 YtacovKpT6xhc4F12GkFgua4+LVv4SKKWApTrCiMS5nlM5KlyzaGWBpIVrJ4nXpYPztXFB7TEX3 +NS4/eyCjXDJ2DYiVnIOG7TIYcKlSLW22L8yA5FV60yw61e9iCm6o= X-Google-Smtp-Source: AGHT+IFNZeemBpKRU+KwGSqsxEfahmTm0M5Fte59NXJbkybC54e7U3imqOJtuYIjkoBi+XXgIC7ZgR5xP9OOtTyW02Y= X-Received: by 2002:a17:906:99c4:b0:ae3:a81a:4796 with SMTP id a640c23a62f3a-ae3a81a4bbbmr470148966b.1.1751386772567; Tue, 01 Jul 2025 09:19:32 -0700 (PDT) MIME-Version: 1.0 References: <396f2fb636248f329c152c6eca189f4348fec726.camel@cybertec.at> <3165866.1751383802@sss.pgh.pa.us> In-Reply-To: <3165866.1751383802@sss.pgh.pa.us> From: Nikhil Shetty Date: Tue, 1 Jul 2025 21:49:20 +0530 X-Gm-Features: Ac12FXzb8q6TDo_WbW_yOwZG0WLXZSsv4-0Kacv8jD198tNYy12BeOVJGoDQlyI Message-ID: Subject: Re: pg_upgrade failure due to dependencies To: Tom Lane Cc: Laurenz Albe , Pgsql-admin Content-Type: multipart/alternative; boundary="000000000000717ce40638e082e9" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000717ce40638e082e9 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Yes both the extension and function are created by the extension but when restoring, extension and associated functions or tables are created separately and in different order . Extension (postgis) grep -wn postgis dbdump.list ---------------------------------------- 80:7; 3079 42851433 EXTENSION - *postgis* 81:7870; 0 0 COMMENT - EXTENSION "*postgis*" Function (st_transform) used by Extension grep -wn st_transform dbdump.list ---------------------------------------- 2180:984; 1255 42851770 FUNCTION public *st_transform*("public"."geometry", integer) postgres 2181:8593; 0 0 COMMENT public FUNCTION "*st_transform*"("public"."geometry"= , integer) postgres 2182:985; 1255 42851771 FUNCTION public *st_transform*("public"."geometry", "text") postgres Table (spatial_ref_sys) used by Extension grep -wn spatial_ref_sys dbdump.list ---------------------------------------- 3373:541; 1259 42851740 TABLE public *spatial_ref_sys* postgres 3374:9127; 0 0 ACL public TABLE "*spatial_ref_sys*" postgres Table that uses the extension table grep -wn table1 dbdump.list ---------------------------------------- 3183:516; 1259 39789310 TABLE grand table1 db1 3184:9020; 0 0 ACL grand TABLE "table1" db1 As seen above from the line number (in green), 'table1' is created earlier than table 'spatial_ref_sys' and it fails to create because of dependency. As suggested by Jeevan in a separate thread, I tried to add this dependency in pg_depend but the restore from pg_upgrade still fails because the table 'spatial_ref_sys' will be empty. Workaround in pg_depend: INSERT INTO pg_depend VALUES ( 'pg_catalog.pg_type'::regclass::oid, 'public.geometry'::regtype::oid, 0, 'pg_catalog.pg_class'::regclass::oid, 'public.spatial_ref_sys'::regclass:: oid, 0, 'n'); Error: pg_restore: from TOC entry 231; 1259 64892012 TABLE table1 postgres pg_restore: error: could not execute query: ERROR: Cannot find SRID (3857) in spatial_ref_sys Thanks, Nikhil On Tue, Jul 1, 2025 at 9:00=E2=80=AFPM Tom Lane wrote: > Laurenz Albe writes: > > 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: 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 > > > The PostGIS people must be misinformed. > > The fault is clearly theirs for marking the function > st_transform(geometry, text, integer) as IMMUTABLE: > > That's clearly pretty risky, but I don't understand the context here. > pg_dump always restores extensions first. Surely both this function > and the spatial_ref_sys table would be created by the PostGIS > extension(s)? > > regards, tom lane > --000000000000717ce40638e082e9 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Yes both= the extension and function are created by the extension but when restoring= , extension and associated functions or tables are created separately and i= n different order .

=
Extension (postgis)

grep -wn post= gis dbdump.list=C2=A0

----------------------------------------

80:= 7; 3079 42851433= EXTENSION - postgis= =C2=A0

81:7870; 0 0 COMMENT - EXTENSION "= ;postgis"=C2=A0<= /span>


=


Function (st_transform) used by Extension


=

grep -wn st_tra= nsform dbdump.list

= ----------------------------------------

2180:= 984; 1255 42851770 FUNCTION public st_transform("public"= ;."geometry", integer) postgres

2181:8593;= 0 0 COMMENT public FUNCTION "st_transform"("publi= c"."geometry", integer)=C2=A0postgres

2182:<= span style=3D"font-variant-ligatures:no-common-ligatures">985; 1255 4285177= 1 FUNCTION public st_transform("public"."geometry&= quot;, "text")=C2=A0postgres



Table (spatial_ref_sy= s) used by Extension


grep -wn spatial_ref_sys dbdump.list<= /p>

----------------------------------------

3373:541; 1259 42851740 TABLE public=C2=A0spa= tial_ref_sys=C2=A0postgres

3374:9127; 0 0 ACL public TABLE "spatial_ref_sys"=C2=A0postgres

Table that uses the extension table=C2=A0


<= p style=3D"margin:0px;font-size:13px;line-height:normal;font-size-adjust:no= ne;font-kerning:auto;font-variant-alternates:normal;font-variant-ligatures:= normal;font-variant-numeric:normal;font-variant-east-asian:normal;font-feat= ure-settings:normal;color:rgb(0,0,0)">grep -wn table1 dbdu= mp.list

------------------= ----------------------

3183:516; 1259 3= 9789310 TABLE grand=C2=A0table1=C2=A0db1

3184:9020; 0 0 ACL= grand=C2=A0TABLE "table1"=C2=A0db1


As seen abo= ve from the line number (in green), 'table1' is created earlier tha= n table 'spatial_ref_sys' and it fails to create because of depende= ncy.


As suggested by Jeevan in a separate thread, I trie= d to add this dependency in pg_depend but the restore from pg_upgrade still= fails because the table 'spatial_ref_sys' will be empty.


Workaround in pg_depend:


INSERT INTO pg_depend VALUES = (
'pg_catalog.pg_type&= #39;::regclass::oid, 'public.geometry'::regtype:= :oid, 0,
'p= g_catalog.pg_class'::regclass::oid, 'public.spatial_ref= _sys'::regclass::oid,= 0,
'n');

Error:


pg_restore: from TOC entry 231; 1259 64892012 TABLE=C2=A0table1=C2=A0postgres

pg_restore: error: could not execute query: ERROR:=C2=A0 Cannot find SRID (3857)= in spatial_ref_sys


Thanks,

Nikhil


On Tue, Jul 1, 2025 at 9:00=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Laurenz Albe <laurenz.albe@cybertec.at> writes:
> 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 PostgreSQL 15 and PostGIS 3.4.2 and got below error
>> pg_restore: error: could not execute query: ERROR:=C2=A0 relation = "public.spatial_ref_sys" does not exist
>> LINE 1: ...LECT proj4text, auth_name, auth_srid, srtext FROM publi= c.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:=C2=A0 SELECT proj4text, auth_name, auth_srid, srtext FROM p= ublic.spatial_ref_sys WHERE srid =3D 3857 LIMIT 1

> The PostGIS people must be misinformed.
> The fault is clearly theirs for marking the function st_transform(geom= etry, text, integer) as IMMUTABLE:

That's clearly pretty risky, but I don't understand the context her= e.
pg_dump always restores extensions first.=C2=A0 Surely both this function and the spatial_ref_sys table would be created by the PostGIS
extension(s)?

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 regards, tom lane
--000000000000717ce40638e082e9--