Postgres Pro
Downloads
Home   >   mailing lists

Thread: AW: AW: Big 7.1 open items

AW: AW: Big 7.1 open items

From
Zeugswetter Andreas SB
Date:
> AFAIK,schema is independent from user in SQL92.
> So default_tablespace_per_user doesn't necessarily imply
> default_tablespace_per_schema.

Well, sombody must be interpreting this wrong, because 
in Informix and Oracle the schema corresponds to the owner
and they say they conform to ansi in this regard.
In both, a user can access other schemas or switch to another
schema, so in that sense you could say that the schema is 
independent of users.

> However SQL92 seems to have another hierarchy:
>     cluster -> catalog -> schema -> object

I would say our "database" corresponds to "catalog" and 
"instance" corresponds to "cluster" in the SQL92 hierarchy.
Instance is probably a bad wording in respect to multiple
machine clusters where you can access all objects 
from every node.
Database was probably not used, because this is often used 
to describe the whole hierarchy.

> I couldn't find clear correspondense between PG's *database*
> and above hierarchy because we have no dot notation for
> objects currently.

This will definitely be a problem because of our current nested dot
interpretation towards functions taking one opaque or _class_type
argument.

Andreas

Re: AW: Big 7.1 open items

From
"Ross J. Reedstrom"
Date:
On Wed, Jun 28, 2000 at 02:07:33PM +0200, Zeugswetter Andreas SB wrote:
> 
> > AFAIK,schema is independent from user in SQL92.
> > So default_tablespace_per_user doesn't necessarily imply
> > default_tablespace_per_schema.
> 
> Well, sombody must be interpreting this wrong, because 
> in Informix and Oracle the schema corresponds to the owner
> and they say they conform to ansi in this regard.

To quote from the SQL92 standard for CERATE SCHEMA:
        <schema definition> ::=             CREATE SCHEMA <schema name clause>               [ <schema character set
specification>]               [ <schema element>... ]
 
        <schema name clause> ::=               <schema name>             | AUTHORIZATION <schema authorization
identifier>            | <schema name> AUTHORIZATION <schema authorization identifier>
 

        1) If <schema name> is not specified, then a <schema name> equal to           <schema authorization identifier>
isimplicit.
 
        2) If AUTHORIZATION <schema authorization identifier> is not speci-           fied, then
           Case:
           a) If the <schema definition> is contained in a <module> that             has a <module authorization
identifier>specified, then an             <authorization identifier> equal to that <module authoriza-             tion
identifier>is implicit for the <schema definition>.
 
           b) Otherwise, an <authorization identifier> equal to the SQL-             session <authorization identifier>
isimplicit.
 

So, we see that the SQL92 default fora schema is the session username.

> In both, a user can access other schemas or switch to another
> schema, so in that sense you could say that the schema is 
> independent of users.

Not only in a sense, it is in fact.
> 
> This will definitely be a problem because of our current nested dot
> interpretation towards functions taking one opaque or _class_type
> argument.

Right. If we're going to support SQL92 dot notation (which I think we
should) we'll either need to lose the function notion completely, or
come up with some really clever hack about applying them in order.

Ross
-- 
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> 
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005

RE: AW: Big 7.1 open items

From
"Hiroshi Inoue"
Date:
> -----Original Message-----
> From: Zeugswetter Andreas SB
> 
> > AFAIK,schema is independent from user in SQL92.
> > So default_tablespace_per_user doesn't necessarily imply
> > default_tablespace_per_schema.
> 
> Well, sombody must be interpreting this wrong, because 
> in Informix and Oracle the schema corresponds to the owner
> and they say they conform to ansi in this regard.

Is there really a schema:user=1:1 limitation in SQL-92 ?
Though both SQL-86 and SQL-89 had the limitation
SQL-92 removed it AFAIK.

Regards.

Hiroshi Inoue

Re: AW: Big 7.1 open items

From
"Ross J. Reedstrom"
Date:
On Thu, Jun 29, 2000 at 02:05:31AM +0900, Hiroshi Inoue wrote:
> 
> Is there really a schema:user=1:1 limitation in SQL-92 ?
> Though both SQL-86 and SQL-89 had the limitation
> SQL-92 removed it AFAIK.

See my other post. In SQL92, the username is the default schema name.

Ross
-- 
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> 
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005