Thread: problem when optimizing the window aggregation

<div class="WordSection1"><p class="MsoNormal">Hi,<p class="MsoNormal"> <p class="MsoNormal"
style="text-indent:9.0pt">I’mtrying to reduce the re-computing of window aggregation. Here the AVG function for
example.<pclass="MsoNormal" style="text-indent:9.0pt"> <p class="MsoNormal" style="text-indent:9.0pt">The original
windowaggregation’s transition value(transValue) of AVG is an ArrayType, that contains two main values(sum, count). <p
class="MsoNormal"style="text-indent:9.0pt"> <p class="MsoNormal" style="text-indent:9.0pt">Now, I’m using a temporary
transitionvalue (tempTransValue), and I need to copy tempTransValue to transValue.<p class="MsoNormal"
style="text-indent:9.0pt"> <pclass="MsoNormal" style="text-indent:9.0pt">I used the function datumCopy as following:<p
class="MsoNormal"style="text-indent:36.0pt">peraggstate->transValue = datumCopy(peraggstate->tempTransValue,
peraggstate->transtypeByVal,peraggstate->transtypeLen);<p class="MsoNormal" style="text-indent:9.0pt"> <p
class="MsoNormal"style="text-indent:9.0pt">But when the copied transValue is passed to the invoke function, here is
int4_avg_accum,the ArrayType returned from PG_GETARG_ARRAYTYPE_P(0) is null.<p class="MsoNormal"
style="text-indent:9.0pt">Whichmeans the copy action is failed.<p class="MsoNormal" style="text-indent:9.0pt"> <p
class="MsoNormal"style="text-indent:9.0pt">Anybody know why? Or give me some suggestions? Thanks very much.<p
class="MsoNormal"style="text-indent:9.0pt"> <p class="MsoNormal" style="text-indent:9.0pt"> <p class="MsoNormal"
style="text-indent:9.0pt">BestRegards<p class="MsoNormal" style="text-indent:9.0pt">Chaoyong Wang<p
class="MsoNormal"> </div>

"Wang, Chaoyong" <Chaoyong.Wang@emc.com> writes:
> I used the function datumCopy as following:

> peraggstate->transValue = datumCopy(peraggstate->tempTransValue,
> peraggstate->transtypeByVal, peraggstate->transtypeLen);

You need to guard that with an is-null check, because datumCopy isn't
designed to cope with null values.  (Which is historical, I guess, but
we're not likely to change it now.)  If you look around you will find
plenty of examples of this coding pattern.
        regards, tom lane

Thanks again, the reason is found.

The following statement frees all the context for aggregation, including the transValue and tempTransValue:
MemoryContextResetAndDeleteChildren(winstate->aggcontext);


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us] 
Sent: 2012年8月22日 9:47
To: Wang, Chaoyong
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] problem when optimizing the window aggregation

"Wang, Chaoyong" <Chaoyong.Wang@emc.com> writes:
> I used the function datumCopy as following:

> peraggstate->transValue = datumCopy(peraggstate->tempTransValue,
> peraggstate->transtypeByVal, peraggstate->transtypeLen);

You need to guard that with an is-null check, because datumCopy isn't
designed to cope with null values.  (Which is historical, I guess, but
we're not likely to change it now.)  If you look around you will find
plenty of examples of this coding pattern.
        regards, tom lane