> > On Thu, Dec 15, 2022 at 08:04:22AM -0500, Regina Obe wrote: > > > On Tue, Nov 22, 2022 at 11:24:19PM -0500, Regina Obe wrote: > > > > > > > If an extension is required by another extension and that required > > > > extension schema is referenced in the extension scripts using the > > > > @extschema:extensionname@ syntax, then ideally we should prevent > > > > the required extension from being relocatable. This would prevent > > > > a user from accidentally moving the required extension, thus > > > > breaking the dependent extensions. > > > > > > > > I didn't add that feature cause I wasn't sure if it was > > > > overstepping the bounds of what should be done, or if we leave it > > > > up to the user to just know better. > > > > > > An alternative would be to forbid using @extschema:extensionname@ to > > > reference relocatable extensions. DBA can toggle relocatability of > > > an extension to allow it to be referenced. > > > > That would be hard to do in a DbaaS setup and not many users know they > > can fiddle with extension control files. > > Plus those would get overwritten with upgrades. > > Wouldn't this also be the case if you override relocatability ? > Case: > > - Install fuzzystrmatch, marked as relocatable > - Install ext2 depending on the former, which is them marked > non-relocatable > - Upgrade database -> fuzzystrmatch becomes relocatable again > - Change fuzzystrmatch schema BREAKING ext2 >
Somewhat. It would be an issue if someone does ALTER EXTENSION fuzzystrmatch UPDATE; And ALTER EXTENSION fuzzystrmatch SET SCHEMA a_different_schema; Otherwise the relocatability of an already installed extension wouldn't change even during upgrade. I haven't checked pg_upgrade, but I suspect it wouldn't change there either. It's my understanding that once an extension is installed, it's relocatable status is recorded in the pg_extension table. So it doesn't matter at that point what the control file says. However if someone does update the extension, then yes it would look at the control file and make it updatable again. I just tested this fiddling with postgis extension and moving it and then upgrading. UPDATE pg_extension SET extrelocatable = true where extname = 'postgis'; ALTER EXTENSION postgis SET schema postgis; ALTER EXTENSION postgis UPDATE; e.g. if the above is already at latest version, get notice NOTICE: version "3.3.2" of extension "postgis" is already installed (and the extension is still relocatable) -- if the extension can be upgraded ALTER EXTENSION postgis UPDATE; -- no longer relocatable (because postgis control file has relocatable = false) But honestly I don't expect this to be a huge issue, more of just an extra safety block. Not a bullet-proof safety block though. > Allowing to relocate a dependency of other extensions using the > @extschema@ syntax is very dangerous. > > I've seen that PostgreSQL itself doesn't even bother to replace @extschema@ > IF the extension using it doesn't mark itself as non-relocatable. For consistency > this patch should basically refuse to expand @extschema:fuzzystrmatch@ if > "fuzzystrmatch" extension is relocatable. > > Changing the current behaviour of PostgreSQL could be proposed but I don't > think it's to be done in this thread ? > > So my suggestion is to start consistent (do not expand if referenced extension > is relocatable). > > > --strk; I don't agree. That would make this patch of not much use. For example lets revisit my postgis_tiger_geocoder which is a good bulk of the reason why I want this. I use indexes that use postgis_tiger_geocoder functions that call fuzzystrmatch which causes pg_restore to break on reload and other issues, because I'm not explicitly referencing the function schema. With your proposal now I got to demand the postgresql project to make fuzzystrmatch not relocatable so I can use this feature. It is so rare for people to go around moving the locations of their extensions once set, that I honestly don't think the ALTER EXTENSION .. UPDATE hole is a huge deal. I'd be more annoyed having to beg an extension provider to mark their extension as not relocatable so that I could explicitly reference the location of their extensions. And even then - think about it. I ask extension provider to make their extension schema relocatable. They do, but some people are using a version before they marked it as schema relocatable. So now if I change my code, users can't install my extension, cause they are using a version before it was schema relocatable and I'm using the new syntax. What would be more bullet-proof is having an extra column in pg_extension or adding an extra array element to pg_extension.extcondition[] that allows you to say "Hey, don't allow this to be relocatable cause other extensions depend on it that have explicitly referenced the schema." Thanks, Regina