Schemas: status report, call for developers

Current CVS tip has most of the needed infrastructure for SQL-spec
schema support: you can create schemas, and you can create objects
within schemas, and search-path-based lookup for named objects works.
There's still a number of things to be done in the backend, but it's
time to start working on schema support in the various frontends that
have been broken by these changes. I believe that pretty much every
frontend library and client application that looks at system catalogs
will need revisions. So, this is a call for help --- I don't have the
time to fix all the frontends, nor sufficient familiarity with many
of them.

JDBC and ODBC metadata code is certainly broken; so are the catalog
lookups in pgaccess, pgadmin, and so on. psql and pg_dump are broken
as well (though I will take responsibility for fixing pg_dump, and will
then look at psql if no one else has done it by then). I'm not even
sure what else might need to change.

Here's an example of what's broken:

test=# create schema foo;
CREATE
test=# create table foo.mytab (f1 int, f2 text);
CREATE
test=# create schema bar;
CREATE
test=# create table bar.mytab (f1 text, f3 int);
CREATE
test=# \d mytab
Table "mytab"
Column | Type | Modifiers
--------+---------+-----------
f1 | text |
f1 | integer |
f2 | text |
f3 | integer |

psql's \d command hasn't the foggiest idea that there might now be more
than one pg_class entry with the same relname. It needs to be taught
about that --- but even before that, we need to work out schema-aware
definitions of the wildcard expansion rules for psql's backslash
commands that accept wildcarded names. In the above example, probably
"\d mytab" should have said "no such table" --- because neither foo nor
bar were in my search path, so I should not see them unless I give a
qualified name (eg, "\d foo.mytab" or "\d bar.mytab"). For commands
that accept wildcard patterns, what should happen --- should "\z my*"
find these tables, if they're not in my search path? Is "\z f*.my*"
sensible to support? I dunno yet.

If you've got time to work on fixing frontend code, or even helping
to work out definitional questions like these, please check out current
CVS tip or a nightly snapshot tarball and give it a try. (But do NOT
put any valuable data into current sources --- until pg_dump is fixed,
you won't be able to produce a useful backup of a database that uses
multiple schemas.)

Some documentation can be found at
http://developer.postgresql.org/docs/postgres/sql-naming.html
http://developer.postgresql.org/docs/postgres/sql-createschema.html
http://developer.postgresql.org/docs/postgres/sql-grant.html
http://developer.postgresql.org/docs/postgres/runtime-config.html#RUNTIME-CONFIG-GENERAL (see SEARCH_PATH)
but more needs to be written. (In particular, I think the Tutorial
could stand to have a short section added about schemas; and the Admin
Guide ought to be revised to discuss running one database with per-user
schemas as a good alternative to per-user databases. Any volunteers to
write that stuff?)

Some things that don't work yet in the backend:

1. There's no DROP SCHEMA. (If you need to, you can drop the contained
objects and then manually delete the pg_namespace row for the schema.)
No ALTER SCHEMA RENAME either (though you can just UPDATE the
pg_namespace row if you need that).

2. CREATE SCHEMA with sub-statements isn't up to SQL spec requirements
yet. Best bet is to create the schema and then create contained objects
separately, as in the above example.

3. I'm not sure that the newly-defined GRANT privileges are all checked
everywhere they should be. Also, the default privilege settings
probably need fine-tuning still.

4. We probably need more helper functions and/or predefined system views
to make it possible to fix the frontends in a reasonable way --- for
example, it's still quite difficult for something looking at pg_class to
determine which tables are visible in the current search path. Thoughts
about what should be provided are welcome.

regards, tom lane