Re: WIP: Enhanced ALTER OPERATOR

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?

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company