BUG #15869: Custom aggregation returns null when parallelized
От | PG Bug reporting form |
---|---|
Тема | BUG #15869: Custom aggregation returns null when parallelized |
Дата | |
Msg-id | 15869-32aa02312ddc6be7@postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #15869: Custom aggregation returns null when parallelized
(David Rowley <david.rowley@2ndquadrant.com>)
|
Список | pgsql-bugs |
The following bug has been logged on the website: Bug reference: 15869 Logged by: Kassym Dorsel Email address: kdorsel@gmail.com PostgreSQL version: 11.4 Operating system: Windows 10 / Ubuntu 18 Description: I have a custom aggregate setup. It run and returns the correct results when run sequentially. Once I set parallel = safe it returns null values. I have tested and gotten the same results on Windows 10 / postgres 11.4 and Ubuntu 18 / postgres 11.3. Here's the aggregate: CREATE OR REPLACE FUNCTION array_sort(ANYARRAY) RETURNS ANYARRAY LANGUAGE SQL AS $$ SELECT ARRAY(SELECT unnest($1) ORDER BY 1) $$; create type _stats_agg_accum_type AS ( cnt bigint, q double precision[], n double precision[], np double precision[], dn double precision[] ); create type _stats_agg_result_type AS ( count bigint, q25 double precision, q50 double precision, q75 double precision ); create or replace function _stats_agg_p2_parabolic(_stats_agg_accum_type, double precision, double precision) returns double precision AS ' DECLARE a alias for $1; i alias for $2; d alias for $3; BEGIN RETURN a.q[i] + d / (a.n[i + 1] - a.n[i - 1]) * ((a.n[i] - a.n[i - 1] + d) * (a.q[i + 1] - a.q[i]) / (a.n[i + 1] - a.n[i]) + (a.n[i + 1] - a.n[i] - d) * (a.q[i] - a.q[i - 1]) / (a.n[i] - a.n[i - 1])); END; ' language plpgsql; create or replace function _stats_agg_p2_linear(_stats_agg_accum_type, double precision, double precision) returns double precision AS ' DECLARE a alias for $1; i alias for $2; d alias for $3; BEGIN return a.q[i] + d * (a.q[i + d] - a.q[i]) / (a.n[i + d] - a.n[i]); END; ' language plpgsql; create or replace function _stats_agg_accumulator(_stats_agg_accum_type, double precision) returns _stats_agg_accum_type AS ' DECLARE a ALIAS FOR $1; x alias for $2; k int; d double precision; qp double precision; BEGIN a.cnt = a.cnt + 1; if a.cnt <= 5 then a.q = array_append(a.q, x); if a.cnt = 5 then a.q = array_sort(a.q); end if; return a; end if; case when x < a.q[1] then a.q[1] = x; k = 1; when x >= a.q[1] and x < a.q[2] then k = 1; when x >= a.q[2] and x < a.q[3] then k = 2; when x >= a.q[3] and x < a.q[4] then k = 3; when x >= a.q[4] and x <= a.q[5] then k = 4; when x > a.q[5] then a.q[5] = x; k = 4; end case; for ii in 1..5 loop if ii > k then a.n[ii] = a.n[ii] + 1; end if; a.np[ii] = a.np[ii] + a.dn[ii]; end loop; for ii in 2..4 loop d = a.np[ii] - a.n[ii]; if (d >= 1 and a.n[ii+1] - a.n[ii] > 1) or (d <= -1 and a.n[ii-1] - a.n[ii] < -1) then d = sign(d); qp = _stats_agg_p2_parabolic(a, ii, d); if qp > a.q[ii-1] and qp < a.q[ii+1] then a.q[ii] = qp; else a.q[ii] = _stats_agg_p2_linear(a, ii, d); end if; a.n[ii] = a.n[ii] + d; end if; end loop; return a; END; ' language plpgsql; create or replace function _stats_agg_combiner(_stats_agg_accum_type, _stats_agg_accum_type) returns _stats_agg_accum_type AS ' DECLARE a alias for $1; b alias for $2; c _stats_agg_accum_type; BEGIN c.cnt = a.cnt + b.cnt; c.q[2] = (a.q[2] + b.q[2]) / 2; c.q[3] = (a.q[3] + b.q[3]) / 2; c.q[4] = (a.q[4] + b.q[4]) / 2; RETURN c; END; ' strict language plpgsql; create or replace function _stats_agg_finalizer(_stats_agg_accum_type) returns _stats_agg_result_type AS ' BEGIN RETURN row( $1.cnt, $1.q[2], $1.q[3], $1.q[4] ); END; ' language plpgsql; create aggregate stats_agg(double precision) ( sfunc = _stats_agg_accumulator, stype = _stats_agg_accum_type, finalfunc = _stats_agg_finalizer, combinefunc = _stats_agg_combiner, --parallel = safe, initcond = '(0, {}, "{1,2,3,4,5}", "{1,2,3,4,5}", "{0,0.25,0.5,0.75,1}")' ); Here's the setup code: --CREATE TABLE temp (val double precision); --insert into temp (val) select i from generate_series(0, 150000) as t(i); --set force_parallel_mode = on; select (stats_agg(val)).* from temp; Expected results: 150001, 37500, 75000, 112500 Results when run in parallel: 150001, null, null, null
В списке pgsql-bugs по дате отправления:
Предыдущее
От: Tom LaneДата:
Сообщение: Re: BUG #15844: MIPS: remove .set mips2 in s_lock.h to fix r6 build