pg_upgrade fails due to lack of dependencies.

[lr at pcorp.us]

I think we have a tickets somewhere complaining about issues with pg_upgrade and geography, but they have always involved just data missing in spatial_ref_sys.

I found this one - https://trac.osgeo.org/postgis/ticket/4405  but these cases have always been issues with data not present in spatial_ref_sys rather than the table being missing.

 

What puzzles me is how this dependency even helps, cause that still wouldn’t guarantee data in the spatial_ref_sys table is loaded and ultimately I would assume it is checking spatial_ref_sys to make sure 4283 is a valid longlat projection and will fail if it can’t find it.

 

I thought extensions are always created before user tables so should only be an issue with the data in them and why spatial_ref_sys doesn’t even exist when you load the user table makes no sense.

 

I don’t see how putting  dependency between geography type and spatial_ref_sys would help here and I’ve never seen this particular error before.

I suppose it’s possibly most people just use 4326 which geography doesn’t need to verify cause that’s the default srid used for geography when none is specified so is probably hard-coded in our system and that might explain why we’ve never seen this issue.

 

 

 

That said, can you give us the output of your postgis versions from your PostgreSQL 11 and PostgreSQL 13

 

And also what versions of 11 and 13 you are running.

 

Thanks,

Regina

 

From: Jeevan Chalke <jeevan.chalke at enterprisedb.com> 
Sent: Thursday, May 15, 2025 9:42 AM
To: postgis-devel at lists.osgeo.org
Subject: Re: pg_upgrade fails due to lack of dependencies.

 

PostGIS hackers/developers,

 

Does this addition make sense?

 

 

On Wed, May 14, 2025 at 11:07 AM Jeevan Chalke <jeevan.chalke at enterprisedb.com <mailto:jeevan.chalke at enterprisedb.com> > wrote:

Hello,

 

While working with one of our customers, we observed that pg_upgrade is failing with PostGIS.

What is happening is (SQL attached below), the user table "prosch"."MyTab" has a column of type "public"."geography", which, in turn, references a row in public.spatial_ref_sys. While there is a recorded dependency between "procsch"."MyTab" and "public"."geography", no such dependency exists between "public"."geography" and public.spatial_ref_sys.

As a result, while the type "public"."geography" is created before the user table, spatial_ref_sys is not. Due to sorting (in pg_dump), the user table is dumped before spatial_ref_sys, leading to an error during restoration.

Since PostgreSQL does not maintain dependencies at the row level (and thus not for tables containing such rows), the extension itself should explicitly define this dependency. This would ensure that public.spatial_ref_sys is dumped before the type "public"."geography", preventing issues during dump and restore.

If we manually add the dependency in the source database, pg_upgrade will work well. The query to achieve this is:

INSERT INTO pg_depend VALUES (
  'pg_catalog.pg_type'::regclass::oid, 'public.geography'::regtype::oid, 0,
  'pg_catalog.pg_class'::regclass::oid, 'public.spatial_ref_sys'::regclass::oid, 0,
  'n');

Can this be considered a valid request and added to the extension SQL file itself?

---

Here is the SQL sequence that yields an error (shared by one of our engineers)

CREATE SCHEMA procsch;
CREATE TABLE "procsch"."MyTab" (
    "id" bigint,
    "loc" "public"."geography"(Point,4283)
);

INSERT INTO procsch.MyTab VALUES (1, ST_GeomFromText('POINT(152.138672 -30.689888)', 4283));

$ strings /PATH/bin/pg_dump | grep POSTGIS_spatial
POSTGIS_spatial_ref_sys
$

/PATH/bin/initdb -D /srv/13/data/pg_data
/PATH11/bin/pg_ctl -D /srv/11/data/pg_data -l logfile stop
/PATH/bin/pg_upgrade --check --link --old-datadir=/srv/11/data/pg_data --new-datadir=/srv/13/data/pg_data --old-bindir=/PATH11/bin --new-bindir=/PATH/bin --old-port=5432 --new-port=5434
# for real, no --check:
/PATH/bin/pg_upgrade --link --old-datadir=/srv/11/data/pg_data --new-datadir=/srv/13/data/pg_data --old-bindir=/PATH11/bin --new-bindir=/PATH/bin --old-port=5432 --new-port=5434


And it did blow up with : 


[..]
pg_restore: creating TABLE "procsch.MyTab"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 371; 1259 17425 TABLE MyTab u1
pg_restore: error: could not execute query: ERROR:  relation "public.spatial_ref_sys" does not exist
LINE 21:     "loc" "public"."geography"(Point,4283)
                   ^
QUERY:  SELECT proj4text, auth_name, auth_srid, srtext FROM public.spatial_ref_sys WHERE srid = 4283 LIMIT 1
[..]
CREATE TABLE "procsch"."MyTab" (
    "id" bigint,
    "loc" "public"."geography"(Point,4283)
);
[..]

 

 

Thanks

-- 

 

  <https://ci3.googleusercontent.com/mail-sig/AIorK4wUmLsFZCrsP0IzlqcrOBS4LG-QdOAM_CPz15Rip0P4elztKb7pS0FF_Tbb_lCOORByN9lcXUTXqe-8> 

 

Jeevan Chalke
Principal Engineer, Engineering Manager
Product Development

enterprisedb.com <https://www.enterprisedb.com> 




 

-- 

 

  <https://ci3.googleusercontent.com/mail-sig/AIorK4wUmLsFZCrsP0IzlqcrOBS4LG-QdOAM_CPz15Rip0P4elztKb7pS0FF_Tbb_lCOORByN9lcXUTXqe-8> 

 

Jeevan Chalke
Principal Engineer, Engineering Manager
Product Development

enterprisedb.com <https://www.enterprisedb.com> 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20250515/167d4a1e/attachment-0001.htm>