Hi,

I'm playing with the new schema functionality.

I login to a database as a user yamada.
There I created 2 schemas yamada and inoue.
By accident I made 2 tables with the same name
vs1 in both public and yamada schemas.

I can see the content of yamada.vs1 by the command
  select * from vs1
but there seems to be no way to see the content of
public.vs1.
If I drop the table yamada.vs1, I can see the content
of public.vs1 by the command
  select * from vs1.
Well there seems to be no concept of the CURRENT schema.
Is it intended ?

regards,
Hiroshi Inoue

Thu, 04 Apr 2002 12:41:05 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
What's the CURRENT schema ?
Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> I can see the content of yamada.vs1 by the command
>   select * from vs1
> but there seems to be no way to see the content of
> public.vs1.

PUBLIC is a reserved keyword, so you have to do something like
	select * from "public".vs1;
if there is a vs1 hiding it in an earlier namespace in the search
path.

I've been vacillating about whether to choose another name for the
public namespace to avoid the need for quotes here.  I can't think
of another good name :-(

			regards, tom lane

Wed, 03 Apr 2002 23:20:19 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: What's the CURRENT schema ? 
Tom Lane wrote:
> PUBLIC is a reserved keyword, so you have to do something like
> 	select * from "public".vs1;
> if there is a vs1 hiding it in an earlier namespace in the search
> path.
> 
> I've been vacillating about whether to choose another name for the
> public namespace to avoid the need for quotes here.  I can't think
> of another good name :-(
> 


What about shared.vs1 or common.vs1?

Joe



Wed, 03 Apr 2002 20:53:53 -0800
Joe Conway <mail@joeconway.com>
Re: What's the CURRENT schema ?
Tom Lane wrote:
> 
> Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> > I can see the content of yamada.vs1 by the command
> >   select * from vs1
> > but there seems to be no way to see the content of
> > public.vs1.
> 
> PUBLIC is a reserved keyword, so you have to do something like
>         select * from "public".vs1;
> if there is a vs1 hiding it in an earlier namespace in the search
> path.

I see. However my main problem is that the schema of unqualified
vs1 is affected by the existence of yamada.vs1. I don't think
it's a useful behavior.

regards,
Hiroshi Inoue

Thu, 04 Apr 2002 13:54:25 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: What's the CURRENT schema ?
Tom Lane writes:

> PUBLIC is a reserved keyword, so you have to do something like
> 	select * from "public".vs1;
> if there is a vs1 hiding it in an earlier namespace in the search
> path.

PUBLIC can be made less reserved easily.  See patch below.

> I've been vacillating about whether to choose another name for the
> public namespace to avoid the need for quotes here.  I can't think
> of another good name :-(

PUBLIC is a good name.  Oracle uses it, I think.


diff -u -r2.299 gram.y
--- gram.y  1 Apr 2002 04:35:38 -0000   2.299
+++ gram.y  4 Apr 2002 05:10:23 -0000
@@ -2558,14 +2558,14 @@
                    n->groupname = NULL;
                    $$ = (Node *)n;
                }
-       | GROUP ColId
+       | GROUP UserId
                {
                    PrivGrantee *n = makeNode(PrivGrantee);
                    n->username = NULL;
                    n->groupname = $2;
                    $$ = (Node *)n;
                }
-       | ColId
+       | UserId
                {
                    PrivGrantee *n = makeNode(PrivGrantee);
                    n->username = $1;
@@ -5897,7 +5897,6 @@

 Iconst:  ICONST                            { $$ = $1; };
 Sconst:  SCONST                            { $$ = $1; };
-UserId:  ColId                         { $$ = $1; };

 /*
  * Name classification hierarchy.
@@ -5913,6 +5912,13 @@
 /* Column identifier --- names that can be column, table, etc names.
  */
 ColId:  IDENT                          { $$ = $1; }
+       | unreserved_keyword            { $$ = $1; }
+       | col_name_keyword              { $$ = $1; }
+       | PUBLIC                        { $$ = "public"; }
+       ;
+
+/* User identifier */
+UserId:  IDENT                         { $$ = $1; }
        | unreserved_keyword            { $$ = $1; }
        | col_name_keyword              { $$ = $1; }
        ;

-- 
Peter Eisentraut   peter_e@gmx.net


Thu, 4 Apr 2002 00:18:22 -0500 (EST)
Peter Eisentraut <peter_e@gmx.net>
Re: What's the CURRENT schema ? 
Peter Eisentraut <peter_e@gmx.net> writes:
> PUBLIC can be made less reserved easily.  See patch below.

Well, we could do that, but this patch seems an ugly way to do it;
we have too many classifications of keywords already, and I don't
want to introduce another one.

I'd be inclined to make PUBLIC not a keyword at all, and instead have
the production grantee -> ColId do this in its action:

	if (strcmp($1, "public") == 0)
		create PUBLIC PrivGrantee node
	else
		create normal PrivGrantee node

An objection to this is that you couldn't make a user named "public"
(with the quotes), since PUBLIC and "public" would look the same to
the action ... but that seems like a good restriction anyway.  I'd
be quite willing to tweak CREATE USER to forbid that name.

I suppose it's a judgment call which is uglier.  Thoughts?

			regards, tom lane

Thu, 04 Apr 2002 10:11:36 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: What's the CURRENT schema ? 
Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> I see. However my main problem is that the schema of unqualified
> vs1 is affected by the existence of yamada.vs1. I don't think
> it's a useful behavior.

Well, if you don't like it, you could set the search_path to be just
public, or public and then the user's personal namespace.  But I think
personal namespace before public should be the default behavior.

			regards, tom lane

Thu, 04 Apr 2002 10:13:18 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: What's the CURRENT schema ? 
Tom Lane wrote:
> 
> I've been vacillating about whether to choose another name for the
> public namespace to avoid the need for quotes here.  I can't think
> of another good name :-(
> 

For the special schemas, we have pg_catalog, (pg_temp, pg_toast ?),
so pg_public could do the trick.

-- 
Fernando Nasser
Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9

Thu, 04 Apr 2002 15:16:07 -0500
Fernando Nasser <fnasser@redhat.com>
Re: What's the CURRENT schema ?
Hiroshi Inoue wrote:
> 
> I see. However my main problem is that the schema of unqualified
> vs1 is affected by the existence of yamada.vs1. I don't think
> it's a useful behavior.
> 

The unqualified one is there mainly for compatibility, so you can
still use your old database set ups without schema names.

Once you redo your database to use schemas, or even while you are 
converting it, there should not be tables with the same name in both 
places.

Anyway, as Tom said, you can change the search order if you prefer.

-- 
Fernando Nasser
Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9

Thu, 04 Apr 2002 15:19:20 -0500
Fernando Nasser <fnasser@redhat.com>
Re: What's the CURRENT schema ?
Tom Lane wrote:
> 
> I suppose it's a judgment call which is uglier.  Thoughts?
> 

Well, PUBLIC is an SQL reserved keyword (pre-92).  We are
already very liberal with keywords. I would leave PUBLIC alone.

I does not _have_ to be "public", so we can just avoid the issue
by adding a pg_ prefix to public, common or something else. 
It is a PostgreSQL concept anyway.   

-- 
Fernando Nasser
Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9

Thu, 04 Apr 2002 15:27:58 -0500
Fernando Nasser <fnasser@redhat.com>
Re: What's the CURRENT schema ?
Fernando Nasser <fnasser@redhat.com> writes:
> Tom Lane wrote:
>> I've been vacillating about whether to choose another name for the
>> public namespace to avoid the need for quotes here.  I can't think
>> of another good name :-(

> For the special schemas, we have pg_catalog, (pg_temp, pg_toast ?),
> so pg_public could do the trick.

Actually that was my initial choice of name, but I changed my mind
later.  The reason is that the dbadmin should be able to restrict or
even delete the public namespace if his usage plans for the database
don't allow any shared objects.  If we call it pg_public then the system
will think it is a reserved namespace, and we'd have to put in a special
case to allow it to be deleted (not to mention recreated again, should
the DBA change his mind later).
The public namespace isn't really special and so it should not be named
like a system-reserved namespace.  IMHO anyway...

			regards, tom lane

Thu, 04 Apr 2002 15:28:50 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: What's the CURRENT schema ? 
Tom Lane wrote:
> 
> Fernando Nasser <fnasser@redhat.com> writes:
> > Tom Lane wrote:
> >> I've been vacillating about whether to choose another name for the
> >> public namespace to avoid the need for quotes here.  I can't think
> >> of another good name :-(
> 
> > For the special schemas, we have pg_catalog, (pg_temp, pg_toast ?),
> > so pg_public could do the trick.
> 
> Actually that was my initial choice of name, but I changed my mind
> later.  The reason is that the dbadmin should be able to restrict or
> even delete the public namespace if his usage plans for the database
> don't allow any shared objects. 

Can't we prevent creation in there by (un)setting permissions?

> If we call it pg_public then the system
> will think it is a reserved namespace, and we'd have to put in a special
> case to allow it to be deleted (not to mention recreated again, should
> the DBA change his mind later).

If we can disallow creation with permissions, then we could always keep
it.
There should be a more practical way of making it empty than having to
drop
each object individually (DROP will drop the contents but refuse to
delete
the schema itself as it is a pg_ one?).


-- 
Fernando Nasser
Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9

Thu, 04 Apr 2002 15:35:49 -0500
Fernando Nasser <fnasser@redhat.com>
Re: What's the CURRENT schema ?
Fernando Nasser <fnasser@redhat.com> writes:
> Tom Lane wrote:
>> Actually that was my initial choice of name, but I changed my mind
>> later.  The reason is that the dbadmin should be able to restrict or
>> even delete the public namespace if his usage plans for the database
>> don't allow any shared objects. 

> Can't we prevent creation in there by (un)setting permissions?

That was what I was referring to by "restrict" ... but ISTM we should
allow dropping the namespace too.  Why waste cycles searching it if
you don't want to use it?

> There should be a more practical way of making it empty than having to
> drop
> each object individually (DROP will drop the contents but refuse to
> delete
> the schema itself as it is a pg_ one?).

I'd expect DROP on a reserved namespace to error out, and thus do
nothing at all.

			regards, tom lane

Thu, 04 Apr 2002 15:45:04 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: What's the CURRENT schema ? 
Tom Lane wrote:
> 
> Fernando Nasser <fnasser@redhat.com> writes:
> > Tom Lane wrote:
> >> Actually that was my initial choice of name, but I changed my mind
> >> later.  The reason is that the dbadmin should be able to restrict or
> >> even delete the public namespace if his usage plans for the database
> >> don't allow any shared objects.
> 
> > Can't we prevent creation in there by (un)setting permissions?
> 
> That was what I was referring to by "restrict" ... but ISTM we should
> allow dropping the namespace too.  Why waste cycles searching it if
> you don't want to use it?
> 

I don't know how the search will be implemented, but it should cost
very few instructions (one isnt checks that a list head is zero and
another gets the next pointer for the next namespace).  And, as we now 
transform things and keep them as Oids, it will be even cheaper.


> > There should be a more practical way of making it empty than having to
> > drop
> > each object individually (DROP will drop the contents but refuse to
> > delete
> > the schema itself as it is a pg_ one?).
> 
> I'd expect DROP on a reserved namespace to error out, and thus do
> nothing at all.
> 

But we could have:

DROP SCHEMA pg_public CONTENTS;

or something of a sort (an extension, but a public schema is an
extension).
And this sintax can come handy for DBAs in general.

-- 
Fernando Nasser
Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9

Thu, 04 Apr 2002 16:07:35 -0500
Fernando Nasser <fnasser@redhat.com>
Re: What's the CURRENT schema ?
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> 
> Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> > I see. However my main problem is that the schema of unqualified
> > vs1 is affected by the existence of yamada.vs1. I don't think
> > it's a useful behavior.
> 
> Well, if you don't like it, you could set the search_path to be just
> public,

Yes I don't like it and probably I would do it for myself but
I couldn't force other people to do so. Well for example, 
how could psqlodbc driver know the CURRENT schema ?

> or public and then the user's personal namespace.

The order isn't the problem at all. Would the *public*
be the CURRENT schema then ? If I recognize correctly,
neither is the CURRENT schema in the current spec.

regards,
Hiroshi Inoue

Fri, 5 Apr 2002 06:49:56 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
Re: What's the CURRENT schema ? 
"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> Well for example, 
> how could psqlodbc driver know the CURRENT schema ?

What "CURRENT" schema?  If you have a search path more than one entry
long, there is no unique notion of a CURRENT schema.

			regards, tom lane

Thu, 04 Apr 2002 17:34:49 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: What's the CURRENT schema ? 
Tom Lane wrote:
> 
> "Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> > Well for example,
> > how could psqlodbc driver know the CURRENT schema ?
> 
> What "CURRENT" schema?  If you have a search path more than one entry
> long, there is no unique notion of a CURRENT schema.

Oh I see but I think using the search SCHEMA path for
table name resolution is harmful.

regards,
Hiroshi Inoue

Fri, 05 Apr 2002 08:40:51 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: What's the CURRENT schema ?
Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> Oh I see but I think using the search SCHEMA path for
> table name resolution is harmful.

Huh?  That's more or less the entire *point* of these changes, IMHO.
What's harmful about having a search path?

			regards, tom lane

Thu, 04 Apr 2002 19:47:16 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: What's the CURRENT schema ? 
Tom Lane wrote:
> 
> Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> > Oh I see but I think using the search SCHEMA path for
> > table name resolution is harmful.
> 
> Huh?  That's more or less the entire *point* of these changes, IMHO.
> What's harmful about having a search path?

I don't object to use a search path to resolve unqualified
function, type etc names. But it is very siginificant for
users to be able to be sure what tables they are handling.
Where's the necessity to use a common search path to resolve
table and other objects' name in the first place ? I don't
know any OS commands which use the command search path to
resolve ordinary file name.

We(at least I)'ve been often confused and damaged even when
using OS's command search path. Does the flexibilty worth
the risk ? The damage would be immeasurable if unexpected
tables are chosen. Would PostgreSQL be a dbms unavailable
for careless users like me ?

regards,
Hiroshi Inoue

Fri, 05 Apr 2002 11:03:01 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: What's the CURRENT schema ?
Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> I don't object to use a search path to resolve unqualified
> function, type etc names. But it is very siginificant for
> users to be able to be sure what tables they are handling.

I really don't buy this argument; it seems exactly comparable to
arguing that the notion of current directory in Unix is evil, and
that users should be forced to specify absolute paths to every
file that they reference.

There is nothing to stop you from writing qualified names (schema.table)
if you are concerned about being sure that you get the table you intend.
In practice, however, people seem to prefer relative pathnames in most
Unix commands, and I think they'll prefer unqualified names in SQL
commands as well.

> Where's the necessity to use a common search path to resolve
> table and other objects' name in the first place ? I don't
> know any OS commands which use the command search path to
> resolve ordinary file name.

I think that's because of security concerns.  I would not object to
having separate search paths for functions/operators and for
tables/datatypes, though, if that would make you happier.

			regards, tom lane

Thu, 04 Apr 2002 21:21:07 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: What's the CURRENT schema ? 
> I really don't buy this argument; it seems exactly comparable to
> arguing that the notion of current directory in Unix is evil, and
> that users should be forced to specify absolute paths to every
> file that they reference.

You know, I'm kinda surprised that the spec doesn't define a CURRENT_SCHEMA
variable you can query???

Chris


Fri, 5 Apr 2002 10:46:17 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: What's the CURRENT schema ? 
Tom Lane wrote:
> 
> Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> > I don't object to use a search path to resolve unqualified
> > function, type etc names. But it is very siginificant for
> > users to be able to be sure what tables they are handling.
> 
> I really don't buy this argument; it seems exactly comparable to
> arguing that the notion of current directory in Unix is evil, and
> that users should be forced to specify absolute paths to every
> file that they reference.
> 
> There is nothing to stop you from writing qualified names (schema.table)
> if you are concerned about being sure that you get the table you intend.

Probably I can do it in many cases but I couldn't force others
to do it. I don't object if PostgreSQL doesn't allow unqualified 
table name other than in public/temporary/catalog schema.
There's no ambiguity and there's no need for the CURRENT schema.

BTW where's the description in SQL standard about the use
of SCHEMA path list to resolve unqualified table name ?
Is it a PostgreSQL's enhancement(extension) ?
As I already mentioned before, SQL-path isn't used to resolve
unqalified table name.

regards,
Hiroshi Inoue

Fri, 05 Apr 2002 12:08:04 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: What's the CURRENT schema ?
Christopher Kings-Lynne wrote:
> 
> > I really don't buy this argument; it seems exactly comparable to
> > arguing that the notion of current directory in Unix is evil, and
> > that users should be forced to specify absolute paths to every
> > file that they reference.
> 
> You know, I'm kinda surprised that the spec doesn't define a CURRENT_SCHEMA
> variable you can query???
> 

Maybe because it would be the same as CURRENT_USER.

For the standard, the schema name used (implied) to qualify objects
outside
a CREATE SCHEMA statement is a schema name with the SQL-session user id.
Except for functions and UDTs where each schema has a SQL-path for 
searching those (the implied schema must always be in it though).
There must be an implementation-defined default for this SQL-path
(but the implied schema must also be in it).



-- 
Fernando Nasser
Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9

Fri, 05 Apr 2002 11:39:55 -0500
Fernando Nasser <fnasser@redhat.com>
Re: What's the CURRENT schema ?
Hiroshi Inoue wrote:
> 
> Tom Lane wrote:
> >
> > Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> > > I don't object to use a search path to resolve unqualified
> > > function, type etc names. But it is very siginificant for
> > > users to be able to be sure what tables they are handling.
> >
> > I really don't buy this argument; it seems exactly comparable to
> > arguing that the notion of current directory in Unix is evil, and
> > that users should be forced to specify absolute paths to every
> > file that they reference.
> >
> > There is nothing to stop you from writing qualified names (schema.table)
> > if you are concerned about being sure that you get the table you intend.
> 
> Probably I can do it in many cases but I couldn't force others
> to do it. I don't object if PostgreSQL doesn't allow unqualified
> table name other than in public/temporary/catalog schema.
> There's no ambiguity and there's no need for the CURRENT schema.
> 

We can't do that.  Accordingly to the SQL if you are user HIROSHI
and write   "SELECT * FROM a;" the  table is actually "HIROSHI.a".

This must work for people who are using SQL-schemas in their databases
or we would have a non-conforming implementation of SCHEMAS (would make
the whole exercise pointless IMO).

The path proposed by Tom (discussed in the list some time ago) actually
does magic:

1) It allows SQL_schema compliant code and database to work as the 
standard expects;

2) It allows backward compatibility as someone will be able to use the
same schema-unaware code and create their databases without schemas as
before.

3) If the DBA is careful enough, she/he can convert his/her database to
use schemas incrementally.



-- 
Fernando Nasser
Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9

Fri, 05 Apr 2002 11:49:29 -0500
Fernando Nasser <fnasser@redhat.com>
Re: What's the CURRENT schema ?
Fernando Nasser <fnasser@redhat.com> writes:
> Christopher Kings-Lynne wrote:
>> You know, I'm kinda surprised that the spec doesn't define a CURRENT_SCHEMA
>> variable you can query???

> Maybe because it would be the same as CURRENT_USER.

It'd probably make sense for us to have one, though, given that I'm
intent on not hardwiring the two concepts together as the spec does ;-).
Although you can interrogate the search path with SHOW, that's not very
accessible at the SQL level, so an SQL function seems useful too.

I'd be inclined to make CURRENT_SCHEMA return the name of the schema
that is the default creation target namespace (viz, the front of the
search path).  Thoughts?

			regards, tom lane

Fri, 05 Apr 2002 11:55:17 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: What's the CURRENT schema ? 
> -----Original Message-----
> From: Fernando Nasser
> 
> Hiroshi Inoue wrote:
> > 
> > Tom Lane wrote:
> > >
> > > Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> > > > I don't object to use a search path to resolve unqualified
> > > > function, type etc names. But it is very siginificant for
> > > > users to be able to be sure what tables they are handling.
> > >
> > > I really don't buy this argument; it seems exactly comparable to
> > > arguing that the notion of current directory in Unix is evil, and
> > > that users should be forced to specify absolute paths to every
> > > file that they reference.
> > >
> > > There is nothing to stop you from writing qualified names 
> (schema.table)
> > > if you are concerned about being sure that you get the table 
> you intend.
> > 
> > Probably I can do it in many cases but I couldn't force others
> > to do it. I don't object if PostgreSQL doesn't allow unqualified
> > table name other than in public/temporary/catalog schema.
> > There's no ambiguity and there's no need for the CURRENT schema.
> > 
> 
> We can't do that.  Accordingly to the SQL if you are user HIROSHI
> and write   "SELECT * FROM a;" the  table is actually "HIROSHI.a".
> 
> This must work for people who are using SQL-schemas in their databases
> or we would have a non-conforming implementation of SCHEMAS (would make
> the whole exercise pointless IMO).

Schema name isn't necessarily a user id since SQL-92
though SQL-86 and SQL-89 had and probably Oracle still
has the limitation. As far as I see PostgreSQL's schema
support wouldn't have the limitation. Probably I wouldn't
create the schema HIROSHI using PostgreSQL. When
I used Oracle I really disliked the limitation.

> The path proposed by Tom (discussed in the list some time ago) actually
> does magic:

The seems a misuse of SQL-path to me.
If I restrict the path to temporary:CURRENT schema:catalog, I would be
able to use the CURRENT schema and I can see no other useful way in 
unqualified table name resolution. Probably I would also be able to use the
path as SQL-path. But how can I use the path in both style simultaneously ?

regards,
Hiroshi Inoue 

Sat, 6 Apr 2002 07:58:00 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
Re: What's the CURRENT schema ?
Hiroshi Inoue wrote:
> 
> > We can't do that.  Accordingly to the SQL if you are user HIROSHI
> > and write   "SELECT * FROM a;" the  table is actually "HIROSHI.a".
> >
> > This must work for people who are using SQL-schemas in their databases
> > or we would have a non-conforming implementation of SCHEMAS (would make
> > the whole exercise pointless IMO).
> 
> Schema name isn't necessarily a user id since SQL-92
> though SQL-86 and SQL-89 had and probably Oracle still
> has the limitation. As far as I see PostgreSQL's schema
> support wouldn't have the limitation. Probably I wouldn't
> create the schema HIROSHI using PostgreSQL. When
> I used Oracle I really disliked the limitation.
> 

You misunderstood what I've said.  You may have how many schemas
you please.  But you will have to refer to their objects specifying
the schema name explicitly.  The only cases where you can omit the
schema name are (accordingly to the SQL'99 standard):

1) The statement is part of a CREATE SCHEMA statement that is
creating the object, so the schema being created is assumed
(and that is what you want).

2) Your schema has the same name as your user id, your statement
is not inside a CREATE SCHEMA and it runs on a session with that 
authorization id.  A schema name equal to the sessuin user id is
assumed (which is what you want in this specific case).  

Otherwise you have to specify the schema explicitly.

So, if you name your schema "APPLE", and not HIROSHI, except 
for inside the CREATE SCHEMA APPLE statement elements, you will
have to keep refering to tables with the "APPLE." prefix.


PostgreSQL will be smarter and try to relax 2) for you, looking
for the table in a public schema as well (if one exists), so old
style (non-schema) databases can still be used and people who have
schemas with names that are not their user id can save some typing. ;-)

-- 
Fernando Nasser
Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9

Fri, 05 Apr 2002 19:08:02 -0500
Fernando Nasser <fnasser@redhat.com>
Re: What's the CURRENT schema ?
Fernando Nasser writes:

> I does not _have_ to be "public", so we can just avoid the issue
> by adding a pg_ prefix to public, common or something else.
> It is a PostgreSQL concept anyway.

No, it's an Oracle concept.

-- 
Peter Eisentraut   peter_e@gmx.net


Fri, 5 Apr 2002 21:48:01 -0500 (EST)
Peter Eisentraut <peter_e@gmx.net>
Re: What's the CURRENT schema ?
> -----Original Message-----
> From: Fernando Nasser
>
> Hiroshi Inoue wrote:
> >
> > > We can't do that.  Accordingly to the SQL if you are user HIROSHI
> > > and write   "SELECT * FROM a;" the  table is actually "HIROSHI.a".
> > >
> > > This must work for people who are using SQL-schemas in their databases
> > > or we would have a non-conforming implementation of SCHEMAS
> (would make
> > > the whole exercise pointless IMO).
> >
> > Schema name isn't necessarily a user id since SQL-92
> > though SQL-86 and SQL-89 had and probably Oracle still
> > has the limitation. As far as I see PostgreSQL's schema
> > support wouldn't have the limitation. Probably I wouldn't
> > create the schema HIROSHI using PostgreSQL. When
> > I used Oracle I really disliked the limitation.
> >
>
> You misunderstood what I've said.  You may have how many schemas
> you please.  But you will have to refer to their objects specifying
> the schema name explicitly.  The only cases where you can omit the
> schema name are (accordingly to the SQL'99 standard):

Please tell me where's the description in SQL99 ?
I wasn't able to find it unfortunately.

regards,
Hiroshi Inoue


Sat, 6 Apr 2002 18:14:45 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
Re: What's the CURRENT schema ?
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
>
> Fernando Nasser <fnasser@redhat.com> writes:
> > Christopher Kings-Lynne wrote:
> >> You know, I'm kinda surprised that the spec doesn't define a
> CURRENT_SCHEMA
> >> variable you can query???
>
> > Maybe because it would be the same as CURRENT_USER.
>
> It'd probably make sense for us to have one, though, given that I'm
> intent on not hardwiring the two concepts together as the spec does ;-).
> Although you can interrogate the search path with SHOW, that's not very
> accessible at the SQL level, so an SQL function seems useful too.
>
> I'd be inclined to make CURRENT_SCHEMA return the name of the schema
> that is the default creation target namespace (viz, the front of the
> search path).  Thoughts?

I think only one schema other than TEMP or catalog is allowed in the search
path for the resolution of table name. I can call the schema the
CURRENT_SCHEMA.
If the restricted search path is inappropriate for the resolution of
function, type etc
name, you have to provide another path IMHO.

BTW every time I examined SQL99, I can find neither the description
CURRENT_SCHEMA == CURRENT_USER nor the one that the schema
name of an unqualified table name may vary according to the table name.
Probably it's because of  my poor English. I'm happy if you could tell me
where to find it.

regards,
Hiroshi Inoue


Sun, 7 Apr 2002 20:44:55 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
Re: What's the CURRENT schema ? 
Hiroshi Inoue wrote:
> 
> > You misunderstood what I've said.  You may have how many schemas
> > you please.  But you will have to refer to their objects specifying
> > the schema name explicitly.  The only cases where you can omit the
> > schema name are (accordingly to the SQL'99 standard):
> 
> Please tell me where's the description in SQL99 ?
> I wasn't able to find it unfortunately.
> 

As most things in the SQL standard, you have to collect information
from several places and add it together.

Look at 4.20, 11.1 and specially at the rules for
<schema qualified name>.

Then think a little bit about scenarios, trying to apply the rules.

It is a pain, but there is no other way.


-- 
Fernando Nasser
Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9

Mon, 08 Apr 2002 12:09:21 -0400
Fernando Nasser <fnasser@redhat.com>
Re: What's the CURRENT schema ?
> -----Original Message-----
> From: Fernando Nasser
>
> Hiroshi Inoue wrote:
> >
> > > You misunderstood what I've said.  You may have how many schemas
> > > you please.  But you will have to refer to their objects specifying
> > > the schema name explicitly.  The only cases where you can omit the
> > > schema name are (accordingly to the SQL'99 standard):
> >
> > Please tell me where's the description in SQL99 ?
> > I wasn't able to find it unfortunately.
> >
>
> As most things in the SQL standard, you have to collect information
> from several places and add it together.
>
> Look at 4.20, 11.1 and specially at the rules for
> <schema qualified name>.

OK I can see at 4.20.
  If a reference to a <table name> does not explicitly contain a <schema
name>,
  then a specific <schema name> is implied. The particular <schema name>
  associated with such a <table name> depends on the context in which the
  <table name> appears and is governed by the rules for <schema qualified
name>.

Unfortunately I can't find what to see at 11.1. Please tell me where to see.

However I can see the following at 5.4 Names and Identifiers
11)   If a <schema qualified name> does not contain a <schema name>, then
       Case:
       a) If the <schema qualified name> is contained in a <schema
definition>,
       then the <schema name> that is specified or implicit in the <schema
definition>
      is implicit.
       b) Otherwise, the <schema name> that is specified or implicit for the
       <SQL-client module definition> is implicit.

regards,
Hiroshi Inoue


Tue, 9 Apr 2002 02:17:54 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
Re: What's the CURRENT schema ?
"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> However I can see the following at 5.4 Names and Identifiers
> 11)   If a <schema qualified name> does not contain a <schema name>, then
>        Case:
>        a) If the <schema qualified name> is contained in a <schema
> definition>,
>        then the <schema name> that is specified or implicit in the <schema
> definition>
>       is implicit.

Yes.  Fernando, our existing CREATE SCHEMA command does not get this
right for references from views to tables, does it?  It seems to me that
to get compliant behavior, we'll need to temporarily push the new schema
onto the front of the namespace search path while parsing view
definitions inside CREATE SCHEMA.

(The relevance to the current discussion is that this is easy to do if
SET variables roll back on error ... but it might be tricky if they do
not.)

			regards, tom lane

Mon, 08 Apr 2002 13:41:31 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: What's the CURRENT schema ? 
Tom Lane wrote:
> 
> "Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> > However I can see the following at 5.4 Names and Identifiers
> > 11)   If a <schema qualified name> does not contain a <schema name>, then
> >        Case:
> >        a) If the <schema qualified name> is contained in a <schema
> > definition>,
> >        then the <schema name> that is specified or implicit in the <schema
> > definition>
> >       is implicit.
> 
> Yes.  Fernando, our existing CREATE SCHEMA command does not get this
> right for references from views to tables, does it?  It seems to me that
> to get compliant behavior, we'll need to temporarily push the new schema
> onto the front of the namespace search path while parsing view
> definitions inside CREATE SCHEMA.
> 

Correct.  It only takes care of proper setting/checking the schema name
for the view (as is done for tables) that are being created.  Doing as
you suggest would be nice (similar to what we do with the authid).

BTW, I think have to properly fill/check the schema when the grant
objects
are tables/views (I am not sure how functions will be handled).
I will send a patch in later today or tomorrow, unless you want to do
it differently.  I prefer to do in in the parser because I can issue
and error if a grant is for something that is not an object in the
schema being created.


Regards,
Fernando

-- 
Fernando Nasser
Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9

Mon, 08 Apr 2002 20:35:54 -0400
Fernando Nasser <fnasser@redhat.com>
Re: What's the CURRENT schema ?
Fernando Nasser wrote:
> 
> As most things in the SQL standard, you have to collect information
> from several places and add it together.
> 
> Look at 4.20, 11.1 and specially at the rules for
> <schema qualified name>.
> 
> Then think a little bit about scenarios, trying to apply the rules.
> 
> It is a pain, but there is no other way.

I couldn't find the description CURRENT_SCHEMA == CURRENT_USER.
If I recognize SQL99 correctly, the CURRENT schema is the schema
defined in a <SQL-client module> not restricted to the CURRENT
user.

Well here's my proposal.
1) Use the different search path for table name and
   others.
2) Allow only one schema other than temp or catalog in
   the table name search path so that we can call it
   the CURRENT schema.

Comments ?

regards,
Hiroshi Inoue

Wed, 10 Apr 2002 18:41:22 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: What's the CURRENT schema ?
Hiroshi Inoue wrote:
> 
> Fernando Nasser wrote:
> >
> > As most things in the SQL standard, you have to collect information
> > from several places and add it together.
> >
> > Look at 4.20, 11.1 and specially at the rules for
> > <schema qualified name>.
> >
> > Then think a little bit about scenarios, trying to apply the rules.
> >
> > It is a pain, but there is no other way.
> 
> I couldn't find the description CURRENT_SCHEMA == CURRENT_USER.
> If I recognize SQL99 correctly, the CURRENT schema is the schema
> defined in a <SQL-client module> not restricted to the CURRENT
> user.
> 

Yes, but we don't have a "module" language.  You have to look for
"session".


-- 
Fernando Nasser
Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9

Wed, 10 Apr 2002 09:27:05 -0400
Fernando Nasser <fnasser@redhat.com>
Re: What's the CURRENT schema ?
Fernando Nasser wrote:
> 
> Hiroshi Inoue wrote:
> >
> > Fernando Nasser wrote:
> > >
> > > As most things in the SQL standard, you have to collect information
> > > from several places and add it together.
> > >
> > > Look at 4.20, 11.1 and specially at the rules for
> > > <schema qualified name>.
> > >
> > > Then think a little bit about scenarios, trying to apply the rules.
> > >
> > > It is a pain, but there is no other way.
> >
> > I couldn't find the description CURRENT_SCHEMA == CURRENT_USER.
> > If I recognize SQL99 correctly, the CURRENT schema is the schema
> > defined in a <SQL-client module> not restricted to the CURRENT
> > user.
> >
> 
> Yes,

OK I wasn't wrong at this point.

> but we don't have a "module" language.  You have to look for
> "session".

Do you mean PostgreSQL by the *we* ?
We have never been and would never be completely in
conformity to standard. If we don't have a "module"
language, does it mean we couldn't have any subsitute
for <SQL-client module> ?

regards,
Hiroshi Inoue

Thu, 11 Apr 2002 15:13:01 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: What's the CURRENT schema ?
Hi all,

I see there's a TODO item for large object security, it's a feature I'd really like to see.  I'm willing to put in the time to write a patch, but know far to little about postgres internals and history to just dive in.  Has there been any discussion on this list about what this feature should be or how it might be implemented?  I saw a passing reference to "LOB LOCATORs" in the list archives, but that was all.

What's a LOB LOCATOR ? 

What about giving each large object its own permission flags? ex:

GRANT SELECT ON LARGE OBJECT 10291 TO USER webapp;
GRANT SELECT, DELETE, UPDATE ON LARGE OBJECT 10291 TO USER admin;

Default permission flags (and INSERT permissions) would be set at the table level.  All objects without specific permissions would use the table rules.  This allows for backward compatibility and convenience.

I think per-object security is important.  A user shouldn't be able to get at another user's data just by guessing the right OID.  Ideally, users without permission would not know there were objects in the database they were not allowed to see.

I can also imagine a security scheme that uses rule/trigger syntax to give the user a hook to provide her own security functions.  I haven't thought that through, though.

Any thoughts?


-Damon

Fri, 19 Apr 2002 02:03:38 -0700
Damon Cokenias <lists@mtn-palace.com>
Large object security