Re: Schemas: status report, call for developers

I think DBD::Pg driver very much depends on system tables.
Hope, Jeffrey (current maintainer) is online.

regards,

Oleg
On Tue, 30 Apr 2002, Tom Lane wrote:

> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83