Re: review: More frame options in window functions

On Thu, 11 Feb 2010, Robert Haas wrote:

> On Thu, Feb 11, 2010 at 3:26 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Martijn van Oosterhout <kleptog@svana.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.

see http://archives.postgresql.org/pgsql-hackers/2009-11/msg01809.php
in knngist we use distance semantic,  distance < 0 means FALSE, so
we compatible with old GIST.


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

yeah
    Regards,        Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83