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
|
-- The gist_page_opaque_info() function prints the page's LSN. Normally,
-- that's constant 1 (GistBuildLSN) on every page of a freshly built GiST
-- index. But with wal_level=minimal, the whole relation is dumped to WAL at
-- the end of the transaction if it's smaller than wal_skip_threshold, which
-- updates the LSNs. Wrap the tests on gist_page_opaque_info() in the
-- same transaction with the CREATE INDEX so that we see the LSNs before
-- they are possibly overwritten at end of transaction.
BEGIN;
-- Create a test table and GiST index.
CREATE 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)
COMMIT;
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;
|
