Re: schema support, was Package support for Postgres

Bill Studenmund writes:

> I guess to get at my point, I can ask this question, "Will schema support
> invalidate existing PostgreSQL database designs."
>
> I would like the answer to be no. I would like our users to be able to
> dump a pre-schema-release db, upgrade, and then restore into a
> schema-aware PostgreSQL. And have their restore work.

I think this can work. Assume a database like this:

user1: CREATE TABLE foo ( );
user2: CREATE TABLE bar ( );

The dump of this would be something like:

\c - user1
CREATE TABLE foo ( );

\c - user2
CREATE TABLE bar ( );

So the tables would be created in the appropriate schema context for each
user. The remaining problem then is that the two schemas user1 and user2
would need to be created first, but we could make this implicit somewhere.
For instance, a user creation would automatically create a schema for the
user in template1. Or at least the dump could be automatically massaged
to this effect.

> But right now, we can have different users owning things in one database.
> So there will be restores out there which will have different users owning
> things in the same restored-to schema, which will be "DEFAULT".

This would fundamentally undermine what an SQL schema is and don't help
interoperability a bit. If we want to implement our own namespace
mechanism we can call it NAMESPACE. But if we want something called
SCHEMA then we should implement it the way it's standardized, and there is
certainly a tight coupling between schemas and ownership. In fact, as
I've said already, a schema *is* the ownership; a user is just a weird
PostgreSQL invention.

> I think that schemas owned by roles are part of SQL99.

Correct.

--
Peter Eisentraut peter_e(at)gmx(dot)net http://funkturm.homeip.net/~peter