Stabilize test of BRIN parallel create

tvondra · tvondra · commit 0bd4b0689ba1 · 2024-04-17T16:14:47.000+02:00
As explained in 4d916dd, the test instability is caused by delayed cleanup of deleted rows. This commit removes the DELETE, stabilizing the test without accidentally disabling parallel builds. The intent of the delete however was to produce empty ranges, and test that the parallel index build populates those correctly. But there's another way to create empty ranges - partial indexes, which does not rely on cleanup of deleted rows. Idea to use partial indexes by Matthias van de Meent, patch by me. Discussion: https://postgr.es/m/95d9cd43-5a92-407c-b7e4-54cd303630fe%40enterprisedb.com
diff --git a/contrib/pageinspect/expected/brin.out b/contrib/pageinspect/expected/brin.out
@@ -117,10 +117,6 @@ SELECT (CASE WHEN (mod(i,231) = 0) OR (i BETWEEN 3500 AND 4000) THEN NULL ELSE i
        (CASE WHEN (mod(i,233) = 0) OR (i BETWEEN 3750 AND 4250) THEN NULL ELSE md5(i::text) END),
        (CASE WHEN (mod(i,233) = 0) OR (i BETWEEN 3850 AND 4500) THEN NULL ELSE (i/100) + mod(i,8) END)
   FROM generate_series(1,5000) S(i);
--- Delete a couple pages, to make the ranges empty.
-DELETE FROM brin_parallel_test WHERE a BETWEEN 1000 and 1500;
--- Vacuum to remove the tuples and make the ranges actually empty.
-VACUUM brin_parallel_test;
 -- Build an index with different opclasses - minmax, bloom and minmax-multi.
 --
 -- For minmax and opclass this is simple, but for minmax-multi we need to be
@@ -135,7 +131,8 @@ VACUUM brin_parallel_test;
 SET max_parallel_maintenance_workers = 0;
 CREATE INDEX brin_test_serial_idx ON brin_parallel_test
  USING brin (a int4_minmax_ops, a int4_bloom_ops, b, c int8_minmax_multi_ops)
-  WITH (pages_per_range=7);
+  WITH (pages_per_range=7)
+ WHERE NOT (a BETWEEN 1000 and 1500);
 -- build index using parallelism
 --
 -- Set a couple parameters to force parallel build for small table. There's a
@@ -147,7 +144,8 @@ SET max_parallel_maintenance_workers = 4;
 SET maintenance_work_mem = '128MB';
 CREATE INDEX brin_test_parallel_idx ON brin_parallel_test
  USING brin (a int4_minmax_ops, a int4_bloom_ops, b, c int8_minmax_multi_ops)
-  WITH (pages_per_range=7);
+  WITH (pages_per_range=7)
+ WHERE NOT (a BETWEEN 1000 and 1500);
 SELECT relname, relpages
   FROM pg_class
  WHERE relname IN ('brin_test_serial_idx', 'brin_test_parallel_idx')
@@ -180,7 +178,8 @@ DROP INDEX brin_test_parallel_idx;
 SET max_parallel_workers = 0;
 CREATE INDEX brin_test_parallel_idx ON brin_parallel_test
  USING brin (a int4_minmax_ops, a int4_bloom_ops, b, c int8_minmax_multi_ops)
-  WITH (pages_per_range=7);
+  WITH (pages_per_range=7)
+ WHERE NOT (a BETWEEN 1000 and 1500);
 SELECT relname, relpages
   FROM pg_class
  WHERE relname IN ('brin_test_serial_idx', 'brin_test_parallel_idx')
diff --git a/contrib/pageinspect/sql/brin.sql b/contrib/pageinspect/sql/brin.sql
@@ -66,12 +66,6 @@ SELECT (CASE WHEN (mod(i,231) = 0) OR (i BETWEEN 3500 AND 4000) THEN NULL ELSE i
        (CASE WHEN (mod(i,233) = 0) OR (i BETWEEN 3850 AND 4500) THEN NULL ELSE (i/100) + mod(i,8) END)
   FROM generate_series(1,5000) S(i);
 
--- Delete a couple pages, to make the ranges empty.
-DELETE FROM brin_parallel_test WHERE a BETWEEN 1000 and 1500;
-
--- Vacuum to remove the tuples and make the ranges actually empty.
-VACUUM brin_parallel_test;
-
 -- Build an index with different opclasses - minmax, bloom and minmax-multi.
 --
 -- For minmax and opclass this is simple, but for minmax-multi we need to be
@@ -87,7 +81,8 @@ VACUUM brin_parallel_test;
 SET max_parallel_maintenance_workers = 0;
 CREATE INDEX brin_test_serial_idx ON brin_parallel_test
  USING brin (a int4_minmax_ops, a int4_bloom_ops, b, c int8_minmax_multi_ops)
-  WITH (pages_per_range=7);
+  WITH (pages_per_range=7)
+ WHERE NOT (a BETWEEN 1000 and 1500);
 
 -- build index using parallelism
 --
@@ -100,7 +95,8 @@ SET max_parallel_maintenance_workers = 4;
 SET maintenance_work_mem = '128MB';
 CREATE INDEX brin_test_parallel_idx ON brin_parallel_test
  USING brin (a int4_minmax_ops, a int4_bloom_ops, b, c int8_minmax_multi_ops)
-  WITH (pages_per_range=7);
+  WITH (pages_per_range=7)
+ WHERE NOT (a BETWEEN 1000 and 1500);
 
 SELECT relname, relpages
   FROM pg_class
@@ -126,7 +122,8 @@ DROP INDEX brin_test_parallel_idx;
 SET max_parallel_workers = 0;
 CREATE INDEX brin_test_parallel_idx ON brin_parallel_test
  USING brin (a int4_minmax_ops, a int4_bloom_ops, b, c int8_minmax_multi_ops)
-  WITH (pages_per_range=7);
+  WITH (pages_per_range=7)
+ WHERE NOT (a BETWEEN 1000 and 1500);
 
 SELECT relname, relpages
   FROM pg_class
