Re: schema support, was Package support for Postgres

On Thu, 25 Oct 2001, Peter Eisentraut wrote:

> Bill Studenmund writes:
>
> > 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.

I think I understand your descriptions of what you will be *able* to do
with schemas. And also that they may describe how you *should* do thing
with schema. I'm not disagreeing with you about that. But that's not the
angle I'm working.

I guess to get at my point, I can ask this question, "Will schema support
invalidate existing PostgreSQL database designs."

I would like the answer to be no. I would like our users to be able to
dump a pre-schema-release db, upgrade, and then restore into a
schema-aware PostgreSQL. And have their restore work.

Since the admin is restoring a db which was made before schema support,
there are no CREATE SCHEMA commands in it (or certainly not ones which do
a real schema create - right now CREATE SCHEMA is a synonym for CREATE
DATABASE). So the restore will create everything in the "DEFAULT" schema
(The schema where creates done w/o a CREATE SCHEMA go).

But right now, we can have different users owning things in one database.
So there will be restores out there which will have different users owning
things in the same restored-to schema, which will be "DEFAULT".

So we have to have (or just retail) the ability to have different users
owning things in one schema.

> 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.)

Hmmm... I don't think so, but hmmm..

> 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

I think that schemas owned by roles are part of SQL99.

> * 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.

While if we were starting over, we might be able to (maybe should have)
design(ed) things so we don't need it, I think a "DEFAULT" schema would
help give users of the schema-aware PostgreSQL an experience similar to
what they have now.

And getting back to where this all started, I think we do need to have the
ability to have users other than the schema owner own things in the
schema, so we should keep the owner id column in the pg_package table. I'm
not against, when things are all said and done, having the default be that
only the schema owner can add things. But that's a policy decision. :-)

Take care,

Bill