There are some things that the current extensions patch leaves
indeterminate during a dump and reload cycle, which strikes me
as a bad thing.
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.
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.
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 ofSET LOCAL search_path = @extschema@
such that the path only contains the target schema and nothing else.
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.
Comments?
regards, tom lane