ffunc called multiple for same value

Поиск
Список
Период
Сортировка
От Ian Burrell
Тема ffunc called multiple for same value
Дата
Msg-id 4100433E.7080700@rentrak.com
обсуждение исходный текст
Ответы Re: ffunc called multiple for same value
Список 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



В списке pgsql-hackers по дате отправления:

Предыдущее
От: David Fetter
Дата:
Сообщение: Tutorial
Следующее
От: "Scott Marlowe"
Дата:
Сообщение: Re: Tutorial