Re: review: More frame options in window functions

On Thu, Feb 11, 2010 at 3:26 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
>> On Tue, Feb 09, 2010 at 12:37:32PM +0900, Hitoshi Harada wrote:
>>> Now that specialized hard-coding "+"/"-" in source seems unacceptable,
>>> I would like to hear how to handle this. Is there any better than
>>> introducing new mechanism such like opclass?
>
>> I imagine it would be easiest to add these operators to the existing
>> opclass infrastructure. Although it didn't start that way, the opclass
>> structure is being more and more used to declare the semantics of a
>> type. Btree operator classes are for *ordered* types, which seems to be
>> the only case you can expect to work here.
>
>> Currently the btree operator classes have only one support function, so
>> it would seem than the easiest approach would be to declare two new
>> support functions for add() and subtract().
>
> Yeah, that was pretty much the same line of thought I had.  The main
> disadvantage seems to be two more catalog lookups per index to fill in
> support function entries that won't ever be used (at least not by the
> index machinery).  However, I think we cache that stuff already inside
> relcache.c, so it might be insignificant.
>
> The real question is whether it's time to bite the bullet and stop
> overloading the opclass infrastructure for semantics-declaration
> purposes.  Are there any other foreseeable cases where we are going
> to need to add operator knowledge of this sort?

knngist wants to jimmy with the opclass semantics too, though the need
there is a little different. The issue is that an amcanorder AM is
good for optimizing ORDER BY <indexed-column-name>, but that's not
what GIST indices can do: they can optimize ORDER BY
<indexed-column-name> <op> <constant>, for suitable values of op.
Teodor and Oleg handled this by adding a new flag to the AM
(amcanorderbyop) and adding the operators to the opclass. But, unlike
the comparison operators, these operators don't necessarily return a
boolean: in fact they probably don't.

It would be nice to come up with a general solution to this problem.

...Robert