Re: Dumping an Extension's Script - Mailing list pgsql-hackers

On Wed, Dec 5, 2012 at 2:19 PM, Andres Freund <andres@2ndquadrant.com> wrote:
>> IIUC, under this proposal, the client would instead need to execute
>> some SQL code that looks something this (I'm faking the syntax here,
>> forgive me, but the patch doesn't seem to contemplate ALTER):
>>
>> ALTER EXTENSION myextension UPDATE TO 1.1 USING SCRIPT $$
>>    ALTER EXTENSION myextension DROP FUNCTION internalfunc1(int);
>>    DROP FUNCTION internalfunc1(int);
>>    CREATE FUNCTION internalfunc2(int);
>>    ALTER EXTENSION myextension ADD FUNCTION internalfunc2(int);
>>    CREATE FUNCTION externalfunc3(int);
>>    ALTER FUNCTION myextension ADD FUNCTION externalfunc3(int);
>> $$;
>>
>> That doesn't really look like an improvement to me.  What am I missing?
>
> They should be able to simply slurp the extension from a file, possibly
> even install it outside their own update mechanism. Given that you don't
> know which version was installed beforehand thats not really possible
> without some infrastructure.
>
> And they should be able to drop the extension again afterwards without
> it leaving a trace. Nearly all I have seen out there fails at that, and
> the extension mechanism provides tracking of that.

Ah, OK.  Well, it sounds like this might be a decent fit for the
TEMPLATE concept proposed upthread, then.

I have no objection whatsoever to the concept of storing the SQL and
control files somewhere that doesn't need access to the server
filesystem - in fact, I think I previously proposed allowing those to
be stored in a database table.  You could do that with something like:

CREATE TEMPLATE yadda;
ALTER TEMPLATE yadda ADD FILE 'yadda--1.0.sql' CONTENT $$...$$;

...or whatever.  And that'd be 100% fine with me, and it could dump
and restore just that way, and life would be good.  Or at least, it
sounds to me like that would meet the requirements you are
articulating without breaking anything that works today.  In fact, it
sounds pretty cool.

The root of my objections upthread, I think, is that the design this
patch puts on the table seems to me to conflate the extension (which
is always a database object) with the template (which is *currently*
always a filesystem object).  I think that's bound to create some
problems.  In the patch as it exists today, I think those problems are
going to leak out in the form of breaking some of the things for which
extensions can currently be used, but even if we address those points
I have a sneaking suspicion that there will be others.

For example, your point (in the portion of your email I'm not quoting
here) about an upgrade across multiple version is well-taken - you
need a different script depending on the version that's currently
installed.  Fixing that, though, seems to require a catalog of upgrade
scripts, so that the server can look at the installed version and the
available scripts and decide how to proceed.  That catalog currently
takes the form of separate files in the filesystem, but I don't see
any reason why we can't store it somewhere else.  What I'm not sold on
is the idea of shuttling it across as part of CREATE/ALTER EXTENSION
statements.  I'm willing to be persuaded, but right now I can't see
how that's ever going either robust or convenient.  Making it part of
a separate SQL object type gets around that problem rather nicely,
IMHO.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company