RFD: schemas and different kinds of Postgres objects 
Author Message
 RFD: schemas and different kinds of Postgres objects

Quote:

> Remember that a schema is a named representation of ownership, so anything
> that can be owned must be in a schema.  (Unless you want to invent a
> parallel universe for a different kind of ownership, which would be
> incredibly confusing.)

I don't buy that premise.  It's true that SQL92 equates ownership of a
schema with ownership of the objects therein, but AFAICS we have no hope
of being forward-compatible with existing database setups (wherein there
can be multiple tables of different ownership all in a single namespace)
if we don't allow varying ownership within a schema.  I think we can
arrange things so that we are upward compatible with both SQL92 and
the old way.  Haven't worked out details yet though.

Have to run, more later.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------



Sat, 10 Jul 2004 09:14:24 GMT
 RFD: schemas and different kinds of Postgres objects

Quote:

> > Quoting Date (pg. 221): "The schema authID for a given schema identifies
> > the owner of that schema (and hence the owner of everything described by
> > that schema also)."

> Yes, I know what the spec says.  I also think we'll have a revolt on our
> hands if we don't make it possible for existing Postgres applications to
> continue working as they have in the past --- and that means allowing
> tables of different ownerships to be accessible in a single namespace.

But them it is not SQL-Schemas.  Call it something else, "packages"
for instance.  The standard has lots of rules and other considerations
all around the document that depend on schemas have the meaning they
assigned to it.

If someone wants to really make use of SQL-Schemas, he/she will need to
reorg the database anyway, which will probably mean dumping the data,
massaging the DLL and recreating it.  I guess most users of SQL-Schemas
will be people creating new databases.

For the current users, (based on your idea below) a default behavior of
searching the current-AuthID schema, them the "default" schema them
"any"
schema will probably make things work.

Fernando

P.S.: Note that the standard has no GRANTs for SCHEMAs themselves; all
GRANTS go to the specific objects as before.

Quote:
> Although I haven't thought through the details yet, it seems to me that
> a solution exists along these lines:

> 1. The creator of an object owns it.  (With some special cases, eg the
>    superuser should be able to create a schema owned by someone else.)

> 2. Whether you can create an object in a schema that is owned by someone
>    else depends on permissions attached to the schema.  By default only
>    the owner of a schema can create anything in it.

> 3. SQL92-compatible behavior is achieved when everyone has their own
>    schema and they don't grant each other create-in-schema rights.
>    Backwards-compatible behavior is achieved when everyone uses a
>    shared "public" schema.

> We'd probably need GUC variable(s) to make it possible to choose which
> behavior is the default.  I haven't thought much about exactly what
> knobs should be provided.  I do think we will want at least these two
> knobs:

> 1. A "search path" that is an ordered list of schemas to look in
>    when trying to resolve an unqualified name.

> 2. A "default schema" variable that identifies the schema to create
>    objects in, if a fully qualified name is not given.

> The default creation location shouldn't be hardwired to equal the
> front of the search path, because the front item of the search path
> is probably always going to be a backend-local temporary schema
> (this is where we'll create temporary tables).

> The most dumbed-down version of this that would work is to reduce the
> search path to just a fixed list of three locations: temp schema, a
> selectable default schema (which is also the default creation location),
> and a system schema (where pg_class and friends live).  But a
> user-settable path wouldn't be any more effort to support, and might
> offer some useful capability.

>                         regards, tom lane

> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate

> message can get through to the mailing list cleanly

--
Fernando Nasser

2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org



Sun, 11 Jul 2004 01:04:30 GMT
 RFD: schemas and different kinds of Postgres objects

Quote:

> But them it is not SQL-Schemas.  Call it something else, "packages"
> for instance.  The standard has lots of rules and other considerations
> all around the document that depend on schemas have the meaning they
> assigned to it.

Where?  And are there any cases where it really matters?

Quote:
> If someone wants to really make use of SQL-Schemas, he/she will need to
> reorg the database anyway, which will probably mean dumping the data,
> massaging the DLL and recreating it.  I guess most users of SQL-Schemas
> will be people creating new databases.

No doubt.  That still leaves us with the problem of providing
backward-compatible behavior in an engine that is going to be designed
to support schemas.  I'm not sure what you think the implementation of
schemas is going to look like --- but I think it's not going to be
something that can be turned off or ignored.  Every table is going to
belong to some schema, and the old behavior has to be available within
that framework.

We are not working in a vacuum here, and that means that "implement
the specification and nothing but" is not a workable design approach.
We are going to end up with something that does the things SQL92 asks
for, but does other things too.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster



Sun, 11 Jul 2004 01:04:31 GMT
 RFD: schemas and different kinds of Postgres objects
OK, so the proposal is that we dissociate the ownership from the
namespace when we implement our version of SQL-Schemas, right?
This way an object will have both an owner and a schema (while in
the standard they are the same thing).

The important is not much to accommodate someone who is creating
schemas (a new thing, so objects can be created the "right" way)
but rather to accommodate current code that does not use schemas
and have several owners for the objects (which would all fall
into a "default" schema). Can you agree with that?

I was looking to see if we choose the proper defaults and search paths
and the "default" schema we could make it look for SQL-compliant code
as if it was a vanilla SQL-Schemas implementation.

To support the current database schema definitions (without SQL-Schemas
and with different owners), things would be created with the current
user authorization id and will have its name defined in the "default"
SQL-Schema
namespace.  Also, when referring to an object if the name is not
qualified
with the schema name, the search would go through the schema with the
current authorization id (as required by the std) and proceed to check
the "default" schema.

The only problem in the scenario above is that the standard says that
when creating objects and not specifying the schema the schema name
should be assumed to be the current user authorization id (or whatever
authorization id the code is running as).  In our case it would go to
the default schema.  If someone wants the SQL std behavior then, he/she
must create things inside a CREATE SCHEMA statement or explicitly
qualify
with the schema name the objects being created.  Can we live with that?
Will we pass the conformance tests? (I saw tests that test the schema
name
that is assumed when referencing but I do not recall seeing one that
tests
what is assumed on creation -- things I saw were all created inside
CREATE SCHEMA statements.  Note, also, that passing the NIST tests
doesn't
make us compliant if we know that we are doing something different than
what is specified -- it just means that we got away with it :-)

--
Fernando Nasser

2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command



Sun, 11 Jul 2004 04:34:15 GMT
 RFD: schemas and different kinds of Postgres objects

Quote:

> Tom Lane writes:
>> No, it doesn't work the same as today, because in that implementation
>> both A and B can create the same tablename without complaint.  It then
>> becomes very unclear which instance other people will get (unless your
>> "any" placeholder somehow implies a search order).
> The "search any schema" switch is only intended for use with legacy
> databases, where duplicate names don't occur anyway.

That's a mighty narrow view of the world.  Do you think that people had
better convert to SQL schemas before they ever again create a table?
The fact is that ordinary non-schema-aware usage will certainly lead to
the above scenario.

Quote:
> that the switch probably doesn't make a whole lot of sense.  However, to
> get reproduceable behaviour anyway we can just define a search order, such
> as by schema name.

Or say that you get an "ambiguous reference" error if there is more than
one possible candidate in the "any" namespace.  (Although that opens the
door for innocent creation of a table foo by one user to break other
people's formerly-working queries that reference some other foo.)
Bottom line for me is that this is an untried concept.  I think the
concept of an "any" searchlist entry is risky enough that I don't much
want to hang the entire usability of the implementation on the
assumption that we won't find any fatal problems with "any".

However, the argument over whether SQL92's concept of ownership should
be taken as gospel is not really the argument I wanted to have in this
thread.  Is it possible to go back to the original point concerning
whether there should be different namespace boundaries for different
types of objects?  You aren't going to avoid those issues by saying that
namespace == ownership is good enough.

I'm particularly troubled by the idea of trying to apply this "any"
lookup concept to resolution of overloaded operators and functions.
Suppose I have a reference func(type1,type2) that I'm trying to resolve,
and I have an inexact match (one requiring coercion) in my own schema.
Do I look to the "any" schema to see if there are better matches?
If so, what happens if the "any" schema contains multiple possibilities
with identical signatures (presumably created by different users)?  ISTM
this will positively guarantee a resolution failure, since there's no
way for the resolver to prefer one over another.  Thus, by creating
a "func(foo,bar)" function --- quite legally --- JRandomLuser might
break other people's formerly working queries that use other functions
named func.  Although it's possible for this to happen now, it'll be
a lot more surprising if JRandomLuser thinks that his functions live
in his own private schema namespace.

I'm thinking that the overloading concept is not going to play well
at all with multiple namespaces for functions or operators, and that
we'd be best off to say that there is only one namespace (per database)
for these things.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org



Sun, 11 Jul 2004 08:14:23 GMT
 RFD: schemas and different kinds of Postgres objects

Quote:


> > Moreover, I figure if we do it that
> > way, the whole schema implementation reduces itself mostly to parser work,
> > no complicated system catalog changes, no complex overhaul of the
> > privilege system -- at least initially.

> Why are you guys so eager to save me work?  I'm not in the least
> interested in implementing a "schema" feature that can only handle
> the entry-level user == schema case.  Therefore, just relabeling the
> owner column as schema isn't an interesting option.

> I really don't see what's wrong with building a namespace mechanism
> that is orthogonal to ownership and then using that to implement what
> SQL92 wants.  I think this will be cleaner, simpler, and more flexible
> than trying to equate ownership with namespace.

I'm with Tom on this: the extended Schema capability he's described
combines the best of both authentication mechanisms, IMHO. It give
us namespace separation ala the SQL standard, and individual object
ownership, like unix FS semantics. Only having ownership on the
'containers' strikes me as limiting, even if that is how the standard
describes it.

Ross

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster



Sun, 11 Jul 2004 08:33:23 GMT
 RFD: schemas and different kinds of Postgres objects

Quote:

> I'm particularly troubled by the idea of trying to apply this "any"
> lookup concept to resolution of overloaded operators and functions.
> Suppose I have a reference func(type1,type2) that I'm trying to resolve,
> and I have an inexact match (one requiring coercion) in my own schema.
> Do I look to the "any" schema to see if there are better matches?
> If so, what happens if the "any" schema contains multiple possibilities
> with identical signatures (presumably created by different users)?  ISTM
> this will positively guarantee a resolution failure, since there's no
> way for the resolver to prefer one over another.  Thus, by creating
> a "func(foo,bar)" function --- quite legally --- JRandomLuser might
> break other people's formerly working queries that use other functions
> named func.  Although it's possible for this to happen now, it'll be
> a lot more surprising if JRandomLuser thinks that his functions live
> in his own private schema namespace.

So, in a nutshell, the price we pay for function overloading is the
inability to have schema-specific functions. Right? Possibly why Oracle
doesn't allow function overloading? As a user, I'd much rather have
schema-specific functions than only global. I'm not downplaying the
value of function overloading, but if I had the choice (which I guess I
can't/won't), I'd choose schema-specific functions over function
overloading...

Mike Mascari

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org



Sun, 11 Jul 2004 09:23:55 GMT
 RFD: schemas and different kinds of Postgres objects

Quote:

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

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

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

Quote:
> 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

---------------------------(end of broadcast)---------------------------



Sun, 11 Jul 2004 09:33:15 GMT
 RFD: schemas and different kinds of Postgres objects

Quote:

> Why not? What's wrong with either schema.foo.function (==>
> function(schema.foo)) or foo.schema.function (==> schema.function(foo))?

Neither is wrong in isolation, but how do you tell the difference?
More to the point, given input x.y.z, how do you tell which component
is what?

Quote:
> Tables and functions can't have the same names as schemas,

News to me.  Where is that written on stone tablets?  Even if that's
considered an acceptable limitation from a purely functional point of
view, I don't like using it to disambiguate input.  The error messages
you'll get from incorrect input to an implementation that depends on
that to disambiguate cases will not be very helpful.

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

(A) I don't believe that; please cite chapter and verse; (B) even if
SQL92 thinks that's okay, we can't do it that way because of
backwards-compatibility issues.

Quote:
> My vote would be to make them schema-specific. As Peter pointed out,
> schemas are how you own things,

Sorry, but this line of argument is trying to assume the very point in
dispute.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------



Sun, 11 Jul 2004 13:13:35 GMT
 RFD: schemas and different kinds of Postgres objects

Quote:
> -----Original Message-----

> Sent: 23 January 2002 00:02
> To: Peter Eisentraut

> Subject: Re: RFD: schemas and different kinds of Postgres objects


> > Tom Lane writes:
> >> No, it doesn't work the same as today, because in that
> implementation
> >> both A and B can create the same tablename without
> complaint.  It then
> >> becomes very unclear which instance other people will get
> (unless your
> >> "any" placeholder somehow implies a search order).

> > The "search any schema" switch is only intended for use with legacy
> > databases, where duplicate names don't occur anyway.

> That's a mighty narrow view of the world.  Do you think that
> people had
> better convert to SQL schemas before they ever again create a table?
> The fact is that ordinary non-schema-aware usage will
> certainly lead to
> the above scenario.

> > that the switch probably doesn't make a whole lot of sense.
>  However, to
> > get reproduceable behaviour anyway we can just define a
> search order, such
> > as by schema name.

> Or say that you get an "ambiguous reference" error if there
> is more than
> one possible candidate in the "any" namespace.  (Although
> that opens the
> door for innocent creation of a table foo by one user to break other
> people's formerly-working queries that reference some other foo.)
> Bottom line for me is that this is an untried concept.  I think the
> concept of an "any" searchlist entry is risky enough that I don't much
> want to hang the entire usability of the implementation on the
> assumption that we won't find any fatal problems with "any".

> However, the argument over whether SQL92's concept of ownership should
> be taken as gospel is not really the argument I wanted to have in this
> thread.  Is it possible to go back to the original point concerning
> whether there should be different namespace boundaries for different
> types of objects?  You aren't going to avoid those issues by
> saying that
> namespace == ownership is good enough.

> I'm particularly troubled by the idea of trying to apply this "any"
> lookup concept to resolution of overloaded operators and functions.
> Suppose I have a reference func(type1,type2) that I'm trying
> to resolve,
> and I have an inexact match (one requiring coercion) in my own schema.
> Do I look to the "any" schema to see if there are better matches?
> If so, what happens if the "any" schema contains multiple
> possibilities
> with identical signatures (presumably created by different
> users)?  ISTM
> this will positively guarantee a resolution failure, since there's no
> way for the resolver to prefer one over another.  Thus, by creating
> a "func(foo,bar)" function --- quite legally --- JRandomLuser might
> break other people's formerly working queries that use other functions
> named func.  Although it's possible for this to happen now, it'll be
> a lot more surprising if JRandomLuser thinks that his functions live
> in his own private schema namespace.

> I'm thinking that the overloading concept is not going to play well
> at all with multiple namespaces for functions or operators, and that
> we'd be best off to say that there is only one namespace (per
> database)
> for these things.

>                    regards, tom lane

Could you just have a general rule of search in order of age (by OID)? This
should prevent changes to existing operation when new definitions come along
(unless new definition is in new own schema or default).
Cheers,
- Stuart

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster



Sun, 11 Jul 2004 19:14:02 GMT
 RFD: schemas and different kinds of Postgres objects

Quote:

> OK, I can accept that.  But then I want to get back at my original point,
> namely that all database objects (except users and groups) should be in
> schemas.  This is also cleaner, simpler, and more flexible.  There is
> clearly demand for schema-local functions.  So I think that designing this
> system from the premise that a schema-qualified operator call will look
> strange is the wrong end to start at.

Okay, a fair point --- or you could have used my own argument against
me: there's nothing wrong with designing a general mechanism and then
choosing not to expose all of the functionality.  So let's assume that
functions and operators live in namespaces, and that we have some kind
of search path across multiple namespaces for use when an unqualified
name is given.

Now, how is that going to play with resolution of ambiguous calls?

The most reasonable semantics I can think of are to collect all the
potential matches (matching op/func name) across all the searchable
namespaces, discarding only those that have exactly the same signature
as one in a prior namespace.  Thus, eg, plus(int4,int4) in an earlier
namespace would hide plus(int4,int4) in a later namespace in the search
path, but it wouldn't hide plus(int8,int8).  After we've collected all
the visible alternatives, do resolution based on argument types the same
way as we do now.

The only alternative semantics that seem defensible at all are to stop
at the first namespace that contains any matching-by-name op or func,
and do resolution using only the candidates available in that namespace.
That strikes me as not a good idea; for example, a user who defines a
"+" operator in his own schema for his own datatype would be quite
unhappy to find it masking all the "+" operators in the system schema.

I believe that this behavior would be fairly reasonable if our
backward-compatibility feature consists of a "public" namespace
that all users can write in.  OTOH I think it would not play at all
well if we use Fernando's idea of an "any" wildcard in the search
path.  (1) Imagine the case where we have some users who are using
the backward-compatible behavior while others have set up private
namespaces.  If Joe SmartGuy creates a "+" operator in his private
namespace, it'll be visible to people using the "any" wildcard and
possibly cause resolution-ambiguity failures for them, even though
Joe deliberately did what he should do to avoid that.  (2) "any"
creates the problem of resolving multiple functions with identical
signatures in different namespaces, with no reasonable rule for
making the choice.

So I'm still of the opinion that an "any" wildcard is too risky a
solution for our backwards-compatibility problem.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster



Sun, 11 Jul 2004 23:33:42 GMT
 RFD: schemas and different kinds of Postgres objects

Quote:

> Wouldn't it make sense to prefer operators/functions earlier in the search
> path for resolving ambiguity.  So if you had plus(int4, int4) in my
> schema and plus(int8, int8) in system, and they'd otherwise cause an
> ambiguity failure for the query, use the plus(int4, int4) on mine. It
> seems not too far from having the search path shadow later exact matches.

Given the complexity of the resolution rules (cf.
http://developer.postgresql.org/docs/postgres/typeconv.html),
it's not clear that we can determine exactly which "later" entry ought
to be blamed for causing a resolution failure.  I'd be interested to
hear Lockhart's opinion on this --- but my gut feeling is we don't
want to go there.  The resolution rules are already complicated enough,
and I think layering an additional mechanism like that onto them might
make the behavior totally unpredictable.

Another problem is that this would probably cause earlier namespace
entries to be over-preferred.  For example, suppose that the system
namespace has plus(int4,int4) and plus(int8,int8) and you choose to
define plus(int4,int8) locally.  I believe you'd suddenly find yours
being used for *any* cross-datatype addition, including cases that
had nothing obvious to do with either int4 or int8 ...

                        regards, tom lane

---------------------------(end of broadcast)---------------------------



Mon, 12 Jul 2004 01:20:04 GMT
 RFD: schemas and different kinds of Postgres objects

Quote:


> > Remember that a schema is a named representation of ownership, so anything
> > that can be owned must be in a schema.  (Unless you want to invent a
> > parallel universe for a different kind of ownership, which would be
> > incredibly confusing.)

> I don't buy that premise.  It's true that SQL92 equates ownership of a
> schema with ownership of the objects therein, but AFAICS we have no hope
> of being forward-compatible with existing database setups (wherein there
> can be multiple tables of different ownership all in a single namespace)
> if we don't allow varying ownership within a schema.  I think we can
> arrange things so that we are upward compatible with both SQL92 and
> the old way.  Haven't worked out details yet though.

Yes we most certianly can! :-)

One of the things schemas have to support is essentially a PATH specifier.
So all we need to do is have all of the schemas created in a new DB have
path specifiers pulling in all of the other schemas. Thus we can make a
schema-savy system act as if it has only one namespace.

Back when Zembu was paying me to work on this, I envisioned a script or
tool you'd feed a DB dump, and it would do the schema fixup, including
adding PATH directives to all schemas, so they all see everything.

Since you have to pg_dump when updating, all this adds is running one tool
during an upgrade. And then existing apps would work. :-)

Take care,

Bill

---------------------------(end of broadcast)---------------------------



Mon, 12 Jul 2004 03:45:55 GMT
 RFD: schemas and different kinds of Postgres objects

Quote:

> One of the things schemas have to support is essentially a PATH specifier.

Yes, but...

Quote:
> So all we need to do is have all of the schemas created in a new DB have
> path specifiers pulling in all of the other schemas. Thus we can make a
> schema-savy system act as if it has only one namespace.

When you create a new user, do all those path specifiers for the
existing users magically update themselves?  Seems like maintenance
would be a pain.

Fernando's "any" idea is probably a cleaner way to handle it if we
wanted to do things like that.  But I still think it'll be safer and
more controllable if we provide a "public" namespace instead; see
followup discussions.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command



Mon, 12 Jul 2004 04:16:24 GMT
 RFD: schemas and different kinds of Postgres objects

Quote:


> > Why not? What's wrong with either schema.foo.function (==>
> > function(schema.foo)) or foo.schema.function (==> schema.function(foo))?

> Neither is wrong in isolation, but how do you tell the difference?
> More to the point, given input x.y.z, how do you tell which component
> is what?

See below.

Quote:
> > Tables and functions can't have the same names as schemas,

> News to me.  Where is that written on stone tablets?  Even if that's

I'm still trying to find the quote, but I found it a few months ago. I'm
looking in SQL99, which is 1100+ pages for section 2. :-)

Quote:
> considered an acceptable limitation from a purely functional point of
> view, I don't like using it to disambiguate input.  The error messages
> you'll get from incorrect input to an implementation that depends on
> that to disambiguate cases will not be very helpful.

?? Depends on how we do it. As I see it, we have four cases. In the
x.y.z.p.q, we have:

1) No table name, but a function name. It's a function call.

2) A table name, but no function name. It's a table reference.

3) Both a table name & function name, and the function is first. I think
this case is an error (I don't think we support function.foo ==
function(foo))

4) Both a table name & function name, and the table is first. This is
foo.function.

Ok, there is a fifth case, no function nor table names, which is an error.

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

> (A) I don't believe that; please cite chapter and verse; (B) even if

Peter got to that one first.

Quote:
> SQL92 thinks that's okay, we can't do it that way because of
> backwards-compatibility issues.

Why do backwards-compatability issues keep us from doing it?

Yes, I understand we have apps now with different users owning things
(tables, functions) which they all can access, just like they were in one
unified name space. With real schemas, they are in differen namespaces.
But as long as the routines, tables, triggers & such in each schema can
find things in the other schemas as if they were in one namespace, where
is the problem? We just have the app gain PATH directives to path in all
the other schemas.

The app runs, even though there are different schemas involved. Where is
the problem?

Quote:
> > My vote would be to make them schema-specific. As Peter pointed out,
> > schemas are how you own things,

> Sorry, but this line of argument is trying to assume the very point in
> dispute.

When you started this thread, you said you were thinking about
"implementing SQL schemas." Are these "SQL schemas" going to follow the
spec or not? SQL'99 is rather clear that ownership happens at the schema
level. Peter spent quite a lot of time last October pounding that into my
head, and after I looked at the spec, I found he was 100% correct.

If these schemas are to follow the standards, ownership happens at the
schema level. If ownership happens elsewhere, whatever we're doing is not
following the standard. Unfortunatly it's that cut & dried. So why should
we call them "SQL schemas" if we aren't following the SQL spec?

Take care,

Bill

---------------------------(end of broadcast)---------------------------



Mon, 12 Jul 2004 05:04:36 GMT
 
 [ 20 post ]  Go to page: [1] [2]

 Relevant Pages 

1. RFD: schemas and different kinds of Postgres objects

2. export/import objects to different schemas

3. Help - tkprof output different for 2 schemas with same objects

4. Postgres Schemas Needed - Please!

5. Postgres Schemas Wanted - Please

6. Universal stored procedure for inserting data in different kinds of tables

7. Transferring data from a SQL server to a different kind of server

8. Seeking information on different kinds of databases.

9. A different kind of join

10. Foreign key references to tables in different schemas ??

11. How do you compare the data in different schemas not just table diffs

12. Union query between two different schemas


 
Powered by phpBB® Forum Software