After some fooling around with gram.y, I have come to the conclusion
that there's just no way to use a schema-qualified name for an operator
in an expression.  I was hoping we might be able to write something like
	operand1 schema.+ operand2
but I can't find any way to make this work without tons of shift/reduce
conflicts.  One counterexample suggesting it can't be done is that
	foo.*
might be either a reference to all the columns of foo, or a qualified
operator name.

We can still put operators into namespaces and allow qualified names in
CREATE/DROP OPERATOR.  However, lookup of operators in expressions would
have to be completely dependent on the search path.  That's not real
cool; among other things, pg_dump couldn't guarantee that dumped
expressions would be interpreted the same way when reloaded.

Things we might do to reduce the uncertainty:

1. Keep operators as database-wide objects, instead of putting them into
namespaces.  This seems a bit silly though: if the types and functions
that underlie an operator are private to a namespace, shouldn't the
operator be as well?

2. Use a restricted, perhaps fixed search-path for searching for
operators.  For example, we might force the search path to have
pg_catalog first even when this is not true for the table name search
path.  But I'm not sure what an appropriate definition would be.
A restricted search path might limit the usefulness of private operators
to the point where we might as well have kept them database-wide.

Comments anyone?  I'm really unsure what's the best way to proceed.

			regards, tom lane

Mon, 15 Apr 2002 12:43:57 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Operators and schemas
Tom Lane wrote:
> 
> 1. Keep operators as database-wide objects, instead of putting them into
> namespaces.  This seems a bit silly though: if the types and functions
> that underlie an operator are private to a namespace, shouldn't the
> operator be as well?
> 

Not necessarily.  One can still create a type and functions to operate 
on them.  Operators are a convenience, not a necessity (except for 
indices extensions).

If some types are really important and operators are desired, it can be
coordinated with the DBA as operators would be a database wide resource.
(This would be the case if indices extensions were involved anyway).

I would keep operators database-wide.  

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

Mon, 15 Apr 2002 14:28:20 -0400
Fernando Nasser <fnasser@redhat.com>
Re: Operators and schemas
Fernando Nasser <fnasser@redhat.com> writes:
> If some types are really important and operators are desired, it can be
> coordinated with the DBA as operators would be a database wide resource.
> (This would be the case if indices extensions were involved anyway).

No, there isn't any particular reason that index extensions should be
considered database-wide resources; if operators are named local to
schemas, then opclasses can be too, and that's all you need.

In practice maybe it doesn't matter; I doubt anyone would try to
implement an indexable datatype in anything but C, and to define
C functions you must be superuser anyway.  But this does not seem
to me to be a good argument why operator names should be global.

			regards, tom lane

Mon, 15 Apr 2002 14:40:04 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Operators and schemas 
> 2. Use a restricted, perhaps fixed search-path for searching for
> operators.  For example, we might force the search path to have
> pg_catalog first even when this is not true for the table name
search
> path.  But I'm not sure what an appropriate definition would be.
> A restricted search path might limit the usefulness of private
operators
> to the point where we might as well have kept them database-wide.

Wanting to open a bucket of worms, what would making the system create
an operator with the schema name in it?

Ie.  Create operator schema.+ would create:

'schema.+'  in pg_catalog
'+' in schema

This would require double the operator entries, but isn't really any
worse than the array types as related to their base type.

So, user could type:
select col1 + col2 from schema.tab;
select col1 schema.+ col2 from tab;



Mon, 15 Apr 2002 15:51:48 -0400
"Rod Taylor" <rbt@zort.ca>
Re: Operators and schemas
Tom Lane writes:

> After some fooling around with gram.y, I have come to the conclusion
> that there's just no way to use a schema-qualified name for an operator
> in an expression.  I was hoping we might be able to write something like
> 	operand1 schema.+ operand2
> but I can't find any way to make this work without tons of shift/reduce
> conflicts.  One counterexample suggesting it can't be done is that
> 	foo.*
> might be either a reference to all the columns of foo, or a qualified
> operator name.

What about foo."*"?

> We can still put operators into namespaces and allow qualified names in
> CREATE/DROP OPERATOR.  However, lookup of operators in expressions would
> have to be completely dependent on the search path.  That's not real
> cool; among other things, pg_dump couldn't guarantee that dumped
> expressions would be interpreted the same way when reloaded.

We could make some sort of escape syntax, like

    op1 myschema.operator(+) op2

-- 
Peter Eisentraut   peter_e@gmx.net


Mon, 15 Apr 2002 15:54:03 -0400 (EDT)
Peter Eisentraut <peter_e@gmx.net>
Re: Operators and schemas
Peter Eisentraut <peter_e@gmx.net> writes:
> We could make some sort of escape syntax, like

>     op1 myschema.operator(+) op2

I thought a little bit about that ... the above syntax does not work
but it looks like we could do something along the lines of

	op1 OPERATOR(myschema.+) op2

where OPERATOR has to become a fully reserved keyword.

But: do you really want to see all dumped rules, defaults, etc in that
style?  Ugh... talk about loss of readability...

			regards, tom lane

Mon, 15 Apr 2002 16:31:02 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Operators and schemas 
Tom Lane writes:

> But: do you really want to see all dumped rules, defaults, etc in that
> style?  Ugh... talk about loss of readability...

I imagine that pg_dump could be able to figure out that certain references
would be "local", so no explicit schema qualification is necessary.
Thus, the only weird-looking operator invocations would be those that were
also created in weird ways.  In general, pg_dump should try to avoid
making unnecessary schema qualifications on any object so that you can
edit the dump and only change the schema name in one place.

-- 
Peter Eisentraut   peter_e@gmx.net


Mon, 15 Apr 2002 17:17:37 -0400 (EDT)
Peter Eisentraut <peter_e@gmx.net>
Re: Operators and schemas 
Peter Eisentraut <peter_e@gmx.net> writes:
> I imagine that pg_dump could be able to figure out that certain references
> would be "local", so no explicit schema qualification is necessary.

Well, if it makes assumptions about the path then it can do that ... or
I guess it could explicitly set the path, and then it knows.  Yeah, that
will probably work well enough.  Okay, good ... the question of what
pg_dump should do about qualifying names was bugging me.

What I'm now envisioning is that pg_dump will explicitly set
	set search_path = 'foo';
when dumping or reloading schema foo.  Given the present semantics of
search_path, that will imply an implicit search of pg_catalog before
foo.  Therefore, we have the following ground rules for schema
qualification in pg_dump:
	* System (pg_catalog) names never need qualification.
	* Names in the current schema need be qualified only if they
	  conflict with system names.
	* Cross-references to other schemas will always be qualified.

This seems workable.  Thoughts?

			regards, tom lane

Mon, 15 Apr 2002 17:55:33 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Operators and schemas 
Tom Lane wrote:
> 
> Peter Eisentraut <peter_e@gmx.net> writes:
> > I imagine that pg_dump could be able to figure out that certain references
> > would be "local", so no explicit schema qualification is necessary.

[snip]

>         * Names in the current schema need be qualified only if they

What does the current schema mean ?
Or What does "local" mean ?

regards,
Hiroshi Inoue

Tue, 16 Apr 2002 09:55:29 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: Operators and schemas
Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> Tom Lane wrote:
>> * Names in the current schema need be qualified only if they

> What does the current schema mean ?

In this case, it means the one pg_dump is trying to dump.

			regards, tom lane

Mon, 15 Apr 2002 20:57:38 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Operators and schemas 
Tom Lane writes:

> What I'm now envisioning is that pg_dump will explicitly set
> 	set search_path = 'foo';
> when dumping or reloading schema foo.

I had imagined that pg_dump would emit commands such as this:

CREATE SCHEMA foo
  CREATE TABLE bar ( ... )
  CREATE otherthings
;

which is how I read the SQL standard.  Are there plans to implement the
CREATE SCHEMA command that way?  I think I recall someone from Toronto
mentioning something along these lines.

Obviously, this command style would be mostly equivalent to temporarily
setting the search path.  We'd also need alter schema, which SQL doesn't
have.

> Given the present semantics of
> search_path, that will imply an implicit search of pg_catalog before
> foo.

Interesting ... Is that only temporary?  (since you say "present"
semantics)

-- 
Peter Eisentraut   peter_e@gmx.net


Mon, 15 Apr 2002 22:02:10 -0400 (EDT)
Peter Eisentraut <peter_e@gmx.net>
Re: Operators and schemas 
Peter Eisentraut <peter_e@gmx.net> writes:
> I had imagined that pg_dump would emit commands such as this:

> CREATE SCHEMA foo
>   CREATE TABLE bar ( ... )
>   CREATE otherthings
> ;

> which is how I read the SQL standard.  Are there plans to implement the
> CREATE SCHEMA command that way?  I think I recall someone from Toronto
> mentioning something along these lines.

We have portions of that now, but I don't think there is any serious
intent to support *all* Postgres CREATE statements inside CREATE SCHEMA.
Because there are no semicolons in there, allowing random statements in
CREATE SCHEMA tends to force promotion of keywords to full-reserved
status (so you can tell where each sub-statement starts).  My
inclination is to allow the minimum necessary for SQL spec compliance.

(Fernando, your thoughts here?)
		
>> Given the present semantics of
>> search_path, that will imply an implicit search of pg_catalog before
>> foo.

> Interesting ... Is that only temporary?  (since you say "present"
> semantics)

Only meant to imply "it hasn't been seriously reviewed, so someone
might have a better idea".  At the moment I'm happy with it.

			regards, tom lane

Tue, 16 Apr 2002 00:21:37 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Operators and schemas 
Tom Lane wrote:
> 
> Peter Eisentraut <peter_e@gmx.net> writes:
> > I had imagined that pg_dump would emit commands such as this:
> 
> > CREATE SCHEMA foo
> >   CREATE TABLE bar ( ... )
> >   CREATE otherthings
> > ;
> 
> > which is how I read the SQL standard.  Are there plans to implement the
> > CREATE SCHEMA command that way?  I think I recall someone from Toronto
> > mentioning something along these lines.
> 
> We have portions of that now, but I don't think there is any serious
> intent to support *all* Postgres CREATE statements inside CREATE SCHEMA.
> Because there are no semicolons in there, allowing random statements in
> CREATE SCHEMA tends to force promotion of keywords to full-reserved
> status (so you can tell where each sub-statement starts).  My
> inclination is to allow the minimum necessary for SQL spec compliance.
> 
> (Fernando, your thoughts here?)
> 

I agree.   And for Entry level SQL'92 we are done -- only tables, views 
and grants are required.  The multiple schemas per user is already
an intermediate SQL feature -- for intermediate SQL'92 we would still 
need domains and a character set specification.

For SQL'99, we would have to add types, functions and triggers
(only triggers are not part of Core SQL'99, but I would not leave them out).

Regards,
Fernando



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

Tue, 16 Apr 2002 14:27:17 -0400
Fernando Nasser <fnasser@redhat.com>
Re: Operators and schemas
Fernando Nasser writes:

> I agree.   And for Entry level SQL'92 we are done -- only tables, views
> and grants are required.  The multiple schemas per user is already
> an intermediate SQL feature -- for intermediate SQL'92 we would still
> need domains and a character set specification.
>
> For SQL'99, we would have to add types, functions and triggers
> (only triggers are not part of Core SQL'99, but I would not leave them out).

I can hardly believe that we want to implement this just to be able to
check off a few boxes on the SQL-compliance test.  Once you have the
ability to use a fixed list of statements in this context it should be
easy to allow a more or less arbitrary list.  Especially if they all start
with the same key word it should be possible to parse this.

-- 
Peter Eisentraut   peter_e@gmx.net


Tue, 16 Apr 2002 18:33:10 -0400 (EDT)
Peter Eisentraut <peter_e@gmx.net>
Re: Operators and schemas
Peter Eisentraut <peter_e@gmx.net> writes:
> I can hardly believe that we want to implement this just to be able to
> check off a few boxes on the SQL-compliance test.  Once you have the
> ability to use a fixed list of statements in this context it should be
> easy to allow a more or less arbitrary list.  Especially if they all start
> with the same key word it should be possible to parse this.

It's not the "start" part that creates the problem, so much as the "end"
part.  What we found was that we were having to reserve secondary
keywords.  CREATE is now fully reserved, which it was not in 7.2,
and that alone doesn't bother me.  But AUTHORIZATION and GRANT are
more reserved than they were before, too, and it'll get worse the
more statements we insist on accepting inside CREATE SCHEMA.

AFAICS, embedding statements inside CREATE SCHEMA adds absolutely zero
functionality; you can just as easily execute them separately.  Do we
really want to push a bunch more keywords into full-reserved status
(and doubtless break some existing table definitions thereby) just
to check off a box that isn't even in the SQL compliance test?

To the extent that we can allow stuff in CREATE SCHEMA without adding
more reserved words, it's fine with me.  But I question having to add
reserved words to do it.

			regards, tom lane

Tue, 16 Apr 2002 18:58:22 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Operators and schemas 