
> > Switches set to historical:
> 
> >   schema search path = (user's own schema, "any" schema, postgres)
> 
> >   [ default creation schema = user's own schema ]
> 
> > The searching in "any" schema (i.e., any owner) will let will find 
> > things that where defined the way they are today, i.e., possibly
> > by several different users.
> 
> No, it won't, because nothing will ever get put into that schema.
> (At least not by existing pg_dump scripts, which are the things that
> really need to see the historical behavior.)  The
> default-creation-schema variable has got to point at any/public/
> whatever-we-call it, or you do not have the historical behavior.

When configured for historical behavior would need to:
1. have search path: temp, any, system
2. guard against duplicate table names across all schemas (except temp schema)

Or are you thinking about a per session behavior ?
I would rather envision a per database behavior.

Maybe the easy way out would be a "default creation schema" property for 
each user, that would default to the username. If you want everything in one 
schema simply alter the users.

Andreas

Wed, 23 Jan 2002 10:11:13 +0100
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at>
Re: RFD: schemas and different kinds of Postgres objects 
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:
> When configured for historical behavior would need to:
> 1. have search path: temp, any, system
> 2. guard against duplicate table names across all schemas (except temp schema)

This would be a *whole* lot simpler if we forgot the notion of "any"
and made the search order look like

	(temp, private, public, system)

where the public namespace is world-writable but the private per-user
ones are (typically at least) not.

It occurs to me that we can get both backward-compatible and SQL92
semantics with this same search path; the only thing that needs to
be different in the two cases is whether the default place to create
objects is your private schema or the public one.  If you don't ever
use your private schema then it doesn't matter if it's on the search
path or not.  I would still prefer that the search path be a settable
option, since a paranoid person might well wish to not have public in
his path at all ... but the default could be as-above.

> Or are you thinking about a per session behavior ?
> I would rather envision a per database behavior.
> Maybe the easy way out would be a "default creation schema" property for 
> each user, that would default to the username. If you want everything in one 
> schema simply alter the users.

I hadn't really gotten to the point of thinking about exactly what and
where the control knobs should be.  I suspect you are right that we will
want the default behavior to be selectable on a per-user or per-database
basis, which seems to eliminate the option of using GUC (at least in its
current form).  We could easily add a field to pg_shadow or pg_database
respectively to determine the default behavior.  It'd be nice though if
the behavior could be changed after connection by a SET statement, which
would be lots easier if the setting were GUC-controlled.  Peter, you see
any way to resolve that?

			regards, tom lane

Wed, 23 Jan 2002 10:44:34 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: RFD: schemas and different kinds of Postgres objects 
Tom Lane wrote:

> 
> This would be a *whole* lot simpler if we forgot the notion of "any"
> and made the search order look like
> 
> 	(temp, private, public, system)
> 
> where the public namespace is world-writable but the private per-user
> ones are (typically at least) not.
> 
> It occurs to me that we can get both backward-compatible and SQL92
> semantics with this same search path; the only thing that needs to
> be different in the two cases is whether the default place to create
> objects is your private schema or the public one.  If you don't ever
> use your private schema then it doesn't matter if it's on the search
> path or not.  I would still prefer that the search path be a settable
> option, since a paranoid person might well wish to not have public in
> his path at all ... but the default could be as-above.
> 


I think it would be desirable to be able to restrict users from 
"publishing" objects into the public schema. As an admin, I'd like some 
control over the objects in this namespace. Hand-in-hand with this would 
be the ability for the superuser to move (or "promote") an object from a 
private schema to the public one. This would allow a user to develop 
their own objects without interfering with others, but then make it 
public with the superuser's assistance.

The search path you suggest above would then lead to the behavior that 
unqualified references to objects will see my own objects before the 
public ones, and other people's private objects must be explicitly 
qualified.

Joe




Wed, 23 Jan 2002 08:56:15 -0800
"Joe Conway (wwc)" <jconway@cox.net>
Re: RFD: schemas and different kinds of Postgres objects
"Joe Conway (wwc)" <jconway@cox.net> writes:
> I think it would be desirable to be able to restrict users from 
> "publishing" objects into the public schema.

Sure.  I'm envisioning that namespaces will have ACLs --- that's what
will keep private namespaces private.  So, while public would by default
be world-writable (at least in the backwards-compatibility case),
there'd be nothing stopping you from marking it as read-only to some
users.

Come to think of it, that's still another reason not to have an "any"
wildcard: there's no way to put any restrictions on what appears in
such a namespace.

			regards, tom lane

Wed, 23 Jan 2002 12:00:56 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: RFD: schemas and different kinds of Postgres objects 
On Wed, 23 Jan 2002, Tom Lane wrote:

> "Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:
> > When configured for historical behavior would need to:
> > 1. have search path: temp, any, system
> > 2. guard against duplicate table names across all schemas (except temp schema)
>
> This would be a *whole* lot simpler if we forgot the notion of "any"
> and made the search order look like
>
> 	(temp, private, public, system)
>
> where the public namespace is world-writable but the private per-user
> ones are (typically at least) not.
>
> It occurs to me that we can get both backward-compatible and SQL92
> semantics with this same search path; the only thing that needs to
> be different in the two cases is whether the default place to create
> objects is your private schema or the public one.  If you don't ever
> use your private schema then it doesn't matter if it's on the search
> path or not.  I would still prefer that the search path be a settable
> option, since a paranoid person might well wish to not have public in
> his path at all ... but the default could be as-above.

s/public/DEFAULT/ and add a way (createdb option) to make the default ACL
on DEFAULT such that anyone can create things and we are in agreement.

One of the parts of the schema system I'd envisioned (and tried to make)
was that there would be an IMPLIMENTATION_SCHEMA which owned all
built-ins, and a DEFAULT schema which was owned by the superuser. Making
the default schema path for a schema
IMPLIMENTATION_SCHEMA:<SCHEMA_NAME>:DEFAULT is fine.

Making the default schema for creation settable to DEFAULT would be fine,
and I think would remove objection. :-)

Take care,

Bill


Wed, 23 Jan 2002 14:49:32 -0800 (PST)
Bill Studenmund <wrstuden@netbsd.org>
Re: RFD: schemas and different kinds of Postgres objects
Tom Lane wrote:
> "Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:
> > When configured for historical behavior would need to:
> > 1. have search path: temp, any, system
> > 2. guard against duplicate table names across all schemas (except temp schema)
> 
> This would be a *whole* lot simpler if we forgot the notion of "any"
> and made the search order look like
> 
> 	(temp, private, public, system)
> 
> where the public namespace is world-writable but the private per-user
> ones are (typically at least) not.

[ I am just reading this schema thread now.]

The above private/public idea seems like a much better than 'any'.  That
'any' thing had me quite confused and the idea thought you would have
duplicates that would only be found at runtime seems destined to random
falures.

I assume 'private' above means search in my personal schema/namespace.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Thu, 24 Jan 2002 22:23:22 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: RFD: schemas and different kinds of Postgres objects
> > Or are you thinking about a per session behavior ?
> > I would rather envision a per database behavior.
> > Maybe the easy way out would be a "default creation schema" property for 
> > each user, that would default to the username. If you want everything in one 
> > schema simply alter the users.
> 
> I hadn't really gotten to the point of thinking about exactly what and
> where the control knobs should be.  I suspect you are right that we will
> want the default behavior to be selectable on a per-user or per-database
> basis, which seems to eliminate the option of using GUC (at least in its
> current form).  We could easily add a field to pg_shadow or pg_database
> respectively to determine the default behavior.  It'd be nice though if
> the behavior could be changed after connection by a SET statement, which
> would be lots easier if the setting were GUC-controlled.  Peter, you see
> any way to resolve that?

I think we could set the database default at db creation time, then
allow SET to modify that default per session;  seems flexible enough. 
It is basically a GUC value who's default is stored in pg_database
rather than postgresql.conf.   You could use postgresql.conf to set the
default schema type at db creation time.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Thu, 24 Jan 2002 22:26:43 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: RFD: schemas and different kinds of Postgres objects
Tom Lane writes:

> It'd be nice though if
> the behavior could be changed after connection by a SET statement, which
> would be lots easier if the setting were GUC-controlled.  Peter, you see
> any way to resolve that?

We had a text[] field to pg_shadow and/or pg_database containing
name=value assignments which are executed just before the session starts.
Doesn't look terribly difficult, and it's something I've always wanted to
do anyway.

-- 
Peter Eisentraut   peter_e@gmx.net


Thu, 24 Jan 2002 23:00:57 -0500 (EST)
Peter Eisentraut <peter_e@gmx.net>
Re: RFD: schemas and different kinds of Postgres objects
Peter Eisentraut <peter_e@gmx.net> writes:
> We [add] a text[] field to pg_shadow and/or pg_database containing
> name=value assignments which are executed just before the session starts.
> Doesn't look terribly difficult, and it's something I've always wanted to
> do anyway.

Seems like a fine idea, with many uses besides this one.

			regards, tom lane

Thu, 24 Jan 2002 23:23:39 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: RFD: schemas and different kinds of Postgres objects 
Peter Eisentraut wrote:
> Tom Lane writes:
> 
> > It'd be nice though if
> > the behavior could be changed after connection by a SET statement, which
> > would be lots easier if the setting were GUC-controlled.  Peter, you see
> > any way to resolve that?
> 
> We had a text[] field to pg_shadow and/or pg_database containing
> name=value assignments which are executed just before the session starts.
> Doesn't look terribly difficult, and it's something I've always wanted to
> do anyway.

So are thinking of "dbname=schema_type"?  Seems this is really something
that should be in pg_database.  If you create a database, who wants to
edit postgresql.conf to set its default schema type?  Why not set the
GUC value from pg_database?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Thu, 24 Jan 2002 23:33:52 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: RFD: schemas and different kinds of Postgres objects
Peter Eisentraut wrote:
> Tom Lane writes:
> 
> > It'd be nice though if
> > the behavior could be changed after connection by a SET statement, which
> > would be lots easier if the setting were GUC-controlled.  Peter, you see
> > any way to resolve that?
> 
> We had a text[] field to pg_shadow and/or pg_database containing
> name=value assignments which are executed just before the session starts.
> Doesn't look terribly difficult, and it's something I've always wanted to
> do anyway.

Sorry, I see what you are saying now, that the name=value pairs would
set in pg_database and pg_shadow and get executed on session startup. 
Very good.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Fri, 25 Jan 2002 00:55:32 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: RFD: schemas and different kinds of Postgres objects
On Thu, 24 Jan 2002, Bruce Momjian wrote:

> I think we could set the database default at db creation time, then
> allow SET to modify that default per session;  seems flexible enough.
> It is basically a GUC value who's default is stored in pg_database
> rather than postgresql.conf.   You could use postgresql.conf to set the
> default schema type at db creation time.

Specifically to the question of schema pathing, why would you want it to
be session-settable? Either your DB app is designed to work w/ schemas, or
it isn't. That's a pretty fundamental design concept. Given that, I don't
see how it can make sense to try to operate in the opposite mode as the
app was designed for - that'll only lead to chaos.

Take care,

Bill


Fri, 25 Jan 2002 14:07:58 -0800 (PST)
Bill Studenmund <wrstuden@netbsd.org>
Re: RFD: schemas and different kinds of Postgres objects
Bill Studenmund <wrstuden@netbsd.org> writes:
> Specifically to the question of schema pathing, why would you want it to
> be session-settable? Either your DB app is designed to work w/ schemas, or
> it isn't.

So that you can set the correct mode for your client application.  It is
silly to suppose that an installation-wide or even database-wide setting
is sufficient.  Consider for example a database shared by multiple
pieces of client software; wouldn't you like to be able to upgrade them
to schema-awareness one at a time?

You could possibly make a case for a single setting per user, but even
that makes an assumption (user == client software) that I think is not
reasonable for us to force on all Postgres installations.

Basically I haven't got a lot of patience for arguments that say we do
not need flexibility.  There are more people out there, using Postgres
in more different ways, than either you or I know about.

			regards, tom lane

Fri, 25 Jan 2002 17:26:14 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: RFD: schemas and different kinds of Postgres objects 
On Fri, 25 Jan 2002, Tom Lane wrote:

> Bill Studenmund <wrstuden@netbsd.org> writes:
> > Specifically to the question of schema pathing, why would you want it to
> > be session-settable? Either your DB app is designed to work w/ schemas, or
> > it isn't.
>
> So that you can set the correct mode for your client application.  It is
> silly to suppose that an installation-wide or even database-wide setting
> is sufficient.  Consider for example a database shared by multiple
> pieces of client software; wouldn't you like to be able to upgrade them
> to schema-awareness one at a time?

What exactly does it mean to upgrade to schema-awareness? I know the jist
of what you mean, but what does it entail? What steps? I ask as, when I
think of what it means in practical steps, an upgraded app won't have
problems with extra stuff pathed in. So the upgraded app will be fine with
the pathing set up to include all (not-upgraded) schemas. Also, if it does
have a problem with stuff pathed in, since you can easily set the new apps
up to live in different schemas than the old ones, you can have upgraded &
from-before schemas.

> You could possibly make a case for a single setting per user, but even
> that makes an assumption (user == client software) that I think is not
> reasonable for us to force on all Postgres installations.

But we will have the ability to set the path per schema. Since
schema-aware apps should be able to choose which schema they connect to (I
envision it being a connect parameter), the different apps can implicitly
get different behaviors by connecting to schemas that are designed to be
schema-savy, or connecting to ones which aren't (i.e. have all of the
schema-unaware stuff pathed in).

> Basically I haven't got a lot of patience for arguments that say we do
> not need flexibility.  There are more people out there, using Postgres
> in more different ways, than either you or I know about.

Tom, please listen to what I'm saying. I'm trying to be as clear as I can
& making sure I'm not working from details in my head and not my posts.
I'm sorry if it isn't clear but I'm _not_ saying that we don't need the
flexability you describe. We do.

I'm saying that IT IS ALREADY THERE! The pathing built into schemas can be
very powerful. Powerful enough that I haven't heard of an example yet that
can't be taken care of with judicious use of pathing. And I don't think
the pathing needed is beyond mid-level admins (I don't see it as something
which only say 5 people on the lists can get right). Yes, people will have
to learn it, but it doesn't strike me as that hard a thing.

What I am saying is that we don't need the solution you & Bruce mentioned
to get the flexability you mentioned as the reason for adding it. So why
add the feature which isn't needed?

One of my objections to a "mode" supporting the old behavior is that, as I
understand it, there would be a schema ("public") where different users
could own objects in the same schema. That goes against one of the
advantages I see for schemas: we can consolidate ownership info in the
system tables. If you know what schema something is in, you know its
owner. That means that adding schema support doesn't mean growing system
tables, just renaming a column (the user id gets turned into the schema
id).

Maybe that's not such a big deal. But it seems when we're doing things
right, things should get cleaner. Having to keep ownership info at both
the schema level and at the object level strikes me as not making things
cleaner. That just seems to be going in the wrong direction.

Especially as, AFAICT, it wouldn't be hard to let the sysadmins have all
the flexability you want them to have (and also that I agree they should
have) in a system which is, at its core, very schema-savy (everything in
one schema is owned by the same user or group).

I also agree that migration is important. Apps from 7.2 (and 7.1 and
earlier as possible) should run on the schema-savy backend I describe. A
migration tool to take the dump from before and add update schema commands
to path everything in (so it looks like one namespace) should make the old
apps keep working.

The one thing I'll concede could be useful would be for createuser to be
told to automatically set the new user's schema to include all the other
schemas, and to update all the other user schemas to add this user. That
way you can add new users to your DB when you're acting as if it didn't
have schemas.

Hmmm. If we made the above behavior a per-db-configurable default, the
pg_dump file wouldn't need to be changed. That would be good. It would
make the path updates O(nusers^2) rather than O(nusers), but that probably
won't be bad. And offering both options would probably be good.

Take care,

Bill


Fri, 25 Jan 2002 15:41:16 -0800 (PST)
Bill Studenmund <wrstuden@netbsd.org>
Re: RFD: schemas and different kinds of Postgres objects
Bill Studenmund <wrstuden@netbsd.org> writes:
> But we will have the ability to set the path per schema.

?? I don't follow that at all. A namespace is something that's referred
to by a search path, not vice versa.  Or are you defining "schema" to
mean some higher-level concept that incorporates a search path of
multiple primitive namespaces?  Maybe that could work, but I'm not sure
I see the point yet.

			regards, tom lane

Fri, 25 Jan 2002 19:04:15 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: RFD: schemas and different kinds of Postgres objects 
On Fri, 25 Jan 2002, Tom Lane wrote:

> Bill Studenmund <wrstuden@netbsd.org> writes:
> > But we will have the ability to set the path per schema.
>
> ?? I don't follow that at all. A namespace is something that's referred
> to by a search path, not vice versa.  Or are you defining "schema" to
> mean some higher-level concept that incorporates a search path of
> multiple primitive namespaces?  Maybe that could work, but I'm not sure
> I see the point yet.

Oh. That would make a difference. We've been talking past each other.

SQL schemas, as I understand the spec, are both. A shema is a container
that holds things like tables and views and functions (and for PostgreSQL
operators and aggregates and I'd suggest index operators, etc.). It also
can include a schema path specification, which defines the search path
used by routines (stored procedures & functions) contained in that schema.

So say I have schemas foo, bar, and baz. I can set the schema path for
schema bar to be foo:bar:baz:IMPLIMENTATION_SCHEMA, and all routines in
bar will look in those four schemas for types, functions and tables (and
everything else we use the search path for).

(*) IMPLIMENTATION_SCHEMA is required by the spec, and contains all the
built-ins. It's be implimentation_schema for pg. Also, if you have a path
that doesn't list it, the db is supposed to prepend it to the list.

So when migrating an app from a schema-unaware PostgreSQL to a
schema-aware one, if we create a schema for each user, and make each
such schema path in all the other such schemas, we make it such that all
of the procedures in those schemas act like they have a unified namespace.

There also is also the concept of the CURRENT_PATH which is the schema
path used for parsed queries (like ones typed into psql). I got lost in
the spec trying to find what this is supposed to default to, but what I
understand other DBs to do is your CURRENT_PATH is set to the path of the
schema you log into.

Add to this mix the default schema for user X is schema X (which I thought
was in the spec but I can't find now), and let's look at that example
again.

Say we had users foo, bar and baz before. We made schemas foo, bar, and
baz. We set the default paths for each of these schemas to
foo:bar:baz:IMPLIMENTATION_SCHEMA. Now the routines in each of these
schemas will see a unified namespace. Next, when we log in as users foo,
bar, or baz, and our CURRENT_PATH ends up including the namespaces of the
three original users. So now all of our submitted queries also see a
unified namespace.

So with a schema-savy backend, by adding PATH statements to the schemas
that pull in all of the previous schemas, we can make the old app behave
as if it had a unified namespace.

Does that make sense?

Take care,

Bill

P.S. does anyone need copies of the spec? I found pdf's on the web a while
back..


Fri, 25 Jan 2002 17:35:11 -0800 (PST)
Bill Studenmund <wrstuden@netbsd.org>
Re: RFD: schemas and different kinds of Postgres objects