Re: search_path vs extensions - Mailing list pgsql-hackers

Hi,

Tom Lane <tgl@sss.pgh.pa.us> writes:
> Andrew Gierth <andrew@tao11.riddles.org.uk> writes:
>> Splitting up search_path is something I've been thinking about for a
>> while (and threw out on IRC as a suggestion, which is where Dimitri
>> got it); it was based on actual experience running an app that set the
>> search path in the connection parameters in order to select which of
>> several different schemas to use for part (not all) of the data.  When
>> setting search_path this way, there is no way to set only part of it;
>> the client-supplied value overrides everything.
>
>> Obviously there are other possible solutions, but pretending there
>> isn't a problem will get nowhere.
>
> I agree that some more flexibility in search_path seems reasonable,
> but what we've got at the moment is pretty handwavy.  Dimitri didn't
> suggest what the uses of the different parts of a three-part path
> would be, and also failed to say what the implications for the default
> creation namespace would be, as well as the existing special handling
> of pg_temp and pg_catalog.  That stuff all works together pretty
> closely; it'd be easy to end up making it less usable not more so.

What I have in mind is not to change current semantics, but allow users
to have easier ways to manage things. Some other place in this thread we
see syntax sugar propositions or tools to allow adding schemas in first
or last place of search_path.

It could be that some other ideas or better tools would be a much better
way to solve the problem at hand, but as you asked, here's a rough
sketch of how I'd use what I'm proposing:

The mydb database is used from several applications and roles, and host
10 application schemas and 3 extensions (ip4r, prefix, pgq,
say). Depending on the role, not all 10 schemas are in the search_path,
and we're using non qualified objects names when the application
developer think they're part of the database system (that includes
extensions).

What this currently means is that all role specific schemas must embed
the extensions schemas at the right place. When prefix extension is
added, all of them are to get reviewed.

A better way to solve this is to have the database post_search_path (or
call it search_path_suffix) contain the extensions schemas. Now the
roles are set up without search_path_suffix, and it's easy to add an
extension living in its own schema. (we'll have to choose whether
defining a role specific search_path_suffix overrides the database
specific one, too).

Having all extensions live in pg_extension schema also solves the
problem in a much easier way, except for people who care about not
messing it all within a single schema (fourre-tout is the french for a
place where you put anything and everything).

As Josh is saying too, as soon as we have SQL level extension object
with dependancies, we'll be able to list all of a particular extension's
objects without needing to have them live in separate schemas.\df pgq.  -- list all functions in schema pgq\dt pgq.  --
listall tables in schema pgq\de pgq.  -- list all objects provided by extension pgq
 

Still, for extension upgrading or name collisions between extensions, or
some more cases I'm not thinking about now, pg_extension will not be all
what you need. We already have schemas and search_path, and it's not
always pretty nor fun to play with. Would prefix/suffix components help?

Regards,
-- 
dim