public inbox for [email protected]help / color / mirror / Atom feed
Ability to reference other extensions by schema in extension scripts 8+ messages / 3 participants [nested] [flat]
* Ability to reference other extensions by schema in extension scripts @ 2022-11-10 02:43 Regina Obe <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: Regina Obe @ 2022-11-10 02:43 UTC (permalink / raw) To: pgsql-hackers I think I had brought this up a while ago, but I forget what the opinion was on the matter. PostGIS has a number of extensions that rely on it. For the extensions that are packaged with PostGIS, we force them all into the same schema except for the postgis_topology and postgis_tiger_geocoder extensions which are already installed in dedicated schemas. This makes it impossible for postgis_topology and postgis_tiger_geocoder to schema qualify their use of postgis. Other extensions like pgRouting, h3-pg, mobilitydb have similar issues. My proposal is this. If you think it's a good enough idea I can work up a patch for this. Extensions currently are allowed to specify a requires in the control file. I propose to use this information, to allow replacement of phrases @extschema_nameofextension@ as a variable, where nameofextension has to be one of the extensions listed in the requires. The extension plumbing will then use this information to look up the schema that the current required extensions are installed in, and replace the variables with the schema of where the dependent extension is installed. Does anyone see any issue with this idea. Thanks, Regina ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Ability to reference other extensions by schema in extension scripts @ 2022-11-10 03:49 Tom Lane <[email protected]> parent: Regina Obe <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: Tom Lane @ 2022-11-10 03:49 UTC (permalink / raw) To: Regina Obe <[email protected]>; +Cc: pgsql-hackers "Regina Obe" <[email protected]> writes: > My proposal is this. If you think it's a good enough idea I can work up a > patch for this. > Extensions currently are allowed to specify a requires in the control file. > I propose to use this information, to allow replacement of phrases > @extschema_nameofextension@ as a variable, where nameofextension has to be > one of the extensions listed in the requires. I have a distinct sense of deja vu here. I think this idea, or something isomorphic to it, was previously discussed with some other syntax details. I'm too lazy to go searching the archives right now, but I suggest that you try to find that discussion and see if the discussed syntax seems better or worse than what you mention. I think it might've been along the line of @extschema:nameofextension@, which seems like it might be superior because colon isn't a valid identifier character so there's less risk of ambiguity. regards, tom lane ^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: Ability to reference other extensions by schema in extension scripts @ 2022-11-10 04:44 Regina Obe <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: Regina Obe @ 2022-11-10 04:44 UTC (permalink / raw) To: 'Tom Lane' <[email protected]>; +Cc: pgsql-hackers > "Regina Obe" <[email protected]> writes: > > My proposal is this. If you think it's a good enough idea I can work > > up a patch for this. > > Extensions currently are allowed to specify a requires in the control file. > > I propose to use this information, to allow replacement of phrases > > @extschema_nameofextension@ as a variable, where nameofextension has > > to be one of the extensions listed in the requires. > > I have a distinct sense of deja vu here. I think this idea, or something > isomorphic to it, was previously discussed with some other syntax details. > I'm too lazy to go searching the archives right now, but I suggest that you try to > find that discussion and see if the discussed syntax seems better or worse than > what you mention. > > I think it might've been along the line of @extschema:nameofextension@, > which seems like it might be superior because colon isn't a valid identifier > character so there's less risk of ambiguity. > > regards, tom lane I found the old discussion I recalled having and Stephen had suggested using @extschema{'postgis'}@ On this thread -- https://www.postgresql.org/message-id/[email protected] .net Is that the one you remember? Thanks, Regina ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Ability to reference other extensions by schema in extension scripts @ 2022-11-10 17:37 Tom Lane <[email protected]> parent: Regina Obe <[email protected]> 0 siblings, 2 replies; 8+ messages in thread From: Tom Lane @ 2022-11-10 17:37 UTC (permalink / raw) To: Regina Obe <[email protected]>; +Cc: pgsql-hackers "Regina Obe" <[email protected]> writes: >> I have a distinct sense of deja vu here. I think this idea, or something >> isomorphic to it, was previously discussed with some other syntax details. > I found the old discussion I recalled having and Stephen had suggested using > @extschema{'postgis'}@ > On this thread -- > https://www.postgresql.org/message-id/[email protected] > Is that the one you remember? Hmmm ... no, ISTM it was considerably more recent than that. [ ...digs... ] Here we go, it was in the discussion around converting contrib SQL functions to new-style: https://www.postgresql.org/message-id/flat/3395418.1618352794%40sss.pgh.pa.us There are a few different ideas bandied around in there. Personally I still like the @extschema:extensionname@ option the best, though. regards, tom lane ^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: Ability to reference other extensions by schema in extension scripts @ 2022-11-10 18:42 Regina Obe <[email protected]> parent: Tom Lane <[email protected]> 1 sibling, 0 replies; 8+ messages in thread From: Regina Obe @ 2022-11-10 18:42 UTC (permalink / raw) To: 'Tom Lane' <[email protected]>; +Cc: pgsql-hackers > "Regina Obe" <[email protected]> writes: > >> I have a distinct sense of deja vu here. I think this idea, or > >> something isomorphic to it, was previously discussed with some other > syntax details. > > > I found the old discussion I recalled having and Stephen had suggested > > using @extschema{'postgis'}@ On this thread -- > > https://www.postgresql.org/message- > id/[email protected] > > nowman.net > > Is that the one you remember? > > Hmmm ... no, ISTM it was considerably more recent than that. > [ ...digs... ] Here we go, it was in the discussion around converting contrib SQL > functions to new-style: > > https://www.postgresql.org/message- > id/flat/3395418.1618352794%40sss.pgh.pa.us > > There are a few different ideas bandied around in there. > Personally I still like the @extschema:extensionname@ option the best, > though. > > regards, tom lane I had initially thought of a syntax that could always be used even outside of extension install as some mentioned. Like the PG_EXTENSION_SCHEMA(cube) example. Main benefit I see with that is that even if an extension is moved, all the dependent extensions that reference it would still work fine. I had dismissed that because it seemed too invasive. Seems like it would require changes to the parser and possibly add query performance overhead to resolve the schema. Not to mention the added testing required to do no harm. The other reason I dismissed it is because at least for PostGIS it would be harder to conditionally replace. The issue with PG_EXTENSION_SCHEMA(cube) is we can't support that in lower PG versions so we'd need to strip for lower versions, and that would introduce the possibility of missing PG_EXTENSION_SCHEMA(cube) vs. PG_EXTENSION_SCHEMA( cube ), not a huge deal though, but not quite as easy and precise as just stripping @extschema:extensionname@. References. With the @extschema:extensionname@, it doesn't solve all problems, but the key ones we care about like breakage of functions used in indexes, materialized views, and added security and is a little easier to strip out. I'll work on producing a patch. Thanks, Regina ^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: Ability to reference other extensions by schema in extension scripts @ 2022-11-23 04:24 Regina Obe <[email protected]> parent: Tom Lane <[email protected]> 1 sibling, 1 reply; 8+ messages in thread From: Regina Obe @ 2022-11-23 04:24 UTC (permalink / raw) To: 'Tom Lane' <[email protected]>; [email protected]; +Cc: pgsql-hackers > > "Regina Obe" <[email protected]> writes: > >> I have a distinct sense of deja vu here. I think this idea, or > >> something isomorphic to it, was previously discussed with some other > syntax details. > > > I found the old discussion I recalled having and Stephen had suggested > > using @extschema{'postgis'}@ On this thread -- > > https://www.postgresql.org/message- > id/[email protected] > > nowman.net > > Is that the one you remember? > > Hmmm ... no, ISTM it was considerably more recent than that. > [ ...digs... ] Here we go, it was in the discussion around converting contrib SQL > functions to new-style: > > https://www.postgresql.org/message- > id/flat/3395418.1618352794%40sss.pgh.pa.us > > There are a few different ideas bandied around in there. > Personally I still like the @extschema:extensionname@ option the best, > though. > > regards, tom lane Here is first version of my patch using the @extschema:extensionname@ syntax you proposed. This patch includes: 1) Changes to replace references of @extschema:extensionname@ with the schema of the required extension 2) Documentation for the feature 3) Tests for the feature. There is one issue I thought about that is not addressed by this. 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. Thanks, Regina ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Ability to reference other extensions by schema in extension scripts @ 2022-12-15 08:52 Sandro Santilli <[email protected]> parent: Regina Obe <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: Sandro Santilli @ 2022-12-15 08:52 UTC (permalink / raw) To: Regina Obe <[email protected]>; +Cc: 'Tom Lane' <[email protected]>; pgsql-hackers On Tue, Nov 22, 2022 at 11:24:19PM -0500, Regina Obe wrote: > Here is first version of my patch using the @extschema:extensionname@ syntax > you proposed. > > This patch includes: > 1) Changes to replace references of @extschema:extensionname@ with the > schema of the required extension > 2) Documentation for the feature > 3) Tests for the feature. > > There is one issue I thought about that is not addressed by this. > > 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. --strk; ^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: Ability to reference other extensions by schema in extension scripts @ 2022-12-15 13:04 Regina Obe <[email protected]> parent: Sandro Santilli <[email protected]> 0 siblings, 0 replies; 8+ messages in thread From: Regina Obe @ 2022-12-15 13:04 UTC (permalink / raw) To: [email protected]; +Cc: 'Tom Lane' <[email protected]>; pgsql-hackers > On Tue, Nov 22, 2022 at 11:24:19PM -0500, Regina Obe wrote: > > > Here is first version of my patch using the @extschema:extensionname@ > > syntax you proposed. > > > > This patch includes: > > 1) Changes to replace references of @extschema:extensionname@ with the > > schema of the required extension > > 2) Documentation for the feature > > 3) Tests for the feature. > > > > There is one issue I thought about that is not addressed by this. > > > > 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. > > --strk; 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. In my case for example I have postgis_tiger_geocoder that relies on both postgis and fuzzystrmatch. I'd rather not have to explain to users how to fiddle with the fuzzystrmatch.control file to make it not relocatable. But I don't think anyone would mind if it's forced after install because it's a rare thing for people to be moving extensions to different schemas after install. Thanks, Regina ^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2022-12-15 13:04 UTC | newest] Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2022-11-10 02:43 Ability to reference other extensions by schema in extension scripts Regina Obe <[email protected]> 2022-11-10 03:49 ` Tom Lane <[email protected]> 2022-11-10 04:44 ` Regina Obe <[email protected]> 2022-11-10 17:37 ` Tom Lane <[email protected]> 2022-11-10 18:42 ` Regina Obe <[email protected]> 2022-11-23 04:24 ` Regina Obe <[email protected]> 2022-12-15 08:52 ` Sandro Santilli <[email protected]> 2022-12-15 13:04 ` Regina Obe <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox