Re: search_path vs extensions

On May 29, 2009, at 12:41 PM, Greg Stark wrote:

> That said, I don't mind the idea of having a way to push things onto
> search path like you often do in sh using PATH=/foo/bar:$PATH.

Yes, +1.

> But I think the only reason to install something into a separate
> schema is precisely if you *want* that schema to not be visible to
> users automatically. So having more and more complex ways to include
> schemas in the search path automatically is fixing a problem created
> by setting things up wrong in the first place.

A reason I've run into is to make database maintenance and migration
easier. For a recent client, all contrib modules were installed in a
single, separate schema, named contrib. This makes it easy to dump all
of the database code but not dump the contrib stuff, and that's useful
for two reasons:

1. The client was dumping the schema into svn every night, and the
contrib stuff just cluttered it up (I'm not saying checking a schema
in like this is a good idea, just that I've seen it).

2. Migrating to a new version of PostgreSQL, the server can be pre-
build with the contrib schema, with new versions with the new release,
and then the dump from the old server doesn't have the contrib crap in
it to cause conflicts.

So, yeah, there may be collisions that a given DBA has to deal with,
and then will want more than one schema. But for the vast majority of
uses, I think that a pg_extensions schema will serve nicely to keep
third-party extensions separate from in-house database objects.

Best,

David