1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
|
--
-- BTREE_INDEX
-- test retrieval of min/max keys for each index
--
SELECT b.*
FROM bt_i4_heap b
WHERE b.seqno < 1;
seqno | random
-------+------------
0 | 1935401906
(1 row)
SELECT b.*
FROM bt_i4_heap b
WHERE b.seqno >= 9999;
seqno | random
-------+------------
9999 | 1227676208
(1 row)
SELECT b.*
FROM bt_i4_heap b
WHERE b.seqno = 4500;
seqno | random
-------+------------
4500 | 2080851358
(1 row)
SELECT b.*
FROM bt_name_heap b
WHERE b.seqno < '1'::name;
seqno | random
-------+------------
0 | 1935401906
(1 row)
SELECT b.*
FROM bt_name_heap b
WHERE b.seqno >= '9999'::name;
seqno | random
-------+------------
9999 | 1227676208
(1 row)
SELECT b.*
FROM bt_name_heap b
WHERE b.seqno = '4500'::name;
seqno | random
-------+------------
4500 | 2080851358
(1 row)
SELECT b.*
FROM bt_txt_heap b
WHERE b.seqno < '1'::text;
seqno | random
-------+------------
0 | 1935401906
(1 row)
SELECT b.*
FROM bt_txt_heap b
WHERE b.seqno >= '9999'::text;
seqno | random
-------+------------
9999 | 1227676208
(1 row)
SELECT b.*
FROM bt_txt_heap b
WHERE b.seqno = '4500'::text;
seqno | random
-------+------------
4500 | 2080851358
(1 row)
SELECT b.*
FROM bt_f8_heap b
WHERE b.seqno < '1'::float8;
seqno | random
-------+------------
0 | 1935401906
(1 row)
SELECT b.*
FROM bt_f8_heap b
WHERE b.seqno >= '9999'::float8;
seqno | random
-------+------------
9999 | 1227676208
(1 row)
SELECT b.*
FROM bt_f8_heap b
WHERE b.seqno = '4500'::float8;
seqno | random
-------+------------
4500 | 2080851358
(1 row)
--
-- Check correct optimization of LIKE (special index operator support)
-- for both indexscan and bitmapscan cases
--
set enable_seqscan to false;
set enable_indexscan to true;
set enable_bitmapscan to false;
select proname from pg_proc where proname like E'RI\\_FKey%del' order by 1;
proname
------------------------
RI_FKey_cascade_del
RI_FKey_noaction_del
RI_FKey_restrict_del
RI_FKey_setdefault_del
RI_FKey_setnull_del
(5 rows)
set enable_indexscan to false;
set enable_bitmapscan to true;
select proname from pg_proc where proname like E'RI\\_FKey%del' order by 1;
proname
------------------------
RI_FKey_cascade_del
RI_FKey_noaction_del
RI_FKey_restrict_del
RI_FKey_setdefault_del
RI_FKey_setnull_del
(5 rows)
--
-- Test B-tree page deletion. In particular, deleting a non-leaf page.
--
-- First create a tree that's at least four levels deep. The text inserted
-- is long and poorly compressible. That way only a few index tuples fit on
-- each page, allowing us to get a tall tree with fewer pages.
create table btree_tall_tbl(id int4, t text);
create index btree_tall_idx on btree_tall_tbl (id, t) with (fillfactor = 10);
insert into btree_tall_tbl
select g, g::text || '_' ||
(select string_agg(md5(i::text), '_') from generate_series(1, 50) i)
from generate_series(1, 100) g;
-- Delete most entries, and vacuum. This causes page deletions.
delete from btree_tall_tbl where id < 950;
vacuum btree_tall_tbl;
--
-- Test B-tree insertion with a metapage update (XLOG_BTREE_INSERT_META
-- WAL record type). This happens when a "fast root" page is split.
--
-- The vacuum above should've turned the leaf page into a fast root. We just
-- need to insert some rows to cause the fast root page to split.
insert into btree_tall_tbl (id, t)
select g, repeat('x', 100) from generate_series(1, 500) g;
--
-- Test vacuum_cleanup_index_scale_factor
--
-- Simple create
create table btree_test(a int);
create index btree_idx1 on btree_test(a) with (vacuum_cleanup_index_scale_factor = 40.0);
select reloptions from pg_class WHERE oid = 'btree_idx1'::regclass;
reloptions
------------------------------------------
{vacuum_cleanup_index_scale_factor=40.0}
(1 row)
-- Fail while setting improper values
create index btree_idx_err on btree_test(a) with (vacuum_cleanup_index_scale_factor = -10.0);
ERROR: value -10.0 out of bounds for option "vacuum_cleanup_index_scale_factor"
DETAIL: Valid values are between "0.000000" and "10000000000.000000".
create index btree_idx_err on btree_test(a) with (vacuum_cleanup_index_scale_factor = 100.0);
create index btree_idx_err on btree_test(a) with (vacuum_cleanup_index_scale_factor = 'string');
ERROR: invalid value for floating point option "vacuum_cleanup_index_scale_factor": string
create index btree_idx_err on btree_test(a) with (vacuum_cleanup_index_scale_factor = true);
ERROR: invalid value for floating point option "vacuum_cleanup_index_scale_factor": true
-- Simple ALTER INDEX
alter index btree_idx1 set (vacuum_cleanup_index_scale_factor = 70.0);
select reloptions from pg_class WHERE oid = 'btree_idx1'::regclass;
reloptions
------------------------------------------
{vacuum_cleanup_index_scale_factor=70.0}
(1 row)
|
