Обсуждение: Database owner installable modules patch
Hi all Here is a patch that provides an initial implementation of the module idea that was kicked around over the last few days. While there certainly wasn't consensus on list, enough people seemed interested in the idea of database-owner-installable modules that I thought it was worth having a play with. The general idea, to recap, is to have modules, whether included in the distribution a la contrib or installed separately, installed under a directory such as $pkglib_dir/modules/foo. A typical module directory might contain: - foo.so/foo.dll - install.sql - uninstall.sql - foo.conf - some-other-file-needed-by-foo-module.dat The module would be installed on the system, but the necessary scripts to install it in a particular database have not been run. In particular, the modules would not usually be install in template1. Database owners themselves can then opt to enable a particular installed module in their own database - they do not have to hassle a sysadmin to do it for them. Features of the patch: - A database owner can issue the command "INSTALL MODULE foo", and pgsql will look for a $pkglib_dir/modules/foo/install.sql file to run, and run it. - The install script can do pretty much anything - the user is treated as the superuser for the duration of the script. The main and obvious usage is to create C language functions required by the module. - An entry is created in a new pg_module catalog. This is mainly to guard against someone trying to install a module twice at this point, but it may have other uses in the future (see below). - "UNINSTALL MODULE foo" looks for and executes $pkglib_dir/modules/foo/uninstall.sql and cleans up the catalog. Here is a list of things that are either still to do before I'd consider it worthy of inclusion (should the general approach be considered acceptable), or which I'd like some guidance on: - Currently the script is executed in one big SPI_execute call, and so errors and NOTICEs print out the entire script as context. I'm not sure how to break it up without writing a full parser - we must have something available in the backend to break a string up into multiple statements to execute, but I'm not sure where to look. Also, is there a better way to do this than SPI? - I've hacked in a bit of an end-run around permissions checks to make the current user look like a super-user while a module script is running. Is there a better way to do this? - I can't create C language functions from dlls under the modules dir. I'd like to be able to specify 'modules/foo/foo' as the library name, but the backend sees a slash and decides that must mean the path is absolute. I see two ways to fix this: change the existing code in dfmgr.c to *really* check for absolute/relative paths rather than the current hack, or I could stick in a special case for when it starts with "modules/". I thought I'd get some guidance on-list. Do people think that sticking the dll in with other resources for the module under $pkglib_dir is a bad thing? (I think having everything in one place is a very good thing myself).Is the existing check written the way it is for a particular reason, or is it just "good enough"? - It would be nice to create the empty modules dir when we install pgsql, but while I suppose hacking a Makefile to install it is the way to go, I'm not sure which one would be appropriate. - Hack pgxs to install stuff into a modules dir if we give it some appropriate flag. - I'd like to add pg_depend entries for stuff installed by the module on the pd_module entry, so that you can't drop stuff required by the module without uninstalling the module itself. There would have to be either a function or more syntax to allow a script to do that, or some sort of module descriptor that let the backend do it itself. - Once the issue of loading a dll from inside the module's directory is done, I'd like to look for an e.g. module_install() function inside there, and execute that rather than the install.sql if found. Ditto for uninstall. - Maybe a basic mechanism to allow a module to require another one. Even just a "SELECT require_module('bar')" call at the top of a script. - It would be nice to suppress NOTICEs when installing stuff - the user almost certainly doesn't care. - Pick up config files in module directories, so that a module can install and pick up config for itself rather than getting the sysadmin to hack the global custom_variable_classes setting. - Should plperl etc be done as modules so that their config can live independently as well? And to allow modules to "require" them? Some other nice to haves for some point in the future: - Have some sort of install module privilege, rather than just a check for database ownership - Allow looking for modules under some module path for e.g. /usr/local module installs - Convert existing contrib to modules where appropriate :) - I really have no idea what happens if non-ascii characters are in an install script at the moment. What happens if funky characters are passed to an SPI_execute call? Very far future: - Have pgxs auto-generate rpm .spec files for modules, plus e.g. .deb equivalent, wix files for windows etc etc. - Versioning on modules? General discussion: I see this work as orthogonal to both the CPAN-style distribution / repository discussion, and the fate-of-contrib discussion. If contrib modules are reworked as this sort of module and left in the distribution, they'll be easier to use and more likely to be installed than they are now. If, as Tom suggested, they're mostly moved out of the pgsql source tree and to e.g. pgfoundry or whatever, this mechanism should make them (and every other extension out there) easy to package, install and enable in a user's database. Similarly, I don't personally care for a CPAN-style distribution setup - on my preferred unix-like system I use yum and on windows I prefer installers. Nonetheless, a standardised system to install and enable/disable modules acts as an enabler for all packaging and distribution systems. I'm not sure about the command names - there was already a tendency in the recent discussion to mix the notion of installation of code on the filesystem versus installation into a particular user's database. The convention for doing stuff in a db is CREATE/DROP, but CREATE MODULE doesn't feel right to me, just as I don't really like CREATE LANGUAGE. How about ENABLE/DISABLE MODULE? Makes it clear that the module is installed, it's just not available in this database yet. Thoughts? Anyway, discussion and feedback hereby solicited! Cheers Tom
Вложения
"Tom Dunstan" <pgsql@tomd.cc> writes: > - I'd like to add pg_depend entries for stuff installed by the module > on the pd_module entry, so that you can't drop stuff required by the > module without uninstalling the module itself. There would have to be > either a function or more syntax to allow a script to do that, or some > sort of module descriptor that let the backend do it itself. > > - Once the issue of loading a dll from inside the module's directory > is done, I'd like to look for an e.g. module_install() function inside > there, and execute that rather than the install.sql if found. Ditto > for uninstall. I wonder if there's much of a use case for any statements aside from CREATE statements. If we restrict it to CREATE statements we could hack things to create pg_depend entries automatically. In which case we wouldn't need an uninstall script at all. The hacks to do this seem pretty dirty but on the other hand the idea of having modules consist of a bunch of objects rather than arbitrary SQL actually seems cleaner and more robust. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services!
On Mon, Apr 7, 2008 at 3:59 AM, Gregory Stark <stark@enterprisedb.com> wrote: > I wonder if there's much of a use case for any statements aside from CREATE > statements. If we restrict it to CREATE statements we could hack things to > create pg_depend entries automatically. In which case we wouldn't need an > uninstall script at all. Well, the example that got me interested in this stuff originally was trying to make pl/java easier to install. It does a bunch of CREATEs... and some GRANTs. Plus ISTM that a pretty common case might be to create a table for some reference data and then fill it with default values. Also, I just had a look at the postgis install script, which at the very least seems to update an opclass entry after creating it. None of that suggests that an uninstaller script would be needed if we understood the deps well enough, but only allowing creates for installs seems a bit restrictive. One thing that's nice about arbitrary sql for install / uninstall is that module authors can test it outside the context of doing an actual module installation - they just execute their scripts. > The hacks to do this seem pretty dirty but on the other hand the idea of > having modules consist of a bunch of objects rather than arbitrary SQL > actually seems cleaner and more robust. It *does* seem cleaner for the examples that I've looked at. Are they totally representative though? Not sure. It also implies a bunch more work to create stuff, as we need to understand what's going on so as to create those pg_depend entries. I'm receptive to the idea of uninstall simply attempting to drop anything related to the module in pg_depend in the correct order. I can't think of anything created by a module that we couldn't represent there, and it's a nice way of ensuring that an uninstall script cleans up properly. Cheers Tom
On Mon, Apr 7, 2008 at 11:46 AM, Tom Dunstan <pgsql@tomd.cc> wrote: > On Mon, Apr 7, 2008 at 3:59 AM, Gregory Stark <stark@enterprisedb.com> wrote: > > I wonder if there's much of a use case for any statements aside from CREATE > > statements. If we restrict it to CREATE statements we could hack things to > > create pg_depend entries automatically. In which case we wouldn't need an > > uninstall script at all. > > The hacks to do this seem pretty dirty but on the other hand the idea of > > having modules consist of a bunch of objects rather than arbitrary SQL > > actually seems cleaner and more robust. > > It *does* seem cleaner for the examples that I've looked at. Are they > totally representative though? Not sure. It also implies a bunch more > work to create stuff, as we need to understand what's going on so as > to create those pg_depend entries. This has been bouncing around in my head a bit. I was picturing the module code itself having to understand all the CREATE statements in order to set up the dependencies... but perhaps an easier way would simply be to have the create statements themselves insert a pg_depend entry when they're done, if they detect that we're currently installing a module. There's already a flag for that that the superuser code looks at in the patch. Maybe you were ahead of me, and this was the hack that you were referring to. :) I tend to hate global flags like that because they leave weird non-obvious dependencies across the codebase, but perhaps it's the best way to do it in this case. It would mean hacking every create command in the system to understand it, though. :( Cheers Tom
Sorry to keep replying to myself, but part of the point of doing a patch was to force myself (and whoever else is interested to examine stuff that comes up... On Mon, Apr 7, 2008 at 11:46 AM, Tom Dunstan <pgsql@tomd.cc> wrote: > None of that suggests that an uninstaller script would be needed if we > understood the deps well enough, but only allowing creates for > installs seems a bit restrictive. OK, I found an example that does NOT fit the "just drop all dependencies" scenario, but that I would still like to support. I just had a look at the postgis pl/java support, and its install does stuff like "SELECT sqlj.install_jar('file://${PWD}/postgis_pljava.jar', 'postgis_pljava_jar', false);" and "SELECT sqlj.add_type_mapping('geometry', 'org.postgis.pljava.PLJGeometry');". There's no way we can deal with that sort of thing automatically, so we'll have to support uninstall scripts regardless. The question then becomes: is it worth trying to do stuff automatically if we provide a manual method anyway? I think the answer is probably yes, because having pgsql clean up automatically for the vast majority of cases is a good thing. If it's only exotic cases that need a manual uninstall script, why force one on everyone else? Cheers Tom
"Tom Dunstan" <pgsql@tomd.cc> writes: > OK, I found an example that does NOT fit the "just drop all > dependencies" scenario, but that I would still like to support. I just > had a look at the postgis pl/java support, and its install does stuff > like "SELECT sqlj.install_jar('file://${PWD}/postgis_pljava.jar', > 'postgis_pljava_jar', false);" and "SELECT > sqlj.add_type_mapping('geometry', 'org.postgis.pljava.PLJGeometry');". > There's no way we can deal with that sort of thing automatically, so > we'll have to support uninstall scripts regardless. Well, that just begs the question of what those commands actually *do*. It seems not unlikely that they'd be inserting data into tables that would belong to the module, in which case an uninstall that dropped the table would be fine. I still like the idea of uninstall being just a "DROP MODULE" with subsequent cascading. If you want to argue that that isn't sufficient you really need a pretty convincing example why not. regards, tom lane
On Mon, Apr 7, 2008 at 7:55 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Tom Dunstan" <pgsql@tomd.cc> writes: > > OK, I found an example that does NOT fit the "just drop all > > dependencies" scenario, but that I would still like to support. I just > > had a look at the postgis pl/java support, and its install does stuff > > like "SELECT sqlj.install_jar('file://${PWD}/postgis_pljava.jar', > > 'postgis_pljava_jar', false);" and "SELECT > > sqlj.add_type_mapping('geometry', 'org.postgis.pljava.PLJGeometry');". > > There's no way we can deal with that sort of thing automatically, so > > we'll have to support uninstall scripts regardless. > > Well, that just begs the question of what those commands actually *do*. > It seems not unlikely that they'd be inserting data into tables that > would belong to the module, in which case an uninstall that dropped > the table would be fine. Those tables belong to a *different* module, though. I'm picturing three modules here: pljava, postgis, and a postgis-pljava support module that requires the first two, since it should be possible to install postgis without requiring pljava. The above stuff was from the install script of the postgis-pljava code, but inserted data into tables owned by pljava. Cheers Tom
On Sun, Apr 06, 2008 at 11:29:50PM +0100, Gregory Stark wrote: > I wonder if there's much of a use case for any statements aside from > CREATE statements. Yes. Some modules could have COPY or equivalent in them, as they could easily contain data. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
Where are we on this? --------------------------------------------------------------------------- Tom Dunstan wrote: > Hi all > > Here is a patch that provides an initial implementation of the module > idea that was kicked around over the last few days. While there > certainly wasn't consensus on list, enough people seemed interested in > the idea of database-owner-installable modules that I thought it was > worth having a play with. > > The general idea, to recap, is to have modules, whether included in > the distribution a la contrib or installed separately, installed under > a directory such as $pkglib_dir/modules/foo. A typical module > directory might contain: > - foo.so/foo.dll > - install.sql > - uninstall.sql > - foo.conf > - some-other-file-needed-by-foo-module.dat > The module would be installed on the system, but the necessary scripts > to install it in a particular database have not been run. In > particular, the modules would not usually be install in template1. > Database owners themselves can then opt to enable a particular > installed module in their own database - they do not have to hassle a > sysadmin to do it for them. > > > Features of the patch: > - A database owner can issue the command "INSTALL MODULE foo", and > pgsql will look for a $pkglib_dir/modules/foo/install.sql file to run, > and run it. > > - The install script can do pretty much anything - the user is > treated as the superuser for the duration of the script. The main and > obvious usage is to create C language functions required by the > module. > > - An entry is created in a new pg_module catalog. This is mainly to > guard against someone trying to install a module twice at this point, > but it may have other uses in the future (see below). > > - "UNINSTALL MODULE foo" looks for and executes > $pkglib_dir/modules/foo/uninstall.sql and cleans up the catalog. > > > > Here is a list of things that are either still to do before I'd > consider it worthy of inclusion (should the general approach be > considered acceptable), or which I'd like some guidance on: > > - Currently the script is executed in one big SPI_execute call, and > so errors and NOTICEs print out the entire script as context. I'm not > sure how to break it up without writing a full parser - we must have > something available in the backend to break a string up into multiple > statements to execute, but I'm not sure where to look. Also, is there > a better way to do this than SPI? > > - I've hacked in a bit of an end-run around permissions checks to > make the current user look like a super-user while a module script is > running. Is there a better way to do this? > > - I can't create C language functions from dlls under the modules > dir. I'd like to be able to specify 'modules/foo/foo' as the library > name, but the backend sees a slash and decides that must mean the path > is absolute. I see two ways to fix this: change the existing code in > dfmgr.c to *really* check for absolute/relative paths rather than the > current hack, or I could stick in a special case for when it starts > with "modules/". I thought I'd get some guidance on-list. Do people > think that sticking the dll in with other resources for the module > under $pkglib_dir is a bad thing? (I think having everything in one > place is a very good thing myself).Is the existing check written the > way it is for a particular reason, or is it just "good enough"? > > - It would be nice to create the empty modules dir when we install > pgsql, but while I suppose hacking a Makefile to install it is the way > to go, I'm not sure which one would be appropriate. > > - Hack pgxs to install stuff into a modules dir if we give it some > appropriate flag. > > - I'd like to add pg_depend entries for stuff installed by the module > on the pd_module entry, so that you can't drop stuff required by the > module without uninstalling the module itself. There would have to be > either a function or more syntax to allow a script to do that, or some > sort of module descriptor that let the backend do it itself. > > - Once the issue of loading a dll from inside the module's directory > is done, I'd like to look for an e.g. module_install() function inside > there, and execute that rather than the install.sql if found. Ditto > for uninstall. > > - Maybe a basic mechanism to allow a module to require another one. > Even just a "SELECT require_module('bar')" call at the top of a > script. > > - It would be nice to suppress NOTICEs when installing stuff - the > user almost certainly doesn't care. > > - Pick up config files in module directories, so that a module can > install and pick up config for itself rather than getting the sysadmin > to hack the global custom_variable_classes setting. > > - Should plperl etc be done as modules so that their config can live > independently as well? And to allow modules to "require" them? > > > Some other nice to haves for some point in the future: > > - Have some sort of install module privilege, rather than just a > check for database ownership > - Allow looking for modules under some module path for e.g. > /usr/local module installs > - Convert existing contrib to modules where appropriate :) > - I really have no idea what happens if non-ascii characters are in > an install script at the moment. What happens if funky characters are > passed to an SPI_execute call? > > Very far future: > - Have pgxs auto-generate rpm .spec files for modules, plus e.g. .deb > equivalent, wix files for windows etc etc. > - Versioning on modules? > > > General discussion: > > I see this work as orthogonal to both the CPAN-style distribution / > repository discussion, and the fate-of-contrib discussion. If contrib > modules are reworked as this sort of module and left in the > distribution, they'll be easier to use and more likely to be installed > than they are now. If, as Tom suggested, they're mostly moved out of > the pgsql source tree and to e.g. pgfoundry or whatever, this > mechanism should make them (and every other extension out there) easy > to package, install and enable in a user's database. > > Similarly, I don't personally care for a CPAN-style distribution setup > - on my preferred unix-like system I use yum and on windows I prefer > installers. Nonetheless, a standardised system to install and > enable/disable modules acts as an enabler for all packaging and > distribution systems. > > I'm not sure about the command names - there was already a tendency in > the recent discussion to mix the notion of installation of code on the > filesystem versus installation into a particular user's database. The > convention for doing stuff in a db is CREATE/DROP, but CREATE MODULE > doesn't feel right to me, just as I don't really like CREATE LANGUAGE. > How about ENABLE/DISABLE MODULE? Makes it clear that the module is > installed, it's just not available in this database yet. Thoughts? > > > Anyway, discussion and feedback hereby solicited! > > Cheers > > Tom [ Attachment, skipping... ] > > -- > Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-patches -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
On Sat, May 10, 2008 at 11:02 AM, Bruce Momjian <bruce@momjian.us> wrote: > > Where are we on this? I haven't had time to do any work since the original patch. That patch was fairly basic - it just ran install / uninstall scripts and created catalog entries, and introduced some slightly exotic syntax to do it (INSTALL/UNINSTALL vs CREATE/DROP). The next version is intended to handle dependencies, which should make uninstallation straight forward for most cases. I was intending to revert the syntax creativity and make the commands CREATE/DROP too. I'll get a bit of time to look at both this and the enum patch this week. Cheers Tom
Added to TODO: * Implement a module capability for loading /contrib-style extensions http://archives.postgresql.org/pgsql-patches/2008-04/msg00164.php --------------------------------------------------------------------------- Tom Dunstan wrote: > Hi all > > Here is a patch that provides an initial implementation of the module > idea that was kicked around over the last few days. While there > certainly wasn't consensus on list, enough people seemed interested in > the idea of database-owner-installable modules that I thought it was > worth having a play with. > > The general idea, to recap, is to have modules, whether included in > the distribution a la contrib or installed separately, installed under > a directory such as $pkglib_dir/modules/foo. A typical module > directory might contain: > - foo.so/foo.dll > - install.sql > - uninstall.sql > - foo.conf > - some-other-file-needed-by-foo-module.dat > The module would be installed on the system, but the necessary scripts > to install it in a particular database have not been run. In > particular, the modules would not usually be install in template1. > Database owners themselves can then opt to enable a particular > installed module in their own database - they do not have to hassle a > sysadmin to do it for them. > > > Features of the patch: > - A database owner can issue the command "INSTALL MODULE foo", and > pgsql will look for a $pkglib_dir/modules/foo/install.sql file to run, > and run it. > > - The install script can do pretty much anything - the user is > treated as the superuser for the duration of the script. The main and > obvious usage is to create C language functions required by the > module. > > - An entry is created in a new pg_module catalog. This is mainly to > guard against someone trying to install a module twice at this point, > but it may have other uses in the future (see below). > > - "UNINSTALL MODULE foo" looks for and executes > $pkglib_dir/modules/foo/uninstall.sql and cleans up the catalog. > > > > Here is a list of things that are either still to do before I'd > consider it worthy of inclusion (should the general approach be > considered acceptable), or which I'd like some guidance on: > > - Currently the script is executed in one big SPI_execute call, and > so errors and NOTICEs print out the entire script as context. I'm not > sure how to break it up without writing a full parser - we must have > something available in the backend to break a string up into multiple > statements to execute, but I'm not sure where to look. Also, is there > a better way to do this than SPI? > > - I've hacked in a bit of an end-run around permissions checks to > make the current user look like a super-user while a module script is > running. Is there a better way to do this? > > - I can't create C language functions from dlls under the modules > dir. I'd like to be able to specify 'modules/foo/foo' as the library > name, but the backend sees a slash and decides that must mean the path > is absolute. I see two ways to fix this: change the existing code in > dfmgr.c to *really* check for absolute/relative paths rather than the > current hack, or I could stick in a special case for when it starts > with "modules/". I thought I'd get some guidance on-list. Do people > think that sticking the dll in with other resources for the module > under $pkglib_dir is a bad thing? (I think having everything in one > place is a very good thing myself).Is the existing check written the > way it is for a particular reason, or is it just "good enough"? > > - It would be nice to create the empty modules dir when we install > pgsql, but while I suppose hacking a Makefile to install it is the way > to go, I'm not sure which one would be appropriate. > > - Hack pgxs to install stuff into a modules dir if we give it some > appropriate flag. > > - I'd like to add pg_depend entries for stuff installed by the module > on the pd_module entry, so that you can't drop stuff required by the > module without uninstalling the module itself. There would have to be > either a function or more syntax to allow a script to do that, or some > sort of module descriptor that let the backend do it itself. > > - Once the issue of loading a dll from inside the module's directory > is done, I'd like to look for an e.g. module_install() function inside > there, and execute that rather than the install.sql if found. Ditto > for uninstall. > > - Maybe a basic mechanism to allow a module to require another one. > Even just a "SELECT require_module('bar')" call at the top of a > script. > > - It would be nice to suppress NOTICEs when installing stuff - the > user almost certainly doesn't care. > > - Pick up config files in module directories, so that a module can > install and pick up config for itself rather than getting the sysadmin > to hack the global custom_variable_classes setting. > > - Should plperl etc be done as modules so that their config can live > independently as well? And to allow modules to "require" them? > > > Some other nice to haves for some point in the future: > > - Have some sort of install module privilege, rather than just a > check for database ownership > - Allow looking for modules under some module path for e.g. > /usr/local module installs > - Convert existing contrib to modules where appropriate :) > - I really have no idea what happens if non-ascii characters are in > an install script at the moment. What happens if funky characters are > passed to an SPI_execute call? > > Very far future: > - Have pgxs auto-generate rpm .spec files for modules, plus e.g. .deb > equivalent, wix files for windows etc etc. > - Versioning on modules? > > > General discussion: > > I see this work as orthogonal to both the CPAN-style distribution / > repository discussion, and the fate-of-contrib discussion. If contrib > modules are reworked as this sort of module and left in the > distribution, they'll be easier to use and more likely to be installed > than they are now. If, as Tom suggested, they're mostly moved out of > the pgsql source tree and to e.g. pgfoundry or whatever, this > mechanism should make them (and every other extension out there) easy > to package, install and enable in a user's database. > > Similarly, I don't personally care for a CPAN-style distribution setup > - on my preferred unix-like system I use yum and on windows I prefer > installers. Nonetheless, a standardised system to install and > enable/disable modules acts as an enabler for all packaging and > distribution systems. > > I'm not sure about the command names - there was already a tendency in > the recent discussion to mix the notion of installation of code on the > filesystem versus installation into a particular user's database. The > convention for doing stuff in a db is CREATE/DROP, but CREATE MODULE > doesn't feel right to me, just as I don't really like CREATE LANGUAGE. > How about ENABLE/DISABLE MODULE? Makes it clear that the module is > installed, it's just not available in this database yet. Thoughts? > > > Anyway, discussion and feedback hereby solicited! > > Cheers > > Tom [ Attachment, skipping... ] > > -- > Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-patches -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +