Re: Parallel Aggregates for string_agg and array_agg

On Wed, 27 Mar 2024 at 03:00, Alexander Lakhin <exclusion(at)gmail(dot)com> wrote:
> I've discovered that the test query:
> -- Ensure parallel aggregation is actually being used.
> explain (costs off) select * from v_pagg_test order by y;
>
> added by 16fd03e95 fails sometimes. For instance:
> https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=urutu&dt=2024-03-19%2021%3A04%3A05
>
> --- /home/bf/bf-build/urutu/HEAD/pgsql/src/test/regress/expected/aggregates.out 2024-02-24 06:42:47.039073180 +0000
> +++ /home/bf/bf-build/urutu/HEAD/pgsql.build/src/test/regress/results/aggregates.out 2024-03-19 22:24:18.155876135 +0000
> @@ -1993,14 +1993,16 @@
> Sort Key: pagg_test.y, (((unnest(regexp_split_to_array((string_agg((pagg_test.x)::text, ','::text)),
> ','::text))))::integer)
> -> Result
> -> ProjectSet
> - -> Finalize HashAggregate
> + -> Finalize GroupAggregate
> ...
>
> https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=phycodurus&dt=2024-02-28%2007%3A38%3A27
>
> https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=avocet&dt=2024-02-08%2008%3A47%3A45
>
> I suspect that these failures caused by autovacuum.

I agree with this analysis. In hindsight, adding 5000 records to a
table without disabling autovacuum or vacuuming the table manually was
a bad idea.

I opted to fix by disabling autovacuum for the table as that allows us
to maintain the current plan. Vacuuming would result in the plan
changing, which I opted not to do as it's a bit more churn in the
expected output file.

Thanks for the report and the analysis.

David