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

On Jan20, 2014, at 15:20 , Florian Pflug <fgp(at)phlo(dot)org> wrote:
> * In CREATE AGGREGATE, we should state the precise axioms we assume about forward
> and inverse transition functions. The last time I read the text there, it was
> a bit ambiguous about whether inverse transition functions assume commutativity,
> i.e. whether we assume that we can remove inputs other than the first one from
> the aggregation. Currently, all the inverse transition functions we have are,
> in fact, commutative, because all the forward transition function are also. But
> we could e.g. add an inverse to array_agg and string_agg, and those would
> obviously need this ordering guarantee. I'd also like us to explain that the
> "inverse" in "inverse transition function" shouldn't be taken too literally. For
> forward transition function F, inverse transition function G, and inputs a,b,...
> we *don't require that G(F(s,a),a) == s. We we do require is that if I is the
> initial state, then
>
> G(F(...F(F(I,a),b)...,z),a) == F(...F(I,b)...,z).
>
> (Well, actually we don't strictly require even that, the two states don't
> need to be identical, they just need to produce identical outputs when passed
> to the final function)
>
> * In CREATE AGGREGATE, we should also explain the NULL semantics you get
> with various combinations of strict and non-strict forward and inverse
> transition functions. I think a table listing all the combinations is in order
> there, with a column explaining the semantics you get. We should also clearly
> state that once you provide an inverse transition function, NULL isn't a valid
> state value anymore, except as an initial state, i.e. that the forward transition
> function must never return NULL in this case.

I gave that a shot, the results are at https://github.com/fgp/postgres/tree/invtrans

> * The window function page should explain the performance hazards when
> a moving frame head is involved. Ideally, we'd list which aggregates never
> have to restart, and which sometimes might, and what you can do to avoid that.
> We can also tell people to use EXPLAIN VERBOSE ANALYZE to check whether
> restarts are occurring. This is were we'd tell people to cast their numeric
> operands to a type with defined scale to avoid restarts, and were we'd state
> the SUM(float) *does* restart always due to precision loss issues.

> BTW, something that we haven't addressed at all is how inverse transition
> functions interact with what is called "ordered-set aggregates". I haven't
> wrapped my head around these fully, I think, so I'm still not sure if there's
> anything to do there or not. Can those even be used as window functions?
> Should we forbid ordered-set aggregates from specifying an inverse transition
> function?

It seems that these aren't valid window function anyway, so there's nothing
to do for now, I think.

best regards,
Florian Pflug