Thread: AW: Big 7.1 open items
> > > > 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
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
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
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
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