Re: RFD: schemas and different kinds of Postgres objects

On Mon, 21 Jan 2002, Tom Lane wrote:

> Continuing to think about implementing SQL schemas for 7.3 ...
>
> Today's topic for discussion: which types of Postgres objects should
> belong to schemas, and which ones should have other name scopes?
>
> Relations (tables, indexes, views, sequences) clearly belong to schemas.
> Since each relation has an associated datatype with the same name, it
> seems that datatypes must belong to schemas as well. (Even if that
> argument doesn't convince you, SQL99 says that user-defined datatypes
> belong to schemas.) However the situation is murkier for other kinds of
> objects.
>
> Here are all the kinds of named objects that exist in Postgres today,
> with some comments on whether they should belong to schemas or not:
>
> relations Must be in schemas
> types Must be in schemas
> databases Databases contain schemas, not vice versa
> users Users are cross-database, so not in schemas
> groups User groups are cross-database, so not in schemas
> languages Probably should not be in schemas
> access methods Probably should not be in schemas
> opclasses See below
> operators See below
> functions/procedures See below
> aggregates Should treat same as regular functions
> constraints See below
> rules See below
> triggers See below
> NOTIFY conditions See below
>
> Languages and access methods are not trivial to add to the system, so
> there's not much risk of name conflicts, and no reason to make their name
> scope less than global.
>
> The situation is a lot murkier for operators and functions. These should
> probably be treated alike, since operators are just syntactic sugar for
> functions. I think the basic argument for making them schema-local is
> that different users might conceivably want to define conflicting
> functions or operators of the same name. Against that, however, there
> are a number of reasons for wanting to keep these objects database-wide.
> First off there are syntactic problems. Do you really want to write
> A schemaname.+ B
> to qualify an ambiguous "+" operator? Looks way too much like a syntax
> error to me. Allowing this would probably turn a lot of simple syntax
> errors into things that get past the grammar and end up producing truly
> confusing error messages. Qualified function names also pose some
> problems, not so much with
> schemaname.function(args)
> which seems reasonable, but with the Berkeley-derived syntax that allows
> "foo.function" to mean "function(foo)" --- there's no way to squeeze a
> schema-name for the function into that. (And you'll recall from my note

Why not? What's wrong with either schema.foo.function (==>
function(schema.foo)) or foo.schema.function (==> schema.function(foo))?
Tables and functions can't have the same names as schemas, so we will be
able to notice the schema names in there. Oh, and I worked out how to get
parse.y to be happy with x.y & x.y.z (schema.package.function) names. :-)

> of the other day that we don't want to abandon this syntax entirely,
> because people would like us to support "sequencename.nextval" for Oracle
> compatibility.) Notice that we are not forced to make functions/operators
> schema-local just because datatypes are, because overloading will save the
> day. func(schema1.type1) and func(schema2.type1) are distinct functions
> because the types are different, even if they live in the same function
> namespace. Finally, SQL99 doesn't appear to think that operator and
> function names are schema-local; though that may just be because it hasn't
> got user-defined operators AFAICT.

Actually functions do have to be schema local. It's in the spec (don't
have exactly where with me).

> I am leaning towards keeping functions/operators database-wide, but would
> like to hear comments. Is there any real value in, eg, allowing different
> users to define different "+" operators *on the same datatypes*?

Yes. It means that third-party developers can develop routines and then
operators based on them without having to worry about conflicts. Obviously
these two different operators would have to be in different schemas. Also,
it would mean that someone could ship replacement operators for built-in
operators. Say adding a + operator which throws exceptions on overflow.
:-)

> Not sure about index opclasses. Given that datatype names are
> schema-local, one can think of scenarios where two users define similar
> datatypes and then try to use the same index opclass name for both.
> But it seems pretty unlikely. I'd prefer to leave opclass names
> database-wide for simplicity. Comments?

My vote would be to make them schema-specific. As Peter pointed out,
schemas are how you own things, so put them in a schema so we can keep
track of ownership.

Take care,

Bill