pg_upgrade fails due to lack of dependencies.

[Jeevan Chalke]

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 Manager*
> *Product 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/20250515/56408f00/attachment.htm>