Re: schema support, was Package support for Postgres

On Tue, 16 Oct 2001, Bill Studenmund wrote:

> I still think that schemas and packages are different, but I now think
> they are interrelated. And that it shouldn't be too hard to leverage the
> package work into schema support. Still a lot of work, but the package
> work has shown how to go from one to two in a number of ways. :-)
>
> First off, do you (Tom) have a spec for schema support? I think that would
> definitly help things.

I found an on-line copy of the SQL92 spec, and I've been looking at it.

I think it wouldn't be _that_ much more work to add shema support to what
I've done for packages. Not trivial, but certainly not double the work.

But I have some questions.

The big one for now is how should you log into one schema or another?
psql database.schema ?

Here's a plan for schema support. But first let me review what packages
have.

Right now (in my implimentation), packages have added a "standard" package
(oid 10) which contains all of the built-in procedures, aggregates, types,
and operators.  Whenever you use the normal CREATE commands, you add a
procedure, aggregate, operator, or type in the "standard" package.

There is a new table, pg_package, which lists the name of each installed
package and its owner. "standard" is owned by PGUID. packages are
referenced by the oid of the row describing the package in this table.

Whenever you look up a function or aggregate, you give the oid of the
package to look in in addition to the name (and types). Having the package
id in the index provides the namespacing.

Whenever you look up a type or operator, you don't have to give a package
id.

Whenever you call the parser to parse a command, you pass it the package
context (oid) in which the parsing takes place. If you are typing in
commands in psql, that package id is 10, or "standard". Likewise for sql
or plpgsql routines not in a package. If you are in an sql or plpgsql
routine which is in a package, the package's oid is passed in. That's what
has package routines look in the package first.

The parser also notes if you gave a package id or not (package.foo vs
foo). If you were in a package context and were not exact (foo in a
procedure in a package for instance), then all of the places which look up
functions will try "standard" if they don't find a match.

There is a table, pg_packglobal, which contains package globals for the
different PLs. It contains 5 columns. The first three are the package oid,
the language oid, and a sequence number. They are indexed. The two others
are variable name and variable type (of PostgreSQL type name and text
respectively). PLs for which these variables don't make sense are free to
ignore them.

Extending this for schema support.

Executive summary: all of the above becomes the infrastructure to let
different schemas have schema-private functions and aggregates.

We add a new table, pg_schema, which lists the schemas in this database.
It would contain a name column, an owner column, something to indicate
character set (?), and other stuff I don't know of. Schemas are referenced
internally by the oid of the entry in this table.

There is a built-in schema, "master". It will have a fixed oid, probalby 9
or 11.

The "master" schema will own the "standard" package oid 10, which contains
all of the built-in functions, and ones added by create function/etc.

Each new schema starts life with a "standard" package of its own. This
package is the one which holds functions & aggregates made with normal
commands (create function, create aggregate) when you're logged into that
schema.

pg_package grows two more columns. One references the schema containing
the package. The other contains the oid of the "parent" package. The idea
is this oid is the next oid to look in when you are doing an inexact oid
search. It's vaguely like ".." on a file system.

For master.standard, this column is 0, indicating no further searching.
For say foo.standard (foo is a schema), it would be the oid of
master.standard (10). Likewise for a package baz in the master schema, it
would be master.standard. For a package in a schema, it would be the oid
of the "standard" package of the schema. As an example, say the foo schema
had a package named bup. For baz.bup, this column would have the oid of
baz.standard.

Right now I'm in the process of redoing the parser changes I made so that
the scanner doesn't need to recognize package names. When this is done,
the parser will be able to deal with schema.function and package.function.
Oh, also schema.table.attr too. schema.package.function won't be hard, but
it will be messy.

The only other part (which is no small one) is to add namespacing to the
rest of the backend. I expect that will mean adding a schema column to
pg_class, pg_type, and pg_operator.

Hmmm... We probably also need a command to create operator classes, and
the tables it touches would need a schema column too, and accesses will
need to be schema savy.

Well, that's a lot for now. Thoughts?

Take care,

Bill