Re: The scope of extensions

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(at)codelibre(dot)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