public inbox for [email protected]
help / color / mirror / Atom feedFrom: Nikhil Shetty <[email protected]>
To: Tom Lane <[email protected]>
Cc: Laurenz Albe <[email protected]>
Cc: Pgsql-admin <[email protected]>
Subject: Re: pg_upgrade failure due to dependencies
Date: Tue, 1 Jul 2025 21:49:20 +0530
Message-ID: <CAFpL5VwgA2ae3c3pvTy=-PKfpa-BxmbrmF+ryux2HdJkhRPxOA@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAFpL5Vw+EK4UN6mHXRqZU+WLyFiSLqPiDAZnA3VX65tNFpyPyA@mail.gmail.com>
<[email protected]>
<[email protected]>
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 PM Tom Lane <[email protected]> wrote:
> Laurenz Albe <[email protected]> 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 = 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
>
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: pg_upgrade failure due to dependencies
In-Reply-To: <CAFpL5VwgA2ae3c3pvTy=-PKfpa-BxmbrmF+ryux2HdJkhRPxOA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox