Re: schema support, was Package support for Postgres - Mailing list pgsql-hackers

Bill Studenmund writes:

> > Because SQL says so.  All objects in a schema belong to the owner of the
> > schema.  In simple setups you have one schema per user with identical
> > names.  This has well-established use patterns in other SQL RDBMS.
>
> Then implimenting schemas will cause a backwards-incompatabile change
> regarding who can add/own functions (and operators and ..).
>
> Mainly because when we introduce schemas, all SQL transactions will have
> to be performed in the context of *some* schema.  I think "DEFAULT" was the
> name you mentioned for when there was no schema matching the username. As
> "DEFAULT" (or whatever we call it) will be made by the PG super user (it
> will actually be added as part of initdb), then that means that only the
> super user will own functions.

If you want to own the function you should create it in your schema.  If
you want to create a function and let someone else own it, then ask
someone else for write access to their schema.  (This should be a rare
operation and I don't think SQL provides for it, so we can ignore it in
the beginning.)  If there is no schema you have write access to then you
cannot create things.  People have been dying for that kind of feature,
and schemas will enable us to have it.

Think about it this way:  In its simplest implementation (which is in fact
the Entry Level SQL92, AFAIR), a schema can only have the name of the user
that owns it.  I suspect that this is because SQL has no CREATE USER, so
CREATE SCHEMA is sort of how you become a user that can do things.  At the
same time, schemas would space off the things each user creates, and if
you want to access someone else's stuff you have to prefix it with the
user's name <user>.<table>, sort of like ~user/file.  The generic
"namespace" nature of schemas only comes from the fact that in higher
SQL92 levels a user can own more than one schema with different names.

(Interesting thesis:  It might be that our users are in fact schemas
(minus the parser changes) and we can forget about the whole thing.)

Now what does this spell for the cooperative development environments you
described?  Difficult to tell, but perhaps some of these would do, none of
which are standard, AFAIK:

* schemas owned by groups/roles

* access privileges to schemas, perhaps some sort of sticky bit functionality

> Or we have to special case the DEFAULT schema. Which strikes me as a bad
> thing to do.

I don't necessarily think of the DEFAULT schemas as a real schema.  It
might just be there so that *some* schema context is set if you don't have
one set otherwise, but you don't necessarily have write access to it.
But it might not be necessary at all.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter