Re: [PATCH] Negative Transition Aggregate Functions (WIP)

On Fri, Jan 10, 2014 at 5:15 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> writes:
> > Reading over this, I realised that there is a problem with NaN
> > handling --- once the state becomes NaN, it can never recover. So the
> > results using the inverse transition function don't match HEAD in
> > cases like this:
>
> Ouch! That takes out numeric, float4, and float8 in one fell swoop.
>
> Given the relative infrequency of NaNs in most data, it seems like
> it might still be possible to get a speedup if we could use inverse
> transitions until we hit a NaN, then do it the hard way until the
> NaN is outside the window, then go back to inverse transitions.
> I'm not sure though if this is at all practical from an implementation
> standpoint. We certainly don't want the core code knowing about
> anything as datatype-specific as a NaN, but maybe the inverse transition
> function could have an API that allows reporting "I can't do it here,
> fall back to the hard way".
>
>
I had thought about that API, not for numeric as I think I've managed to
find another solution, it was for MAX and MIN.

I posted an idea about it here:
http://www.postgresql.org/message-id/CAApHDvqu+yGW0vbPBb+yxHrPG5VcY_kiFYi8xmxFo8KYOczP3A@mail.gmail.com
but it didn't generate much interest at the time and I didn't have any
ideas on how the inverse aggregate functions would communicate this
inability to remove the value to the caller. Perhaps it would be an idea
still, but I had put it to the back of my mind in favour of tuplestore
indexes that could be created on the fly based on the row position within
the frame and the aggregate's sort operator on the aggregate value.
This would mean that MAX and MIN values could be found quickly all the time
rather than just when the value being removed happened not to affect the
current maximum or minimum. It's not something I have planned for this
patch though and I'd have lots of questions around memory allocation before
I'd want to start any work on it.

Regards

David Rowley

> regards, tom lane
>