Thread: Operators and schemas
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 likeoperand1 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 thatfoo.*
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
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
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
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
> 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;
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
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
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 setset 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
qualifiedonly if they conflict with system names.* Cross-references to other schemas will always be qualified.
This seems workable. Thoughts?
regards, tom lane
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
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
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
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
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
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
-----Original Message----- From: Peter Eisentraut [mailto:peter_e@gmx.net] Sent: Tuesday, April 16, 2002 3:33 PM To: Fernando Nasser Cc: Tom Lane; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] 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. >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Items like "schema" are a part of the language for a reason. Being able to create a schema in an area called 'test' and another in an area called 'development' and yet another in an area called 'production' is a key feature for real business usefulness. IMO-YMMV. <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
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
-----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Tuesday, April 16, 2002 3:58 PM To: Peter Eisentraut Cc: Fernando Nasser; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] 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. >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> If the alternative is to make a permanent fork in the road that leads away from ANSI compiliance, then it is a very, very bad decision not to put in the new keywords. Every week that passes by will make correcting the problem become more and more expensive. IMO-YMMV. <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<