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
<snip>
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 |
I would think this should produce the following:
Table "bar.mytab"
Column | Type | Modifiers
--------+---------+-----------
f1 | text |
f1 | integer |
Table "foo.mytab"
Column | Type | Modifiers
--------+---------+-----------
f2 | text |
f3 | integer |
What do you think?
- Bill Cunningham
Bill Cunningham <billc@ballydev.com> writes:
I would think this should produce the following:
test=# \d mytab
Table "bar.mytab"
Column | Type | Modifiers
--------+---------+-----------
f1 | text |
f1 | integer |
Table "foo.mytab"
Column | Type | Modifiers
--------+---------+-----------
f2 | text |
f3 | integer |
Even when schemas bar and foo are not in your search path? (And,
perhaps, not even accessible to you?)
My gut feeling is that "\d mytab" should tell you about the same
table that "select * from mytab" would find. Anything else is
probably noise to you --- if you wanted to know about foo.mytab,
you could say "\d foo.mytab".
However, \d is not a wildcardable operation AFAIR. For the commands
that do take wildcard patterns (like \z), I'm not as sure what should
happen.
regards, tom lane
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@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 30 April 2002 18:32
To: pgsql-hackers@postgresql.org; pgsql-interfaces@postgresql.org
Subject: [INTERFACES] Schemas: status report, call for developersCurrent 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.
Thanks Tom, this is just the post I've been waiting for!
To anyone thinking of hacking pgAdmin at the moment -> now would
probably not be the best time as I will be *seriously* restructuring
pgSchema.
Regards, Dave.
Import Notes
Resolved by subject fallback
Tom Lane wrote:
Bill Cunningham <billc@ballydev.com> writes:
I would think this should produce the following:
test=# \d mytab
Table "bar.mytab"
Column | Type | Modifiers
--------+---------+-----------
f1 | text |
f1 | integer |Table "foo.mytab"
Column | Type | Modifiers
--------+---------+-----------
f2 | text |
f3 | integer |Even when schemas bar and foo are not in your search path? (And,
perhaps, not even accessible to you?)My gut feeling is that "\d mytab" should tell you about the same
table that "select * from mytab" would find. Anything else is
probably noise to you --- if you wanted to know about foo.mytab,
you could say "\d foo.mytab".However, \d is not a wildcardable operation AFAIR. For the commands
that do take wildcard patterns (like \z), I'm not as sure what should
happen.regards, tom lane
So we now have a default schema name of the current user? For example:
foobar@somewhere> psql testme
testme=# select * from mytab
Table "foobar.mytab"
Column | Type | Modifiers
--------+---------+-----------
f2 | text |
f3 | integer |
like that? This is exactly how DB2 operates, implict schemas for each user.
- Bill Cunningham
Bill Cunningham <billc@ballydev.com> writes:
So we now have a default schema name of the current user?
... This is exactly how DB2 operates, implict schemas for each user.
You can operate that way. It's not the default though; the DBA will
have to explicitly do a CREATE SCHEMA for each user. For instance:
test=# CREATE USER tgl;
CREATE USER
test=# CREATE SCHEMA tgl AUTHORIZATION tgl;
CREATE
test=# \c - tgl
You are now connected as new user tgl.
test=> select current_schemas();
current_schemas
-----------------
{tgl,public} -- my search path is now tgl, public
(1 row)
-- this creates tgl.foo:
test=> create table foo(f1 int);
CREATE
test=> select * from foo;
f1
----
(0 rows)
test=> select * from tgl.foo;
f1
----
(0 rows)
If you don't create schemas then you get backwards-compatible behavior
(all the users end up sharing the "public" schema as their current
schema).
See the development-docs pages I mentioned before for details.
regards, tom lane
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.
Technical question - this query:
SELECT nspname AS schema,
relname AS object
FROM pg_class c
INNER JOIN pg_namespace n
ON c.relnamespace=n.oid
WHERE relkind in ('r', 'v', 'S') AND
relname NOT LIKE 'pg$_%%' ESCAPE '$'
produces a result like this:
schema | object
--------+--------
public | abc
foo | abc
foo | xyz
bar | xyz
(4 rows)
How can I restrict the query to the schemas in the
current search path, i.e. the schema names returned
by SELECT current_schemas() ?
Ian Barwick
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.
phpPgAdmin (WebDB) will be broken as well. I think myself and at least a
few other committers lurk here tho.
Other things that will break:
TOra
Various KDE interfaces
Chris
test=# CREATE USER tgl;
CREATE USER
test=# CREATE SCHEMA tgl AUTHORIZATION tgl;
CREATE
What about "CREATE USER tgl WITH SCHEMA;" ?
Which will implicitly do a "CREATE SCHEMA tgl AUTHORIZATION tgl;"
Chris
produces a result like this:
schema | object
--------+--------
public | abc
foo | abc
foo | xyz
bar | xyz
(4 rows)How can I restrict the query to the schemas in the
current search path, i.e. the schema names returned
by SELECT current_schemas() ?
Now, if we had functions-returning-sets, this would all be easy as all you'd
need to do would be to join it with the function returning the set of
schemas in your search path :)
Chris
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
What about "CREATE USER tgl WITH SCHEMA;" ?
Uh, what about it? It's not a standard syntax AFAIK.
If I were running an installation where I wanted "one schema per user"
as default, I'd rather have an "auto_create_schema" SET parameter that
told CREATE USER to do the dirty work for me automatically.
But the sneaky part of this is that users are installation-wide,
whereas schemas are only database-wide. To make this really work
painlessly, you'd want some kind of mechanism that'd auto-create
a schema for the user in every database he's allowed access to.
How can we define that cleanly?
regards, tom lane
On Wed, May 01, 2002 at 10:05:23AM +0800, Christopher Kings-Lynne wrote:
phpPgAdmin (WebDB) will be broken as well. I think myself and at least a
few other committers lurk here tho.Other things that will break:
TOra
Various KDE interfaces
GNUe will break, as well.
Ross
I think it would be much faster simply to list of the programs that
use Postgresql internals that won't break.
--
Rod
----- Original Message -----
From: "Ross J. Reedstrom" <reedstrm@rice.edu>
To: "Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Cc: "Dave Page" <dpage@vale-housing.co.uk>; "Tom Lane"
<tgl@sss.pgh.pa.us>; <pgsql-hackers@postgresql.org>;
<pgsql-interfaces@postgresql.org>; <pgadmin-hackers@postgresql.org>
Sent: Tuesday, April 30, 2002 11:28 PM
Subject: Re: [HACKERS] [INTERFACES] Schemas: status report, call for
developers
On Wed, May 01, 2002 at 10:05:23AM +0800, Christopher Kings-Lynne
wrote:
phpPgAdmin (WebDB) will be broken as well. I think myself and at
least a
few other committers lurk here tho.
Other things that will break:
TOra
Various KDE interfacesGNUe will break, as well.
Ross
---------------------------(end of
broadcast)---------------------------
Show quoted text
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
"Ross J. Reedstrom" <reedstrm@rice.edu> writes:
GNUe will break, as well.
Do I hear a volunteer to fix it?
regards, tom lane
"Rod Taylor" <rbt@zort.ca> writes:
I think it would be much faster simply to list of the programs that
use Postgresql internals that won't break.
Approximately none, I'm sure :-(. This thread isn't about that, it's
about stirring up the troops to fix everything that must be fixed.
regards, tom lane
Ian Barwick <barwick@gmx.net> writes:
How can I restrict the query to the schemas in the
current search path, i.e. the schema names returned
by SELECT current_schemas() ?
Well, this is the issue open for public discussion.
We could define some function along the lines of
"is_visible_table(oid) returns bool", and then you could use
that as a WHERE clause in your query. But I'm worried about
the performance implications --- is_visible_table() would have
to do several retail probes of the system tables, and I don't
see any way to optimize that across hundreds of table OIDs.
I have a nagging feeling that this could be answered by defining
a view on pg_class that only shows visible tables ... but I don't
quite see how to define that efficiently, either. Ideas anyone?
regards, tom lane
On Wed, May 01, 2002 at 12:03:00AM -0400, Tom Lane wrote:
"Ross J. Reedstrom" <reedstrm@rice.edu> writes:
GNUe will break, as well.
Do I hear a volunteer to fix it?
I'm willing to implement whatever clever solution we all come up with.
I'll have to coordinate w/ the GNUe IRC folks to get it checked in.
Ross
"Ross J. Reedstrom" <reedstrm@rice.edu> writes:
GNUe will break, as well.
I'm willing to implement whatever clever solution we all come up with.
If you need help in inventing a solution, it'd be a good idea to explain
the problem. Personally I'm not familiar with GNUe ...
regards, tom lane
On Wed, May 01, 2002 at 12:56:00AM -0400, Tom Lane wrote:
"Ross J. Reedstrom" <reedstrm@rice.edu> writes:
GNUe will break, as well.
I'm willing to implement whatever clever solution we all come up with.
If you need help in inventing a solution, it'd be a good idea to explain
the problem. Personally I'm not familiar with GNUe ...
I think all the interfaces are having the same fundemental problem: how to
limit the tables 'seen' to a particular list of schema (those in the path).
GNUe is GNU Enterprise System - a somewhat grandiose name for a business
middleware solutions project. It's highly modular, with a common core to
deal with things like DB access. There's a reasonably nice forms designer
to handle quickie 2-tier DB apps (client-server, skip the middleware).
Right now, it's mostly coded in python. I'm taking off on a
business trip for the remainder of the week, starting tomorrow (err
today?!) morning. I'll take the GNUe code along and see what it's db
schema discovery code is actually doing, and think about what sort of
clever things to do. I think for GNUe, we might get away with requiring
the end-user (designer) to select a particular schema to work in, and then
just qualify everything.
Later,
Ross
