Re: Parallel Append subplan order instability on aye-aye

On Tue, 21 May 2019 at 11:32, Thomas Munro <thomas(dot)munro(at)gmail(dot)com> wrote:
>
> On Mon, May 20, 2019 at 4:46 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Thomas Munro <thomas(dot)munro(at)gmail(dot)com> writes:
> > > Here's a one-off regression test failure of a sort that commit
> > > 624e440a intended to fix.
> >
> > Note that in the discussion that led up to 624e440a, we never did
> > think that we'd completely explained the original irreproducible
> > failure.
> >
> > I think I've seen a couple of other cases of this same failure
> > in the buildfarm recently, but too tired to go looking right now.
>
> I think it might be dependent on incidental vacuum/analyze activity
> having updated reltuples. With the attached script, I get the two
> plan variants depending on whether I comment out "analyze a_star". I
> guess we should explicitly analyze these X_star tables somewhere?

That's the only theory I came up with yesterday when thinking about
this. We can't really go adding an ANALYZE in a test in a parallel
group though since there'd be race conditions around other parallel
tests which could cause plan changes.

At the moment, these tables are only vacuumed in sanity_check.sql,
which as you can see is run by itself.

# ----------
# sanity_check does a vacuum, affecting the sort order of SELECT *
# results. So it should not run parallel to other tests.
# ----------
test: sanity_check

I did add the following query just before the failing one and included
the expected output from below. The tests pass for me in make check
and the post-upgrade test passes in make check-world too. I guess we
could commit that and see if it fails along with the other mentioned
failure. Alternatively, we could just invent some local tables
instead of using the ?_star tables and analyze them just before the
test, although, that does not guarantee a fix as there may be
something else to blame that we've not thought of.

select relname,last_vacuum is null,last_analyze is
null,last_autovacuum is null,last_autoanalyze is null from
pg_stat_all_tables where relname like '__star' order by relname;
relname | ?column? | ?column? | ?column? | ?column?
---------+----------+----------+----------+----------
a_star | f | t | t | t
b_star | f | t | t | t
c_star | f | t | t | t
d_star | f | t | t | t
e_star | f | t | t | t
f_star | f | t | t | t
(6 rows)

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services