Re: RFD: schemas and different kinds of Postgres objects

On Fri, 25 Jan 2002, Tom Lane wrote:

> Bill Studenmund <wrstuden(at)netbsd(dot)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