ffunc called multiple for same value

Ian Burrell <imb@rentrak.com>

From: Ian Burrell <imb@rentrak.com>
To: pgsql-hackers@postgresql.org
Date: 2004-07-22T22:44:14Z
Lists: pgsql-hackers
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