Downloads
Home   >   mailing lists

Re: pg_upgrade failure due to dependencies - Mailing list pgsql-admin

From Nikhil Shetty
Subject Re: pg_upgrade failure due to dependencies
Date
Msg-id CAFpL5VwgA2ae3c3pvTy=-PKfpa-BxmbrmF+ryux2HdJkhRPxOA@mail.gmail.com
Whole thread Raw
In response to pg_upgrade failure due to dependencies  (Nikhil Shetty <nikhil.dba04@gmail.com>)
Responses Re: pg_upgrade failure due to dependencies
List pgsql-admin
Tree view
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 <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 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

pgsql-admin by date:

Previous
From: Laurenz Albe
Date:
Subject: Re: pg_upgrade failure due to dependencies
Next
From: Nikhil Shetty
Date:
Subject: Re: pg_upgrade failure due to dependencies