Обсуждение: Extensions User Design
Hi, = PostgreSQL Extensions Here's the first round of User Design about PostgreSQL Extensions. I tried to put together the ideas expressed by a lot of different people. The aim here is to first agree on the naming and the goals, then talk about what user design we propose. == name The contenders are extension, module, bundle and package. My vote is extension. The module is something else in the SQL standard, a bundle is an ok choice, a package would certainly make people think we're Oracle compatible (and we don't want to have Ada like skeleton and bodies), and extension is what PGXS is make for and what we -you-name-it- authors made. == v1.0 goals We're not trying to be feature complete on first round. * must have - dump & restore support (when upgrading a cluster or just restoring) - easy install and uninstall - support for home grown SQL/PLpgSQL only extensions in order to make life easier for in-house PG based development (you don't have to code in C to benefit from extensions) - support for "basic" modules, providing a type and its operators and indexing support, such as ip4r, hstore, temporal, prefix and many others, you name it, of even simpler things like preprepare or backports/min_update. - support for procedural languages (a priori easily covered within basic modules but I'm not sure) like plproxy, pllolcode, pllua, plscheme, plsh et al. - support for all what you find in contrib/ for 8.4 (covered already?) * would be great (target later commit fest) - versioning support with upgrade in place facility (hooks?) - supporting more than one version of the same module installed in the same time, possibly (I suppose always but...) in different schemas - custom variables? - PostGIS complete support, with user data dependancy, even if an extensible typmod system would certainly solve this problem in a better place. Maybe someone will come up with another existing extension sharing the problem and not the typmod solution? - a core team approved list of extensions (replacing contribs, maybe adding to it), where approved means code has been reviewed and the only reason why it's not in the core itself is that core team feels that it's not part of a RDBMS per-se, or feel like the code should be maintained and released separately until it gets some more field exposure... (think plproxy). * later please - CPAN or ports like infrastructure for auto downloading a more or less prepared "bundle", place it at the right place on the filesystem and install it in the database(s) of choice - complex support for ad-hoc bootstrap of uncommon modules such as pljava - dependancy graph solving and automatic installation, with depends, recommends and suggest sections and with rules/setup to choose what to pull in by default... == dump & restore We want pg_dump to issue only one line per extension, the one installing the extension in the database, see syntax. == syntax Extensions will need metadata, and after reading several proposals, what I propose here is to have a first explicit step to register the extension name and metadata, then have "basic" tools to play with it. === installing and removing an extension begin; install extension foo with search_path = foo; commit; Extensions authors are asked not to bother about search_path in their sql scripts so that it's easy for DBAs to decide where to install them. The with strange syntax is there to allow for the "install extension" command to default to, e.g., pg_extension, which won't typically be the first schema in the search_path. begin; drop extension foo [cascade]; commit; The "cascade" option is there to care about reverse depends. === creating extensions (authoring) The 'foo' extension author is meant to provide a +foo.sql+ file containing this: create extension foo with version 1.0 install [script] 'foo.install.sql' uninstall [script] 'foo.uninstall.sql' upgrade function upgrade_foo(old version, new version) [ custom_variable_classes 'a,b' configuration file 'foo.conf' ] depends on bar version 0.3 and on baz version >= 1.2; Here we suppose we have also a new datatype "version" to host the versionning information, with the associated operators. See http://packages.debian.org/sid/postgresql-8.3-debversion Doing it this way, we skip the need to provide a way of telling "next comands are meant for creating SQL objects which belongs to such extension", at the expense of forcing authors to manage upgrades to add objects. The upgrade function is mandatory, and has to return the installed version or null, meaning "please run the install script again, that's how I upgrade". The error management is to be made by means of RAISE EXCEPTION. If a specific function is to get called at install or uninstall time, it's easy enough to SELECT install_function(); from within the install script, after having defined it. To support this, internal GUCs (not exposed in postgresql.conf) will be provided and set by PG when running those scripts, named current_extension and current_extension_version. == ACLs The "bulk" ACL management of an extension's objects is pushed to the globing support project for GRANT/REVOKE, so we don't have to speak about what it'll look like here :) == OS Filesystem Interaction PostgreSQL already provides standard paths where to install extensions by means of PGXS, and distribution packagers have been able to adapt those. We should just stick with this, meaning the problem is solved. -- dim PS: using the asciidoc syntax, which allowed me have a nice HTML browsable document. Hope you don't mind, dear reader.
On Jun 23, 2009, at 10:44 AM, Dimitri Fontaine wrote: > The contenders are extension, module, bundle and package. My vote is > extension. +1 > == v1.0 goals > > We're not trying to be feature complete on first round. > > * must have > > - dump & restore support (when upgrading a cluster or just restoring) > > - easy install and uninstall > > - support for home grown SQL/PLpgSQL only extensions in order to > make life > easier for in-house PG based development (you don't have to code > in C to > benefit from extensions) It'd be nice if it supported other core languages like PL/Perl, but it's okay if it doesn't on the first round (I'd likely want to use some CPAN modules in a PL/Perl extension, anyway). > - support for "basic" modules, providing a type and its operators and > indexing support, such as ip4r, hstore, temporal, prefix and many > others, > you name it, of even simpler things like preprepare or > backports/min_update. > > - support for procedural languages (a priori easily covered within > basic > modules but I'm not sure) like plproxy, pllolcode, pllua, > plscheme, plsh > et al. Oh, here it is. So this goes with the point above, and can be simplified to "support all procedural languages," yes? > - support for all what you find in contrib/ for 8.4 (covered already?) > > * would be great (target later commit fest) > > - versioning support with upgrade in place facility (hooks?) Yeah, we should standardize versioning somehow to make upgrading easier. It should be a simple as possible, IMHO. If it tries to do too much, you get stuck with great complexity. > - supporting more than one version of the same module installed in > the same > time, possibly (I suppose always but...) in different schemas Eh. This could be in 2.0 I think. > - custom variables? You mean GUC variables? That'd certainly be useful, but again, probably not necessary for 1.0. > - PostGIS complete support, with user data dependancy, even if an > extensible typmod system would certainly solve this problem in a > better > place. Maybe someone will come up with another existing extension > sharing > the problem and not the typmod solution? Well, PostGIS is itself an extension, no? What we need, then, is dependency tracking. > - a core team approved list of extensions (replacing contribs, maybe > adding > to it), where approved means code has been reviewed and the only > reason > why it's not in the core itself is that core team feels that it's > not > part of a RDBMS per-se, or feel like the code should be maintained > and > released separately until it gets some more field exposure... (think > plproxy). I hate the idea of "approved" extensions, but would love to see a kind of "standard library" as a separate distribution that contains a bunch of stuff that's commonly used. I'd want to steer clear of blessing by the core team other than that, though, because then you start to get into politics. > * later please Yah. > - CPAN or ports like infrastructure for auto downloading a more or > less > prepared "bundle", place it at the right place on the filesystem and > install it in the database(s) of choice Yes, this would be nice. Also, integrated testing as with CPAN. I happen to know of a really nice test framework we could use… > - complex support for ad-hoc bootstrap of uncommon modules such as > pljava Not sure what this means; can you provide more detail? > - dependancy graph solving and automatic installation, with depends, > recommends and suggest sections and with rules/setup to choose > what to > pull in by default... We'd likely have to store this information in some sort of system table, too, yes? > == dump & restore > > We want pg_dump to issue only one line per extension, the one > installing the > extension in the database, see syntax. > > == syntax > > Extensions will need metadata, and after reading several proposals, > what I > propose here is to have a first explicit step to register the > extension name > and metadata, then have "basic" tools to play with it. Register with whom? I have to say that, although there is namespace registration for CPAN, it's not required, and this is, in fact, a big part of the reason for CPAN's success. There is no approval process barrier to entry. > === installing and removing an extension > > begin; > install extension foo with search_path = foo; > commit; It would need something to ensure an appropriate version, too, no? > Extensions authors are asked not to bother about search_path in > their sql > scripts so that it's easy for DBAs to decide where to install them. > The with > strange syntax is there to allow for the "install extension" command > to > default to, e.g., pg_extension, which won't typically be the first > schema in > the search_path. And how will functions that call other functions within an extension know that they're calling those functions in the appropriate schema? I get this all the time with pgTAP: You can install it in its own schema, but you have to include that schema in the search_path in order for it to work, as some pgTAP functions call other pgTAP functions with no schema-qualification. > begin; > drop extension foo [cascade]; > commit; > > The "cascade" option is there to care about reverse depends. Would it fail if there were dependencies on the module in the database, such as functions that use its functions, or tables that depend on a custom data type? > === creating extensions (authoring) > > The 'foo' extension author is meant to provide a +foo.sql+ file > containing > this: > > create extension foo > with version 1.0 > install [script] 'foo.install.sql' > uninstall [script] 'foo.uninstall.sql' > upgrade function upgrade_foo(old version, new version) > [ custom_variable_classes 'a,b' > configuration file 'foo.conf' ] > depends on bar version 0.3 > and on baz version >= 1.2; > > Here we suppose we have also a new datatype "version" to host the > versionning information, with the associated operators. See > http://packages.debian.org/sid/postgresql-8.3-debversion I like this. Then the build file contains, essentially, just a SQL command. That will make it easy for extension authors. However, they might wish to include quite a lot of other metadata for the extension, such as URLs for VC and bug tracking. > Doing it this way, we skip the need to provide a way of telling "next > comands are meant for creating SQL objects which belongs to such > extension", > at the expense of forcing authors to manage upgrades to add objects. The install and uninstall script attributes should also allow either full paths or, if just a simple file name, paths to the extensions installation directory (currently $PGSQL/share/contrib). > The upgrade function is mandatory, and has to return the installed > version > or null, meaning "please run the install script again, that's how I > upgrade". The error management is to be made by means of RAISE > EXCEPTION. I'm not following you here. If I have a bunch of releases with a number of changes to them, this function could get quite complex, I should think. Also, in what language could it be written? > If a specific function is to get called at install or uninstall > time, it's > easy enough to SELECT install_function(); from within the install > script, > after having defined it. To support this, internal GUCs (not exposed > in > postgresql.conf) will be provided and set by PG when running those > scripts, > named current_extension and current_extension_version. Nice. > == ACLs > > The "bulk" ACL management of an extension's objects is pushed to the > globing > support project for GRANT/REVOKE, so we don't have to speak about > what it'll > look like here :) > > == OS Filesystem Interaction > > PostgreSQL already provides standard paths where to install > extensions by > means of PGXS, and distribution packagers have been able to adapt > those. We > should just stick with this, meaning the problem is solved. Sounds fine to me. Best, David
Le 23 juin 09 à 20:30, David E. Wheeler a écrit : > On Jun 23, 2009, at 10:44 AM, Dimitri Fontaine wrote: >> - support for home grown SQL/PLpgSQL only extensions in order to >> make life >> easier for in-house PG based development (you don't have to code >> in C to >> benefit from extensions) > > It'd be nice if it supported other core languages like PL/Perl, but > it's okay if it doesn't on the first round (I'd likely want to use > some CPAN modules in a PL/Perl extension, anyway). At first sight I though you were talking about a non-issue, as I meant that an extension should not have to be a .so (or dll) + a .sql exposing it, but any SQL code PostgreSQL is able to understand, plperl included. But plpgsql and plperl are not available by default on databases, so it makes sense to rise the question, and the right answer might be to expose some (optional?) core components as extensions (version is PG major version), in order for out-of-core extensions to be able to depend on them being there. >> - support for procedural languages (a priori easily covered within >> basic >> modules but I'm not sure) like plproxy, pllolcode, pllua, >> plscheme, plsh >> et al. > > Oh, here it is. So this goes with the point above, and can be > simplified to "support all procedural languages," yes? I've been told pljava is complex in that it requires a part of pljave to be there in order to be installable (like pseudo DDL coded in pljava and needed in the installation procedure). So I'd prefer not to go this far, just in case. >> - supporting more than one version of the same module installed in >> the same >> time, possibly (I suppose always but...) in different schemas > > Eh. This could be in 2.0 I think. Yeah, my point exactly. >> - custom variables? > > You mean GUC variables? That'd certainly be useful, but again, > probably not necessary for 1.0. In fact supporting custom classes GUCs seems to be part of what Tom Dunstan did, so it should be ok to plan to have it? http://archives.postgresql.org/message-id/ca33c0a30804061349s41b4d8fcsa9c579454b27ecd2@mail.gmail.com > Well, PostGIS is itself an extension, no? What we need, then, is > dependency tracking. In fact PostGIS is a complex beast, in that it's registering typmod like information about user columns into its own private tables (think extension's catalog). Now that means circular dependancy of some sort as restoring user data requires to have the PostGIS private tables filled already, but as they refer user data (not sure if it's targeting user tables ---DDL--- only), you have to already have restored them. Even if it's only targeting schema level stuff, you'd need to restore the extension's data after the schema but before the data, but the extension's itself (data types, indexes opclass, etc) BEFORE the data. I'm not sure you should target to support this level of complexity (it has to be generic) in the first incantation of it, but if some hacker tells me it's damn easy to get right with pg_depend, why not? >> - a core team approved list of extensions (replacing contribs, >> maybe adding >> to it), where approved means code has been reviewed and the only >> reason >> why it's not in the core itself is that core team feels that it's >> not >> part of a RDBMS per-se, or feel like the code should be maintained >> and >> released separately until it gets some more field exposure... (think >> plproxy). > > I hate the idea of "approved" extensions, but would love to see a > kind of "standard library" as a separate distribution that contains > a bunch of stuff that's commonly used. I'd want to steer clear of > blessing by the core team other than that, though, because then you > start to get into politics. Maybe it's just a (non native) misuse of vocabulary, I see contrib as the current incarnation of the standard extension library and would like to see it evolve into a list of reviewed and maintained extensions, which in a later step you'll be able to remotely fetch and install easily from source from postgresql.org services, or in binary from your distribution package. But I think we'll still need a contrib/ like suite that core hackers keep an eye on and maintain in minor branches and adapt in major releases. Now if we ever get to a point where we can setup an http repository of easily installable extensions that you can point a built-in core tool to, that means there will be the standard official one and a myriad of others (pgfoundry, and self hosting). >> * later please > Yes, this would be nice. Also, integrated testing as with CPAN. I > happen to know of a really nice test framework we could use… hehe >> - complex support for ad-hoc bootstrap of uncommon modules such as >> pljava > > Not sure what this means; can you provide more detail? See above. >> - dependancy graph solving and automatic installation, with depends, >> recommends and suggest sections and with rules/setup to choose >> what to >> pull in by default... > > We'd likely have to store this information in some sort of system > table, too, yes? Yes, that'd be part of the extension "meta data". >> Extensions will need metadata, and after reading several proposals, >> what I >> propose here is to have a first explicit step to register the >> extension name >> and metadata, then have "basic" tools to play with it. > > Register with whom? I have to say that, although there is namespace > registration for CPAN, it's not required, and this is, in fact, a > big part of the reason for CPAN's success. There is no approval > process barrier to entry. None of this, stay aboard :) Register within the database where you'll want to install it. The install step as shown below will then use the meta-data to do the sanity checking (dependancies) and the installation (what script to read?). >> === installing and removing an extension >> >> begin; >> install extension foo with search_path = foo; >> commit; > > It would need something to ensure an appropriate version, too, no? So it's: create schema foo; install extension foo with version = 1.2, search_path = foo; That's fine by me, but I'm not sure whether first extension's implementation will support installing several versions of the same extension in parallel, so I'm unsure what we get here... one more sanity check? I buy it. >> Extensions authors are asked not to bother about search_path in >> their sql >> scripts so that it's easy for DBAs to decide where to install them. >> The with >> strange syntax is there to allow for the "install extension" >> command to >> default to, e.g., pg_extension, which won't typically be the first >> schema in >> the search_path. > > And how will functions that call other functions within an extension > know that they're calling those functions in the appropriate schema? > I get this all the time with pgTAP: You can install it in its own > schema, but you have to include that schema in the search_path in > order for it to work, as some pgTAP functions call other pgTAP > functions with no schema-qualification. I don't think we want to cancel user ability to choose schema where to install, so an idea could be to ask extensions author to systematically use pg_extension (or non-qualify), and PostgreSQL could replace this with the INSTALL EXTENSION command schema. >> begin; >> drop extension foo [cascade]; >> commit; >> >> The "cascade" option is there to care about reverse depends. > > Would it fail if there were dependencies on the module in the > database, such as functions that use its functions, or tables that > depend on a custom data type? Yes, when you don't use the CASCADE keyword. >> === creating extensions (authoring) >> >> The 'foo' extension author is meant to provide a +foo.sql+ file >> containing >> this: >> >> create extension foo >> with version 1.0 >> install [script] 'foo.install.sql' >> uninstall [script] 'foo.uninstall.sql' >> upgrade function upgrade_foo(old version, new version) >> [ custom_variable_classes 'a,b' >> configuration file 'foo.conf' ] >> depends on bar version 0.3 >> and on baz version >= 1.2; >> >> Here we suppose we have also a new datatype "version" to host the >> versionning information, with the associated operators. See >> http://packages.debian.org/sid/postgresql-8.3-debversion > > I like this. Then the build file contains, essentially, just a SQL > command. That will make it easy for extension authors. However, they > might wish to include quite a lot of other metadata for the > extension, such as URLs for VC and bug tracking. I guess it'll get easy to add those once we agree on the way to go here. >> Doing it this way, we skip the need to provide a way of telling "next >> comands are meant for creating SQL objects which belongs to such >> extension", >> at the expense of forcing authors to manage upgrades to add objects. > > The install and uninstall script attributes should also allow either > full paths or, if just a simple file name, paths to the extensions > installation directory (currently $PGSQL/share/contrib). Sold, with current privileges and location restrictions about file system access from within the database... does this boils down to $PGDATA subdirectory only? >> The upgrade function is mandatory, and has to return the installed >> version >> or null, meaning "please run the install script again, that's how I >> upgrade". The error management is to be made by means of RAISE >> EXCEPTION. > > I'm not following you here. If I have a bunch of releases with a > number of changes to them, this function could get quite complex, I > should think. Also, in what language could it be written? It'll get as complex as you need it to be, and it's only required that it's a PostgreSQL function. I guess writing the plphp upgrade function in plphp would be quite challenging, unless we're able to guarantee that the newer extension's code won't get loaded before until the fonction returned (and didn't RAISE EXCEPTION). Regards, -- dim
On Jun 23, 2009, at 2:06 PM, Dimitri Fontaine wrote: >> It'd be nice if it supported other core languages like PL/Perl, but >> it's okay if it doesn't on the first round (I'd likely want to use >> some CPAN modules in a PL/Perl extension, anyway). > > At first sight I though you were talking about a non-issue, as I > meant that an extension should not have to be a .so (or dll) + > a .sql exposing it, but any SQL code PostgreSQL is able to > understand, plperl included. Well, C code with /[.](?:so|dll|dylib)/ should be allowed as well. > But plpgsql and plperl are not available by default on databases, so > it makes sense to rise the question, and the right answer might be > to expose some (optional?) core components as extensions (version is > PG major version), in order for out-of-core extensions to be able to > depend on them being there. Yes, and it could also be that a particular extension requires an unsafe version of a PL. That would need to be included in the metadata for the PL extension. PL/pgSQL should be in core and enabled by default, IMHO. The other PLs should be extensions. > I've been told pljava is complex in that it requires a part of > pljave to be there in order to be installable (like pseudo DDL coded > in pljava and needed in the installation procedure). So I'd prefer > not to go this far, just in case. Well, if each PL aside from SQL, C, and PL/pgSQL is an extension, then it's just a dependency, right? >> In fact supporting custom classes GUCs seems to be part of what Tom >> Dunstan did, so it should be ok to plan to have it? > http://archives.postgresql.org/message-id/ca33c0a30804061349s41b4d8fcsa9c579454b27ecd2@mail.gmail.com Ah, cool. > In fact PostGIS is a complex beast, in that it's registering typmod > like information about user columns into its own private tables > (think extension's catalog). Now that means circular dependancy of > some sort as restoring user data requires to have the PostGIS > private tables filled already, but as they refer user data (not sure > if it's targeting user tables ---DDL--- only), you have to already > have restored them. Ouch. Must be a nightmare today, too. > Even if it's only targeting schema level stuff, you'd need to > restore the extension's data after the schema but before the data, > but the extension's itself (data types, indexes opclass, etc) BEFORE > the data. > > I'm not sure you should target to support this level of complexity > (it has to be generic) in the first incantation of it, but if some > hacker tells me it's damn easy to get right with pg_depend, why not? Yeah, we should KISS to start with. > Maybe it's just a (non native) misuse of vocabulary, I see contrib > as the current incarnation of the standard extension library and > would like to see it evolve into a list of reviewed and maintained > extensions, which in a later step you'll be able to remotely fetch > and install easily from source from postgresql.org services, or in > binary from your distribution package. Oh, yeah, I'm on board with that. > But I think we'll still need a contrib/ like suite that core hackers > keep an eye on and maintain in minor branches and adapt in major > releases. This says the same thing as the last paragraph, no? I don't think I'd call such a distribution "contrib," though. Maybe standard extensions. > Now if we ever get to a point where we can setup an http repository > of easily installable extensions that you can point a built-in core > tool to, that means there will be the standard official one and a > myriad of others (pgfoundry, and self hosting). Yes. > None of this, stay aboard :) > Register within the database where you'll want to install it. The > install step as shown below will then use the meta-data to do the > sanity checking (dependancies) and the installation (what script to > read?). Oh, *that* kind of registration. Fine, of course! >> It would need something to ensure an appropriate version, too, no? > > So it's: > create schema foo; > install extension foo with version = 1.2, search_path = foo; > > That's fine by me, but I'm not sure whether first extension's > implementation will support installing several versions of the same > extension in parallel, so I'm unsure what we get here... one more > sanity check? I buy it. Yes, although as I said before, version numbers are hard to get right. We should keep them very simple, with a strict requirement as to the simple format (perhaps /\d+[.]\d{2}/) or perhaps NUMERIC or some other core data type, and then we'd be able to use simple operators: install extension foo with version = 1.2 OR version >= 1.4, search_path = foo; > I don't think we want to cancel user ability to choose schema where > to install, so an idea could be to ask extensions author to > systematically use pg_extension (or non-qualify), and PostgreSQL > could replace this with the INSTALL EXTENSION command schema. Replace what? How would pg_extension or INSTALL EXTENSION know to magically schema-qualify the function calls internal to an extension? >> I like this. Then the build file contains, essentially, just a SQL >> command. That will make it easy for extension authors. However, >> they might wish to include quite a lot of other metadata for the >> extension, such as URLs for VC and bug tracking. > > I guess it'll get easy to add those once we agree on the way to go > here. I think that people will want to be able to associate arbitrary metadata. It'd be useful for configuration, too. > Sold, with current privileges and location restrictions about file > system access from within the database... does this boils down to > $PGDATA subdirectory only? Yes, probably, since the database system user will need to have permission to access them. > It'll get as complex as you need it to be, and it's only required > that it's a PostgreSQL function. I guess writing the plphp upgrade > function in plphp would be quite challenging, unless we're able to > guarantee that the newer extension's code won't get loaded before > until the fonction returned (and didn't RAISE EXCEPTION). The upgrade function stuff is what I understand least about this proposal. Can you provide a real-world type example of how it will be used? Thanks, David
[Skipping most of it as I'd like to read what other people think about it before going in lengthy thread already] :) Le 23 juin 09 à 23:41, David E. Wheeler a écrit : > Yes, although as I said before, version numbers are hard to get > right. We should keep them very simple, with a strict requirement as > to the simple format (perhaps /\d+[.]\d{2}/) or perhaps NUMERIC or > some other core data type, and then we'd be able to use simple > operators: > > install extension foo with version = 1.2 OR version >= 1.4, > search_path = foo; If we happen to accept the debian policy versioning scheme, then the hard work is already done for us, it seems: http://packages.debian.org/fr/sid/postgresql-8.3-debversion >> I don't think we want to cancel user ability to choose schema where >> to install, so an idea could be to ask extensions author to >> systematically use pg_extension (or non-qualify), and PostgreSQL >> could replace this with the INSTALL EXTENSION command schema. > > Replace what? How would pg_extension or INSTALL EXTENSION know to > magically schema-qualify the function calls internal to an extension? It's "just" PostgreSQL reading an SQL file (foo.install.sql) and parsing each statement etc, so we obviously have the machinery to recognize SQL objects names and schema qualification. Replacing the schema on-the-fly should be a SMOP? (*cough*) > I think that people will want to be able to associate arbitrary > metadata. It'd be useful for configuration, too. Oh, you want EAV already? Or maybe a supplementary hstore column into the pg_extension catalog... but I guess we can't have this dependancy :) > The upgrade function stuff is what I understand least about this > proposal. Can you provide a real-world type example of how it will > be used? You provide a function upgrade(old, new) where parameters are version numbers. The body of the (typically plpgsql) function should implement the ALTER TABLE or CREATE OR REPLACE FUNCTION stuff you need to do, with some conditions on the version numbers. I expect people would write a upgrade_10_to_11() function then call it from upgrade() when old = 1.0 and new = 1.1, for example. Maybe we should also provide some support functions to run the install and uninstall script, and some more facilities, so that you could implement as follow: BEGIN -- loop over columns storing data from our type FOR s, t, c IN SELECT nspname, relname, attname FROM pg_find_columns('mytype'::regclass) LOOP EXECUTE 'ALTER TABLE $1.$2 ALTER COLUMN $3TYPE text USING mycast($3)' USING s, t, c; END LOOP; PERFORM pg_extension_uninstall('foo', old); PERFORM pg_extension_install('foo', new); -- ALTER TYPE the other way round END; Some other stuff could be needed to check about indexes to, storing a list of them in a temp table then recreating them, but it seems to me you can already hand craft the catalog queries now. But as it becomes common practise, we might want to offer them in a more ready for public consumption way. Regards, -- dim
On Jun 23, 2009, at 3:02 PM, Dimitri Fontaine wrote: > If we happen to accept the debian policy versioning scheme, then the > hard work is already done for us, it seems: > http://packages.debian.org/fr/sid/postgresql-8.3-debversion As long as we don't need to implement a new data type, fine. >> Replace what? How would pg_extension or INSTALL EXTENSION know to >> magically schema-qualify the function calls internal to an extension? > > It's "just" PostgreSQL reading an SQL file (foo.install.sql) and > parsing each statement etc, so we obviously have the machinery to > recognize SQL objects names and schema qualification. Replacing the > schema on-the-fly should be a SMOP? (*cough*) Well, no. I might have written a function in PL/Perl. Is PostgreSQL going to parse my Perl function for unqualified function calls? Really? Hell, I don't think that PL/pgSQL is parsed until functions are loaded, either, though I may be wrong about that. Better is to have some magic so that functions in an extension magically have their schema put onto the front of search_path when they're called. Or when they're compiled. Or something. > Oh, you want EAV already? Or maybe a supplementary hstore column > into the pg_extension catalog... but I guess we can't have this > dependancy :) No, but a simple key/value table with an FK constraint should be sufficient for non-core metadata. >> The upgrade function stuff is what I understand least about this >> proposal. Can you provide a real-world type example of how it will >> be used? > > You provide a function upgrade(old, new) where parameters are > version numbers. The body of the (typically plpgsql) function should > implement the ALTER TABLE or CREATE OR REPLACE FUNCTION stuff you > need to do, with some conditions on the version numbers. Well, CREATE OR REPLACE FUNCTION is probably already in my install.sql file. But I could see dropping deprecated functions and, of course, altering tables. > I expect people would write a upgrade_10_to_11() function then call > it from upgrade() when old = 1.0 and new = 1.1, for example. Okay, that makes sense. > Maybe we should also provide some support functions to run the > install and uninstall script, and some more facilities, so that you > could implement as follow: > BEGIN > -- loop over columns storing data from our type > FOR s, t, c IN SELECT nspname, relname, attname > FROM pg_find_columns('mytype'::regclass) > LOOP > EXECUTE 'ALTER TABLE $1.$2 ALTER COLUMN $3 TYPE text USING > mycast($3)' > USING s, t, c; > END LOOP; > > PERFORM pg_extension_uninstall('foo', old); > PERFORM pg_extension_install('foo', new); > > -- ALTER TYPE the other way round > END; > > Some other stuff could be needed to check about indexes to, storing > a list of them in a temp table then recreating them, but it seems to > me you can already hand craft the catalog queries now. But as it > becomes common practise, we might want to offer them in a more ready > for public consumption way. Yes, whatever tools we can provide to make things easier for extension authors/maintainers, the better. But I recognize that we might have to wait and see what cow paths develop. Best, David
David E. Wheeler wrote: > On Jun 23, 2009, at 3:02 PM, Dimitri Fontaine wrote: >> It's "just" PostgreSQL reading an SQL file (foo.install.sql) and >> parsing each statement etc, so we obviously have the machinery to >> recognize SQL objects names and schema qualification. Replacing the >> schema on-the-fly should be a SMOP? (*cough*) > > Well, no. I might have written a function in PL/Perl. Is PostgreSQL > going to parse my Perl function for unqualified function calls? Really? > Hell, I don't think that PL/pgSQL is parsed until functions are loaded, > either, though I may be wrong about that. > > Better is to have some magic so that functions in an extension magically > have their schema put onto the front of search_path when they're called. > Or when they're compiled. Or something. With the given example of extension "foo" depending on "bar" and "baz", I'd suggest: - Default search_path = ext:self, pg_catalog - ext:self = <wherever foo installs> - ext:bar = <wherever bar installs> - ext:baz = <wherever baz installs> You *can't* have anything other than the current package in the search-path in case bar/baz have conflicting objects. I've no idea if ext:<name> makes sense from a parser point of view, but the idea is to map extension name to a schema. If possible, this should work anywhere in PG that a schema can be specified. So - If extension foo is installed in schema1 then ext:foo.fn1() is the same as schema1.fn1() -- Richard Huxton Archonet Ltd
On Tue, Jun 23, 2009 at 12:44 PM, Dimitri Fontaine<dfontaine@hi-media.com> wrote: > > - a core team approved list of extensions (replacing contribs, are you aware of the enormous job that will imply for core team? maybe a community approved list of extensions or maybe we can have some kind of jury (just like patch reviewers) that could test and mark as tested... i remember a conversation about this very point -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157
Jaime Casanova <jcasanov@systemguards.com.ec> writes: > On Tue, Jun 23, 2009 at 12:44 PM, Dimitri > Fontaine<dfontaine@hi-media.com> wrote: >> >> - a core team approved list of extensions (replacing contribs, > > are you aware of the enormous job that will imply for core team? I'm not saying I want core to take care of all pgfoundry projects that will make them available as extensions, but to have contrib/ made extensions and have core if possible continue doing what they have been doing... forever? > maybe a community approved list of extensions or maybe we can have > some kind of jury (just like patch reviewers) that could test and mark > as tested... i remember a conversation about this very point Having other sources of extensions apart from contrib seems to me a very good idea. -- Dimitri Fontaine PostgreSQL DBA, Architecte
On Jun 24, 2009, at 12:59 AM, Dimitri Fontaine wrote: >> are you aware of the enormous job that will imply for core team? > > I'm not saying I want core to take care of all pgfoundry projects that > will make them available as extensions, but to have contrib/ made > extensions and have core if possible continue doing what they have > been > doing... forever? Right, in an independent distribution. Best, David
Dim, > The contenders are extension, module, bundle and package. My vote is > extension. +1 on "extension". > We're not trying to be feature complete on first round. > > * must have > > > - support for all what you find in contrib/ for 8.4 (covered already?) ... most of. Some of the things in contrib are largely examples or hacker tools; if we don't cover those it's OK. > * would be great (target later commit fest) > > - versioning support with upgrade in place facility (hooks?) We need versioning support right now, separate from any UIP support. Otherwise the dump/reload won't work. > - supporting more than one version of the same module installed in the same > time, possibly (I suppose always but...) in different schemas We can put this off until we have a use-case for it. I can't imagine one. > - custom variables? Don't we have these already? > - PostGIS complete support, with user data dependancy, even if an > extensible typmod system would certainly solve this problem in a better > place. Maybe someone will come up with another existing extension sharing > the problem and not the typmod solution? Or we just fix that issue for 8.5. > - a core team approved list of extensions (replacing contribs, maybe adding > to it), where approved means code has been reviewed and the only reason > why it's not in the core itself is that core team feels that it's not > part of a RDBMS per-se, or feel like the code should be maintained and > released separately until it gets some more field exposure... (think > plproxy). The core team isn't appropriate for this. We'd start a new committee/list somewhere instead, and it would be part of the same effort which produces a "recommended" list of extensions and drivers for packagers. > - CPAN or ports like infrastructure for auto downloading a more or less > prepared "bundle", place it at the right place on the filesystem and > install it in the database(s) of choice This may not be necessary if simple download-unzip-and-install is simple enough. > - complex support for ad-hoc bootstrap of uncommon modules such as pljava > > - dependancy graph solving and automatic installation, with depends, > recommends and suggest sections and with rules/setup to choose what to > pull in by default... Uh-huh. That'll be the day ... > === installing and removing an extension > > begin; > install extension foo with search_path = foo; Needs install file location: INSTALL EXTENSION foo FROM '~/downloads/foo' WITH search_path = 'foo'; > == OS Filesystem Interaction > > PostgreSQL already provides standard paths where to install extensions by > means of PGXS, and distribution packagers have been able to adapt those. We > should just stick with this, meaning the problem is solved. I think that the user should be able to put the extension file download anywhere in their filesystem, and on install PostgreSQL should copy the files to the appropriate place. That is, they shouldn't have to first copy the files to /pg_source_dir/contrib/. Maybe you had that covered, but I didn't see it explicitly. Also, this means that we'll want to make sure that PGXS is included in all existing packages of PostgresQL. Is it? -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com
Josh Berkus wrote: > >> - a core team approved list of extensions (replacing contribs, >> maybe adding >> to it), where approved means code has been reviewed and the only >> reason >> why it's not in the core itself is that core team feels that it's >> not >> part of a RDBMS per-se, or feel like the code should be >> maintained and >> released separately until it gets some more field exposure... (think >> plproxy). > > The core team isn't appropriate for this. We'd start a new > committee/list somewhere instead, and it would be part of the same > effort which produces a "recommended" list of extensions and drivers > for packagers. > > Actually, I think we should be like Perl here. There is a list of standard modules that comes with the base Perl distro, and then there are addons, such as you find on CPAN. File::Find is an example of a standard module, DBD::Pg is an example of an addon. Quite apart from anything else, having some extensions maintained by core will help in validating the extension mechanism. Good candidates for core-supported extensions would include PL{Perl,Python,Tcl}, pgcrypto and hstore, IMNSHO. Between them they illustrate a number of the major extension paradigms. Beyond standard extensions, I'm not sure we need a committee to "approve" extensions. Does Perl have such an animal? I'm fairly wary of creating new decision-making bureaucracies. cheers andrew
Le 24 juin 09 à 22:43, Josh Berkus a écrit : > ... most of. Some of the things in contrib are largely examples or > hacker tools; if we don't cover those it's OK. Good to know. > We need versioning support right now, separate from any UIP support. > Otherwise the dump/reload won't work. You want pg_dump to issue an INSTALL EXTENSION command with specific version needed, right? >> - supporting more than one version of the same module installed in >> the same >> time, possibly (I suppose always but...) in different schemas > > We can put this off until we have a use-case for it. I can't > imagine one. Good for me :) >> - custom variables? > > Don't we have these already? It's a matter of exposing a way to attach them to a specific extension. Are GUCs a possible element of pg_depend? >> - PostGIS complete support, with user data dependancy, even if an >> extensible typmod system would certainly solve this problem in a >> better >> place. Maybe someone will come up with another existing >> extension sharing >> the problem and not the typmod solution? > > Or we just fix that issue for 8.5. That'd make my day. >> - a core team approved list of extensions (replacing contribs, >> maybe adding >> to it), where approved means code has been reviewed and the only >> reason >> why it's not in the core itself is that core team feels that >> it's not >> part of a RDBMS per-se, or feel like the code should be >> maintained and >> released separately until it gets some more field exposure... >> (think >> plproxy). > > The core team isn't appropriate for this. We'd start a new > committee/list somewhere instead, and it would be part of the same > effort which produces a "recommended" list of extensions and drivers > for packagers. It'd still deprecate contrib/, which could maybe become examples/? >> - CPAN or ports like infrastructure for auto downloading a more or >> less >> prepared "bundle", place it at the right place on the filesystem >> and >> install it in the database(s) of choice > > This may not be necessary if simple download-unzip-and-install is > simple enough. I hope it'll get simple enough, yes, as simple as current PGXS modules from source are: - cvs up or wget - tar xzf ... && cd ... - make install - psql -f ... mydb >> begin; >> install extension foo with search_path = foo; > > Needs install file location: No, extensions meta-data are in foo.sql and already loaded into the database by the time you get to INSTALL EXTENSION. That's a part I like because it makes it simple to handle meta-data and to declare that SQL objects from the script are part of the extension. I also dislike the CREATE EXTENSION which is not INSTALLing it... maybe a WITH INSTALL syntax option could do? >> PostgreSQL already provides standard paths where to install >> extensions by >> means of PGXS, and distribution packagers have been able to adapt >> those. We >> should just stick with this, meaning the problem is solved. > > I think that the user should be able to put the extension file > download anywhere in their filesystem, and on install PostgreSQL > should copy the files to the appropriate place. That is, they > shouldn't have to first copy the files to /pg_source_dir/contrib/. > Maybe you had that covered, but I didn't see it explicitly. PGXS has it covered, and we're not yet there, but I'm thinking PGXS should be a pre requisite of the extension facility as far as extensions authors are concerned. Then packagers will make it so that users won't typically face those details. > Also, this means that we'll want to make sure that PGXS is included > in all existing packages of PostgresQL. Is it? Only those packages you want to have extension support from source ;) -- dim
Le 24 juin 09 à 23:07, Andrew Dunstan a écrit : > Actually, I think we should be like Perl here. There is a list of > standard modules that comes with the base Perl distro, and then > there are addons, such as you find on CPAN. File::Find is an example > of a standard module, DBD::Pg is an example of an addon. Agreed. > Quite apart from anything else, having some extensions maintained by > core will help in validating the extension mechanism. > > Good candidates for core-supported extensions would include > PL{Perl,Python,Tcl}, pgcrypto and hstore, IMNSHO. Between them they > illustrate a number of the major extension paradigms. That read as a good start, even if I'd maybe like to add ltree and plproxy, maybe more for convenience than anything else. > Beyond standard extensions, I'm not sure we need a committee to > "approve" extensions. Does Perl have such an animal? I'm fairly wary > of creating new decision-making bureaucracies. I think what Josh is referring too is to have the standard core extensions whose aim is to show how extensions work, provided maintained examples etc, *and* a community list of useful extensions (temporal, prefix, oracfe, pgtap, you name it) that users will probably want to find. This list will have to provide some more information, ones that are implicit within the first group: is the software maintained, by whom, is it production ready, feature complete, is it a community endorsed product, etc. While I'm all for avoiding bureaucracy, I'd like us to be able to show how rich and trustworthy the PostgreSQL overall solution and community is. Core-supported extensions won't allow that on their own. Regards, -- dim
Andrew, > Actually, I think we should be like Perl here. There is a list of > standard modules that comes with the base Perl distro, and then there > are addons, such as you find on CPAN. File::Find is an example of a > standard module, DBD::Pg is an example of an addon. Actually, chromatic, Allison, etc. regard the Standard Modules as a mistake and are talking about moving away from having any for Perl 6. On the other hand, their main reason for doing this (the issues with maintaining the included version and the CPAN version separately) wouldn't apply to us. On the third hand, having "all modules equal, just some recommended" approach woudl make it far easier to drop a module which went unmaintained, e.g. CUBE. But some people may regard this as a misfeature. -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com
On Jun 24, 2009, at 2:07 PM, Andrew Dunstan wrote: > Actually, I think we should be like Perl here. There is a list of > standard modules that comes with the base Perl distro, and then > there are addons, such as you find on CPAN. Actually, the lesson slowly emerging in the Perl community is that there should be very few extensions distributed with the core, as keeping them in sync is a giant PITA and in part responsible for the duration of time between releases. A separate distribution of "recommended extensions" would fill the same need, but not bind core to the schedule of extension updates. > Beyond standard extensions, I'm not sure we need a committee to > "approve" extensions. Does Perl have such an animal? I'm fairly wary > of creating new decision-making bureaucracies. Agreed. Perl does not have such a thing. PHP does, and it's no doubt part of the reason that PEAR has so few modules. Best, David
On Jun 24, 2009, at 2:12 PM, Dimitri Fontaine wrote: >> The core team isn't appropriate for this. We'd start a new >> committee/list somewhere instead, and it would be part of the same >> effort which produces a "recommended" list of extensions and >> drivers for packagers. > > It'd still deprecate contrib/, which could maybe become examples/? No, it would not be distributed with core at all. They could all be packaged up together in a single distribution of recommended modules, however. >> This may not be necessary if simple download-unzip-and-install is >> simple enough. > > I hope it'll get simple enough, yes, as simple as current PGXS > modules from source are: > - cvs up or wget > - tar xzf ... && cd ... > - make install > - psql -f ... mydb Then it could also be easily scripted, too. > PGXS has it covered, and we're not yet there, but I'm thinking PGXS > should be a pre requisite of the extension facility as far as > extensions authors are concerned. Then packagers will make it so > that users won't typically face those details. +1. Best, David
Josh Berkus wrote: > Andrew, > >> Actually, I think we should be like Perl here. There is a list of >> standard modules that comes with the base Perl distro, and then there >> are addons, such as you find on CPAN. File::Find is an example of a >> standard module, DBD::Pg is an example of an addon. > > Actually, chromatic, Allison, etc. regard the Standard Modules as a > mistake and are talking about moving away from having any for Perl 6. > > On the other hand, their main reason for doing this (the issues with > maintaining the included version and the CPAN version separately) > wouldn't apply to us. I agree they have too many. I think moving to none would be a mistake, though. Would they even drop things like Dynaloader or ExtUtils::MakeMaker? That would be crazy, IMNSHO. I think there's a sweet spot here and we are not very far away from it in the number of things we currently ship. > > On the third hand, having "all modules equal, just some recommended" > approach woudl make it far easier to drop a module which went > unmaintained, e.g. CUBE. But some people may regard this as a > misfeature. I would happily push cube out of the nest now :-) cheers andrew
On Jun 24, 2009, at 2:41 PM, Andrew Dunstan wrote: > I agree they have too many. I think moving to none would be a > mistake, though. Would they even drop things like Dynaloader or > ExtUtils::MakeMaker? That would be crazy, IMNSHO. I think there's a > sweet spot here and we are not very far away from it in the number > of things we currently ship. They want to drop everything except for tools to download, build, test, and install other modules. That's the limitation. Best, David
David E. Wheeler wrote: > On Jun 24, 2009, at 2:41 PM, Andrew Dunstan wrote: > >> I agree they have too many. I think moving to none would be a >> mistake, though. Would they even drop things like Dynaloader or >> ExtUtils::MakeMaker? That would be crazy, IMNSHO. I think there's a >> sweet spot here and we are not very far away from it in the number of >> things we currently ship. > > They want to drop everything except for tools to download, build, > test, and install other modules. That's the limitation. > > Well, I think in our case that would be going too far. I think there is a very good case for keeping a few key extensions in core both as exemplars and to make it easy to validate the extension mechanism itself. There have been suggestions in the past about throwing a bunch of things overboard, sometimes out of a passion for neatness more than anything else ISTM, but there have been good arguments against as well, particularly in the case of the PLs, which are tied so closely to the backend. cheers andrew
On Wed, Jun 24, 2009 at 4:07 PM, Andrew Dunstan<andrew@dunslane.net> wrote: > > Beyond standard extensions, I'm not sure we need a committee to "approve" > extensions. Does Perl have such an animal? I'm fairly wary of creating new > decision-making bureaucracies. > not "approve", just mark it as something like: "tested with pg vX.XX", "not ready for production", etc... -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157
On Jun 24, 2009, at 3:09 PM, Andrew Dunstan wrote: > Well, I think in our case that would be going too far. I think there > is a very good case for keeping a few key extensions in core both as > exemplars and to make it easy to validate the extension mechanism > itself. There have been suggestions in the past about throwing a > bunch of things overboard, sometimes out of a passion for neatness > more than anything else ISTM, but there have been good arguments > against as well, particularly in the case of the PLs, which are tied > so closely to the backend. Exemplars are good if they behave in the same way as non-core extensions. So it might be good for the core to maintain contrib extensions, although I would urge them to keep the size down quite low, and to be very conservative about adding new extensions. Part of the issue Perl ran into is that it was too liberal about adding new stuff to core, especially modules with large dependency trees. Anything in core should be kept very simple, both to avoid bloat and to minimize the maintenance overhead for the core team. Best, David
David E. Wheeler wrote: > On Jun 24, 2009, at 3:09 PM, Andrew Dunstan wrote: > >> Well, I think in our case that would be going too far. I think there >> is a very good case for keeping a few key extensions in core both as >> exemplars and to make it easy to validate the extension mechanism >> itself. There have been suggestions in the past about throwing a >> bunch of things overboard, sometimes out of a passion for neatness >> more than anything else ISTM, but there have been good arguments >> against as well, particularly in the case of the PLs, which are tied >> so closely to the backend. > > Exemplars are good if they behave in the same way as non-core > extensions. So it might be good for the core to maintain contrib > extensions, although I would urge them to keep the size down quite > low, and to be very conservative about adding new extensions. Part of > the issue Perl ran into is that it was too liberal about adding new > stuff to core, especially modules with large dependency trees. > Anything in core should be kept very simple, both to avoid bloat and > to minimize the maintenance overhead for the core team. > > We have been conservative about this in the past and there is no reason to expect we will not be in the future. If anything, we are likely to become more so. cheers andrew
On Jun 24, 2009, at 3:41 PM, Andrew Dunstan wrote: > We have been conservative about this in the past and there is no > reason to expect we will not be in the future. If anything, we are > likely to become more so. Good, perfect. Best, David
Re-reading in the morning rather that late at night... Josh Berkus <josh@agliodbs.com> writes: >> === installing and removing an extension >> >> begin; >> install extension foo with search_path = foo; > > Needs install file location: > > INSTALL EXTENSION foo FROM '~/downloads/foo' WITH search_path = 'foo'; This would run the foo.sql file containing the CREATE EXTENSION call, then run the install procedure itself which will run the author's foo.install.sql script, right? I'm all for it. Now, I'm not sure which form would pg_dump issue, maybe it should dumps the CREATE EXTENSION and the INSTALL EXTENSION commands separately? -- Dimitri Fontaine PostgreSQL DBA, Architecte
On Wed, Jun 24, 2009 at 9:43 PM, Josh Berkus<josh@agliodbs.com> wrote: >> == OS Filesystem Interaction >> >> PostgreSQL already provides standard paths where to install extensions by >> means of PGXS, and distribution packagers have been able to adapt those. >> We >> should just stick with this, meaning the problem is solved. > > I think that the user should be able to put the extension file download > anywhere in their filesystem, and on install PostgreSQL should copy the > files to the appropriate place. That is, they shouldn't have to first copy > the files to /pg_source_dir/contrib/. Maybe you had that covered, but I > didn't see it explicitly. > > Also, this means that we'll want to make sure that PGXS is included in all > existing packages of PostgresQL. Is it? Apologies if I missed further discussion on this - I'm somewhat distracted with release preparations at the moment... PGXS is essentially useless on Windows unless you're compiling your own code using Mingw/msys, which will be a miniscule percentage of users. Our installers for 8.3 and above are all built using VC++. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
Dave Page <dpage@pgadmin.org> writes: > On Wed, Jun 24, 2009 at 9:43 PM, Josh Berkus<josh@agliodbs.com> wrote: >> Also, this means that we'll want to make sure that PGXS is included in all >> existing packages of PostgresQL. Is it? > > Apologies if I missed further discussion on this - I'm somewhat > distracted with release preparations at the moment... Yeah, wasn't the best timing for me to open the thread, but I've been sitting on it what seemed far too much time... > PGXS is essentially useless on Windows unless you're compiling your > own code using Mingw/msys, which will be a miniscule percentage of > users. Our installers for 8.3 and above are all built using VC++. I guess we'll have to see how the windows world installers are comparable / compatible with PGXS here, I confess I know nothing about them... Is it possible to design this part of the extension system with only PGXS in mind and later adapt the windows toolsuite? Regards, -- dim
On Thu, Jun 25, 2009 at 9:58 AM, Dimitri Fontaine<dfontaine@hi-media.com> wrote: > Dave Page <dpage@pgadmin.org> writes: >> PGXS is essentially useless on Windows unless you're compiling your >> own code using Mingw/msys, which will be a miniscule percentage of >> users. Our installers for 8.3 and above are all built using VC++. > > I guess we'll have to see how the windows world installers are > comparable / compatible with PGXS here, I confess I know nothing about > them... > > Is it possible to design this part of the extension system with only > PGXS in mind and later adapt the windows toolsuite? Anything is possible :-). Better to ask someone with more perl expertise than me how much effort it might take to have the VC++ build system be able to create a project from an arbitrary PGXS makefile. Andrew or Magnus would seem the obvious people. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
Dave Page wrote: > On Thu, Jun 25, 2009 at 9:58 AM, Dimitri Fontaine<dfontaine@hi-media.com> wrote: >> Dave Page <dpage@pgadmin.org> writes: >>> PGXS is essentially useless on Windows unless you're compiling your >>> own code using Mingw/msys, which will be a miniscule percentage of >>> users. Our installers for 8.3 and above are all built using VC++. >> I guess we'll have to see how the windows world installers are >> comparable / compatible with PGXS here, I confess I know nothing about >> them... >> >> Is it possible to design this part of the extension system with only >> PGXS in mind and later adapt the windows toolsuite? > > Anything is possible :-). Better to ask someone with more perl > expertise than me how much effort it might take to have the VC++ build > system be able to create a project from an arbitrary PGXS makefile. > Andrew or Magnus would seem the obvious people. We do it for Makefiles in contrib, so in theory it should be doable. The problem is, I think, that the Makefile format is way too flexible. You can write anything as shell commands in there, and there is no way we can ever parse that and make it work in the msvc build system. I haven't read the pgxs docs in a while so I don't know if it makes restrictions on this, but AFAIK there are no technical reasons preventing people from doing this. -- Magnus HaganderSelf: http://www.hagander.net/Work: http://www.redpill-linpro.com/
Magnus Hagander <magnus@hagander.net> writes: > We do it for Makefiles in contrib, so in theory it should be doable. Excellent! > The problem is, I think, that the Makefile format is way too flexible. > You can write anything as shell commands in there, and there is no way > we can ever parse that and make it work in the msvc build system. I > haven't read the pgxs docs in a while so I don't know if it makes > restrictions on this, but AFAIK there are no technical reasons > preventing people from doing this. Well if the consequence of using random unix invocations in the Makefile (which is otherwise quite short for simple extensions) is that the extension won't work in windows, that means the burden is on the extension author. We can't force them to write windows compatible code in the first place, I presume. It sounds like PGXS dependancy is the way to go, knowing that some perl magic will have to get from the Makefile to the .project. Right? -- dim
Magnus Hagander <magnus@hagander.net> writes: >> On Thu, Jun 25, 2009 at 9:58 AM, Dimitri Fontaine<dfontaine@hi-media.com> wrote: >>> Is it possible to design this part of the extension system with only >>> PGXS in mind and later adapt the windows toolsuite? > We do it for Makefiles in contrib, so in theory it should be doable. > The problem is, I think, that the Makefile format is way too flexible. I think the contrib makefiles are considered to be our standard test suite for PGXS. If a 3rd-party makefile is doing anything not represented in contrib, it's not guaranteed to work anyway. So I'd be plenty satisfied if we just made the existing contrib infrastructure work for 3rd-party modules. regards, tom lane
On Jun 25, 2009, at 2:21 AM, Dave Page wrote: >> Is it possible to design this part of the extension system with only >> PGXS in mind and later adapt the windows toolsuite? > > Anything is possible :-). Better to ask someone with more perl > expertise than me how much effort it might take to have the VC++ build > system be able to create a project from an arbitrary PGXS makefile. > Andrew or Magnus would seem the obvious people. I think my head just exploded. Best, David
On Jun 25, 2009, at 7:16 AM, Tom Lane wrote: >> The problem is, I think, that the Makefile format is way too >> flexible. > > I think the contrib makefiles are considered to be our standard test > suite for PGXS. If a 3rd-party makefile is doing anything not > represented in contrib, it's not guaranteed to work anyway. So I'd > be plenty satisfied if we just made the existing contrib > infrastructure > work for 3rd-party modules. Is there no platform-independent build system we could take advantage of? One reason the Perl community is (very gradually) moving away from ExtUtils::MakMaker towards Module::Build (pure Perl installer) is to minimize such issues. I realize that we don't depend on Perl on Unix platforms, so it wouldn't make sense to use its build system for our extensions, but perhaps there's something else we could do? Best, David
On 6/25/09, David E. Wheeler <david@kineticode.com> wrote: > On Jun 25, 2009, at 2:21 AM, Dave Page wrote: > >>> Is it possible to design this part of the extension system with only >>> PGXS in mind and later adapt the windows toolsuite? >> >> Anything is possible :-). Better to ask someone with more perl >> expertise than me how much effort it might take to have the VC++ build >> system be able to create a project from an arbitrary PGXS makefile. >> Andrew or Magnus would seem the obvious people. > > I think my head just exploded. Sounds messy... -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
"David E. Wheeler" <david@kineticode.com> writes: > Is there no platform-independent build system we could take advantage > of? There's been some talk of using cmake, but the conversion effort would be massive, and I'm not sure the benefits would warrant it. regards, tom lane
On Jun 25, 2009, at 10:10 AM, Tom Lane wrote: >> Is there no platform-independent build system we could take advantage >> of? > > There's been some talk of using cmake, but the conversion effort would > be massive, and I'm not sure the benefits would warrant it. Might it be worthwhile just for the extensions stuff? Best, David
On Thursday 25 June 2009 01:09:17 Andrew Dunstan wrote: > Well, I think in our case that would be going too far. I think there is > a very good case for keeping a few key extensions in core both as > exemplars and to make it easy to validate the extension mechanism > itself. There have been suggestions in the past about throwing a bunch > of things overboard, sometimes out of a passion for neatness more than > anything else ISTM, but there have been good arguments against as well, > particularly in the case of the PLs, which are tied so closely to the > backend. Another thing we might want to consider once we have a robust extension mechanism is to move some things out of the backend into extensions. Candidates could be uuid, legacy geometry types, inet/cidr, for example. These extensions would still be available and probably installed by default, but they need not be hardcoded into the backend. But a policy of shipping zero extensions with the postgresql tarball obviously leaves very little flexibility to do any sort of thing like this.
Peter Eisentraut wrote: > > Another thing we might want to consider once we have a robust extension > mechanism is to move some things out of the backend into extensions. > Candidates could be uuid, legacy geometry types, inet/cidr, for example. > These extensions would still be available and probably installed by default, > but they need not be hardcoded into the backend. Presumably would help the prospective upgrader too. Upgrade tool can't cope with the change to inet types? No problem, I *know* they're not in use, since they're not loaded. -- Richard Huxton Archonet Ltd
Hi, While backporting UUID stuff to 8.2 where I'll need it (I wish I could have more impact on PostgreSQL upgrade schedules... who doesn't), I faced a problem I didn't foresee, and that we maybe should think about. http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/backports/ The idea in this backport is to have UUID as an extension to 8.2, which was plain easy to do once you remember about adding the PG_FUNCTION_INFO_V1() macro calls where they fit. Then there's the uuid-ossp contrib stuff, already packaged as an extension, but with code dependancy to the UUID provided functions (uuid_in, uuid_out). What I ended up doing was duplicating code in order not to have to tweak local_preload_libraries, so that uuid-ossp.so is self-contained. Any advice or missing knowledge about loading modules which depends on code from another module not already loaded in the backend is welcome :) Josh Berkus <josh@agliodbs.com> writes: >> - dependancy graph solving and automatic installation, with depends, >> recommends and suggest sections and with rules/setup to choose what to >> pull in by default... > > Uh-huh. That'll be the day ... So it seems we will have to teach the extension facility about loading dependant extensions first when calling a function, which I guess we can do as soon as we have the dependancies information in there? (calling a function from 'MODULE_PATHNAME' will have the .so loaded, soI guess than knowing it depends on another 'MODULE_PATHNAME'willenable us to load those first) Regards, -- dim
Dimitri Fontaine <dfontaine@hi-media.com> writes: > Any advice or missing knowledge about loading modules which depends on > code from another module not already loaded in the backend is welcome :) You should be able to configure the dynamic loader to do that, although in the case of uuid I strongly doubt it's worth the trouble. Duplicated code would be a lot simpler to manage ... regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > You should be able to configure the dynamic loader to do that, although > in the case of uuid I strongly doubt it's worth the trouble. In the context of the extensions facility, will we be able to do this configuration automatically from the backend, or to "manually" load any dependant .so? > Duplicated code would be a lot simpler to manage ... Ok, I'll keep it this way then. Regards, -- dim
Dimitri Fontaine <dfontaine@hi-media.com> writes: > Tom Lane <tgl@sss.pgh.pa.us> writes: >> You should be able to configure the dynamic loader to do that, although >> in the case of uuid I strongly doubt it's worth the trouble. > In the context of the extensions facility, will we be able to do this > configuration automatically from the backend, or to "manually" load any > dependant .so? I have zero interest in trying to support either. I doubt it's even possible --- the backend code has no way to inform the dynamic loader how to resolve cross-library references. So if the DL doesn't already understand the dependency it's never going to work. regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > I have zero interest in trying to support either. I doubt it's even > possible --- the backend code has no way to inform the dynamic loader > how to resolve cross-library references. So if the DL doesn't already > understand the dependency it's never going to work. Ok, that means less work for the extension facility (it was not targetted for it's first incarnation anyway) stuff. FWIW, I had in mind to use the dependancy information in the extension meta-data to issue more than one "dlopen()" when a plugin function is called. I'm being told that in my case linking uuid-ossp.so against uuid.so should do the trick, though. I'll drop the idea off the scope of the extension facility. Regards, -- dim
On Tuesday 23 June 2009 20:44:39 Dimitri Fontaine wrote: > === installing and removing an extension > > begin; > install extension foo with search_path = foo; > commit; > > Extensions authors are asked not to bother about search_path in their sql > scripts so that it's easy for DBAs to decide where to install them. The > with strange syntax is there to allow for the "install extension" command > to default to, e.g., pg_extension, which won't typically be the first > schema in the search_path. > > begin; > drop extension foo [cascade]; > commit; > > The "cascade" option is there to care about reverse depends. I have been thinking about a different use case for this, and I wonder whether that can fit into your proposal. Instead of installing an "extension", that is, say, a collection of types and functions provided by a third-party source, I would like to have a mechanism to deploy my own actual database application code. That is, after all, how I work with non-database deployments: I build a package (deb, rpm) from the code, and install it on the target machine. The package system here functions as a deployment aid both for "extensions" of the operating system and for local custom code. Applying this method to database code, with regard to your proposal, means first of all that naming this thing "extension" is questionable, and that installing everything by default into some schema like pg_extensions is inappropriate. If you look at how a dpkg or rpm package is structured, it's basically an archive (ar or cpio) of the files to install plus some control information such as name, version, dependencies, and various pre/post scripts. We already have the first part of this: pg_dump/pg_restore are basically tools to create an archive file out of a database and extract an archive file into a database. I have been toying with the idea lately to create a thin wrapper around pg_restore that would contain a bit of metainformation of the kind listed above. That would actually solve a number of problems already. And then, if pg_restore could be taught to do upgrades instead of just overwriting (e.g., ALTER TABLE ADD COLUMN instead of DROP TABLE + CREATE TABLE), this would all fall into place nicely. What this needs below the surface is basically librpm: an interface to describe and query which objects belong to which "package" and to associate pre/post scripts with packages. And I think that that interface is quite like the CREATE/DROP EXTENSION stuff that you are describing. (Pre/post scripts could be functions, actually, instead of scripts.) On the matter of schemas, I suggest that we consider two ideas that have helped RPM in its early days, when everyone had their own very specific ideas about what should be installed where: - file system hierarchy standard - relocations This means, we'd write up standard of where we think you *should* install things. And we expect that quality packages/bundles/extensions created for wider distribution install themselves in the right place without additional user intervention. But the packaging tool would provide a way to override this. Then, something that is a true extension could in fact be set up to install itself by default into pg_extensions, but a bundle containing local custom code would be set up so that it installs into a different schema or schemas by default. What do you think?
Peter Eisentraut wrote: > Instead of installing an "extension", that is, say, a collection> of types and functions provided by a third-party source,I would> like to have a mechanism to deploy my own actual database> application code. > On the matter of schemas, I suggest that we consider two ideas that have > helped RPM in its early days, when everyone had their own very specific ideas > about what should be installed where: > > - file system hierarchy standard > - relocations Of course if you have IMPORT from an extension, it's down to the DBA: INSTALL chinese_calendar; IMPORT FROM chinese_calendar SECTION (default) INTO SCHEMA pg_extension; IMPORT FROM chinese_calendar SECTION (year_names) INTO SCHEMA lookups; INSTALL peter_e_app; IMPORT FROM peter_e_app SECTION (all) INTO SCHEMA public; Of course this means two things: 1. Every "extension" has to have its own schema mappings. 2. The application view of the database is a sort of "default extension" Pros: - Namespace collisions begone! - Anything to help extension upgrades could be re-used for applications (and vice-versa) - Some stuff isn't visible outside the extension *at all* - You can separate extension installation from usage (good for multi-user setups). Cons: - Extra layer of indirection (find my namespace => namespace lookup => object) - Extensions need to list what they export in what sections - More code required -- Richard Huxton Archonet Ltd
Peter Eisentraut <peter_e@gmx.net> writes: > On Tuesday 23 June 2009 20:44:39 Dimitri Fontaine wrote: > Instead of installing an "extension", that is, say, a collection of types and > functions provided by a third-party source, I would like to have a mechanism > to deploy my own actual database application code. I'd like for the extension facility to cover application code in the database too, yes. Short of install time choice of schema I think we're there, but please refer to the infamous "search_path vs extensions" debate we had, that I wanted to consider as a pre-requisite for User Extension Design: http://archives.postgresql.org/pgsql-hackers/2009-05/msg00912.php After this, I'm considering that if we want to have anything, we'll have to begin implementing extensions and find a schema relocation facility later on. Unless you have one now? :) > That is, after all, how I work with non-database deployments: I build a > package (deb, rpm) from the code, and install it on the target machine. The > package system here functions as a deployment aid both for "extensions" of the > operating system and for local custom code. > > Applying this method to database code, with regard to your proposal, means > first of all that naming this thing "extension" is questionable, and that > installing everything by default into some schema like pg_extensions is > inappropriate. I'll be happy to be provided a better name if we manage to implement both ideas into the same facility, or see a way to get there in a near future :) > And then, if > pg_restore could be taught to do upgrades instead of just overwriting (e.g., > ALTER TABLE ADD COLUMN instead of DROP TABLE + CREATE TABLE), this would all > fall into place nicely. I'm not sure about this. What we want when using pg_restore is typically an upgrade, of PostgreSQL itself but of the extensions too... and I don't think we can manage from the metadata what the extension upgrading needs are. > What this needs below the surface is basically librpm: an interface to > describe and query which objects belong to which "package" and to associate > pre/post scripts with packages. And I think that that interface is quite like > the CREATE/DROP EXTENSION stuff that you are describing. (Pre/post scripts > could be functions, actually, instead of scripts.) Yes, and we're having both an entry into pg_catalog.pg_extension containing the metadata and pg_catalog.pg_depend entries to cook up a query acting as either `dpkg -L` or `rpm -ql`. Now, pre and post script if needed could also be pre_install.sql and post_install.sql with some support at the CREATE EXTENSION level. I didn't want to add them on the first round to avoid being pointed at doing over engineering, but now that it is you asking for it, let's do that :) > On the matter of schemas, I suggest that we consider two ideas that have > helped RPM in its early days, when everyone had their own very specific ideas > about what should be installed where: > > - file system hierarchy standard > - relocations > > This means, we'd write up standard of where we think you *should* install > things. And we expect that quality packages/bundles/extensions created for > wider distribution install themselves in the right place without additional > user intervention. The aim is for users to \i extension.sql which only contains the CREATE EXTENSION command, then INSTALL EXTENSION extension, and be done with it. > But the packaging tool would provide a way to override > this. Then, something that is a true extension could in fact be set up to > install itself by default into pg_extensions, but a bundle containing local > custom code would be set up so that it installs into a different schema or > schemas by default. > > What do you think? How do you implement relocate in a way to guarantee there's no security disaster waiting to happen? Namely that a function foo() calling another function foo_support_fn() from within the extension won't be calling a (malicious?) user defined foo_support_fn() from another schema, depending on run time search_path? Having both extension function calls schema qualified and relocations is the biggest problem we're facing, and it seems we're still short of a solution for it... or did I just miss it? -- dim
Richard Huxton <dev@archonet.com> writes: > INSTALL chinese_calendar; > IMPORT FROM chinese_calendar SECTION (default) INTO SCHEMA pg_extension; > IMPORT FROM chinese_calendar SECTION (year_names) INTO SCHEMA lookups; Please see Andrew Dunstan mail about using some notion of ALIAS (is that a standard compliant SYNONYM?) for handling this: http://archives.postgresql.org/pgsql-hackers/2009-05/msg01391.php http://archives.postgresql.org/pgsql-hackers/2009-05/msg01401.php Regards, -- dim
Dimitri Fontaine wrote: > Richard Huxton <dev@archonet.com> writes: > >> INSTALL chinese_calendar; >> IMPORT FROM chinese_calendar SECTION (default) INTO SCHEMA pg_extension; >> IMPORT FROM chinese_calendar SECTION (year_names) INTO SCHEMA lookups; >> > > Please see Andrew Dunstan mail about using some notion of ALIAS (is that > a standard compliant SYNONYM?) for handling this: > > http://archives.postgresql.org/pgsql-hackers/2009-05/msg01391.php > http://archives.postgresql.org/pgsql-hackers/2009-05/msg01401.php > > Please particularly see this sentence: "But unless someone wants to tackle that I think we should leave schema management entirely alone, and leave it up to the extension author / DBA between them." I think we are in some danger of massively overdesigning this feature (and of repeating past discussions with little extra content). Please don't keep adding bells and whistles. The best development is almost always incremental. Let's start simple and then add features. cheers andrew
On Jul 23, 2009, at 9:09 AM, Andrew Dunstan wrote: > Please particularly see this sentence: "But unless someone wants to > tackle that I think we should leave schema management entirely > alone, and leave it up to the extension author / DBA between them." > > I think we are in some danger of massively overdesigning this > feature (and of repeating past discussions with little extra > content). Please don't keep adding bells and whistles. The best > development is almost always incremental. Let's start simple and > then add features. This is what I was trying to get at in my last post in the other thread. While throwing some ideas out on how to handle some of these issues, where there is no clear agreement on what to do, I think we should punt in favor of implementing those parts for which there *is* general agreement. Best, David