Re: search_path vs extensions - Mailing list pgsql-hackers

On May 27, 2009, at 1:50 AM, Dimitri Fontaine wrote:

> The moment you're adding specific schemas where to put extensions
> into,
> you have to adapt your search_path. Some applications already have to
> manage search_path for their own needs, so we're trying to avoid
> having
> those people to care about extensions schemas and application schema
> at
> the same time.

That doesn't seem like much of a problem to me. I already do this for
extensions. I agree that what you suggest should be the default, but I
should be able to optionally install extensions in whatever schema I
deem appropriate, especially if I want to avoid conflicts.

> This proposal tries to solve previous one limitations. It's very
> good in
> the typical case when you want each extension to be installed in one
> (or
> more) schemas but don't want to have the application to care about it.
> Then you add your extensions schemas into pre_search_path and
> application schemas into search_path, so that the application doesn't
> have to manage pre_search_path.

So are pre_search_path and search_path and post_search_path basically
just concatenated into that order? That doesn't seem to buy you much.

> Now it could be that your application is historically using the same
> function names as some extension you're now adding to the server, and
> you want to control which function is called when not schema
> qualified. So you have the post_search_path to play with too.

It seems to me you'd just schema-qualify in this case. I mean, that's
kind of the point of schemas.

> The idea being that application developpers will maintain search_path
> for the application schemas (and this search_path can vary depending
> on
> the application role which connects to the database, of course), and
> the
> DBA team will make extensions available transparently to the
> application
> by adding the extension's schemas in either pre_search_path or
> post_search_path.

I think more useful would be a way to append or prepend schemas to the
search path within a given context (in a transaction or a connection).
That way, instead of doing stuff like this:
    BEGIN;     SET search_path = foo,bar,public;
    -- ...
     COMMIT;     RESET search_path;

…which suffers from an inability to easily modify an existing path
(yes, I know I  can look it up and parse it, but please), I could just
do something like this:
     BEGIN;     prepend_search_path('foo,bar');
     COMMIT;

And then it would be reverted at the end of the transaction. Or it
could be for the duration of a connection; that probably makes more
sense.

> I hope I've added clarity to the point, rather than only some extra
> verbosity... :)

Yes, but it just seems like unnecessary complexity to me. We don't
want to learn the lessons of Java's CLASSPATH by making things *more*
complicated.

Best,

David