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

Commit88fc719

Browse files
committed
Add test cases for indexam operations not currently covered.
That includes VACUUM on GIN, GiST and SP-GiST indexes, and B-tree indexeslarge enough to cause page deletions in B-tree. Plus some other specialcases.After this patch, the regression tests generate all different WAL recordtypes. Not all branches within the redo functions are covered, but it's astep forward.
1 parenta016555 commit88fc719

File tree

11 files changed

+247
-3
lines changed

11 files changed

+247
-3
lines changed

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

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -127,3 +127,26 @@ select proname from pg_proc where proname like E'RI\\_FKey%del' order by 1;
127127
RI_FKey_setnull_del
128128
(5 rows)
129129

130+
--
131+
-- Test B-tree page deletion. In particular, deleting a non-leaf page.
132+
--
133+
-- First create a tree that's at least four levels deep. The text inserted
134+
-- is long and poorly compressible. That way only a few index tuples fit on
135+
-- each page, allowing us to get a tall tree with fewer pages.
136+
create table btree_tall_tbl(id int4, t text);
137+
create index btree_tall_idx on btree_tall_tbl (id, t) with (fillfactor = 10);
138+
insert into btree_tall_tbl
139+
select g, g::text || '_' ||
140+
(select string_agg(md5(i::text), '_') from generate_series(1, 50) i)
141+
from generate_series(1, 100) g;
142+
-- Delete most entries, and vacuum. This causes page deletions.
143+
delete from btree_tall_tbl where id < 950;
144+
vacuum btree_tall_tbl;
145+
--
146+
-- Test B-tree insertion with a metapage update (XLOG_BTREE_INSERT_META
147+
-- WAL record type). This happens when a "fast root" page is split.
148+
--
149+
-- The vacuum above should've turned the leaf page into a fast root. We just
150+
-- need to insert some rows to cause the fast root page to split.
151+
insert into btree_tall_tbl (id, t)
152+
select g, repeat('x', 100) from generate_series(1, 500) g;

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

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,23 @@
1+
--
2+
-- Test GIN indexes.
3+
--
4+
-- There are other tests to test different GIN opclassed. This is for testing
5+
-- GIN itself.
6+
-- Create and populate a test table with a GIN index.
7+
create table gin_test_tbl(i int4[]);
8+
create index gin_test_idx on gin_test_tbl using gin (i) with (fastupdate = on);
9+
insert into gin_test_tbl select array[1, 2, g] from generate_series(1, 20000) g;
10+
insert into gin_test_tbl select array[1, 3, g] from generate_series(1, 1000) g;
11+
vacuum gin_test_tbl; -- flush the fastupdate buffers
12+
-- Test vacuuming
13+
delete from gin_test_tbl where i @> array[2];
14+
vacuum gin_test_tbl;
15+
-- Disable fastupdate, and do more insertions. With fastupdate enabled, most
16+
-- insertions (by flushing the list pages) cause page splits. Without
17+
-- fastupdate, we get more churn in the GIN data leaf pages, and exercise the
18+
-- recompression codepaths.
19+
alter index gin_test_idx set (fastupdate = off);
20+
insert into gin_test_tbl select array[1, 2, g] from generate_series(1, 1000) g;
21+
insert into gin_test_tbl select array[1, 3, g] from generate_series(1, 1000) g;
22+
delete from gin_test_tbl where i @> array[2];
23+
vacuum gin_test_tbl;

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

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,19 @@
1+
--
2+
-- Test GiST indexes.
3+
--
4+
-- There are other tests to test different GiST opclasses. This is for
5+
-- testing GiST code itself. Vacuuming in particular.
6+
create table gist_point_tbl(id int4, p point);
7+
create index gist_pointidx on gist_point_tbl using gist(p);
8+
-- Insert enough data to create a tree that's a couple of levels deep.
9+
insert into gist_point_tbl (id, p)
10+
select g, point(g*10, g*10) from generate_series(1, 10000) g;
11+
insert into gist_point_tbl (id, p)
12+
select g+100000, point(g*10+1, g*10+1) from generate_series(1, 10000) g;
13+
-- To test vacuum, delete some entries from all over the index.
14+
delete from gist_point_tbl where id % 2 = 1;
15+
-- And also delete some concentration of values. (GiST doesn't currently
16+
-- attempt to delete pages even when they become empty, but if it did, this
17+
-- would exercise it)
18+
delete from gist_point_tbl where id < 10000;
19+
vacuum gist_point_tbl;

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

Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,39 @@
1+
--
2+
-- Test SP-GiST indexes.
3+
--
4+
-- There are other tests to test different SP-GiST opclasses. This is for
5+
-- testing SP-GiST code itself.
6+
create table spgist_point_tbl(id int4, p point);
7+
create index spgist_point_idx on spgist_point_tbl using spgist(p);
8+
-- Test vacuum-root operation. It gets invoked when the root is also a leaf,
9+
-- i.e. the index is very small.
10+
insert into spgist_point_tbl (id, p)
11+
select g, point(g*10, g*10) from generate_series(1, 10) g;
12+
delete from spgist_point_tbl where id < 5;
13+
vacuum spgist_point_tbl;
14+
-- Insert more data, to make the index a few levels deep.
15+
insert into spgist_point_tbl (id, p)
16+
select g, point(g*10, g*10) from generate_series(1, 10000) g;
17+
insert into spgist_point_tbl (id, p)
18+
select g+100000, point(g*10+1, g*10+1) from generate_series(1, 10000) g;
19+
-- To test vacuum, delete some entries from all over the index.
20+
delete from spgist_point_tbl where id % 2 = 1;
21+
-- And also delete some concentration of values. (SP-GiST doesn't currently
22+
-- attempt to delete pages even when they become empty, but if it did, this
23+
-- would exercise it)
24+
delete from spgist_point_tbl where id < 10000;
25+
vacuum spgist_point_tbl;
26+
-- The point opclass's choose method only uses the spgMatchNode action,
27+
-- so the other actions are not tested by the above. Create an index using
28+
-- text opclass, which uses the others actions.
29+
create table spgist_text_tbl(id int4, t text);
30+
create index spgist_text_idx on spgist_text_tbl using spgist(t);
31+
insert into spgist_text_tbl (id, t)
32+
select g, 'f' || repeat('o', 100) || g from generate_series(1, 10000) g
33+
union all
34+
select g, 'baaaaaaaaaaaaaar' || g from generate_series(1, 1000) g;
35+
-- Do a lot of insertions that have to split an existing node. Hopefully
36+
-- one of these will cause the page to run out of space, causing the inner
37+
-- tuple to be moved to another page.
38+
insert into spgist_text_tbl (id, t)
39+
select -g, 'f' || repeat('o', 100-g) || 'surprise' from generate_series(1, 100) g;

‎src/test/regress/output/misc.source

Lines changed: 6 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -597,6 +597,7 @@ SELECT user_relns() AS user_relns
597597
bt_i4_heap
598598
bt_name_heap
599599
bt_txt_heap
600+
btree_tall_tbl
600601
c
601602
c_star
602603
char_tbl
@@ -622,6 +623,8 @@ SELECT user_relns() AS user_relns
622623
float4_tbl
623624
float8_tbl
624625
func_index_heap
626+
gin_test_tbl
627+
gist_point_tbl
625628
hash_f8_heap
626629
hash_i4_heap
627630
hash_name_heap
@@ -671,6 +674,8 @@ SELECT user_relns() AS user_relns
671674
road
672675
shighway
673676
slow_emp4000
677+
spgist_point_tbl
678+
spgist_text_tbl
674679
street
675680
stud_emp
676681
student
@@ -700,7 +705,7 @@ SELECT user_relns() AS user_relns
700705
tvvmv
701706
varchar_tbl
702707
xacttest
703-
(122 rows)
708+
(127 rows)
704709

705710
SELECT name(equipment(hobby_construct(text 'skywalking', text 'mer')));
706711
name

‎src/test/regress/parallel_schedule

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -83,7 +83,7 @@ test: select_into select_distinct select_distinct_on select_implicit select_havi
8383
# ----------
8484
# Another group of parallel tests
8585
# ----------
86-
test: brin privileges security_label collate matview lock replica_identity rowsecurity
86+
test: bringin gist spgistprivileges security_label collate matview lock replica_identity rowsecurity
8787

8888
# ----------
8989
# Another group of parallel tests

‎src/test/regress/serial_schedule

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -95,6 +95,10 @@ test: update
9595
test: delete
9696
test: namespace
9797
test: prepared_xacts
98+
test: brin
99+
test: gin
100+
test: gist
101+
test: spgist
98102
test: privileges
99103
test: security_label
100104
test: collate
@@ -103,7 +107,6 @@ test: lock
103107
test: replica_identity
104108
test: rowsecurity
105109
test: alter_generic
106-
test: brin
107110
test: misc
108111
test: psql
109112
test: async

‎src/test/regress/sql/btree_index.sql

Lines changed: 28 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -64,3 +64,31 @@ select proname from pg_proc where proname like E'RI\\_FKey%del' order by 1;
6464
set enable_indexscan to false;
6565
set enable_bitmapscan to true;
6666
select pronamefrom pg_procwhere pronamelike E'RI\\_FKey%del'order by1;
67+
68+
--
69+
-- Test B-tree page deletion. In particular, deleting a non-leaf page.
70+
--
71+
72+
-- First create a tree that's at least four levels deep. The text inserted
73+
-- is long and poorly compressible. That way only a few index tuples fit on
74+
-- each page, allowing us to get a tall tree with fewer pages.
75+
createtablebtree_tall_tbl(id int4, ttext);
76+
createindexbtree_tall_idxon btree_tall_tbl (id, t) with (fillfactor=10);
77+
insert into btree_tall_tbl
78+
select g, g::text||'_'||
79+
(select string_agg(md5(i::text),'_')from generate_series(1,50) i)
80+
from generate_series(1,100) g;
81+
82+
-- Delete most entries, and vacuum. This causes page deletions.
83+
deletefrom btree_tall_tblwhere id<950;
84+
vacuum btree_tall_tbl;
85+
86+
--
87+
-- Test B-tree insertion with a metapage update (XLOG_BTREE_INSERT_META
88+
-- WAL record type). This happens when a "fast root" page is split.
89+
--
90+
91+
-- The vacuum above should've turned the leaf page into a fast root. We just
92+
-- need to insert some rows to cause the fast root page to split.
93+
insert into btree_tall_tbl (id, t)
94+
select g, repeat('x',100)from generate_series(1,500) g;

‎src/test/regress/sql/gin.sql

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,29 @@
1+
--
2+
-- Test GIN indexes.
3+
--
4+
-- There are other tests to test different GIN opclassed. This is for testing
5+
-- GIN itself.
6+
7+
-- Create and populate a test table with a GIN index.
8+
createtablegin_test_tbl(i int4[]);
9+
createindexgin_test_idxon gin_test_tbl using gin (i) with (fastupdate=on);
10+
insert into gin_test_tblselect array[1,2, g]from generate_series(1,20000) g;
11+
insert into gin_test_tblselect array[1,3, g]from generate_series(1,1000) g;
12+
13+
vacuum gin_test_tbl;-- flush the fastupdate buffers
14+
15+
-- Test vacuuming
16+
deletefrom gin_test_tblwhere i @> array[2];
17+
vacuum gin_test_tbl;
18+
19+
-- Disable fastupdate, and do more insertions. With fastupdate enabled, most
20+
-- insertions (by flushing the list pages) cause page splits. Without
21+
-- fastupdate, we get more churn in the GIN data leaf pages, and exercise the
22+
-- recompression codepaths.
23+
alterindex gin_test_idxset (fastupdate= off);
24+
25+
insert into gin_test_tblselect array[1,2, g]from generate_series(1,1000) g;
26+
insert into gin_test_tblselect array[1,3, g]from generate_series(1,1000) g;
27+
28+
deletefrom gin_test_tblwhere i @> array[2];
29+
vacuum gin_test_tbl;

‎src/test/regress/sql/gist.sql

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,25 @@
1+
--
2+
-- Test GiST indexes.
3+
--
4+
-- There are other tests to test different GiST opclasses. This is for
5+
-- testing GiST code itself. Vacuuming in particular.
6+
7+
createtablegist_point_tbl(id int4, ppoint);
8+
createindexgist_pointidxon gist_point_tbl using gist(p);
9+
10+
-- Insert enough data to create a tree that's a couple of levels deep.
11+
insert into gist_point_tbl (id, p)
12+
select g,point(g*10, g*10)from generate_series(1,10000) g;
13+
14+
insert into gist_point_tbl (id, p)
15+
select g+100000,point(g*10+1, g*10+1)from generate_series(1,10000) g;
16+
17+
-- To test vacuum, delete some entries from all over the index.
18+
deletefrom gist_point_tblwhere id %2=1;
19+
20+
-- And also delete some concentration of values. (GiST doesn't currently
21+
-- attempt to delete pages even when they become empty, but if it did, this
22+
-- would exercise it)
23+
deletefrom gist_point_tblwhere id<10000;
24+
25+
vacuum gist_point_tbl;

‎src/test/regress/sql/spgist.sql

Lines changed: 50 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,50 @@
1+
--
2+
-- Test SP-GiST indexes.
3+
--
4+
-- There are other tests to test different SP-GiST opclasses. This is for
5+
-- testing SP-GiST code itself.
6+
7+
createtablespgist_point_tbl(id int4, ppoint);
8+
createindexspgist_point_idxon spgist_point_tbl using spgist(p);
9+
10+
-- Test vacuum-root operation. It gets invoked when the root is also a leaf,
11+
-- i.e. the index is very small.
12+
insert into spgist_point_tbl (id, p)
13+
select g,point(g*10, g*10)from generate_series(1,10) g;
14+
deletefrom spgist_point_tblwhere id<5;
15+
vacuum spgist_point_tbl;
16+
17+
-- Insert more data, to make the index a few levels deep.
18+
insert into spgist_point_tbl (id, p)
19+
select g,point(g*10, g*10)from generate_series(1,10000) g;
20+
insert into spgist_point_tbl (id, p)
21+
select g+100000,point(g*10+1, g*10+1)from generate_series(1,10000) g;
22+
23+
-- To test vacuum, delete some entries from all over the index.
24+
deletefrom spgist_point_tblwhere id %2=1;
25+
26+
-- And also delete some concentration of values. (SP-GiST doesn't currently
27+
-- attempt to delete pages even when they become empty, but if it did, this
28+
-- would exercise it)
29+
deletefrom spgist_point_tblwhere id<10000;
30+
31+
vacuum spgist_point_tbl;
32+
33+
34+
-- The point opclass's choose method only uses the spgMatchNode action,
35+
-- so the other actions are not tested by the above. Create an index using
36+
-- text opclass, which uses the others actions.
37+
38+
createtablespgist_text_tbl(id int4, ttext);
39+
createindexspgist_text_idxon spgist_text_tbl using spgist(t);
40+
41+
insert into spgist_text_tbl (id, t)
42+
select g,'f'|| repeat('o',100)|| gfrom generate_series(1,10000) g
43+
union all
44+
select g,'baaaaaaaaaaaaaar'|| gfrom generate_series(1,1000) g;
45+
46+
-- Do a lot of insertions that have to split an existing node. Hopefully
47+
-- one of these will cause the page to run out of space, causing the inner
48+
-- tuple to be moved to another page.
49+
insert into spgist_text_tbl (id, t)
50+
select-g,'f'|| repeat('o',100-g)||'surprise'from generate_series(1,100) g;

0 commit comments

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