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
|
-- The gist_page_opaque_info() function prints the page's LSN.
-- Use an unlogged index, so that the LSN is predictable.
CREATE UNLOGGED TABLE test_gist AS SELECT point(i,i) p, i::text t FROM
generate_series(1,1000) i;
CREATE INDEX test_gist_idx ON test_gist USING gist (p);
-- Page 0 is the root, the rest are leaf pages
SELECT * FROM gist_page_opaque_info(get_raw_page('test_gist_idx', 0));
lsn | nsn | rightlink | flags
-----+-----+------------+-------
0/1 | 0/0 | 4294967295 | {}
(1 row)
SELECT * FROM gist_page_opaque_info(get_raw_page('test_gist_idx', 1));
lsn | nsn | rightlink | flags
-----+-----+------------+--------
0/1 | 0/0 | 4294967295 | {leaf}
(1 row)
SELECT * FROM gist_page_opaque_info(get_raw_page('test_gist_idx', 2));
lsn | nsn | rightlink | flags
-----+-----+-----------+--------
0/1 | 0/0 | 1 | {leaf}
(1 row)
SELECT * FROM gist_page_items(get_raw_page('test_gist_idx', 0), 'test_gist_idx');
itemoffset | ctid | itemlen | dead | keys
------------+-----------+---------+------+-------------------------------
1 | (1,65535) | 40 | f | (p)=("(185,185),(1,1)")
2 | (2,65535) | 40 | f | (p)=("(370,370),(186,186)")
3 | (3,65535) | 40 | f | (p)=("(555,555),(371,371)")
4 | (4,65535) | 40 | f | (p)=("(740,740),(556,556)")
5 | (5,65535) | 40 | f | (p)=("(870,870),(741,741)")
6 | (6,65535) | 40 | f | (p)=("(1000,1000),(871,871)")
(6 rows)
SELECT * FROM gist_page_items(get_raw_page('test_gist_idx', 1), 'test_gist_idx') LIMIT 5;
itemoffset | ctid | itemlen | dead | keys
------------+-------+---------+------+---------------------
1 | (0,1) | 40 | f | (p)=("(1,1),(1,1)")
2 | (0,2) | 40 | f | (p)=("(2,2),(2,2)")
3 | (0,3) | 40 | f | (p)=("(3,3),(3,3)")
4 | (0,4) | 40 | f | (p)=("(4,4),(4,4)")
5 | (0,5) | 40 | f | (p)=("(5,5),(5,5)")
(5 rows)
-- gist_page_items_bytea prints the raw key data as a bytea. The output of that is
-- platform-dependent (endianness), so omit the actual key data from the output.
SELECT itemoffset, ctid, itemlen FROM gist_page_items_bytea(get_raw_page('test_gist_idx', 0));
itemoffset | ctid | itemlen
------------+-----------+---------
1 | (1,65535) | 40
2 | (2,65535) | 40
3 | (3,65535) | 40
4 | (4,65535) | 40
5 | (5,65535) | 40
6 | (6,65535) | 40
(6 rows)
-- Suppress the DETAIL message, to allow the tests to work across various
-- page sizes and architectures.
\set VERBOSITY terse
-- Failures with non-GiST index.
CREATE INDEX test_gist_btree on test_gist(t);
SELECT gist_page_items(get_raw_page('test_gist_btree', 0), 'test_gist_btree');
ERROR: "test_gist_btree" is not a GiST index
SELECT gist_page_items(get_raw_page('test_gist_btree', 0), 'test_gist_idx');
ERROR: input page is not a valid GiST page
-- Failure with various modes.
-- invalid page size
SELECT gist_page_items_bytea('aaa'::bytea);
ERROR: invalid page size
SELECT gist_page_items('aaa'::bytea, 'test_gist_idx'::regclass);
ERROR: invalid page size
SELECT gist_page_opaque_info('aaa'::bytea);
ERROR: invalid page size
-- invalid special area size
SELECT * FROM gist_page_opaque_info(get_raw_page('test_gist', 0));
ERROR: input page is not a valid GiST page
SELECT gist_page_items_bytea(get_raw_page('test_gist', 0));
ERROR: input page is not a valid GiST page
SELECT gist_page_items_bytea(get_raw_page('test_gist_btree', 0));
ERROR: input page is not a valid GiST page
\set VERBOSITY default
-- Tests with all-zero pages.
SHOW block_size \gset
SELECT gist_page_items_bytea(decode(repeat('00', :block_size), 'hex'));
gist_page_items_bytea
-----------------------
(0 rows)
SELECT gist_page_items(decode(repeat('00', :block_size), 'hex'), 'test_gist_idx'::regclass);
gist_page_items
-----------------
(0 rows)
SELECT gist_page_opaque_info(decode(repeat('00', :block_size), 'hex'));
gist_page_opaque_info
-----------------------
(1 row)
-- Test gist_page_items with included columns.
-- Non-leaf pages contain only the key attributes, and leaf pages contain
-- the included attributes.
ALTER TABLE test_gist ADD COLUMN i int DEFAULT NULL;
CREATE INDEX test_gist_idx_inc ON test_gist
USING gist (p) INCLUDE (t, i);
-- Mask the value of the key attribute to avoid alignment issues.
SELECT regexp_replace(keys, '\(p\)=\("(.*?)"\)', '(p)=("<val>")') AS keys_nonleaf_1
FROM gist_page_items(get_raw_page('test_gist_idx_inc', 0), 'test_gist_idx_inc')
WHERE itemoffset = 1;
keys_nonleaf_1
----------------
(p)=("<val>")
(1 row)
SELECT keys AS keys_leaf_1
FROM gist_page_items(get_raw_page('test_gist_idx_inc', 1), 'test_gist_idx_inc')
WHERE itemoffset = 1;
keys_leaf_1
------------------------------------------------------
(p) INCLUDE (t, i)=("(1,1),(1,1)") INCLUDE (1, null)
(1 row)
DROP TABLE test_gist;
|
