Re: search_path improvements - Mailing list pgsql-hackers

Greg,

> Well the goal is to make them simpler. I don't know any language that
> has implemented what you describe. Either you have access to the
> internal methods of a class or you don't and you only have access to
> the public api. That seems to work for much more sophisticated
> languages than ours just fine.

Um, PostgreSQL isn't a programming language.  It's a DBMS.

You're arguing what DBAs should do using some theoretical idealized 
DBMS.  You're ignoring what DBAs *do* do currently using the real world 
PostgreSQL.  I, for one, am not interested in theory.

>> Organization/Maintainability: when you have hundreds or thousands of
>> database objects, you want "folders" to put them in just so that you can
>> keep track of them and view them in easy-to-digest groups, just as you deal
>> with files in a filesystem.
>
> Well when you ls a directory or perform some operation on a file you
> only work with what's in that directory, not everything in the
> hierarchy.

Precisely!  And that's why DBAs often use lots of schema to divide up 
their hundreds of database objects.

> I don't think that gets any easier with better tools. This is the same
> reason unix systems don't put every tool in a different directory and
> then insist you put every directory in your path based on which tools
> each user should have access to.

What, you're telling me you never had a cron job break because of $PATH 
issues?

> It doesn't get any ideasier if you have
> every function hard coding inside it assumptions about what schemas it
> will need.

When have I proposed that?

>> As I said before, schema conbine 3 purposes: organization, visibility and
>> security, into one structure.  Which is why it's difficult to make them work
>> perfectly for all 3 purposes.  We could, however, make them work better.
>
> How is this different from any other analogous system? The filesystem
> uses directories for all three of the above, for example?

It's very similar to issues with the filesystem.  Unfortunately, while 
very familiar, Unix filesystems aren't really a positive example; $PATHs 
and UMASK are a PITA an have forced many and admin (and OS) to come up 
with complex tools to manage them.

> Having three different namespaces, one for organizing your code, one
> to control visibility, and one to control security would be 9 times
> more complex, i think.

I didn't say I had a good answer to this problem.  You just need to be 
aware of the three purposes of schema when proposing any improvements; 
your previous e-mails kept making the assumption that schema were used 
*only* for visibility, and never for security or organization.

> 3rd-party vendor code is precisely what I'm thinking of when I point
> out that having global state to override what the code requests is a
> recipe for problems. 3rd-party vendors would be left with no way to
> write their code such that they could guarantee it would work -- the
> DBA would always be able to break it by setting this variable. And
> some other code might require this variable to be set leaving the
> hapless DBA with no right option.

"You must set search_path_suffix='information_schema'" to use this tool 
is vastly simpler than what you'd deal with currently if you had to deal 
with a Microsoftian tool which assumed that information_schema was 
automatically in your search path.

Again, I'm looking to improve what we actually *have* right now, rather 
than implement some theoretically ideal database.

> Well I don't mind push but I still think pop is an error. What you
> really want to do is restore it to the value you started with. You
> don't want to remove the last element since that may not be the
> element you added. Some function you called may have added an extra
> element on the head.

Yeah, "pop" is a misnomer; what I'd want is 
search_path_del(search_path,'admin') ... that is, a way to remove a 
specific schema from the list.

-- 
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com