Re: Dumping an Extension's Script

On Wed, Dec 5, 2012 at 5:22 AM, Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr> wrote:
> This patch is all about enabling users to create extension without
> having to ship them as root on the file system of the database(s)
> server(s) first.

Right, but it changes the way that existing extensions *dump*, which
seems to me to undo the use case that works now.

I mean, the advantage of dumping an extension as CREATE EXTENSION
hstore is that you can reload that dump on a different server with a
newer version of hstore installed, and it'll still work. If we go
back to dumping all of the SQL commands that compose that extension,
then it'll break again, in exactly the way things were broken before
we had extensions in the first place. Back in the bad old days, you'd
dump your old database (which had all of the SQL commands for say
hstore) and then reload it on your new database - and it would fail,
because the old SQL commands didn't match the new binaries. Oops.
With the extension mechanism, it all works just fine: the old database
emits CREATE EXTENSION hstore and the new database can execute that
just fine. You still have a problem if the extension has meanwhile
been changed in a backwards-incompatible way that doesn't work for
your application (i.e. you're using the => operator which has since
been removed) but hopefully that doesn't happen too often, and in any
event it seems relatively unavoidable. And it takes nothing away from
the problem that extensions DO solve, which is incompatibilities
between the SQL file and the shared library.

> When you can code your extension using dynamic code such as SQL or
> PL/pgSQL, PL/pythonu or PL/perl, there's absolutely no good reason to
> have to do the "ship on the server's file system first" that I can see.
>
> Technically creating an extension "inline" (sending its definition in
> the CREATE EXTENSION query itself) solves the problem of having to
> access the server's file system as root.

True, but so does not putting the code into an extension at all. You
can just create loose functions and operators. It's unclear to me
what advantage the extension mechanism provides if there's no shared
library and no on-disk files involved.

> Then, next pg_dump will include "CREATE EXTENSION foo;" as usual and at
> pg_restore time that access files on the file systems. But maybe you
> still are not granted access to the server's file system as root on the
> pg_restore target, right? So now you need to be able to include the
> extension's script into the dump.

Granting for the moment that there's a reason to call this an
extension at all, rather than a schema or just a bunch of random
CREATE commands, which is not obvious to me, yes, you need to include
it in the dump. But sure then the extension needs to be marked as
being, somehow, a different flavor of extension that can only use SQL
(not shlibs) and needs to be dumped in-line, because otherwise, as
noted above, we break things for the flavor of extensions we've
already got.

Also, even there, it seems to me that it ought to work something like this:

CREATE EXTENSION inline_extension NULL; -- create an extension with no members
CREATE FUNCTION blahblahblah ...
ALTER EXTENSION inline_extension ADD FUNCTION blahblab ...
and so on for all the other members

That is, the extension members should just become dumpable objects.
This seems quite bizarre since the whole point of extensions AIUI is
to avoid dumping the members, but it's better than what the patch
implements. In the patch, IIRC, you emit all the members as a
"separate" dump that gets enclosed by dollar quotes. This strikes me
as ugly, and I think you can construct circular dependency situations
in which it will fail outright.

> Now, we don't want to have more than one kind of extensions. That's what
> we learnt all together when reviewing my proposal from last year. Having
> more than one way to ship an extension is good, having two different
> animals with two different incompatible behaviors named the same thing
> is bad. The solution we found is then to be able to include an
> extension's script into pg_dump's output, and that's what my current
> patch implements, per last year review.

I don't think I agree. I don't see a problem having more than one
kind of extensions, but I'm worried that you're trying to shoehorn
something that isn't really an extension into an extension-sized box.
And I sure don't want that to mean "let's break stuff that works right
now".

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