Re: Aggregate transition state merging vs. hypothetical set functions

On 10/13/2017 02:41 AM, Tom Lane wrote:
> David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> writes:
>> On 13 October 2017 at 12:08, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Therefore, I think we need to bite the bullet and provide an aggregate
>>> property (CREATE AGGREGATE argument / pg_aggregate column) that tells
>>> whether the aggregate supports transition state merging. Likely this
>>> should have been in the state-merging patch to begin with, but better
>>> late than never.
>
>> Are you considering that this is an option only for ordered-set
>> aggregates or for all?
>
> All.
>
>> If the user defines their normal aggregate as not safe for merging,
>> then surely it'll not be suitable to be used as a window function
>> either, since the final function will also be called there multiple
>> times per state.
>
> Yeah, we would probably also want to check the flag in nodeWindowAgg.
> Not sure exactly how that should play out --- maybe we end up with
> a tri-valued property "works as normal agg without merging, works
> as normal agg with merging, works as window agg". But this would
> arguably be an improvement over the current situation. Right now
> I'm sure there are user-written aggs out there that will just crash
> if used as a window agg, and the authors don't have much choice because
> the performance costs of not modifying the transition state in the
> finalfn are higher than they're willing to bear. At least with a
> flag they could ensure that the case will fail cleanly.

Sounds right to me. I'm not so sure there really are aggregates out
there that would crash today if used as a window aggregate, but it sure
would be nice to give some control on that.

We've been doing that window agg thing for a long time, so I think
"works as window agg" should be the default for regular aggregates. For
ordered-set aggregates, "no merging, no more transfn() calls after
finalfn()" seems safest.

It's a bit of a shame to have different defaults for regular and
ordered-set aggregates. But that is what we implicitly assume today,
anyway, and it's not too that hard to document.

- Heikki