Re: AW: Big 7.1 open items

Peter Eisentraut wrote:

> Hiroshi Inoue writes:
>
> > According to your another posting,your *database* hierarchy is
> > instance -> database -> schema -> object
> > like Oracle.
> >
> > However SQL92 seems to have another hierarchy:
> > cluster -> catalog -> schema -> object
> > and dot notation catalog.schema.object could be used.
>
> FYI:

Thanks.
I'm asking to all what our *DATABASE* is.
Different from you,I couldn't see any decisive feature in our *DATABASE*.

>
>
> An "instance" is a "cluster". I don't know where the word instance came

I could find the word in Oracle.
IMHO,it corresponds to our initdb'ed thing(a postmaster controls).

>
> from, the docs sometimes call it "installation" or "site", which is even
> worse. I have been using "database cluster" for the latest documentation
> work. My dictionary defines a cluster as "a group of things gathered or
> occurring closely together", which is what this is. Call it a "data area"
> or an "initdb'ed thing", etc.
>

SQL92 seems to say that a cluster corresponds to a target of connection
and has no name(after connection was established). Isn't it same as our
*DATABASE* ?

>
> A "catalog" can be equated with our "database". The method of creating
> catalogs is implementation defined, so our CREATE DATABASE command is in
> perfect compliance with the standard. We don't support the
> catalog.schema.object notation but that notation only makes sense when you
> can access more than one catalog at a time.

Yes,it's most essential that we couldn't access more than one catalog.
This means that we have only one (noname) "catalog" per "cluster".

> We don't allow that and SQL
> doesn't require it. We could allow that notation and throw an error when
> the catalog name doesn't match the current database, but that's mere
> cosmetic work.
>
> In entry level SQL 92, a "schema" is essentially the same as table
> ownership. You can execute the command CREATE SCHEMA AUTHORIZATION
> "peter", which means that user "peter" (where he came from is
> "implementation-defined") can now create tables under his name. There is
> no such thing as a table owner, there's the "containing schema" and its
> owner. The tables "peter" creates can then be referenced by the dotted
> notation. But it is not correct to equate this with CREATE USER. Even if
> there was no schema for "peter" he could still connect and query other
> people's tables.
>

I've used *username* "schema"s in Oracle for a long time but I've never
thought that it's the essence of "schema". If I recoginze correctly,the
concept of "catalog" hasn't necessarily been important while "schema"
= "user". The conflict of "schema" name is equivalent to the conflict
of "user" name if "schema" = "user". IMHO,SQL92 has required the
concept of "catalog" because "schema" has been changed to be
independent of "user".

Anyway in current PG "cluster":"catalog":"schema"=1:1:1(0) and
our *DATABASE* is an only confusing concept in the hierarchy..

Regards,

Hiroshi Inoue