Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit86cf9a5

Browse files
committed
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 surethat the set of data we insert on the second round is not identical tothe first one. Both measures are intended to improve coverage of thetest.Also uncomment the ON COMMIT DROP clause on the CREATE TEMP TABLEcommands. This doesn't have any effect for someone examining theregression database after the tests are done, but it reduces clutter forthose that execute the script directly.
1 parent51f9ea2 commit86cf9a5

File tree

2 files changed

+24
-19
lines changed

2 files changed

+24
-19
lines changed

‎src/test/regress/expected/brin.out

Lines changed: 12 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,3 @@
1-
SET synchronous_commit = 0;
21
CREATE TABLE brintest (byteacol bytea,
32
charcol "char",
43
namecol name,
@@ -24,7 +23,7 @@ CREATE TABLE brintest (byteacol bytea,
2423
numericcol numeric,
2524
uuidcol uuid,
2625
lsncol pg_lsn
27-
) WITH (fillfactor=50);
26+
) WITH (fillfactor=10);
2827
INSERT INTO brintest SELECT
2928
repeat(stringu1, 42)::bytea,
3029
substr(stringu1, 1, 1)::"char",
@@ -50,7 +49,9 @@ INSERT INTO brintest SELECT
5049
tenthous::numeric(36,30) * fivethous * even / (hundred + 1),
5150
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,
5251
format('%s/%s%s', odd, even, tenthous)::pg_lsn
53-
FROM tenk1;
52+
FROM tenk1 LIMIT 5;
53+
-- throw in some NULL-only tuples too
54+
INSERT INTO brintest SELECT NULL FROM tenk1 LIMIT 25;
5455
CREATE INDEX brinidx ON brintest USING brin (
5556
byteacol,
5657
charcol,
@@ -78,6 +79,7 @@ CREATE INDEX brinidx ON brintest USING brin (
7879
uuidcol,
7980
lsncol
8081
) with (pages_per_range = 1);
82+
BEGIN;
8183
CREATE TABLE brinopers (colname name, op text[], value text[],
8284
check (cardinality(op) = cardinality(value)));
8385
INSERT INTO brinopers VALUES ('byteacol', '{>, >=, =, <=, <}', '{ZZAAAA, ZZAAAA, AAAAAA, AAAAAA, AAAAAA}');
@@ -104,7 +106,8 @@ INSERT INTO brinopers VALUES ('macaddrcol', '{>, >=, =, <=, <}', '{ff:fe:00:00:0
104106
INSERT INTO brinopers VALUES ('bitcol', '{>, >=, =, <=, <}', '{1111111000, 1111111000, 0000000010, 0000000010, 0000000010}');
105107
INSERT INTO brinopers VALUES ('varbitcol', '{>, >=, =, <=, <}', '{1111111111111000, 1111111111111000, 0000000000000100, 0000000000000100, 0000000000000100}');
106108
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}');
107-
INSERT INTO brinopers VALUES ('lsncol', '{>, >=, =, <=, <}', '{198/1999799, 198/1999799, 30/312815, 0/1200, 0/1200}');
109+
INSERT INTO brinopers VALUES ('lsncol', '{>, >=, =, <=, <, IS, IS NOT}', '{198/1999799, 198/1999799, 30/312815, 0/1200, 0/1200, NULL, NULL}');
110+
COMMIT;
108111
DO $x$
109112
DECLARE
110113
r record;
@@ -122,15 +125,15 @@ BEGIN
122125
-- run the query using the brin index
123126
SET enable_seqscan = 0;
124127
SET enable_bitmapscan = 1;
125-
EXECUTE format('create temp table %s (tid tid)/*ON COMMIT DROP*/', tabname);
128+
EXECUTE format('create temp table %s (tid tid) ON COMMIT DROP', tabname);
126129
EXECUTE query;
127130

128131
-- run the query using a seqscan
129132
SET enable_seqscan = 1;
130133
SET enable_bitmapscan = 0;
131134
query = format($y$INSERT INTO %s SELECT ctid FROM brintest WHERE %s %s %L $y$,
132135
tabname_ss, r.colname, r.oper, r.value);
133-
EXECUTE format('create temp table %s (tid tid)/*ON COMMIT DROP */', tabname_ss);
136+
EXECUTE format('create temp table %s (tid tid) ON COMMIT DROP', tabname_ss);
134137
EXECUTE query;
135138

136139
-- make sure both return the same results
@@ -168,12 +171,12 @@ INSERT INTO brintest SELECT
168171
tenthous::numeric(36,30) * fivethous * even / (hundred + 1),
169172
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,
170173
format('%s/%s%s', odd, even, tenthous)::pg_lsn
171-
FROM tenk1;
174+
FROM tenk1 LIMIT 5 OFFSET 5;
172175
SELECT brin_summarize_new_values('brinidx'::regclass);
173176
brin_summarize_new_values
174177
---------------------------
175-
2000
178+
5
176179
(1 row)
177180

178181
UPDATE brintest SET int8col = int8col * int4col;
179-
SETsynchronous_commit =1;
182+
UPDATE brintestSETtextcol ='' WHERE textcol IS NOT NULL;

‎src/test/regress/sql/brin.sql

Lines changed: 12 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,3 @@
1-
SET synchronous_commit=0;
2-
31
CREATETABLEbrintest (byteacolbytea,
42
charcol"char",
53
namecol name,
@@ -25,7 +23,7 @@ CREATE TABLE brintest (byteacol bytea,
2523
numericcolnumeric,
2624
uuidcol uuid,
2725
lsncol pg_lsn
28-
) WITH (fillfactor=50);
26+
) WITH (fillfactor=10);
2927

3028
INSERT INTO brintestSELECT
3129
repeat(stringu1,42)::bytea,
@@ -52,7 +50,10 @@ INSERT INTO brintest SELECT
5250
tenthous::numeric(36,30)* fivethous* even/ (hundred+1),
5351
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,
5452
format('%s/%s%s', odd, even, tenthous)::pg_lsn
55-
FROM tenk1;
53+
FROM tenk1LIMIT5;
54+
55+
-- throw in some NULL-only tuples too
56+
INSERT INTO brintestSELECTNULLFROM tenk1LIMIT25;
5657

5758
CREATEINDEXbrinidxON brintest USING brin (
5859
byteacol,
@@ -82,6 +83,7 @@ CREATE INDEX brinidx ON brintest USING brin (
8283
lsncol
8384
) with (pages_per_range=1);
8485

86+
BEGIN;
8587
CREATETABLEbrinopers (colname name, optext[], valuetext[],
8688
check (cardinality(op)= cardinality(value)));
8789

@@ -109,7 +111,8 @@ INSERT INTO brinopers VALUES ('macaddrcol', '{>, >=, =, <=, <}', '{ff:fe:00:00:0
109111
INSERT INTO brinopersVALUES ('bitcol','{>, >=, =, <=, <}','{1111111000, 1111111000, 0000000010, 0000000010, 0000000010}');
110112
INSERT INTO brinopersVALUES ('varbitcol','{>, >=, =, <=, <}','{1111111111111000, 1111111111111000, 0000000000000100, 0000000000000100, 0000000000000100}');
111113
INSERT INTO brinopersVALUES ('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}');
112-
INSERT INTO brinopersVALUES ('lsncol','{>, >=, =, <=, <}','{198/1999799, 198/1999799, 30/312815, 0/1200, 0/1200}');
114+
INSERT INTO brinopersVALUES ('lsncol','{>, >=, =, <=, <, IS, IS NOT}','{198/1999799, 198/1999799, 30/312815, 0/1200, 0/1200, NULL, NULL}');
115+
COMMIT;
113116

114117
DO $x$
115118
DECLARE
@@ -128,15 +131,15 @@ BEGIN
128131
-- run the query using the brin index
129132
SET enable_seqscan=0;
130133
SET enable_bitmapscan=1;
131-
EXECUTE format('create temp table %s (tid tid)/*ON COMMIT DROP*/', tabname);
134+
EXECUTE format('create temp table %s (tid tid) ON COMMIT DROP', tabname);
132135
EXECUTE query;
133136

134137
-- run the query using a seqscan
135138
SET enable_seqscan=1;
136139
SET enable_bitmapscan=0;
137140
query= format($y$INSERT INTO %sSELECT ctidFROM brintestWHERE %s %s %L $y$,
138141
tabname_ss,r.colname,r.oper,r.value);
139-
EXECUTE format('create temp table %s (tid tid)/*ON COMMIT DROP */', tabname_ss);
142+
EXECUTE format('create temp table %s (tid tid) ON COMMIT DROP', tabname_ss);
140143
EXECUTE query;
141144

142145
-- make sure both return the same results
@@ -175,10 +178,9 @@ INSERT INTO brintest SELECT
175178
tenthous::numeric(36,30)* fivethous* even/ (hundred+1),
176179
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,
177180
format('%s/%s%s', odd, even, tenthous)::pg_lsn
178-
FROM tenk1;
181+
FROM tenk1LIMIT5 OFFSET5;
179182

180183
SELECT brin_summarize_new_values('brinidx'::regclass);
181184

182185
UPDATE brintestSET int8col= int8col* int4col;
183-
184-
SET synchronous_commit=1;
186+
UPDATE brintestSET textcol=''WHERE textcolIS NOT NULL;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp