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