Re: Partial aggregates pushdown

Fujii(dot)Yuki(at)df(dot)MitsubishiElectric(dot)co(dot)jp писал(а) 2024-05-28 00:30:
> Hi Mr. Pyhalov.
>
Hi.

>> Found one more problem. You can fire partial aggregate over
>> partitioned
>> table, but convert_combining_aggrefs() will make non-partial copy,
>> which
>> leads to
>> 'variable not found in subplan target list' error.
> Thanks for the correction as well.
> As you pointed out,
> the original patch certainly had the potential to cause problems.
> However, I could not actually reproduce the problem in cases such as
> the following.
>
> Settings:
> t(c1, c2) is a patitioned table whose partition key is c1.
> t1, t2 are patitions of t and are partitioned table.
> t11, t12: partitions of t1 and foreign table of postgres_fdw.
> t21, t22: partitions of t2 and foreign table of postgres_fdw.
> Query:
> select c2 / 2, sum(c1) from t group by c2 / 2 order by 1
>
> If you have a reproducible example, I would like to add it to
> the regression test.
> Do you have a reproducible example?
>

The fix was to set child_agg->agg_partial to orig_agg->agg_partial in
convert_combining_aggrefs(), it's already in the patch,
as well as the example - without this fix

-- Check partial aggregate over partitioned table
EXPLAIN (VERBOSE, COSTS OFF)
SELECT avg(PARTIAL_AGGREGATE a), avg(a) FROM pagg_tab;

fails with

ERROR: variable not found in subplan target list
--
Best regards,
Alexander Pyhalov,
Postgres Professional