Thread: I probably don't understand aggregates.

I'm new to postgres, sql and mailing lists, so I think I'm in the right
place here.

I have a pl/pgsql function, that calls a selfwritten aggregate function
(consisting of stepfunction and finalfunction) and afterwards processes
the results.

The Problem:

if I call
   SELECT myAggrFunction(x) FROM table;
I get the correct and expected output.

but when myAggrFunction gets called inside the "wrapper"-function, the
finalfunction gets executed before all rows are processed and hence I
get a "Division by zero" error.

E.g.
   SELECT myWrapperFunction(x) FROM table;
   ERROR: division by zero


myWrapperFunction would look something like:

   BEGIN
     M := myAggrFunction(x);
     --do some more stuff
     RETURN M;
   END;

I hope the description is somehow understandable.

Any feedback will be highly appreciated.

Markus

Markus Neumann wrote
> I'm new to postgres, sql and mailing lists, so I think I'm in the right
> place here.
>
> I have a pl/pgsql function, that calls a selfwritten aggregate function
> (consisting of stepfunction and finalfunction) and afterwards processes
> the results.
>
> The Problem:
>
> if I call
>    SELECT myAggrFunction(x) FROM table;
> I get the correct and expected output.
>
> but when myAggrFunction gets called inside the "wrapper"-function, the
> finalfunction gets executed before all rows are processed and hence I
> get a "Division by zero" error.
>
> E.g.
>    SELECT myWrapperFunction(x) FROM table;
>    ERROR: division by zero
>
>
> myWrapperFunction would look something like:
>
>    BEGIN
>      M := myAggrFunction(x);
>      --do some more stuff
>      RETURN M;
>    END;
>
> I hope the description is somehow understandable.
>
> Any feedback will be highly appreciated.
>
> Markus

Is there a reason you cannot provide a self-contained example?

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/I-probably-don-t-understand-aggregates-tp5806879p5806885.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

Sorry for the PM David. (as I said thats my first mailing list...)
I checked for conflicts, there are none.

Thanks a lot for your time.

This is the smallest setup I could come up with, that reproduces the same
behaviour...

CREATE OR REPLACE FUNCTION myAggrStep(state INTEGER, sumup INTEGER)
RETURNS INTEGER AS
$$
   DECLARE outval INTEGER;
   BEGIN
     IF state IS NULL THEN
       outval := sumup;
     ELSE
       outval := state + sumup;
     END IF;
   RETURN outval;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION myAggrFinal(finalstate INTEGER)
RETURNS INTEGER AS
$$
   DECLARE outval INTEGER;
   BEGIN
     outval := 1/finalstate;
     RETURN outval;
END;
$$ LANGUAGE plpgsql;

CREATE AGGREGATE myAggr(INTEGER)(
     SFUNC = myAggrStep,
     STYPE = INTEGER,
     FINALFUNC = myAggrFinal
);

CREATE OR REPLACE FUNCTION myWrapperFunc(x INTEGER)
RETURNS INTEGER AS
$$
   DECLARE outval INTEGER;
   BEGIN
     outval := myAggr(x);
     RETURN outval;
END;
$$ LANGUAGE plpgsql;

CREATE TABLE test(x integer);
INSERT INTO test VALUES(0);
INSERT INTO test VALUES(0);
INSERT INTO test VALUES(1);

After all this:
SELECT myAggr(x) FROM test;

works.

SELECT myWrapperFunc(x) FROM test;

Division by zero!


Hope I didn't put any copy/paste errors...
On 11.06.2014 22:41, David G Johnston wrote:
> Markus Neumann wrote
>> I'm new to postgres, sql and mailing lists, so I think I'm in the right
>> place here.
>>
>> I have a pl/pgsql function, that calls a selfwritten aggregate function
>> (consisting of stepfunction and finalfunction) and afterwards processes
>> the results.
>>
>> The Problem:
>>
>> if I call
>>     SELECT myAggrFunction(x) FROM table;
>> I get the correct and expected output.
>>
>> but when myAggrFunction gets called inside the "wrapper"-function, the
>> finalfunction gets executed before all rows are processed and hence I
>> get a "Division by zero" error.
>>
>> E.g.
>>     SELECT myWrapperFunction(x) FROM table;
>>     ERROR: division by zero
>>
>>
>> myWrapperFunction would look something like:
>>
>>     BEGIN
>>       M := myAggrFunction(x);
>>       --do some more stuff
>>       RETURN M;
>>     END;
>>
>> I hope the description is somehow understandable.
>>
>> Any feedback will be highly appreciated.
>>
>> Markus
> Is there a reason you cannot provide a self-contained example?
>
> David J.
>
>
>
>
> --
> View this message in context:
http://postgresql.1045698.n5.nabble.com/I-probably-don-t-understand-aggregates-tp5806879p5806885.html
> Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
>
>

On 11.06.2014 23:43, Markus Neumann wrote:
> Sorry for the PM David. (as I said thats my first mailing list...)
> I checked for conflicts, there are none.
>
> Thanks a lot for your time.
>
> This is the smallest setup I could come up with, that reproduces the same
> behaviour...
>
> CREATE OR REPLACE FUNCTION myAggrStep(state INTEGER, sumup INTEGER)
> RETURNS INTEGER AS
> $$
>   DECLARE outval INTEGER;
>   BEGIN
>     IF state IS NULL THEN
>       outval := sumup;
>     ELSE
>       outval := state + sumup;
>     END IF;
>   RETURN outval;
> END;
> $$ LANGUAGE plpgsql;
>
> CREATE OR REPLACE FUNCTION myAggrFinal(finalstate INTEGER)
> RETURNS INTEGER AS
> $$
>   DECLARE outval INTEGER;
>   BEGIN
>     outval := 1/finalstate;
>     RETURN outval;
> END;
> $$ LANGUAGE plpgsql;
>
> CREATE AGGREGATE myAggr(INTEGER)(
>     SFUNC = myAggrStep,
>     STYPE = INTEGER,
>     FINALFUNC = myAggrFinal
> );
>
> CREATE OR REPLACE FUNCTION myWrapperFunc(x INTEGER)
> RETURNS INTEGER AS
> $$
>   DECLARE outval INTEGER;
>   BEGIN
>     outval := myAggr(x);
>     RETURN outval;
> END;
> $$ LANGUAGE plpgsql;
>
> CREATE TABLE test(x integer);
> INSERT INTO test VALUES(0);
> INSERT INTO test VALUES(0);
> INSERT INTO test VALUES(1);
>
> After all this:
> SELECT myAggr(x) FROM test;
>
> works.
>
> SELECT myWrapperFunc(x) FROM test;
>
> Division by zero!
>
>
> Hope I didn't put any copy/paste errors...
> On 11.06.2014 22:41, David G Johnston wrote:
>> Markus Neumann wrote
>>> I'm new to postgres, sql and mailing lists, so I think I'm in the right
>>> place here.
>>>
>>> I have a pl/pgsql function, that calls a selfwritten aggregate function
>>> (consisting of stepfunction and finalfunction) and afterwards processes
>>> the results.
>>>
>>> The Problem:
>>>
>>> if I call
>>>     SELECT myAggrFunction(x) FROM table;
>>> I get the correct and expected output.
>>>
>>> but when myAggrFunction gets called inside the "wrapper"-function, the
>>> finalfunction gets executed before all rows are processed and hence I
>>> get a "Division by zero" error.
>>>
>>> E.g.
>>>     SELECT myWrapperFunction(x) FROM table;
>>>     ERROR: division by zero
>>>
>>>
>>> myWrapperFunction would look something like:
>>>
>>>     BEGIN
>>>       M := myAggrFunction(x);
>>>       --do some more stuff
>>>       RETURN M;
>>>     END;
>>>
>>> I hope the description is somehow understandable.
>>>
>>> Any feedback will be highly appreciated.
>>>
>>> Markus
>> Is there a reason you cannot provide a self-contained example?
>>
>> David J.
>>
>>
>>
>>
>> --
>> View this message in context:
>> http://postgresql.1045698.n5.nabble.com/I-probably-don-t-understand-aggregates-tp5806879p5806885.html
>> Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
>>
>>
>
>
>
And now I even understood what you meant by Bottom-Post... -.-
Sorry again.

Markus Neumann wrote
> After all this:
> SELECT myAggr(x) FROM test;
>
> works.

Because this is an aggregate call with an implicit GROUP BY.  The
final_func, sees 0+0+1, performs the reciprocal, and returns 1


> SELECT myWrapperFunc(x) FROM test;
>
> Division by zero!

Because myWrapperFunction is not an aggregate you end up calling myAggr
three times, once each with for the values 0, 0, 1.  The way myAggr is
written if the final state of the function is 0 a division by zero will
occur.

My privately noted confusion about the lack of a GROUP BY still applies - I
just forgot about the implicit GROUP BY when all output columns are defined
using aggregates.

Try executing:

SELECT myAggr(x), x FROM test GROUP BY x;

This is basically what you are doing when you put the aggregate into the
wrapper.

David J.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/I-probably-don-t-understand-aggregates-tp5806879p5806896.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

Markus Neumann <markus.neumann@math.uzh.ch> writes:
> if I call
>    SELECT myAggrFunction(x) FROM table;
> I get the correct and expected output.

> but when myAggrFunction gets called inside the "wrapper"-function, the
> finalfunction gets executed before all rows are processed and hence I
> get a "Division by zero" error.

> E.g.
>    SELECT myWrapperFunction(x) FROM table;
>    ERROR: division by zero

> myWrapperFunction would look something like:

>    BEGIN
>      M := myAggrFunction(x);
>      --do some more stuff
>      RETURN M;
>    END;

That is certainly not going to do what you presumably want.
The outer query doesn't think that the wrapper function is an
aggregate; it's just a plain scalar function that will be called
separately on each row of the table.  Meanwhile, the best way
to think about what's happening inside the wrapper is that
     M := myAggrFunction(x);
is equivalent to
     SELECT myAggrFunction(x) INTO M FROM anonymous-one-row-table;
or if you're an Oracle refugee,
     SELECT myAggrFunction(x) INTO M FROM dual;

So the aggregate is evaluated over just one row, and I suppose the
zero-divide error happens because your final function is failing
to cope with that case.

            regards, tom lane

On 12.06.2014 00:09, Tom Lane wrote:
> That is certainly not going to do what you presumably want. The outer
> query doesn't think that the wrapper function is an aggregate; it's
> just a plain scalar function that will be called separately on each
> row of the table. Meanwhile, the best way to think about what's
> happening inside the wrapper is that M := myAggrFunction(x); is
> equivalent to SELECT myAggrFunction(x) INTO M FROM
> anonymous-one-row-table; or if you're an Oracle refugee, SELECT
> myAggrFunction(x) INTO M FROM dual; So the aggregate is evaluated over
> just one row, and I suppose the zero-divide error happens because your
> final function is failing to cope with that case. regards, tom lane

On 12.06.2014 00:08, David G Johnston wrote:
> Because myWrapperFunction is not an aggregate you end up calling
> myAggr three times, once each with for the values 0, 0, 1. The way
> myAggr is written if the final state of the function is 0 a division
> by zero will occur. My privately noted confusion about the lack of a
> GROUP BY still applies - I just forgot about the implicit GROUP BY
> when all output columns are defined using aggregates. Try executing:
> SELECT myAggr(x), x FROM test GROUP BY x; This is basically what you
> are doing when you put the aggregate into the wrapper. David J. --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/I-probably-don-t-understand-aggregates-tp5806879p5806896.html
> Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

Thank you both, that helped a lot.

I'm doing a project for my informatics minor, where I have to use
aggregates to get some matrix multiplication and inversion done inside
pgsql.

But how can I get my wrapper function to evaluate the aggregate on the
whole table and work with the result afterwards?

Or do I need to change the aggregate?

Markus

But how can I get my wrapper function to evaluate the aggregate on the
whole table and work with the result afterwards?

Or do I need to change the aggregate?

​I do not follow but...

SELECT myWrapper(agg_output) FROM (

SELECT myAgg(x) AS agg_output FROM test

)​ agg_subquery;

It is not clear why you need both an aggregate and a wrapper...

David J.

---

View this message in context: Re: I probably don't understand aggregates.
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.