pg_upgrade fails due to lack of dependencies.

Fri May 16 03:58:29 PDT 2025

Thank you, Regina

You’re right -- this dependency alone doesn't fully resolve the issue.

We did receive a complaint about missing data in the spatial_ref_sys table,
with the error:
ERROR: Cannot find SRID (4283) in spatial_ref_sys.

By tweaking pg_dump, we managed to dump data for this table *before* any
user tables accessed it. However, we later encountered a new issue:
ERROR: relation "public.spatial_ref_sys" does not exist.

As a PostgreSQL developer, this new error came as a surprise. The table in
question is part of an extension, and that extension does create it. Upon
further investigation, I discovered that the issue lies in the sort order
used by pg_dump when dumping extension and user tables, the order isn't
always correct. Although pg_dump and pg_restore are generally smart enough
to account for object dependencies, based on entries in the pg_depend
catalog, this case seems to expose a gap in that logic.

Specifically, since both spatial_ref_sys and geography are part of the same
extension and have a dependency relationship, this relationship is *not*
reflected in the pg_depend table. If it were, pg_dump would ensure the
table is dumped before the dependent type, avoiding this issue. This is
what I’ve suggested as a fix.

We encountered this problem while using the latest server versions for
PostgreSQL v11, v13, and v16, along with PostGIS versions 3.2.4 and 3.4.3.

Thanks

On Thu, May 15, 2025 at 10:03 PM <lr at pcorp.us> wrote:

> 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> 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
>
> --
>
>
>
>
>
> *Jeevan Chalke*
>
> *Principal Engineer, Engineering ManagerProduct Development*
>
> *enterprisedb.com <https://www.enterprisedb.com>*
>
>
>
>
> --
>
>
>
>
>
> *Jeevan Chalke*
>
> *Principal Engineer, Engineering ManagerProduct Development*
>
> *enterprisedb.com <https://www.enterprisedb.com>*
>

-- 

*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/20250516/003de541/attachment.htm>