Re: Why is parula failing?

On Wed, 20 Mar 2024 at 11:50, Matthias van de Meent
<boekewurm+postgres(at)gmail(dot)com> wrote:
>
> On Tue, 19 Mar 2024 at 20:58, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >
> > For the last few days, buildfarm member parula has been intermittently
> > failing the partition_prune regression test, due to unexpected plan
> > changes [1][2][3][4]. The symptoms can be reproduced exactly by
> > inserting a "vacuum" of one or another of the partitions of table
> > "ab", so we can presume that the underlying cause is an autovacuum run
> > against one of those tables. But why is that happening? None of
> > those tables receive any insertions during the test, so I don't
> > understand why autovacuum would trigger on them.

> This may be purely coincidental, but yesterday I also did have a
> seemingly random failure in the recovery test suite locally, in
> t/027_stream_regress.pl, where it changed the join order of exactly
> one of the queries (that uses the tenk table multiple times, iirc 3x
> or so).
[...]
> Sadly, I did not save the output of that test run, so this is just
> about all the information I have. The commit I was testing on was
> based on ca108be7, and system config is available if needed.

It looks like Cirrus CI reproduced my issue with recent commit
a0390f6c [0] and previously also with 4665cebc [1], 875e46a0 [2],
449e798c [3], and other older commits, on both the Windows Server 2019
build and Debian for a39f1a36 (with slightly different plan changes on
this Debian run). That should rule out most of the environments.

After analyzing the logs produced by the various primaries, I can't
find a good explanation why they would have this issue. The table is
vacuum analyzed before the regression tests start, and in this run
autovacuum/autoanalyze doesn't seem to kick in until (at least)
seconds after this query was run.

Kind regards,

Matthias van de Meent

[0] https://cirrus-ci.com/task/6295909005262848
[1] https://cirrus-ci.com/task/5229745516838912
[2] https://cirrus-ci.com/task/5098544567156736
[3] https://cirrus-ci.com/task/4783906419900416