Обсуждение: The scope of extensions
Hi folks, I'm a happy creator/user of extensions (e.g. my postgresql_debversion extension). But I'm wondering if they can be used for more than just simple types: - can an extension depend upon another extension? This would probably be implicit based upon the use of an object which belonged to another extension? (Are the META.json dependency metadata actually used at runtime?) - can relations just as tables, indexes, views and associated stuff such as triggers be included as part of an extension? Are there limits upon what may be part of an extension? The reason for the above is that I'd very much like to be able to version my entire application's schema using the extension mechanism (or something based upon the ideas in the extensions mechanism). Since SCHEMA is already taken, maybe CREATE/ALTER/DROP_APPLICATION. This would permit easy installation and upgrade of all the objects relating to a single application installed in the database. Thanks, Roger -- .''`. Roger Leigh : :' : Debian GNU/Linux http://people.debian.org/~rleigh/ `. `' schroot and sbuild http://alioth.debian.org/projects/buildd-tools `- GPG Public Key F33D 281D 470A B443 6756 147C 07B3 C8BC 4083 E800
On Mon, Apr 16, 2012 at 10:05 AM, Roger Leigh <rleigh@codelibre.net> wrote: > Hi folks, > > I'm a happy creator/user of extensions (e.g. my postgresql_debversion > extension). But I'm wondering if they can be used for more than just > simple types: > > - can an extension depend upon another extension? This would probably > be implicit based upon the use of an object which belonged to another > extension? (Are the META.json dependency metadata actually used at > runtime?) yes, and they already do. for example earthdistance depends on cube. > - can relations just as tables, indexes, views and associated stuff > such as triggers be included as part of an extension? Are there > limits upon what may be part of an extension? sure. generally anything you can install with SQL can be included (and that includes SQL wrapped C functions). there are of course pretty high requirements of code quality, documentation and usability if you want your extension to be packaged with the core system but otherwise just take it where you want to go. > The reason for the above is that I'd very much like to be able to > version my entire application's schema using the extension mechanism > (or something based upon the ideas in the extensions mechanism). Since > SCHEMA is already taken, maybe CREATE/ALTER/DROP_APPLICATION. This > would permit easy installation and upgrade of all the objects relating > to a single application installed in the database. not following that -- it sounds like you are trying to hook into the grammar? that's something you can't do through an extension. but it's an interesting thought to do application versioning through the extension system...i'm pretty sure it hasn't been tried. there may be some pitfalls though. merlin
On Mon, Apr 16, 2012 at 10:22:19AM -0500, Merlin Moncure wrote: > On Mon, Apr 16, 2012 at 10:05 AM, Roger Leigh <rleigh@codelibre.net> wrote: > > The reason for the above is that I'd very much like to be able to > > version my entire application's schema using the extension mechanism > > (or something based upon the ideas in the extensions mechanism). Since > > SCHEMA is already taken, maybe CREATE/ALTER/DROP_APPLICATION. This > > would permit easy installation and upgrade of all the objects relating > > to a single application installed in the database. > > not following that -- it sounds like you are trying to hook into the > grammar? that's something you can't do through an extension. but it's > an interesting thought to do application versioning through the > extension system...i'm pretty sure it hasn't been tried. there may be > some pitfalls though. This was mainly just speculative--in the case that the extension system didn't support everything I wanted, I was wondering if extending the grammar would be a viable approach; obviously it would require other work too! Every project I've worked on which uses PostgreSQL has independently implemented its own set of installation and upgrade scripts, which has typically included some form of table for storing the current schema version and other settings to allow the scripts to safely do their job. However, I'm not a big fan of unnecessary wheel reinvention, and if PostgreSQL could provide a standard mechanism for doing this which all applications could utilise, that would be (IMO) an absolutely fantastic feature. If extensions can be used as they stand to realise this, then that's absolutely great: the end user installation instructions can be reduced to CREATE EXTENSION myapplication; and the equivalent for upgrades. I'm not sure if another keyword would be useful in this context, since this is much more than a single extension, it's an entire schema. Regards, Roger -- .''`. Roger Leigh : :' : Debian GNU/Linux http://people.debian.org/~rleigh/ `. `' schroot and sbuild http://alioth.debian.org/projects/buildd-tools `- GPG Public Key F33D 281D 470A B443 6756 147C 07B3 C8BC 4083 E800
On Mon, 2012-04-16 at 16:46 +0100, Roger Leigh wrote: > On Mon, Apr 16, 2012 at 10:22:19AM -0500, Merlin Moncure wrote: > > On Mon, Apr 16, 2012 at 10:05 AM, Roger Leigh <rleigh@codelibre.net> wrote: > > > The reason for the above is that I'd very much like to be able to > > > version my entire application's schema using the extension mechanism > > > (or something based upon the ideas in the extensions mechanism). Since > > > SCHEMA is already taken, maybe CREATE/ALTER/DROP_APPLICATION. This > > > would permit easy installation and upgrade of all the objects relating > > > to a single application installed in the database. > > > > not following that -- it sounds like you are trying to hook into the > > grammar? that's something you can't do through an extension. but it's > > an interesting thought to do application versioning through the > > extension system...i'm pretty sure it hasn't been tried. there may be > > some pitfalls though. > > This was mainly just speculative--in the case that the extension > system didn't support everything I wanted, I was wondering if > extending the grammar would be a viable approach; obviously it would > require other work too! > > Every project I've worked on which uses PostgreSQL has independently > implemented its own set of installation and upgrade scripts, which > has typically included some form of table for storing the current > schema version and other settings to allow the scripts to safely do > their job. However, I'm not a big fan of unnecessary wheel > reinvention, and if PostgreSQL could provide a standard mechanism > for doing this which all applications could utilise, that would be > (IMO) an absolutely fantastic feature. If extensions can be used > as they stand to realise this, then that's absolutely great: the > end user installation instructions can be reduced to > CREATE EXTENSION myapplication; > and the equivalent for upgrades. I'm not sure if another keyword > would be useful in this context, since this is much more than a > single extension, it's an entire schema. > Won't work if you care to save your database with pg_dump. Any tables created by extensions won't be saved with pg_dump. All you will get is a "CREATE EXTENSION myapplication;", and no data. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
On Mon, 2012-04-16 at 21:16 +0200, Guillaume Lelarge wrote: > On Mon, 2012-04-16 at 16:46 +0100, Roger Leigh wrote: > > On Mon, Apr 16, 2012 at 10:22:19AM -0500, Merlin Moncure wrote: > > > On Mon, Apr 16, 2012 at 10:05 AM, Roger Leigh <rleigh@codelibre.net> wrote: > > > > The reason for the above is that I'd very much like to be able to > > > > version my entire application's schema using the extension mechanism > > > > (or something based upon the ideas in the extensions mechanism). Since > > > > SCHEMA is already taken, maybe CREATE/ALTER/DROP_APPLICATION. This > > > > would permit easy installation and upgrade of all the objects relating > > > > to a single application installed in the database. > > > > > > not following that -- it sounds like you are trying to hook into the > > > grammar? that's something you can't do through an extension. but it's > > > an interesting thought to do application versioning through the > > > extension system...i'm pretty sure it hasn't been tried. there may be > > > some pitfalls though. > > > > This was mainly just speculative--in the case that the extension > > system didn't support everything I wanted, I was wondering if > > extending the grammar would be a viable approach; obviously it would > > require other work too! > > > > Every project I've worked on which uses PostgreSQL has independently > > implemented its own set of installation and upgrade scripts, which > > has typically included some form of table for storing the current > > schema version and other settings to allow the scripts to safely do > > their job. However, I'm not a big fan of unnecessary wheel > > reinvention, and if PostgreSQL could provide a standard mechanism > > for doing this which all applications could utilise, that would be > > (IMO) an absolutely fantastic feature. If extensions can be used > > as they stand to realise this, then that's absolutely great: the > > end user installation instructions can be reduced to > > CREATE EXTENSION myapplication; > > and the equivalent for upgrades. I'm not sure if another keyword > > would be useful in this context, since this is much more than a > > single extension, it's an entire schema. > > > > Won't work if you care to save your database with pg_dump. Any tables > created by extensions won't be saved with pg_dump. All you will get is a > "CREATE EXTENSION myapplication;", and no data. > Actually, I'm not completely right here. You may configure your extension to allow your tables to be dumped. See http://www.postgresql.org/docs/9.1/interactive/extend-extensions.html#AEN51978 for details. IOW, it may work, but you need to be extra-careful. I don't know anyone doing this right now. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
On Mon, Apr 16, 2012 at 2:20 PM, Guillaume Lelarge <guillaume@lelarge.info> wrote: >> > Every project I've worked on which uses PostgreSQL has independently >> > implemented its own set of installation and upgrade scripts, which >> > has typically included some form of table for storing the current >> > schema version and other settings to allow the scripts to safely do >> > their job. However, I'm not a big fan of unnecessary wheel >> > reinvention, and if PostgreSQL could provide a standard mechanism >> > for doing this which all applications could utilise, that would be >> > (IMO) an absolutely fantastic feature. If extensions can be used >> > as they stand to realise this, then that's absolutely great: the >> > end user installation instructions can be reduced to >> > CREATE EXTENSION myapplication; >> > and the equivalent for upgrades. I'm not sure if another keyword >> > would be useful in this context, since this is much more than a >> > single extension, it's an entire schema. >> > >> >> Won't work if you care to save your database with pg_dump. Any tables >> created by extensions won't be saved with pg_dump. All you will get is a >> "CREATE EXTENSION myapplication;", and no data. >> > > Actually, I'm not completely right here. You may configure your > extension to allow your tables to be dumped. See > http://www.postgresql.org/docs/9.1/interactive/extend-extensions.html#AEN51978 for details. > > IOW, it may work, but you need to be extra-careful. I don't know anyone > doing this right now. yeah -- it's a cute idea that may (or may not) work. other things that may burn you is the order execution of create extension scripts? merlin
On Mon, Apr 16, 2012 at 09:20:14PM +0200, Guillaume Lelarge wrote: > On Mon, 2012-04-16 at 21:16 +0200, Guillaume Lelarge wrote: > > On Mon, 2012-04-16 at 16:46 +0100, Roger Leigh wrote: > > > On Mon, Apr 16, 2012 at 10:22:19AM -0500, Merlin Moncure wrote: > > > > On Mon, Apr 16, 2012 at 10:05 AM, Roger Leigh <rleigh@codelibre.net> wrote: > > > > > The reason for the above is that I'd very much like to be able to > > > > > version my entire application's schema using the extension mechanism > > > > > (or something based upon the ideas in the extensions mechanism). Since > > > > > SCHEMA is already taken, maybe CREATE/ALTER/DROP_APPLICATION. This > > > > > would permit easy installation and upgrade of all the objects relating > > > > > to a single application installed in the database. > > > > > > > > not following that -- it sounds like you are trying to hook into the > > > > grammar? that's something you can't do through an extension. but it's > > > > an interesting thought to do application versioning through the > > > > extension system...i'm pretty sure it hasn't been tried. there may be > > > > some pitfalls though. > > > > > > This was mainly just speculative--in the case that the extension > > > system didn't support everything I wanted, I was wondering if > > > extending the grammar would be a viable approach; obviously it would > > > require other work too! > > > > > > Every project I've worked on which uses PostgreSQL has independently > > > implemented its own set of installation and upgrade scripts, which > > > has typically included some form of table for storing the current > > > schema version and other settings to allow the scripts to safely do > > > their job. However, I'm not a big fan of unnecessary wheel > > > reinvention, and if PostgreSQL could provide a standard mechanism > > > for doing this which all applications could utilise, that would be > > > (IMO) an absolutely fantastic feature. If extensions can be used > > > as they stand to realise this, then that's absolutely great: the > > > end user installation instructions can be reduced to > > > CREATE EXTENSION myapplication; > > > and the equivalent for upgrades. I'm not sure if another keyword > > > would be useful in this context, since this is much more than a > > > single extension, it's an entire schema. > > > > Won't work if you care to save your database with pg_dump. Any tables > > created by extensions won't be saved with pg_dump. All you will get is a > > "CREATE EXTENSION myapplication;", and no data. > > Actually, I'm not completely right here. You may configure your > extension to allow your tables to be dumped. See > http://www.postgresql.org/docs/9.1/interactive/extend-extensions.html#AEN51978 for details. > > IOW, it may work, but you need to be extra-careful. I don't know anyone > doing this right now. Thanks, I'll have a closer look at this. An equivalent to EXTENSION which did dump everything by default would be the ideal solution. I wonder how much work that would entail--I could take a stab at it if it's not insanely complex, and it's not considered absolutely insane. Regards, Roger -- .''`. Roger Leigh : :' : Debian GNU/Linux http://people.debian.org/~rleigh/ `. `' schroot and sbuild http://alioth.debian.org/projects/buildd-tools `- GPG Public Key F33D 281D 470A B443 6756 147C 07B3 C8BC 4083 E800