pg_upgrade fails due to lack of dependencies.

[Jeevan Chalke]

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 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/20250514/8aecc29e/attachment.htm>