pg_upgrade fails due to lack of dependencies.

Nikhil Shetty nikhil.dba04 at gmail.com
Mon Jun 30 22:56:56 PDT 2025


Thank you Regina, Paul. Let me check this with the postgres community.


On Mon, Jun 30, 2025 at 11:18 PM Paul Ramsey <pramsey at cleverelephant.ca>
wrote:

>
>
> > On Jun 30, 2025, at 10:44 AM, Regina Obe <lr at pcorp.us> wrote:
> >
> > I’m still confused how having a dependency between spatial_ref_sys and
> geography would guarantee that the spatial_ref_sys data will get loaded
> before anything that references it is created.
> >  Ultimately you’ll run into the issue spatial_ref_sys doesn’t have
> record for 4326 or 3857.
> >  To fix the issue that jeevanchalke had raised, I think Paul decided the
> cleaner way is just to get rid of the longlat validation.  But that won’t
> fix your issue because you are doing an ST_Transform.
> >  Like I said a dependency on a structure just guarantees the structure
> has to exist.
>
> So your concern is that even with a dep between st_transform and
> spatial_ref_sys, it’s entirely possible that pg_restore will just restore
> the empty table, and the problem will still occur?
>
> >  I think this solution is frankly ugly  even if it consistently works,
> cause then we’d have to apply the same crazy dependency to any function in
> postgis extension that uses that table, which is crazy.  The problem should
> be fixed in PostgreSQL proper.
> >    From: Nikhil Shetty <nikhil.dba04 at gmail.com>
> > Sent: Monday, June 30, 2025 1:19 PM
> > To: Regina Obe <lr at pcorp.us>
> > Cc: Jeevan Chalke <jeevan.chalke at enterprisedb.com>;
> postgis-devel at lists.osgeo.org
> > Subject: Re: pg_upgrade fails due to lack of dependencies.
> >  Hi Regina,
> >  Is it possible to add dependency that Jeevan mentioned to postgis so
> that pg_dump can verify the dependency during upgrade and proceed with
> restore accordingly?
> >  Thanks,
> > Nikhil
> >  On Mon, Jun 30, 2025 at 7:05 PM Regina Obe <lr at pcorp.us> wrote:
> >>
> >>
> >> Okay so this is just related to a pg_upgrade custom restore, so the
> same issue as Jeevan raised.
> >>  Pg_upgrade restores don’t build extensions from scripts like user
> initiated restores do, they build naked extensions, and then reload what
> the db has in them from the backup.
> >>  And I think the order is does these is out of order.
> >>  So still a pg_upgrade issue here.
> >>  From: Nikhil Shetty <nikhil.dba04 at gmail.com>
> >> Sent: Monday, June 30, 2025 9:30 AM
> >> To: Regina Obe <lr at pcorp.us>
> >> Cc: Jeevan Chalke <jeevan.chalke at enterprisedb.com>;
> postgis-devel at lists.osgeo.org
> >> Subject: Re: pg_upgrade fails due to lack of dependencies.
> >>  Hi Regina,
> >>  Below is the command that was run by pg_upgrade
> >>  "/usr/pgsql-15/bin/pg_restore" --host /tmp --port 5302 --username
> postgres --create --exit-on-error --verbose --dbname template1
> "/data/pg/13/pg_upgrade_dump_16403.custom"
> >>  Thanks,
> >> Nikhil
> >>   On Mon, Jun 30, 2025 at 6:27 PM Regina Obe <lr at pcorp.us> wrote:
> >>>
> >>>
> >>> Yes ST_Transform refers to spatial_ref_sys too.
> >>>  This one is a bit odd though I thought during regular pg_resstore
> that CREATE EXTENSION postgis is one of the first steps that happens, so
> this should never happen.
> >>>  What commands are you using for pg_restore?
> >>>  From: Nikhil Shetty <nikhil.dba04 at gmail.com>
> >>> Sent: Monday, June 30, 2025 6:14 AM
> >>> To: lr at pcorp.us
> >>> Cc: Jeevan Chalke <jeevan.chalke at enterprisedb.com>;
> postgis-devel at lists.osgeo.org
> >>> Subject: Re: pg_upgrade fails due to lack of dependencies.
> >>>  Hi All,
> >>>  We also faced a similar issue during pg_upgrade. Table
> spatial_ref_sys is not created and when pg_restore creates a table
> referring tospatial_ref_sys, it will error out.
> >>>  I am upgrading PostgreSQL 13 and PostGIS 3.1.2 TO PostgreSQL 15 and
> PostGIS 3.4.2
> >>>  pg_restore: creating TABLE "table1"
> >>> pg_restore: while PROCESSING TOC:
> >>> pg_restore: from TOC entry 551; 1259 39789310 TABLE table1 db1
> >>> 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
> >>> Command was:
> >>>  CREATE TABLE table1 (
> >>>     "id1" numeric NOT NULL,
> >>>     "geom" "public"."geometry"(Geometry,4326),
> >>>     "geom_3857" "public"."geometry"(Geometry,3857) GENERATED ALWAYS AS
> ("public"."st_transform"("public"."st_intersection"("geom",
> "public"."st_transform"("public"."st_tileenvelope"(0, 0, 0), 4326)), 3857))
> STORED
> >>> );
> >>>  IS st_transform function is referring to 'spatial_ref_sys'?.
> >>>  Thanks,
> >>> Nikhil
> >>>    On Sat, May 17, 2025 at 12:35 AM <lr at pcorp.us> wrote:
> >>>>
> >>>>
> >>>> Okay seems like an issue with pg_upgrade frankly that it doesn’t also
> load data in an extension it is restoring from the migrated cluster and how
> the heck it doesn’t even load the structure before any user data seems like
> real bug.
> >>>>  As mentioned I think the reason we never see this issue is because
> most people use 4326 for geography and that I suspect is hard-coded in our
> system to be never checked against spatial_ref_sys cause it’s the default.
> >>>>  I’ve ticketed issue here -
> https://trac.osgeo.org/postgis/ticket/5899
> >>>>  Paul,
> >>>>  You think there is some way we could just hard-code longlat srids in
> PostGIS code (or check proj sqlite db now that we require newer proj
> anyway) to address, so there is no dependency with spatial_ref_sys when the
> code is confirming the srid is a longlat projection.
> >>>> I’m assuming that’s why it’s checking spatial_ref_sys in the first
> place Only if it can’t find it in our list would it then resort to
> interrogating spatial_ref_sys?
> >>>>  Thanks,
> >>>> Regina
> >>>>  From: Jeevan Chalke <jeevan.chalke at enterprisedb.com>
> >>>> Sent: Friday, May 16, 2025 6:58 AM
> >>>> To: lr at pcorp.us
> >>>> Cc: postgis-devel at lists.osgeo.org
> >>>> Subject: Re: pg_upgrade fails due to lack of dependencies.
> >>>>  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_dumpwhen 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 Manager
> >>>>>> Product Development
> >>>>>>
> >>>>>> enterprisedb.com
> >>>>>
> >>>>>
> >>>>>  --   Jeevan Chalke
> >>>>> Principal Engineer, Engineering Manager
> >>>>> Product Development
> >>>>>
> >>>>> enterprisedb.com
> >>>>
> >>>>
> >>>>  --   Jeevan Chalke
> >>>> Principal Engineer, Engineering Manager
> >>>> Product Development
> >>>>
> >>>> enterprisedb.com
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20250701/69687823/attachment-0001.htm>


More information about the postgis-devel mailing list