Re: schema support, was Package support for Postgres

On Wed, 24 Oct 2001, Peter Eisentraut wrote:

> Bill Studenmund writes:
>
> > So I am a "naive" programmer because I mention intent above?
>
> No.

Sorry, that's the way it came across. As you've said that was not your
intent, please disregard my response; I was responding to something you
did not mean.

> > So if we have INFORMATION_SCHEMA with the right vies in it, we are fine
> > doing whatever we want.
>
> I think some interpretation of the SQL standard can be used to prove that
> a new schema should not contain any objects. So you're going to have to
> stick to the two predefined schemas to put the system catalogs in. Then
> again, other interpretations may be used to prove other things. But to me
> the intent of the standard is clear that system catalogs are meant to go
> into the defintion schema, and I don't see a reason why this could not be
> so.

I had been thining that we could have the built-in objects (functions,
types, operators, etc.) in whatever was the "default.master" package, but
it looks like SQL99 doesn't like that. You're right that built-in things
have to be in a different schema than user-added things.

Section 10.4 contains text:

ii) If RN contains a <schema name> SN, then

Case:

1) If SN is INFORMATION_SCHEMA, then the single candidate routine of RI is
the built-in function identified by <routine name>.

Actually 4.24 is more exact. It defines a built-in function as a routine
which is returned from teh query:

SELECT DISTINCT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = INFORMATION_SCHEMA

Actually, since we have to have an INFORMATION_SCHEMA, and
"INFORMATION_SCHEMA" gets thrown around a lot, I think it'd be easiest to
make "INFORMATION_SCHEMA" the schema containing built-in things. Otherwise
(among other things) we have to replace DEFINTION_SCHEMA with
INFORMATION_SCHEMA in the above-defined view (and in a lot of other
places).

Thoughts?

> > I stil think we can't do that, since someone other than the schema owner
> > can add a package to a schema. :-) Or at least that's the assumption I'm
> > running on; we allow users other than PGUID to create functions (and
> > operators and aggregates and types) in the default (whatever it will be
> > called) schema, so why shouldn't they be allowed to add packages?
>
> 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. That's not how things are now, and imposing
that on upgrading users will likely cause pain.

Think about a dump/restore upgrade from 7.2 to 7.3. Right now users other
than PGUID can own functions (and triggers, etc.). When you do the
restore, though, since your dump had no schema support, it all goes into
DEFAULT. Which will be owned by PGUID. So now we either have a schema with
things owned by a user other than the schema owner, or we have a broken
restore.

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

For now, I'd suggest letting users other than a schema owner own things in
a schema, and later on add controls over who can add things to a schema.
Then when you do a "CREATE SCHEMA" command, you will implicitly be adding
restrictions prohibiting someone other than the owner from adding things
(including packages/subschemas).

> I agree that this might not be what everyone would want, but it seems
> extensible. However, I feel we're trying to design too many things at
> once. Let's do schemas first the way they're in the SQL standard, and
> then we can try to tack on ownership or subschemas or package issues.

Well, the packages changes can easily be turned into schema support for
functions and aggregates, so we are part way there. Also, the packages
changes illustrate how to make system-wide internal schema changes of the
type adding SQL schemas will need. Plus, packages as they are now are
useful w/o schema support.

And there's the fact that schemas were wanted for 7.2, and didn't happen.
Withouth external adgitation, will they happen for 7.3? Given the size of
the job, I understand why they didn't happen (the package changes so far
represent over 3 months of full-time programming). We've got some momentum
now, I'd say let's run with it. :-)

Take care,

Bill