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