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@fetter.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