Reduce disk footprint of brin regression test

Per complaint from Tom.

While at it, throw in some extra tests for nulls as well, and make sure
that the set of data we insert on the second round is not identical to
the first one.  Both measures are intended to improve coverage of the
test.

Also uncomment the ON COMMIT DROP clause on the CREATE TEMP TABLE
commands.  This doesn't have any effect for someone examining the
regression database after the tests are done, but it reduces clutter for
those that execute the script directly.

2 files changed, 24 insertions, 19 deletions

diff --git a/src/test/regress/expected/brin.out b/src/test/regress/expected/brin.out
index f8be27e5e30..61a544efedd 100644
--- a/src/test/regress/expected/brin.out
+++ b/src/test/regress/expected/brin.out
@@ -1,4 +1,3 @@
-SET synchronous_commit = 0;
 CREATE TABLE brintest (byteacol bytea,
 	charcol "char",
 	namecol name,
@@ -24,7 +23,7 @@ CREATE TABLE brintest (byteacol bytea,
 	numericcol numeric,
 	uuidcol uuid,
 	lsncol pg_lsn
-) WITH (fillfactor=50);
+) WITH (fillfactor=10);
 INSERT INTO brintest SELECT
 	repeat(stringu1, 42)::bytea,
 	substr(stringu1, 1, 1)::"char",
@@ -50,7 +49,9 @@ INSERT INTO brintest SELECT
 	tenthous::numeric(36,30) * fivethous * even / (hundred + 1),
 	format('%s%s-%s-%s-%s-%s%s%s', to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'))::uuid,
 	format('%s/%s%s', odd, even, tenthous)::pg_lsn
-FROM tenk1;
+FROM tenk1 LIMIT 5;
+-- throw in some NULL-only tuples too
+INSERT INTO brintest SELECT NULL FROM tenk1 LIMIT 25;
 CREATE INDEX brinidx ON brintest USING brin (
 	byteacol,
 	charcol,
@@ -78,6 +79,7 @@ CREATE INDEX brinidx ON brintest USING brin (
 	uuidcol,
 	lsncol
 ) with (pages_per_range = 1);
+BEGIN;
 CREATE TABLE brinopers (colname name, op text[], value text[],
 	check (cardinality(op) = cardinality(value)));
 INSERT INTO brinopers VALUES ('byteacol', '{>, >=, =, <=, <}', '{ZZAAAA, ZZAAAA, AAAAAA, AAAAAA, AAAAAA}');
@@ -104,7 +106,8 @@ INSERT INTO brinopers VALUES ('macaddrcol', '{>, >=, =, <=, <}', '{ff:fe:00:00:0
 INSERT INTO brinopers VALUES ('bitcol', '{>, >=, =, <=, <}', '{1111111000, 1111111000, 0000000010, 0000000010, 0000000010}');
 INSERT INTO brinopers VALUES ('varbitcol', '{>, >=, =, <=, <}', '{1111111111111000, 1111111111111000, 0000000000000100, 0000000000000100, 0000000000000100}');
 INSERT INTO brinopers VALUES ('uuidcol', '{>, >=, =, <=, <}', '{99989998-9998-9998-9998-999899989998, 99989998-9998-9998-9998-999899989998, 00040004-0004-0004-0004-000400040004, 00040004-0004-0004-0004-000400040004, 00040004-0004-0004-0004-000400040005}');
-INSERT INTO brinopers VALUES ('lsncol', '{>, >=, =, <=, <}', '{198/1999799, 198/1999799, 30/312815, 0/1200, 0/1200}');
+INSERT INTO brinopers VALUES ('lsncol', '{>, >=, =, <=, <, IS, IS NOT}', '{198/1999799, 198/1999799, 30/312815, 0/1200, 0/1200, NULL, NULL}');
+COMMIT;
 DO $x$
 DECLARE
         r record;
@@ -122,7 +125,7 @@ BEGIN
 				-- run the query using the brin index
                 SET enable_seqscan = 0;
                 SET enable_bitmapscan = 1;
-                EXECUTE format('create temp table %s (tid tid) /* ON COMMIT DROP*/', tabname);
+                EXECUTE format('create temp table %s (tid tid) ON COMMIT DROP', tabname);
                 EXECUTE query;
 
 				-- run the query using a seqscan
@@ -130,7 +133,7 @@ BEGIN
                 SET enable_bitmapscan = 0;
 				query = format($y$INSERT INTO %s SELECT ctid FROM brintest WHERE %s %s %L $y$,
                         tabname_ss, r.colname, r.oper, r.value);
-                EXECUTE format('create temp table %s (tid tid) /* ON COMMIT DROP */', tabname_ss);
+                EXECUTE format('create temp table %s (tid tid) ON COMMIT DROP', tabname_ss);
                 EXECUTE query;
 
 				-- make sure both return the same results
@@ -168,12 +171,12 @@ INSERT INTO brintest SELECT
 	tenthous::numeric(36,30) * fivethous * even / (hundred + 1),
 	format('%s%s-%s-%s-%s-%s%s%s', to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'))::uuid,
 	format('%s/%s%s', odd, even, tenthous)::pg_lsn
-FROM tenk1;
+FROM tenk1 LIMIT 5 OFFSET 5;
 SELECT brin_summarize_new_values('brinidx'::regclass);
  brin_summarize_new_values 
 ---------------------------
-                      2000
+                         5
 (1 row)
 
 UPDATE brintest SET int8col = int8col * int4col;
-SET synchronous_commit = 1;
+UPDATE brintest SET textcol = '' WHERE textcol IS NOT NULL;
diff --git a/src/test/regress/sql/brin.sql b/src/test/regress/sql/brin.sql
index 244652f4c40..b1d2c5c8eaa 100644
--- a/src/test/regress/sql/brin.sql
+++ b/src/test/regress/sql/brin.sql
@@ -1,5 +1,3 @@
-SET synchronous_commit = 0;
-
 CREATE TABLE brintest (byteacol bytea,
 	charcol "char",
 	namecol name,
@@ -25,7 +23,7 @@ CREATE TABLE brintest (byteacol bytea,
 	numericcol numeric,
 	uuidcol uuid,
 	lsncol pg_lsn
-) WITH (fillfactor=50);
+) WITH (fillfactor=10);
 
 INSERT INTO brintest SELECT
 	repeat(stringu1, 42)::bytea,
@@ -52,7 +50,10 @@ INSERT INTO brintest SELECT
 	tenthous::numeric(36,30) * fivethous * even / (hundred + 1),
 	format('%s%s-%s-%s-%s-%s%s%s', to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'))::uuid,
 	format('%s/%s%s', odd, even, tenthous)::pg_lsn
-FROM tenk1;
+FROM tenk1 LIMIT 5;
+
+-- throw in some NULL-only tuples too
+INSERT INTO brintest SELECT NULL FROM tenk1 LIMIT 25;
 
 CREATE INDEX brinidx ON brintest USING brin (
 	byteacol,
@@ -82,6 +83,7 @@ CREATE INDEX brinidx ON brintest USING brin (
 	lsncol
 ) with (pages_per_range = 1);
 
+BEGIN;
 CREATE TABLE brinopers (colname name, op text[], value text[],
 	check (cardinality(op) = cardinality(value)));
 
@@ -109,7 +111,8 @@ INSERT INTO brinopers VALUES ('macaddrcol', '{>, >=, =, <=, <}', '{ff:fe:00:00:0
 INSERT INTO brinopers VALUES ('bitcol', '{>, >=, =, <=, <}', '{1111111000, 1111111000, 0000000010, 0000000010, 0000000010}');
 INSERT INTO brinopers VALUES ('varbitcol', '{>, >=, =, <=, <}', '{1111111111111000, 1111111111111000, 0000000000000100, 0000000000000100, 0000000000000100}');
 INSERT INTO brinopers VALUES ('uuidcol', '{>, >=, =, <=, <}', '{99989998-9998-9998-9998-999899989998, 99989998-9998-9998-9998-999899989998, 00040004-0004-0004-0004-000400040004, 00040004-0004-0004-0004-000400040004, 00040004-0004-0004-0004-000400040005}');
-INSERT INTO brinopers VALUES ('lsncol', '{>, >=, =, <=, <}', '{198/1999799, 198/1999799, 30/312815, 0/1200, 0/1200}');
+INSERT INTO brinopers VALUES ('lsncol', '{>, >=, =, <=, <, IS, IS NOT}', '{198/1999799, 198/1999799, 30/312815, 0/1200, 0/1200, NULL, NULL}');
+COMMIT;
 
 DO $x$
 DECLARE
@@ -128,7 +131,7 @@ BEGIN
 				-- run the query using the brin index
                 SET enable_seqscan = 0;
                 SET enable_bitmapscan = 1;
-                EXECUTE format('create temp table %s (tid tid) /* ON COMMIT DROP*/', tabname);
+                EXECUTE format('create temp table %s (tid tid) ON COMMIT DROP', tabname);
                 EXECUTE query;
 
 				-- run the query using a seqscan
@@ -136,7 +139,7 @@ BEGIN
                 SET enable_bitmapscan = 0;
 				query = format($y$INSERT INTO %s SELECT ctid FROM brintest WHERE %s %s %L $y$,
                         tabname_ss, r.colname, r.oper, r.value);
-                EXECUTE format('create temp table %s (tid tid) /* ON COMMIT DROP */', tabname_ss);
+                EXECUTE format('create temp table %s (tid tid) ON COMMIT DROP', tabname_ss);
                 EXECUTE query;
 
 				-- make sure both return the same results
@@ -175,10 +178,9 @@ INSERT INTO brintest SELECT
 	tenthous::numeric(36,30) * fivethous * even / (hundred + 1),
 	format('%s%s-%s-%s-%s-%s%s%s', to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'))::uuid,
 	format('%s/%s%s', odd, even, tenthous)::pg_lsn
-FROM tenk1;
+FROM tenk1 LIMIT 5 OFFSET 5;
 
 SELECT brin_summarize_new_values('brinidx'::regclass);
 
 UPDATE brintest SET int8col = int8col * int4col;
-
-SET synchronous_commit = 1;
+UPDATE brintest SET textcol = '' WHERE textcol IS NOT NULL;