Re: Spilling hashed SetOps and aggregates to disk

On 06/05/2018 07:39 PM, David Fetter wrote:
> On Tue, Jun 05, 2018 at 01:27:01PM -0400, Tom Lane wrote:
>> David Fetter <david(at)fetter(dot)org> writes:
>>> On Tue, Jun 05, 2018 at 02:56:23PM +1200, David Rowley wrote:
>>>> True. Although not all built in aggregates have those defined.
>>
>>> Just out of curiosity, which ones don't? As of
>>> 3f85c62d9e825eedd1315d249ef1ad793ca78ed4, pg_aggregate has both of
>>> those as NOT NULL.
>>
>> NOT NULL isn't too relevant; that's just protecting the fixed-width
>> nature of the catalog rows. What's important is which ones are zero.
>
> Thanks for helping me understand this better.
>
>> # select aggfnoid::regprocedure, aggkind from pg_aggregate where (aggserialfn=0 or aggdeserialfn=0) and aggtranstype = 'internal'::regtype;
>> aggfnoid | aggkind
>> ------------------------------------------------------+---------
>> [snip]
>> (19 rows)
>>
>> Probably the ordered-set/hypothetical ones aren't relevant for this
>> issue.
>>
>> Whether or not we feel like fixing the above "normal" aggs for this,
>> the patch would have to not fail on extension aggregates that don't
>> support serialization.
>
> Could there be some kind of default serialization with reasonable
> properties?
>

Not really, because the aggregates often use "internal" i.e. a pointer
referencing whothehellknowswhat, and how do you serialize/deserialize
that? The other issue is that serialize/deserialize is only a part of a
problem - you also need to know how to do "combine", and not all
aggregates can do that ... (certainly not in universal way).

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services