Bug in CREATE OPERATOR
As part of our development, one of our programmers created a new database
type, plus operators and functions to go with it. One of the things he did
was this:
CREATE OPERATOR testbit (
leftarg = bitset,
rightarg = int4,
procedure = testbit,
commutator = testbit
);
Notice that this is an ILLEGAL type - the name of the type (from docs) must
only contain these characters:
+ - * / < > = ~ ! @ # % ^ & | ` ? $
However, PostgreSQL 7.0.3 went right ahead and created the operator anyway!!
Now we have a big problem, as the DROP OPERATOR command cannot delete the
illegally named operator.
eg:
usa=# drop operator testbit (bitset, int4);
ERROR: parser: parse error at or near "testbit "
usa=# drop operator 'testbit ' (bitset, int4);
ERROR: parser: parse error at or near "'"
usa=# drop operator "testbit " (bitset, int4);
ERROR: parser: parse error at or near """
We can't delete it!!! I also assume that it was a bug that it could even be
created in the first place...
Chris
--
Christopher Kings-Lynne
Family Health Network (ACN 089 639 243)
CREATE OPERATOR testbit (
leftarg = bitset,
rightarg = int4,
procedure = testbit,
commutator = testbit
);Now we have a big problem, as the DROP OPERATOR command
cannot delete the illegally named operator.
Have you tried deleting it directly from pg_operator instead of using
DROP OPERATOR?
Darren
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
[ "CREATE OPERATOR testbit" is accepted ]
Not only that, but it looks like you can create aggregate functions and
types that have operator-like names :-(. Someone was way too eager to
save a production or two, I think:
DefineStmt: CREATE def_type def_name definition
{
...
}
;
def_type: OPERATOR { $$ = OPERATOR; }
| TYPE_P { $$ = TYPE_P; }
| AGGREGATE { $$ = AGGREGATE; }
;
def_name: PROCEDURE { $$ = "procedure"; }
| JOIN { $$ = "join"; }
| all_Op { $$ = $1; }
| ColId { $$ = $1; }
;
Seems to me that this should be simplified down to
CREATE OPERATOR all_Op ...
CREATE TYPE ColId ...
CREATE AGGREGATE ColId ...
Any objections? Has anyone got an idea why PROCEDURE and JOIN are
special-cased here? PROCEDURE, at least, could be promoted from
ColLabel to ColId were it not offered as an alternative to ColId here.
Now we have a big problem, as the DROP OPERATOR command cannot delete the
illegally named operator.
Just remove it by DELETEing the row from pg_operator.
regards, tom lane