Re: More extension issues: ownership and search_path

Tom Lane <tgl@sss.pgh.pa.us> writes:
> One is ownership.  Since we don't record the identity of the user who
> created an extension, there's no way for pg_dump to ensure that it's
> recreated by the same user.  I think we'll regret that in future even
> if you don't think it's problematic today.  In particular, I foresee
> eventually allowing non-superusers to load extensions, so I think this
> is going to follow pretty much the same trajectory as procedural
> languages, which we originally did not track ownership for.  In short,
> I think we'd better add an extowner column to pg_extension.

Agreed.  There's no need to have it now but we will add it at some
point.  So if now is when that works the best for you, I'm happy to see
that happen :)

Would it help that I prepare some of those modifications, as patches
over the extension's patch that you started from?

> Another is the search_path setting.  Currently this is actually rather
> broken since pg_dump makes no effort at all to ensure that search_path
> is the same at reload time as it was when the extension was created,
> and thus the contained objects could easily go into the wrong schema.

Well there's some code to place the extension's schema at the first
place in the search_path before executing the script, already.

> But even without thinking about dump/reload, it seems to me that it
> would be a good thing for reproducibility if CREATE EXTENSION were to
> forcibly set search_path before running the extension's SQL script.
>
> The level-zero version of that would be to do the equivalent of
>     SET LOCAL search_path = @extschema@
> such that the path only contains the target schema and nothing else.

Spelled this way, I could see attaching SET to CREATE EXTENSION the same
way we did for CREATE FUNCTION.  I'm not too sure about what other set
of GUCs would be useful to support here, but that would be a good
mechanism to use I would say.

> The trouble with this simplistic approach is that it doesn't work nicely
> if one extension depends on another --- you probably want the search
> path to include the schema(s) the required extensions got installed
> into.  Of course inter-extension dependencies aren't going to work
> anyway unless pg_dump knows about them so it can make sure to load the
> extensions in the right order.  So where I think we're going to end up
> is adding a clause along the line of "USING list-of-extension-names"
> to CREATE EXTENSION, storing those dependencies explicitly, and having
> the CREATE EXTENSION code set search_path to the target schema followed
> by the target schema(s) of the USING extensions.  Not sure if this is
> worth doing immediately or can be left for 9.2.  At least in the contrib
> modules, there are no interdependencies, and I don't know whether any
> exist in the wider world of existing extensions.

We have a interdependency in contrib, earthdistance depends on cube
already.  In skytools, PGQ is composed of several parts that are
packaged as a single extension now, but whose sources are maintained in
separate parts.  Other than that, I think tricky scripts that depends on
some objects of the extension to already be usable will be simpler to
solve with splitting the extensions and adding some dependency.

So while we said this is 9.2 material, if you want to tackle the whole
search_path at restore time issue (I did only the creation namespace,
thinking it would be enough) fully, you need dependency too.

I think we should then register some core components as extensions for
the sake of interdependencies here, too.  \dx would then list PostgreSQL
itself and its (major) version, and the installed PL would need to be
there, and maybe some "features" too — but the way we handle bugfix only
minor upgrade makes that useless for us I think.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support