pg_upgrade fails due to lack of dependencies.

[Jeevan Chalke]

On Wed, Jul 2, 2025 at 3:03 AM Regina Obe <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.
>
>

-- 



*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/3a5ef181/attachment.htm>