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