pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Heikki Linnakangas <hlinnaka@iki.fi>
Cc: David Rowley <david.rowley@2ndquadrant.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: Aggregate transition state merging vs. hypothetical set functions
Date: Fri, 13 Oct 2017 10:05:23 -0400
Message-ID: <5691.1507903523@sss.pgh.pa.us> (raw)
In-Reply-To: <58219c24-ed31-3a3d-db28-ace9390973c5@iki.fi>
References: <4834.1507849699@sss.pgh.pa.us>
	<58219c24-ed31-3a3d-db28-ace9390973c5@iki.fi>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>

Heikki Linnakangas <hlinnaka@iki.fi> writes:
> On 10/13/2017 02:08 AM, Tom Lane wrote:
>> I started to look into fixing orderedsetaggs.c so that we could revert
>> 52328727b, and soon found a rather nasty problem.  Although the plain
>> OSAs seem amenable to supporting multiple finalfn calls on the same
>> transition state, the "hypothetical set" functions are not at all.
>> What they do is to accumulate all the regular aggregate input into
>> a tuplesort, then add the direct arguments as an additional "hypothetical"
>> row, and finally sort the result.  There's no realistic way of undoing
>> the addition of the hypothetical row, so these finalfns simply can't
>> share tuplesort state.

> The current implementation, with the extra flag column, is quite naive. 
> We could add some support to tuplesort to find a row with given 
> attributes, and call that instead of actually adding the hypothetical 
> row to the tuplesort and iterating to re-find it after performsort. For 
> a result set that fits in memory, you could even do a binary search 
> instead of linearly iterating through the result set.

I'd had some idle thoughts about using a heap to support window
aggregation of an OSA, rather than ever performing a full sort.
It'd probably work well as long as the window doesn't get wide enough
that the heap stops fitting in memory.  But in any case, the immediate
point is that we shouldn't just assume that every aggregate
implementation is capable of supporting state merging, and while we're
at it, it'd be better to have a non-syntax-based distinction between
aggs that can support use as window functions and aggs that can't.

Moving on to the exact color of the bikeshed: it seems like the right
way to present this to users of CREATE AGGREGATE is in terms of "does
the final function modify the transition state?".  So maybe the values
could be spelled

SMODIFY = READ_ONLY   ffunc never touches state, ok as window agg
SMODIFY = SHARABLE    ffunc does some one-time change like sorting,
                      so state merging is OK but not window agg
SMODIFY = READ_WRITE  ffunc trashes state, can't do merging either

I'm not set on these names by any means; anyone have a better idea?
Also, I'm not sure whether we'd need a separate MMODIFY flag for
the moving-aggregate sub-implementation, but it seems possible.

			regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers



view thread (12+ messages)  latest in thread

Message-ID: <5691.1507903523@sss.pgh.pa.us>
Permalink:  ../5691.1507903523@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/5691.1507903523@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-hackers@postgresql.org
  Cc: tgl@sss.pgh.pa.us, hlinnaka@iki.fi, david.rowley@2ndquadrant.com
  Subject: Re: Aggregate transition state merging vs. hypothetical set functions
  In-Reply-To: <5691.1507903523@sss.pgh.pa.us>
  References: <4834.1507849699@sss.pgh.pa.us> <58219c24-ed31-3a3d-db28-ace9390973c5@iki.fi> <5691.1507903523@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox