Thread: pg_upgrade ability to create extension from scripts

It's my understanding that right now when you run pg_upgrade it creates the
extension from what exists in the to be upgraded databases.

Is there a reason why we can't have some sort of switch option that allows
the CREATE EXTENSION from the scripts instead of what is loaded in the db.

The reason I'm asking is for a couple of reasons.

1) For projects that version their libraries, sometimes the old versioned
library does not exist in the new install and not available for the new
install.
PostGIS project largely satisfied this issue by major versioning our
extension, but of cause this would still be an issue for folks going from
PostGIS 2.5 to PostGIS 3+.

2) Yes I know this is a no-no but the postgis spatial_ref_sys table is
assumed to be an essentially static table, and people build table
constraints using ST_Transform.
Also as we observed, for some reason the postgis.spatial_ref_sys table is
sometimes not created before user data is loaded or is empty and loaded
after user data.

As discussed here -  https://trac.osgeo.org/postgis/ticket/5899  

3) People often forget to run upgrade extension scripts after doing a
pg_upgrade so are left in an incomplete upgrade state after upgrade.
Having the extension be the latest version would largely solve this.
There are other issues with run into in the past where a users old extension
can not be upgraded cleanly, we've had this recently with BRIN.

Note I'm not asking to change the default behavior, just wondering if there
are obstacles to giving users an option to install from the scripts instead
of what's currently in the database.

Thanks,
Regina

> On 5/17/25 12:18 AM, Regina Obe wrote:
> > It's my understanding that right now when you run pg_upgrade it
> > creates the extension from what exists in the to be upgraded databases.
> >
> > Is there a reason why we can't have some sort of switch option that
> > allows the CREATE EXTENSION from the scripts instead of what is loaded in
> the db.
>
> We could in theory have such a switch but I am not a fan of the idea since
> objects in the database can depend on objects in the extension, objects which
> might not exist in the newer version of the extension making the dump
> impossible to load.
>
> We would also lose other things such as permissions on the extension objects,
> but that might be something the users are willing to accept.
>
> If we are doing something like this I would rather have a switch for attempting
> to upgrade all extension to the latest version after upgrade and let the users
> get the potential errors there.
>
> Andreas

Yah that is why I was suggesting as a switch not a default option. I think the default option works fine in most cases.
But I was thinking does pg_upgrade rely at all on OIDs? I guess that might make this not doable.  I wasn't sure if that
ispart of the reason why it has to use 
the objects of the extension as they existed in the db.

There are other situations I've seen where this would have been useful.

1) In some cases we may drop a function because we introduce a replacement that takes default args.
In these cases the fact that the view definition is bound to the oid of the old function causes people quite a few of
issuesand even more so with materialized views. 
So we have to leave the old version around until they have the opportunity to drop and recreate their dependent views.

These new versions of the functions would satisfy the existing view definitions.

2) We did have a situation in PG12 days when PG system catalogs were changed, which prevented people upgrading from an
olderversion 
Of PostGIS to not be able to pg_upgrade because our geometry_columns view had reference to a removed system catalog
column.
So people were unable to pg_upgrade  without first upgrading their current version, because the view could not be
recreatedin PG12 and above with the old extension version. 

If the extension views had been recreated from the scripts, this would not have been an issue.

On 5/17/25 7:58 PM, Regina Obe wrote:
> Yah that is why I was suggesting as a switch not a default option. I think the default option works fine in most
cases.
> But I was thinking does pg_upgrade rely at all on OIDs? I guess that might make this not doable.  I wasn't sure if
thatis part of the reason why it has to use
 
> the objects of the extension as they existed in the db.

It relies on OIDs which actually does make this a non-starter. Array 
types for example include the type's OID in the data. So I cannot see 
how this even can be made work without possibly tons of ugly hacks. 
pg_upgrade does enough clever hacks.

Andreas