Re: pg_upgrade failure due to dependencies

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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
>