Re: To what extent should tests rely on VACUUM ANALYZE?

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