Cleaning up cross-type arithmetic operators

There was a discussion back here:
http://archives.postgresql.org/pgsql-bugs/2008-01/msg00189.php
that came to the conclusion that cross-type operators are a bad idea
if they don't come in complete sets: if you don't have an exact match
to the input types, and there are multiple possible candidates, then
the system doesn't know what to pick.

I looked through pg_operator just now, and found that we seem to be okay
as far as comparison operators go, but we do have issues for basic
arithmetic operators. Specifically, these cross-type operators aren't
part of complete sets:

OID | OPERATOR

548 | % (smallint,integer)
549 | % (integer,smallint)

690 | * (bigint,integer)
544 | * (smallint,integer)
694 | * (integer,bigint)
545 | * (integer,smallint)

688 | + (bigint,integer)
552 | + (smallint,integer)
692 | + (integer,bigint)
553 | + (integer,smallint)

689 | - (bigint,integer)
556 | - (smallint,integer)
693 | - (integer,bigint)
557 | - (integer,smallint)

691 | / (bigint,integer)
546 | / (smallint,integer)
695 | / (integer,bigint)
547 | / (integer,smallint)

We could either remove all of these, or fill in the sets. Removal
would mean that cross-type cases would get implemented as a coercion
function feeding a single-data-type operator, which would be marginally
slower to execute (I'm not sure it would be significant).

What I'm inclined to do is remove the two % operators, which don't seem
likely to be performance-critical, and fill in the missing int2-vs-int8
cases for the four basic arithmetic operators. But I could be talked
into just nuking everything listed above (and their underlying functions
of course).

Comments?

regards, tom lane

On Tue, Jun 17, 2008 at 01:29:56PM -0400, Tom Lane wrote:
> ...
> What I'm inclined to do is remove the two % operators, which don't seem
> likely to be performance-critical, and fill in the missing int2-vs-int8
> cases for the four basic arithmetic operators. But I could be talked
> into just nuking everything listed above (and their underlying functions
> of course).
>
> Comments?
>
> regards, tom lane
>
+1 for getting rid of the % operators and fleshing out the cases
for the four basic arithmetic operators.

Regards,
Ken

On Tue, 2008-06-17 at 13:29 -0400, Tom Lane wrote:

> What I'm inclined to do is remove the two % operators, which don't
> seem likely to be performance-critical

Can you discuss what you see as the benefit or trade-offs for doing
that? Removing things tends to have major potential for annoying users
following an upgrade.

We can put them back manually if they are performance critical, right?
Can we document this somehow/somewhere? Which operators were not thought
to be generally important? Thanks.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> On Tue, 2008-06-17 at 13:29 -0400, Tom Lane wrote:
>> What I'm inclined to do is remove the two % operators, which don't
>> seem likely to be performance-critical

> Can you discuss what you see as the benefit or trade-offs for doing
> that? Removing things tends to have major potential for annoying users
> following an upgrade.

It should be pretty much invisible to users --- any occurrences of the
case will be handled via an implicit coercion to int4.

regards, tom lane