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