Thread

1. pg_upgrade ability to create extension from scripts

   Regina Obe <lr@pcorp.us> — 2025-05-16T22:18:52Z

   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
   
   
   
   
   

2. Re: pg_upgrade ability to create extension from scripts

   Andreas Karlsson <andreas@proxel.se> — 2025-05-17T17:46:23Z

   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
   
   
   
   
   

3. RE: pg_upgrade ability to create extension from scripts

   Regina Obe <lr@pcorp.us> — 2025-05-17T17:58:37Z

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

4. Re: pg_upgrade ability to create extension from scripts

   Andreas Karlsson <andreas@proxel.se> — 2025-05-17T18:09:55Z

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