Re: Parallel Append subplan order instability on aye-aye

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Thomas Munro <thomas(dot)munro(at)gmail(dot)com>
Cc: David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>, Andres Freund <andres(at)anarazel(dot)de>
Subject: Re: Parallel Append subplan order instability on aye-aye
Date: 2019-09-26 17:14:02
Message-ID: 24480.1569518042@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I wrote:
> Yeah. I think we've had quite enough of the stats-transmission-related
> failures, and they're no longer proving anything about the original
> problem. So I will go do what I proposed in mid-July and revert the
> stats queries, while keeping the reltuples/relpages check. (I'd kind
> of like to get more confirmation that the plan shape change is associated
> with those fields reading as zeroes, before we decide what to do about the
> underlying instability.)

Christoph Berg's recent complaint reminded me to scan the buildfarm
database again for info related to this issue, and I found this:

https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=moonjelly&dt=2019-07-02%2017%3A17%3A02

in which the failure diffs are

diff -U3 /home/fabien/pg/build-farm-10/buildroot/HEAD/pgsql.build/src/test/regress/expected/select_parallel.out /home/fabien/pg/build-farm-10/buildroot/HEAD/pgsql.build/src/bin/pg_upgrade/tmp_check/regress/results/select_parallel.out
--- /home/fabien/pg/build-farm-10/buildroot/HEAD/pgsql.build/src/test/regress/expected/select_parallel.out 2019-05-21 19:17:03.472207619 +0200
+++ /home/fabien/pg/build-farm-10/buildroot/HEAD/pgsql.build/src/bin/pg_upgrade/tmp_check/regress/results/select_parallel.out 2019-07-02 19:21:53.643095637 +0200
@@ -98,7 +98,7 @@
a_star | 1 | 3
b_star | 1 | 4
c_star | 1 | 4
- d_star | 1 | 16
+ d_star | 0 | 0
e_star | 1 | 7
f_star | 1 | 16
(6 rows)
@@ -130,7 +130,7 @@
-----------------------------------------------------
Finalize Aggregate
-> Gather
- Workers Planned: 1
+ Workers Planned: 3
-> Partial Aggregate
-> Append
-> Parallel Seq Scan on a_star
diff -U3 /home/fabien/pg/build-farm-10/buildroot/HEAD/pgsql.build/src/test/regress/expected/stats.out /home/fabien/pg/build-farm-10/buildroot/HEAD/pgsql.build/src/bin/pg_upgrade/tmp_check/regress/results/stats.out
--- /home/fabien/pg/build-farm-10/buildroot/HEAD/pgsql.build/src/test/regress/expected/stats.out 2019-05-21 19:17:03.472207619 +0200
+++ /home/fabien/pg/build-farm-10/buildroot/HEAD/pgsql.build/src/bin/pg_upgrade/tmp_check/regress/results/stats.out 2019-07-02 19:21:57.891105601 +0200
@@ -208,7 +208,7 @@
a_star | 1 | 3
b_star | 1 | 4
c_star | 1 | 4
- d_star | 1 | 16
+ d_star | 0 | 0
e_star | 1 | 7
f_star | 1 | 16
(6 rows)

While this fails to show the plan ordering difference we were looking for,
it does show that relpages/reltuples can sometimes read as zeroes for one
of these tables. (It also indicates that at least some of the
worker-count instability we've seen might trace to this same issue.)

That's the only related failure I could find in the last three months,
which makes me think that we've changed the regression tests enough that
the chance timing needed to cause this is (once again) very improbable.
So I'm prepared to give up waiting for more buildfarm evidence.

I propose to finish reverting f03a9ca43 in HEAD, and instead install
the attached in HEAD and v12. This follows the upthread suggestions
from Thomas and myself to use ANALYZE to ensure that these tables
have the expected relpages/reltuples entries.

In principle, we might need this further back than v12, but without having
seen a test failure in the wild I'm not tempted to back-patch further.

regards, tom lane

Attachment Content-Type Size
analyze-x-star-tables.patch text/x-diff 1.2 KB

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Jehan-Guillaume de Rorthais 2019-09-26 17:20:46 Re: Fetching timeline during recovery
Previous Message legrand legrand 2019-09-26 16:57:57 Re: Hooks for session start and end, take two