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 по дате отправления: