pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedSchemas: status report, call for developers
7+ messages / 4 participants
[nested] [flat]
* Schemas: status report, call for developers
@ 2002-04-30 17:31 Tom Lane <tgl@sss.pgh.pa.us>
2002-04-30 17:45 ` Re: Schemas: status report, call for developers Bill Cunningham <billc@ballydev.com>
2002-04-30 18:41 ` Re: Schemas: status report, call for developers Oleg Bartunov <oleg@sai.msu.su>
0 siblings, 2 replies; 7+ messages in thread
From: Tom Lane @ 2002-04-30 17:31 UTC (permalink / raw)
To: pgsql-hackers@postgreSQL.org; pgsql-interfaces
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Schemas: status report, call for developers
2002-04-30 17:31 Schemas: status report, call for developers Tom Lane <tgl@sss.pgh.pa.us>
@ 2002-04-30 17:45 ` Bill Cunningham <billc@ballydev.com>
2002-04-30 18:23 ` Re: Schemas: status report, call for developers Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 7+ messages in thread
From: Bill Cunningham @ 2002-04-30 17:45 UTC (permalink / raw)
To: ; +Cc: pgsql-hackers@postgreSQL.org; pgsql-interfaces
<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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Schemas: status report, call for developers
2002-04-30 17:31 Schemas: status report, call for developers Tom Lane <tgl@sss.pgh.pa.us>
2002-04-30 17:45 ` Re: Schemas: status report, call for developers Bill Cunningham <billc@ballydev.com>
@ 2002-04-30 18:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-04-30 20:46 ` Re: Schemas: status report, call for developers Bill Cunningham <billc@ballydev.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2002-04-30 18:23 UTC (permalink / raw)
To: Bill Cunningham <billc@ballydev.com>; +Cc: pgsql-hackers@postgresql.org; pgsql-interfaces
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Schemas: status report, call for developers
2002-04-30 17:31 Schemas: status report, call for developers Tom Lane <tgl@sss.pgh.pa.us>
2002-04-30 17:45 ` Re: Schemas: status report, call for developers Bill Cunningham <billc@ballydev.com>
2002-04-30 18:23 ` Re: Schemas: status report, call for developers Tom Lane <tgl@sss.pgh.pa.us>
@ 2002-04-30 20:46 ` Bill Cunningham <billc@ballydev.com>
2002-04-30 21:16 ` Re: Schemas: status report, call for developers Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Bill Cunningham @ 2002-04-30 20:46 UTC (permalink / raw)
To: pgsql-hackers <pgsql-hackers@postgreSQL.org>; pgsql-interfaces
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Schemas: status report, call for developers
2002-04-30 17:31 Schemas: status report, call for developers Tom Lane <tgl@sss.pgh.pa.us>
2002-04-30 17:45 ` Re: Schemas: status report, call for developers Bill Cunningham <billc@ballydev.com>
2002-04-30 18:23 ` Re: Schemas: status report, call for developers Tom Lane <tgl@sss.pgh.pa.us>
2002-04-30 20:46 ` Re: Schemas: status report, call for developers Bill Cunningham <billc@ballydev.com>
@ 2002-04-30 21:16 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2002-04-30 21:16 UTC (permalink / raw)
To: Bill Cunningham <billc@ballydev.com>; +Cc: pgsql-hackers <pgsql-hackers@postgresql.org>; pgsql-interfaces
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Schemas: status report, call for developers
2002-04-30 17:31 Schemas: status report, call for developers Tom Lane <tgl@sss.pgh.pa.us>
@ 2002-04-30 18:41 ` Oleg Bartunov <oleg@sai.msu.su>
1 sibling, 0 replies; 7+ messages in thread
From: Oleg Bartunov @ 2002-04-30 18:41 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; jwbaker@acm.org; +Cc: Pgsql Hackers <pgsql-hackers@postgresql.org>; pgsql-interfaces
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [INTERFACES] Schemas: status report, call for developers
@ 2002-04-30 18:54 Dave Page <dpage@vale-housing.co.uk>
0 siblings, 0 replies; 7+ messages in thread
From: Dave Page @ 2002-04-30 18:54 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers@postgresql.org; pgsql-interfaces; +Cc: pgadmin-hackers@postgresql.org
> -----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 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.
>
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2002-04-30 21:16 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-04-30 17:31 Schemas: status report, call for developers Tom Lane <tgl@sss.pgh.pa.us>
2002-04-30 17:45 ` Bill Cunningham <billc@ballydev.com>
2002-04-30 18:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-04-30 20:46 ` Bill Cunningham <billc@ballydev.com>
2002-04-30 21:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-04-30 18:41 ` Oleg Bartunov <oleg@sai.msu.su>
2002-04-30 18:54 Re: [INTERFACES] Schemas: status report, call for developers Dave Page <dpage@vale-housing.co.uk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox