Re: WIP: Enhanced ALTER OPERATOR

On Fri, May 29, 2015 at 4:28 AM, Alexander Korotkov
<a(dot)korotkov(at)postgrespro(dot)ru> wrote:
> On Thu, May 28, 2015 at 6:43 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>
>> Alexander Korotkov <a(dot)korotkov(at)postgrespro(dot)ru> writes:
>> > Could we address both this problems by denying changing existing
>> > commutators and negator? ISTM that setting absent commutator and negator
>> > is
>> > quite enough for ALTER OPERATOR. User extensions could need setting of
>> > commutator and negator because they could add new operators which don't
>> > exist before. But it's rather uncommon to unset or change commutator or
>> > negator.
>>
>> Note that this functionality is already covered, in that you can specify
>> the commutator/negator linkage when you create the second operator.
>> I'm not particularly convinced that we need to have it in ALTER OPERATOR.
>
>
> Yeah, I didn't notice that CREATE OPERATOR sets commutator and negator on
> opposite side as well. Then we probably can leave ALTER OPERATOR without
> altering commutator and negator.
>
> BTW, does DROP OPERATOR works correctly?
>
> # create operator == (procedure = int8eq, leftarg = bigint, rightarg =
> bigint);
> CREATE OPERATOR
> # create operator !== (procedure = int8ne, leftarg = bigint, rightarg =
> bigint, negator = ==);
> CREATE OPERATOR
> # select oid, * from pg_operator where oprnamespace = (select oid from
> pg_namespace where nspname = 'public');
> oid | oprname | oprnamespace | oprowner | oprkind | oprcanmerge |
> oprcanhash | oprleft | oprright | oprresult | oprcom | oprnegate | oprcode |
> oprrest | oprjoin
> -------+---------+--------------+----------+---------+-------------+------------+---------+----------+-----------+--------+-----------+---------+---------+---------
> 46355 | !== | 2200 | 10 | b | f | f
> | 20 | 20 | 16 | 0 | 46354 | int8ne | - |
> -
> 46354 | == | 2200 | 10 | b | f | f
> | 20 | 20 | 16 | 0 | 46355 | int8eq | - |
> -
> (2 rows)
> # create table test (id bigint);
> CREATE TABLE
> # explain verbose select * from test where not id == 10::bigint;
> QUERY PLAN
> ---------------------------------------------------------------
> Seq Scan on public.test (cost=0.00..38.25 rows=1130 width=8)
> Output: id
> Filter: (test.id !== '10'::bigint)
> (3 rows)
> # drop operator !== (int8, int8);
> DROP OPERATOR
> # select oid, * from pg_operator where oprnamespace = (select oid from
> pg_namespace where nspname = 'public');
> oid | oprname | oprnamespace | oprowner | oprkind | oprcanmerge |
> oprcanhash | oprleft | oprright | oprresult | oprcom | oprnegate | oprcode |
> oprrest | oprjoin
> -------+---------+--------------+----------+---------+-------------+------------+---------+----------+-----------+--------+-----------+---------+---------+---------
> 46354 | == | 2200 | 10 | b | f | f
> | 20 | 20 | 16 | 0 | 46355 | int8eq | - |
> -
> (1 row)
> # explain verbose select * from test where not id == 10::bigint;
> ERROR: cache lookup failed for function 0
>
> DROP OPERATOR leaves broken reference in oprnegate. Should we fix it?

Yeah, that doesn't seem good.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company