Attempt to fix unstable regression tests

b07642dbc added code to trigger autovacuums based on the number of
inserts into a table. This seems to have caused some regression test
results to destabilize. I suspect this is due to autovacuum triggering a
vacuum sometime after the test's ANALYZE run and perhaps reltuples is
ending up being set to a slightly different value as a result.

Attempt to resolve this by running a VACUUM ANALYZE on the affected table
instead of just ANALYZE. pg_class.reltuples will still get set to whatever
ANALYZE chooses but we should no longer get the proceeding autovacuum
overriding that.

The overhead this adds to each test's runtime seems small enough not to
worry about. I measure 3-4% on stats_ext and can't measure any change in
partition_aggregate.

I'm unable to recreate the issue locally, so this is a bit of a blind
fix.

Discussion: https://postgr.es/m/CAApHDvpWmpqYrKwwDQyeDq8dAyK7GMNaxDhrG69CkSuXoEg%2BVg%40mail.gmail.com

diff --git a/src/test/regress/expected/partition_aggregate.out b/src/test/regress/expected/partition_aggregate.out
index fbc8d3ac6c4b9106d7ed44f3ead129710898b571..69724d54b9e84470cb3dc4e5930db4289d791b2c 100644 (file)
--- a/src/test/regress/expected/partition_aggregate.out
+++ b/src/test/regress/expected/partition_aggregate.out
@@ -934,7 +934,7 @@ CREATE TABLE pagg_tab_ml_p3_s2 PARTITION OF pagg_tab_ml_p3 FOR VALUES FROM (5) T
 ALTER TABLE pagg_tab_ml_p3 ATTACH PARTITION pagg_tab_ml_p3_s1 FOR VALUES FROM (0) TO (5);
 ALTER TABLE pagg_tab_ml ATTACH PARTITION pagg_tab_ml_p3 FOR VALUES FROM (20) TO (30);
 INSERT INTO pagg_tab_ml SELECT i % 30, i % 10, to_char(i % 4, 'FM0000') FROM generate_series(0, 29999) i;
-ANALYZE pagg_tab_ml;
+VACUUM (ANALYZE) pagg_tab_ml;
 -- For Parallel Append
 SET max_parallel_workers_per_gather TO 2;
 -- Full aggregation at level 1 as GROUP BY clause matches with PARTITION KEY

diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out
index f2f4008283f7ee37272472347de223d8a3314a9c..f71f76c5cdda9fb39cc2b35e21b277cb45e9fcaa 100644 (file)
--- a/src/test/regress/expected/stats_ext.out
+++ b/src/test/regress/expected/stats_ext.out
@@ -842,7 +842,7 @@ CREATE TABLE mcv_lists (
 -- random data (no MCV list)
 INSERT INTO mcv_lists (a, b, c, filler1)
      SELECT mod(i,37), mod(i,41), mod(i,43), mod(i,47) FROM generate_series(1,5000) s(i);
-ANALYZE mcv_lists;
+VACUUM (ANALYZE) mcv_lists;
 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 AND b = ''1''');
  estimated | actual 
 -----------+--------
@@ -875,7 +875,7 @@ TRUNCATE mcv_lists;
 DROP STATISTICS mcv_lists_stats;
 INSERT INTO mcv_lists (a, b, c, filler1)
      SELECT mod(i,100), mod(i,50), mod(i,25), i FROM generate_series(1,5000) s(i);
-ANALYZE mcv_lists;
+VACUUM (ANALYZE) mcv_lists;
 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 AND b = ''1''');
  estimated | actual 
 -----------+--------
@@ -1175,7 +1175,7 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 AND b =
          1 |     50
 (1 row)
 
-ANALYZE mcv_lists;
+VACUUM (ANALYZE) mcv_lists;
 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 AND b = ''1''');
  estimated | actual 
 -----------+--------
@@ -1192,7 +1192,7 @@ INSERT INTO mcv_lists (a, b, c, filler1)
          (CASE WHEN mod(i,25) = 1  THEN NULL ELSE mod(i,25) END),
          i
      FROM generate_series(1,5000) s(i);
-ANALYZE mcv_lists;
+VACUUM (ANALYZE) mcv_lists;
 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IS NULL AND b IS NULL');
  estimated | actual 
 -----------+--------
@@ -1259,7 +1259,7 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IN (0, 1) AN
 -- test pg_mcv_list_items with a very simple (single item) MCV list
 TRUNCATE mcv_lists;
 INSERT INTO mcv_lists (a, b, c) SELECT 1, 2, 3 FROM generate_series(1,1000) s(i);
-ANALYZE mcv_lists;
+VACUUM (ANALYZE) mcv_lists;
 SELECT m.*
   FROM pg_statistic_ext s, pg_statistic_ext_data d,
        pg_mcv_list_items(d.stxdmcv) m
@@ -1280,7 +1280,7 @@ INSERT INTO mcv_lists (a, b, c, d)
          (CASE WHEN mod(i,2) = 0 THEN NULL ELSE 0 END),
          (CASE WHEN mod(i,2) = 0 THEN NULL ELSE 'x' END)
      FROM generate_series(1,5000) s(i);
-ANALYZE mcv_lists;
+VACUUM (ANALYZE) mcv_lists;
 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b = ''x'' OR d = ''x''');
  estimated | actual 
 -----------+--------

diff --git a/src/test/regress/sql/partition_aggregate.sql b/src/test/regress/sql/partition_aggregate.sql
index dcd6edbad28ce63c96a8fbff6a03152ae807336d..331d92708d001d51fb3369ee87ce2e79e56a227c 100644 (file)
--- a/src/test/regress/sql/partition_aggregate.sql
+++ b/src/test/regress/sql/partition_aggregate.sql
@@ -213,7 +213,7 @@ ALTER TABLE pagg_tab_ml_p3 ATTACH PARTITION pagg_tab_ml_p3_s1 FOR VALUES FROM (0
 ALTER TABLE pagg_tab_ml ATTACH PARTITION pagg_tab_ml_p3 FOR VALUES FROM (20) TO (30);
 
 INSERT INTO pagg_tab_ml SELECT i % 30, i % 10, to_char(i % 4, 'FM0000') FROM generate_series(0, 29999) i;
-ANALYZE pagg_tab_ml;
+VACUUM (ANALYZE) pagg_tab_ml;
 
 -- For Parallel Append
 SET max_parallel_workers_per_gather TO 2;

diff --git a/src/test/regress/sql/stats_ext.sql b/src/test/regress/sql/stats_ext.sql
index f3b652dc4afec76ffdeb2dd24ff23af215b38313..46a219e11eeaa3fcfb478c98872bd6ecc747d8c8 100644 (file)
--- a/src/test/regress/sql/stats_ext.sql
+++ b/src/test/regress/sql/stats_ext.sql
@@ -454,7 +454,7 @@ CREATE TABLE mcv_lists (
 INSERT INTO mcv_lists (a, b, c, filler1)
      SELECT mod(i,37), mod(i,41), mod(i,43), mod(i,47) FROM generate_series(1,5000) s(i);
 
-ANALYZE mcv_lists;
+VACUUM (ANALYZE) mcv_lists;
 
 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 AND b = ''1''');
 
@@ -476,7 +476,7 @@ DROP STATISTICS mcv_lists_stats;
 INSERT INTO mcv_lists (a, b, c, filler1)
      SELECT mod(i,100), mod(i,50), mod(i,25), i FROM generate_series(1,5000) s(i);
 
-ANALYZE mcv_lists;
+VACUUM (ANALYZE) mcv_lists;
 
 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 AND b = ''1''');
 
@@ -589,7 +589,7 @@ ALTER TABLE mcv_lists ALTER COLUMN c TYPE numeric;
 
 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 AND b = ''1''');
 
-ANALYZE mcv_lists;
+VACUUM (ANALYZE) mcv_lists;
 
 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 AND b = ''1''');
 
@@ -605,7 +605,7 @@ INSERT INTO mcv_lists (a, b, c, filler1)
          i
      FROM generate_series(1,5000) s(i);
 
-ANALYZE mcv_lists;
+VACUUM (ANALYZE) mcv_lists;
 
 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IS NULL AND b IS NULL');
 
@@ -635,8 +635,7 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IN (0, 1) AN
 -- test pg_mcv_list_items with a very simple (single item) MCV list
 TRUNCATE mcv_lists;
 INSERT INTO mcv_lists (a, b, c) SELECT 1, 2, 3 FROM generate_series(1,1000) s(i);
-ANALYZE mcv_lists;
-
+VACUUM (ANALYZE) mcv_lists;
 SELECT m.*
   FROM pg_statistic_ext s, pg_statistic_ext_data d,
        pg_mcv_list_items(d.stxdmcv) m
@@ -655,7 +654,7 @@ INSERT INTO mcv_lists (a, b, c, d)
          (CASE WHEN mod(i,2) = 0 THEN NULL ELSE 'x' END)
      FROM generate_series(1,5000) s(i);
 
-ANALYZE mcv_lists;
+VACUUM (ANALYZE) mcv_lists;
 
 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b = ''x'' OR d = ''x''');