Re: Parallel Append subplan order instability on aye-aye

On Tue, 21 May 2019 at 11:32, Thomas Munro <thomas.munro@gmail.com> wrote:
>
> On Mon, May 20, 2019 at 4:46 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Thomas Munro <thomas.munro@gmail.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