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
|
--
-- 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;
|
