Thread: AW: Big 7.1 open items

AW: Big 7.1 open items

From
Zeugswetter Andreas SB
Date:
> > > > The symlinks wouldn't do any good for what Bruce had in 
> > > > mind anyway (IIRC, he wanted to get useful per-database
> > > > numbers from "du").
> > > 
> > > Our database design seems to be in the opposite direction
> > > if it is restricted for the convenience of command calls.
> > 
> > Well, I don't see any reason not to use tablespace/database 
> > rather than just tablespace. Seems having fewer files in 
> each directory
> 
> Once again - ability to use different tablespaces (disks) for 
> tables/indices
> in the same schema. Schemas must not dictate where to store objects <-
> bad design.

Can we agree, that the schema is below the database hierarchy, and thus
is something different than a database ?
A table under another schema will simply get another oid, and thus no
collision.
But I agree that schema should not dictate storage location,
but the schema might imply a default storage location like in Oracle
(default tablespaces for a user).

> > will be a little faster, and if we can make administration easier,
> > why not?
> 
> Because you'll not be able use du/ls once we'll implement new 
> smgr anyway.

Leaving the file per table design imho does imply an order of magnitude 
increase in the impact of errors in the smgr. Now an error is likely to
destroy 
one table only, then it can destroy a whole tablespace.
But I am still a fan of the single file/raw device per tablespace design,
since it can remove a lot of the OS overhead. 

> And, btw, - for what are we going implement tablespaces? Just to have
> fewer files in each dir ?!

No, I guess the idea is to have a tool to manipulate physical distribution 
of objects (which disk, which filesystem ...)

Andreas


Re: AW: Big 7.1 open items

From
Hiroshi Inoue
Date:
Zeugswetter Andreas SB wrote:

> > > > > The symlinks wouldn't do any good for what Bruce had in
> > > > > mind anyway (IIRC, he wanted to get useful per-database
> > > > > numbers from "du").
> > > >
> > > > Our database design seems to be in the opposite direction
> > > > if it is restricted for the convenience of command calls.
> > >
> > > Well, I don't see any reason not to use tablespace/database
> > > rather than just tablespace. Seems having fewer files in
> > each directory
> >
> > Once again - ability to use different tablespaces (disks) for
> > tables/indices
> > in the same schema. Schemas must not dictate where to store objects <-
> > bad design.
>
> Can we agree, that the schema is below the database hierarchy, and thus
> is something different than a database ?

I don't think we have a common understanding for PG's *database*
(created by createdb). Every one seems to have his own *database*.

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.

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


>
> A table under another schema will simply get another oid, and thus no
> collision.
> But I agree that schema should not dictate storage location,
> but the schema might imply a default storage location like in Oracle
> (default tablespaces for a user).

AFAIK,schema is independent from user in SQL92.
So default_tablespace_per_user doesn't necessarily imply
default_tablespace_per_schema.

Regards.

Hiroshi Inoue



RE: AW: Big 7.1 open items

From
Peter Mount
Date:
The SQL7 way is the schema is the username, with the exception of "dba" -
it's used as a "global" schema.

--
Peter Mount
Enterprise Support
Maidstone Borough Council
Any views stated are my own, and not those of Maidstone Borough Council


-----Original Message-----
From: Hiroshi Inoue [mailto:Inoue@tpf.co.jp]
Sent: Thursday, June 29, 2000 9:22 AM
To: Zeugswetter Andreas SB
Cc: PostgreSQL-development
Subject: RE: AW: [HACKERS] Big 7.1 open items


> -----Original Message-----
> From: pgsql-hackers-owner@hub.org [mailto:pgsql-hackers-owner@hub.org]On
> Behalf Of 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.
> 
> As I said in another posting a user does not need to exist
> for each schema. The dba can create objects under any 
> schema name.
>

Sorry for my poor understanding.
What I meant was that SQL92 allows the following.
schema        owner---------------------------schema1    user1schema2    user1schema3    user2schema4    user3schema5
user3schema6    user3
 

Is my understaning same as yours ?

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp 


Re: AW: Big 7.1 open items

From
Peter Eisentraut
Date:
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:

An "instance" is a "cluster". I don't know where the word instance came
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.

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

Moving beyond SQL 92 you can also create schemas with a different name
than your user name. This is merely a little more naming flexibility.


-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden



Re: AW: Big 7.1 open items

From
Hiroshi Inoue
Date:
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