pg_upgrade fails due to lack of dependencies.
Regina Obe
lr at pcorp.us
Tue Jul 1 12:49:55 PDT 2025
Jeevan,
The issue I have is that geography doesn’t have a dependency on the spatial_ref_sys , it has a dependency on the actual data which can’t be expressed with postgres dependency offering.
In the case of geography, it’s not the geography type but the longlat check that needs spatial_ref_sys to exist and that is a questionable useful check anyway, so seems simpler to remove the check thus removing geography dependency on spatial_ref_sys.
Note this longlat check doesn’t kick in unless your geography srid is NOT the default (which is 4326). In your particular case, which is rare, it’s not which is why it kicks in.
In Nikhil’s case, he’s not even using geography. It’s kicking in because of the ST_Transform call. There are other functions like geography ST_Intersection, ST_Buffer that also use ST_Transform under the hood.
Now I was thinking, I presume pg_upgrade takes the definitions and overlays them on top of the data, so maybe the data does exist if the structure exists, so lying and saying both
geometry and geography have a dependency on spatial_ref_sys would fix most cases as there is sure to be present a geometry or geography definition in there.
Perhaps putting a dependency on both geography and geometry would do the trick, but still seems strange to me that pg_upgrade doesn’t fully load EXTENSION definitions before user data.
Why is that?
From: Jeevan Chalke <jeevan.chalke at enterprisedb.com>
Sent: Tuesday, July 1, 2025 2:40 AM
To: Nikhil Shetty <nikhil.dba04 at gmail.com>
Cc: Paul Ramsey <pramsey at cleverelephant.ca>; Regina Obe <lr at pcorp.us>; PostGIS Development Discussion <postgis-devel at lists.osgeo.org>
Subject: Re: pg_upgrade fails due to lack of dependencies.
Hello,
I believe it is the responsibility of the extension to record the necessary dependencies.
When a developer or feature creates an object in PostgreSQL, it is standard practice to explicitly record any dependencies at that time. Similarly, the extension should follow the same approach.
By design, PostgreSQL records the dependency between a user-defined table and the geography type. However, the server has no inherent way of knowing that the geography type itself depends on the spatial_ref_sys table. This relationship is known to the extension, and it should explicitly register that dependency.
Thanks
On Tue, Jul 1, 2025 at 11:27 AM Nikhil Shetty <nikhil.dba04 at gmail.com <mailto:nikhil.dba04 at gmail.com> > wrote:
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 <mailto:pramsey at cleverelephant.ca> > wrote:
> On Jun 30, 2025, at 10:44 AM, Regina Obe <lr at pcorp.us <mailto: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 <mailto:nikhil.dba04 at gmail.com> >
> Sent: Monday, June 30, 2025 1:19 PM
> To: Regina Obe <lr at pcorp.us <mailto:lr at pcorp.us> >
> Cc: Jeevan Chalke <jeevan.chalke at enterprisedb.com <mailto:jeevan.chalke at enterprisedb.com> >; postgis-devel at lists.osgeo.org <mailto: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 <mailto: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 <mailto:nikhil.dba04 at gmail.com> >
>> Sent: Monday, June 30, 2025 9:30 AM
>> To: Regina Obe <lr at pcorp.us <mailto:lr at pcorp.us> >
>> Cc: Jeevan Chalke <jeevan.chalke at enterprisedb.com <mailto:jeevan.chalke at enterprisedb.com> >; postgis-devel at lists.osgeo.org <mailto: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 <mailto: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 <mailto:nikhil.dba04 at gmail.com> >
>>> Sent: Monday, June 30, 2025 6:14 AM
>>> To: lr at pcorp.us <mailto:lr at pcorp.us>
>>> Cc: Jeevan Chalke <jeevan.chalke at enterprisedb.com <mailto:jeevan.chalke at enterprisedb.com> >; postgis-devel at lists.osgeo.org <mailto: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 <mailto: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 <mailto:jeevan.chalke at enterprisedb.com> >
>>>> Sent: Friday, May 16, 2025 6:58 AM
>>>> To: lr at pcorp.us <mailto:lr at pcorp.us>
>>>> Cc: postgis-devel at lists.osgeo.org <mailto: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 <mailto: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 <mailto:jeevan.chalke at enterprisedb.com> >
>>>>> Sent: Thursday, May 15, 2025 9:42 AM
>>>>> To: postgis-devel at lists.osgeo.org <mailto: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 <mailto: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 <http://enterprisedb.com>
>>>>>
>>>>>
>>>>> -- Jeevan Chalke
>>>>> Principal Engineer, Engineering Manager
>>>>> Product Development
>>>>>
>>>>> enterprisedb.com <http://enterprisedb.com>
>>>>
>>>>
>>>> -- Jeevan Chalke
>>>> Principal Engineer, Engineering Manager
>>>> Product Development
>>>>
>>>> enterprisedb.com <http://enterprisedb.com>
--
<https://ci3.googleusercontent.com/mail-sig/AIorK4wUmLsFZCrsP0IzlqcrOBS4LG-QdOAM_CPz15Rip0P4elztKb7pS0FF_Tbb_lCOORByN9lcXUTXqe-8>
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/20250701/f70fe006/attachment-0001.htm>
More information about the postgis-devel
mailing list