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)


Wed, 20 Dec 2000 16:04:17 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Bug in CREATE OPERATOR
>
> 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


Wed, 20 Dec 2000 12:40:18 -0500
"Darren King" <darrenk@insightdist.com>
RE: Bug in CREATE OPERATOR
"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

Wed, 20 Dec 2000 12:51:58 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Bug in CREATE OPERATOR 