Re: aggregate functions on massive number of rows

On Wed, Mar 02, 2005 at 12:23:45PM -0500, Todd Kover wrote:
>
> I have an aggregate function setup (attached) that I'm calling over a
> massive amount of data and am running into:
>
> ERROR: cannot have more than 2^32-1 commands in a transaction
> CONTEXT: PL/pgSQL function "float8_jitter_add" line 16 at if
>
> error. Since I'm able to do count() and avg() over the same values
> without this aggregate function, it's theoretically possible.
>
> Something was making me think that it was the extract() that was doing
> it (I used to have 'select extract(milliseconds from v_rtt_in) into
> v_rtt' and something leaded me to believe the error was with that), but
> I don't recall what that is, so it looks like it's just the if like it
> says.

Note the following from the PL/pgSQL "Expressions" documentation:

All expressions used in PL/pgSQL statements are processed using
the server's regular SQL executor. In effect, a query like

SELECT expression

is executed using the SPI manager.

I'd guess that you are indeed hitting the command limit. You might
have more luck with one of the other procedural languages (PL/Perl,
PL/Tcl, PL/Python, etc.), but I'd consider coding something like
this in C if I were using it with so much data.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/