RE: pg_upgrade ability to create extension from scripts

> 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 is part 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 issues and 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 older version
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 recreated in 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.