pg_upgrade fails due to lack of dependencies.

Wed Jul 2 08:12:48 PDT 2025

Okay so I see no point in adding this dependency as it still requires people to do work arounds with loading the data and people can already fuss with pg_depend if they have superuser rights.

From: Jeevan Chalke <jeevan.chalke at enterprisedb.com> 
Sent: Wednesday, July 2, 2025 11:06 AM
To: Regina Obe <lr at pcorp.us>
Cc: Laurenz Albe <laurenz.albe at cybertec.at>; Paul Ramsey <pramsey at cleverelephant.ca>; PostGIS Development Discussion <postgis-devel at lists.osgeo.org>
Subject: Re: pg_upgrade fails due to lack of dependencies.

On Wed, Jul 2, 2025 at 8:20 PM Regina Obe <lr at pcorp.us <mailto:lr at pcorp.us> > wrote:

Jeevan,

So you are saying adding a dependency of spatial_ref_sys for geometry and geography should be sufficient to have the spatial_ref_sys data loaded for those before any user data with said columns?

No. Data won't be loaded at that point by just adding dependencies.

However, the  spatial_ref_sys  table will be forced to dump (CREATE TABLE) ahead of geography and geography types if there are dependencies present in the pg_depend.

This will get rid of the upgrade errors that complain 

"ERROR:  relation "public.spatial_ref_sys" does not exist".

---

Somehow we managed to dump the data immediately after its creation for our customer (as a workaround). Fixing that at the PostGIS side might be difficult (might be doable at the Postgres side). But this dependency issue can only be fixed at the PostGIS side.

Thanks

I can’t think of a case where ST_Transform would be used in a table definition where there wouldn’t be a geometry/geography column definition nearby.  So I suppose we can put in PostGIS 3.6.

I wouldn’t want to backport it without extensive testing though.

From: Jeevan Chalke <jeevan.chalke at enterprisedb.com <mailto:jeevan.chalke at enterprisedb.com> > 
Sent: Wednesday, July 2, 2025 5:01 AM
To: Regina Obe <lr at pcorp.us <mailto:lr at pcorp.us> >
Cc: Laurenz Albe <laurenz.albe at cybertec.at <mailto:laurenz.albe at cybertec.at> >; Paul Ramsey <pramsey at cleverelephant.ca <mailto:pramsey at cleverelephant.ca> >; PostGIS Development Discussion <postgis-devel at lists.osgeo.org <mailto:postgis-devel at lists.osgeo.org> >
Subject: Re: pg_upgrade fails due to lack of dependencies.

On Wed, Jul 2, 2025 at 3:03 AM Regina Obe <lr at pcorp.us <mailto:lr at pcorp.us> > wrote:

> > Yes that's a no go for 2 reasons
> >
> > 1) As you saw with the example mentioned, many people build indexes etc
> >    around this function.  If it were marked stable it would prevent usage
> >    in indexes etc.  The medicine would be way worse than the sickness.
> 
> Even if the sickness is index corruption and inability to upgrade and restore a
> dump?
> 

YES.  It's only pg_upgrade people have issues with cause it doesn't load all extension objects first like regular restores do. If you can't restore these with pg_upgrade, just restore them the old fashioned way.
Changing the functions to stable, means people can't create such indexes in the first place or use ST_Transform in their computed columns.
So it's the same as saying "If you don't want to feel pain, don't do that" vs. "We just won't let you do that"

Usually people that do these kinds of things are fairly clever, they are smart enough to find a way out of it without us snatching the gun from their hand and they'll scream if we try to grab their footgun from them.

> > 2) We tried making it stable like 10 years ago, and that made the function
> >    super unbearably slow.  Maybe things have changed since then, but the
> >    case 1 is still an issue.
> 
> Could that have been problems around inlining that git solved with optimizer
> support functions in v12?

We could revisit to see that - could be.
> 
> > > However, that dependency does not extend to the data in
> > > "spatial_ref_sys", so using the function in a generated column is still a
> problem.
> >
> > Yap my main concern.  But in case of pg_upgrade, since the data is
> > overlaid, at what point does it record, there is data in spatial_ref_sys?
> 
> I didn't scrutinize the code, but the table data seem to get dumped in
> alphabetical order of "schemaname.tablename".
> 

Okay so then a dependency wouldn't be guaranteed to help them.

Not exactly.

While it's true that objects are initially considered for dumping in alphabetical order, as mentioned earlier, pg_dump is smart enough to analyze pg_depend and reorder dependent objects to ensure they are dumped and restored in the correct order.

A simpler solution would be install postgis in a dedicated schema like I always tell people to do. In addition make sure that schema is alphabetically ahead of user data 😊

> > The other discussion we had had, which I know Paul wants to throw up
> > everytime I mention it is to get rid of spatial_ref_sys and read
> > directly from the proj.db sqlite db that has the definitions.
> > Most of the time postgis just uses the proj.db def anyway.
> 
> How IMMUTABLE is that?
> 

For most users it's pretty IMMUTABLE but could change with proj upgrades, but it really doesn't matter is spatial_ref_sys is there or not
cause ultimately if the definition of an srid is in that proj.db that is what will be used.  Since proj.db is a separate file altogether, not part of PostgreSQL
it won't be screwed up by pg_upgrade not finding data.  Probably the same story as your collations/fulltext dictionaries etc.

I think Paul had issue with 

Sometimes people want to make changes, and they have no access to file system if they are on DbaaS

Proj itself does have a mechanism of users keeping their own custom projections separate from the ones proj ships but that feature probably isn't available if you are on a DbaaS
Which a large majority of users are these days.

-- 

  <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> 

-- 

  <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/20250702/f5261def/attachment-0001.htm>