Re: schema support, was Package support for Postgres

On Fri, 26 Oct 2001, Peter Eisentraut wrote:

> 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.

Hmmm.... I've been looking into this, and you are right. All of the views
in INFORMATION_SCHEMA that I looked at contain text like

WHERE (SCHEMA_OWNER = CURRENT_USER OR SCHEMA_OWNER IN (SELECT ROLL_NAME
FROM ENABLED_ROLES) )

So then we'll need a tool to massage old-style dumps to:

1) create the schema, and

2) path all of the schemas together by default.

Well, at least a number of tables won't gain a new colum as a result of
this; the owner column will become the schema_id column. :-)

Take care,

Bill