Re: search_path vs extensions

On Fri, 29 May 2009, Greg Stark wrote:

> The only reason people are having trouble managing their search_path is
> because they're not using it as intended and putting things in lots of
> different schemas that they intend to all be visible. If they put
> everything they intend to be visible to users in one schema they
> wouldn't have this problem.

Every PostgreSQL installation I've ever seen that heavily uses schemas
aggressively uses them to partition up the various applications into
components that can easily be reinstalled, the goal being to make
deploying new versions easier. Put component A into schema A, component B
into schema B, and then if you need to make a change just to the workings
of B you can easily dump the data from B, "DROP SCHEMA s CASCADE",. apply
new DDL change, and then reinstall things associated with that component
without touching anything in A. The nice thing about this approach,
compared with applying DDL deltas, is that afterwards you know you've got
a complete chunk of code each time that will also install somewhere else
identically into that schema.

That I run into all the time, usually with every schema in the default
search_path. Using schemas primarly as a security mechanism isn't nearly
as popular as far as I've seen.

Anyway, I think the answer to all the extension related questions should
be to pick whatever lets a prototype that handles the dependency and
dump/reload problems get solved most easily. You really need to use the
simplest possible schema standard that works for extensions and decouple
the problems from one another if any progress is going to get made here.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD