29.03.2024 11:59, Alexander Lakhin wrote:
But it looks like subselect is not the only test that can fail due to vacuum instability. I see that create_index also suffers from cranky ConditionalLockBufferForCleanup() (+if (rand() % 10 == 0) ...
Just for the record, I think I've reproduced the same failure as: https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=sungazer&dt=2024-03-17%2003%3A03%3A57 not ok 66 + create_index 27509 ms ... and the similar occurrences: https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=hornet&dt=2024-01-02%2007%3A09%3A09 not ok 66 + create_index 25830 ms https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=sungazer&dt=2023-11-15%2006%3A16%3A15 not ok 66 + create_index 38508 ms by running 8 027_stream_regress instances in parallel on a slow ARMv7 device like this: for i in `seq 10`; do echo "I $i"; parallel --halt now,fail=1 -j8 \ --linebuffer --tag NO_TEMP_INSTALL=1 make -s check -C \ src/test/recovery_{}/ PROVE_TESTS="t/027*" ::: `seq 8` || break; done 5 5 # Failed test 'regression tests pass' 5 # at t/027_stream_regress.pl line 92. 5 # got: '256' 5 # expected: '0' 5 t/027_stream_regress.pl .. 5 Dubious, test returned 1 (wstat 256, 0x100) 5 Failed 1/6 subtests not ok 66 + create_index 152995 ms ... === dumping .../src/test/recovery_5/tmp_check/regression.diffs === diff -U3 .../src/test/regress/expected/create_index.out .../src/test/recovery_5/tmp_check/results/create_index.out --- .../src/test/regress/expected/create_index.out 2024-05-30 15:30:34.523048633 +0000 +++ .../src/test/recovery_5/tmp_check/results/create_index.out 2024-05-31 13:07:56.359001362 +0000 @@ -1916,11 +1916,15 @@ SELECT unique1 FROM tenk1 WHERE unique1 IN (1,42,7) ORDER BY unique1; - QUERY PLAN -------------------------------------------------------- - Index Only Scan using tenk1_unique1 on tenk1 - Index Cond: (unique1 = ANY ('{1,42,7}'::integer[])) -(2 rows) + QUERY PLAN +------------------------------------------------------------------- + Sort + Sort Key: unique1 + -> Bitmap Heap Scan on tenk1 + Recheck Cond: (unique1 = ANY ('{1,42,7}'::integer[])) + -> Bitmap Index Scan on tenk1_unique1 + Index Cond: (unique1 = ANY ('{1,42,7}'::integer[])) +(6 rows) SELECT unique1 FROM tenk1 WHERE unique1 IN (1,42,7) @@ -1936,12 +1940,13 @@ SELECT thousand, tenthous FROM tenk1 WHERE thousand < 2 AND tenthous IN (1001,3000) ORDER BY thousand; - QUERY PLAN -------------------------------------------------------- - Index Only Scan using tenk1_thous_tenthous on tenk1 - Index Cond: (thousand < 2) - Filter: (tenthous = ANY ('{1001,3000}'::integer[])) -(3 rows) + QUERY PLAN +-------------------------------------------------------------------------------------- + Sort + Sort Key: thousand + -> Index Only Scan using tenk1_thous_tenthous on tenk1 + Index Cond: ((thousand < 2) AND (tenthous = ANY ('{1001,3000}'::integer[]))) +(4 rows) SELECT thousand, tenthous FROM tenk1 WHERE thousand < 2 AND tenthous IN (1001,3000) === EOF === I got failures on iteration 2, 3, 7, 1. But with the repeated VACUUM ANALYZE: --- a/src/test/regress/sql/test_setup.sql +++ b/src/test/regress/sql/test_setup.sql @@ -163,6 +163,8 @@ CREATE TABLE tenk1 ( \set filename :abs_srcdir '/data/tenk.data' COPY tenk1 FROM :'filename'; VACUUM ANALYZE tenk1; +VACUUM ANALYZE tenk1; +VACUUM ANALYZE tenk1; 20 iterations succeeded in the same environment. So I think that that IOS plan change can be explained by the issue discussed here. Best regards, Alexander
