Thread: Re: AW: Unhappy thoughts about pg_dump and objects inherited from template1

At 08:59 9/11/00 +0100, Zeugswetter Andreas SB wrote:
>
>> Just seems like we'd be forcing non-standard syntax on 
>> ourselves when/if
>> CREATE DATABASE becomes CREATE SCHEMA;
>
>I do not think this will be the way.
>

I know there was a lot of discussion of this a while ago, but was there a
consistant set of decisions made? 

I'd be very interested if someone could post the current thinking re:
schemas, catalogs, and environments, because the way I read the SQL99 docs,
the catalog seems to correspond to a single postgres installation, and a
schema seems to correspond to a postgres database (ie. tables and views are
defined in a schema, and schemas are defined in a catalog, and catalogs are
defined in an environment, and it looks like the environment is akin to the
file system/implementation & postmaster). 

Presumably this was raised before, but I'd love to see the consensus view,
if it is documented.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|                                |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Philip Warner <pjw@rhyme.com.au> writes:
> Presumably this was raised before, but I'd love to see the consensus view,
> if it is documented.

AFAIR, the discussion trailed off without any specific decisions being
made.  One of the things that's still very open in my mind is whether
we want to keep the existing notion of independent databases within an
installation, and if so how that maps onto the SQL-defined concepts.

To me, though, the point of independent databases is that they be
*independent*, and therefore if we keep them I'd vote for mapping them
to the top-level SQL notion (catalog, you said?).  Schemas ought to be
substructure within a database.
        regards, tom lane

At 10:36 9/11/00 -0500, Tom Lane wrote:
>Philip Warner <pjw@rhyme.com.au> writes:
>> Presumably this was raised before, but I'd love to see the consensus view,
>> if it is documented.
>
>AFAIR, the discussion trailed off without any specific decisions being
>made.  One of the things that's still very open in my mind is whether
>we want to keep the existing notion of independent databases within an
>installation, and if so how that maps onto the SQL-defined concepts.
>To me, though, the point of independent databases is that they be
>*independent*,

I agree; it's a pain that one DB misbehaving kills an entire installation.


>and therefore if we keep them I'd vote for mapping them
>to the top-level SQL notion (catalog, you said?).  Schemas ought to be
>substructure within a database.

I think the hierarchy goes:
   Environment->Catalog->Schema

From what I can tell:

1. the environment contains truly general things like the SQL parser, the
tools for connecting to the DB etc - which I assume also contains the
user-authorization stuff.

2. The catalog contains multiple schemas (this is the top level as far as
data definitions go, I think). Some predefined schemas (eg. the
DEFINITION_SCHEMA) contain views that allow querying of all schema
definitions in the catalog.

3. Schemas are what we call databases. They contain tables, views wtc.

The SQL standard is careful to avoid using the term database in these
discussions, though at one point it does equate 'database' with the part of
the environment that contains the actual SQL data (as opposed to metadata).
It's a pretty broad definition, and contrary to most peoples expections, I
think. Perhaps I'm wrong, but I think most people will equate database with
a schema (ie. the thing in which you define tables).


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|                                |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Philip Warner <pjw@rhyme.com.au> writes:
> I think the hierarchy goes:
>     Environment->Catalog->Schema
> From what I can tell:
> 1. the environment contains truly general things like the SQL parser, the
> tools for connecting to the DB etc - which I assume also contains the
> user-authorization stuff.

In that case it would seem that environment maps to what we call an
installation --- if users are environment-wide then that's certainly
the natural mapping.  Then database maps to catalog and schema is a
new feature within databases.

> 3. Schemas are what we call databases. They contain tables, views wtc.

Schemas are *not* databases, because (IIRC) it's possible for a single
session to access multiple schemas.
        regards, tom lane

Philip Warner writes:> Perhaps I'm wrong, but I think most people will equate database> with a schema (ie. the thing in
whichyou define tables).
 

I agree with most of what you say.  However I am used to conflating
catalog with database.  For example, the last product I put together
had one read-only schema and created one schema per project managed.
The client code accessed two or more schemata at a time.  We used the
term database to mean all the schemata.
-- 
Pete Forman                 -./\.- Disclaimer: This post is originated
Western Geophysical           -./\.-  by myself and does not represent
pete.forman@westgeo.com         -./\.-  the opinion of Baker Hughes or
http://www.crosswinds.net/~petef  -./\.-  its divisions.
***==  My old email address gsez020@kryten.bedford.waii.com will ==***
***==  not be operational from Fri 10 to Tue 14 Nov 2000.        ==***

Phil - 
My take on this can be found at:

http://www.postgresql.org/mhonarc/pgsql-hackers/2000-03/msg00137.html

Peter agrees with me (from my personal archive: the postgresql.org one
has holes in it!):

http://cooker.ir.rice.edu/postgresql/msg19913.html

There was another discussion, a little earlier, over in GENERAL, I think.

What seems to happen to all these dicussions is they wander off into
tablespaces, and dataspaces, and what have you, instead of sticking to
just SCHEMA. ;-)

Ross

On Fri, Nov 10, 2000 at 02:53:37AM +1100, Philip Warner wrote:
> At 10:36 9/11/00 -0500, Tom Lane wrote:
> >Philip Warner <pjw@rhyme.com.au> writes:
> >> Presumably this was raised before, but I'd love to see the consensus view,
> >> if it is documented.
> >
> 
> I think the hierarchy goes:
> 
>     Environment->Catalog->Schema

SQL92 talks of a 'cluster of catalogs'

Ross
-- 
Open source code is like a natural resource, it's the result of providing
food and sunshine to programmers, and then staying out of their way.
[...] [It] is not going away because it has utility for both the developers 
and users independent of economic motivations.  Jim Flynn, Sunnyvale, Calif.

Philip Warner writes:

> I'd be very interested if someone could post the current thinking re:
> schemas, catalogs, and environments, because the way I read the SQL99 docs,
> the catalog seems to correspond to a single postgres installation, and a
> schema seems to correspond to a postgres database (ie. tables and views are
> defined in a schema, and schemas are defined in a catalog, and catalogs are
> defined in an environment, and it looks like the environment is akin to the
> file system/implementation & postmaster). 

The thing you get from initdb is a "cluster of catalogs", a database is a
"catalog", a schema is something below a catalog.  (There is no such
thing as an "environment" as a hierarchy level.)  The idea was most likely
that a schema would be a purely logical hierarchy but a catalog may be a
physical hierarchy.  (For example, it is not required that you can access
more than one catalog from a connection.)

I think all people that were interested in this issue agreed with this.  
(If not, you better speak up, because I'd like to see schemas implemented
ASAP.)

-- 
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/

At 17:34 9/11/00 +0100, Peter Eisentraut wrote:
>
>The thing you get from initdb is a "cluster of catalogs", a database is a
>"catalog", a schema is something below a catalog.  (There is no such
>thing as an "environment" as a hierarchy level.) 

I think that's what SQL99 calls the 'cluster of catalogs'.

>I think all people that were interested in this issue agreed with this.  
>(If not, you better speak up, because I'd like to see schemas implemented
>ASAP.)

Nope. Ross's links cover the issues I wanted to ask about.

Thanks.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|                                |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/