[HACKERS] ffunc called multiple for same value

[Ian Burrell]

I posted a message a couple weeks ago abou having a problem with a 
user-defined C language aggregate and the ffunc being called multiple 
times with the same state.   I came up with a test case which shows the 
problem with plpgsql functions.  It occurs with an aggregate in an inner 
query, when a nested loop is used.  ANALYZE the tables with zero rows 
causes it to use a nested loop.  We first discovered the problem when we 
analyzed a test database and our ffunc started failing because we 
assumed the ffunc was called once and could free memory.

CREATE TABLE foo (a integer);
CREATE TABLE bar (a integer, b integer, c integer);
ANALYZE foo;
ANALYZE bar;
INSERT INTO foo VALUES (1);
INSERT INTO foo VALUES (2);
INSERT INTO foo VALUES (3);
INSERT INTO bar VALUES (1, 5, 19);
INSERT INTO bar VALUES (2, 7, 23);
INSERT INTO bar VALUES (2, 9, 29);
INSERT INTO bar VALUES (3, 11, 31);
INSERT INTO bar VALUES (3, 13, 37);
INSERT INTO bar VALUES (3, 17, 41);
CREATE OR REPLACE FUNCTION custom_agg_sfunc(integer, integer) RETURNS 
integer
LANGUAGE 'plpgsql'
AS '
    BEGIN
        RAISE NOTICE ''custom_agg_sfunc: state: % value % '', $1, $2;
        RETURN $1 * $2;
    END;
';

CREATE OR REPLACE FUNCTION custom_agg_ffunc(integer) RETURNS integer
LANGUAGE 'plpgsql'
AS '
    BEGIN
        RAISE NOTICE ''custom_agg_ffunc: % '', $1;
        RETURN $1;
    END;
';
CREATE AGGREGATE custom_agg (
    sfunc = custom_agg_sfunc,
    basetype = integer,
    stype = integer,
    finalfunc = custom_agg_ffunc,
    initcond = 1
);
SELECT foo.a, comp
FROM foo, (
    SELECT a, custom_agg(c) AS comp
    FROM bar
    GROUP BY a
) x
WHERE foo.a = x.a;
The results are:
NOTICE:  custom_agg_sfunc: state: 1 value 31
NOTICE:  custom_agg_sfunc: state: 1 value 37
NOTICE:  custom_agg_sfunc: state: 37 value 41
NOTICE:  custom_agg_sfunc: state: 1 value 43
NOTICE:  custom_agg_sfunc: state: 43 value 47
NOTICE:  custom_agg_sfunc: state: 2021 value 53
NOTICE:  custom_agg_ffunc: 31
NOTICE:  custom_agg_ffunc: 1517
NOTICE:  custom_agg_ffunc: 107113
NOTICE:  custom_agg_ffunc: 31
NOTICE:  custom_agg_ffunc: 1517
NOTICE:  custom_agg_ffunc: 107113
NOTICE:  custom_agg_ffunc: 31
NOTICE:  custom_agg_ffunc: 1517
NOTICE:  custom_agg_ffunc: 107113
 a |  comp
---+--------
 3 |     31
 5 |   1517
 7 | 107113
(3 rows)
 - Ian
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])