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
|
create extension spgist_name_ops;
select opcname, amvalidate(opc.oid)
from pg_opclass opc join pg_am am on am.oid = opcmethod
where amname = 'spgist' and opcname = 'name_ops';
opcname | amvalidate
----------+------------
name_ops | t
(1 row)
-- warning expected here
select opcname, amvalidate(opc.oid)
from pg_opclass opc join pg_am am on am.oid = opcmethod
where amname = 'spgist' and opcname = 'name_ops_old';
INFO: SP-GiST leaf data type text does not match declared type name
opcname | amvalidate
--------------+------------
name_ops_old | f
(1 row)
create table t(f1 name, f2 integer, f3 text);
create index on t using spgist(f1) include(f2, f3);
\d+ t_f1_f2_f3_idx
Index "public.t_f1_f2_f3_idx"
Column | Type | Key? | Definition | Storage | Stats target
--------+---------+------+------------+----------+--------------
f1 | text | yes | f1 | extended |
f2 | integer | no | f2 | plain |
f3 | text | no | f3 | extended |
spgist, for table "public.t"
insert into t select
proname,
case when length(proname) % 2 = 0 then pronargs else null end,
prosrc from pg_proc;
vacuum analyze t;
explain (costs off)
select * from t
where f1 > 'binary_upgrade_set_n' and f1 < 'binary_upgrade_set_p'
order by 1;
QUERY PLAN
---------------------------------------------------------------------------------------------------
Sort
Sort Key: f1
-> Index Only Scan using t_f1_f2_f3_idx on t
Index Cond: ((f1 > 'binary_upgrade_set_n'::name) AND (f1 < 'binary_upgrade_set_p'::name))
(4 rows)
select * from t
where f1 > 'binary_upgrade_set_n' and f1 < 'binary_upgrade_set_p'
order by 1;
f1 | f2 | f3
------------------------------------------------------+----+------------------------------------------------------
binary_upgrade_set_next_array_pg_type_oid | | binary_upgrade_set_next_array_pg_type_oid
binary_upgrade_set_next_heap_pg_class_oid | | binary_upgrade_set_next_heap_pg_class_oid
binary_upgrade_set_next_heap_relfilenode | 1 | binary_upgrade_set_next_heap_relfilenode
binary_upgrade_set_next_index_pg_class_oid | 1 | binary_upgrade_set_next_index_pg_class_oid
binary_upgrade_set_next_index_relfilenode | | binary_upgrade_set_next_index_relfilenode
binary_upgrade_set_next_multirange_array_pg_type_oid | 1 | binary_upgrade_set_next_multirange_array_pg_type_oid
binary_upgrade_set_next_multirange_pg_type_oid | 1 | binary_upgrade_set_next_multirange_pg_type_oid
binary_upgrade_set_next_pg_authid_oid | | binary_upgrade_set_next_pg_authid_oid
binary_upgrade_set_next_pg_enum_oid | | binary_upgrade_set_next_pg_enum_oid
binary_upgrade_set_next_pg_tablespace_oid | | binary_upgrade_set_next_pg_tablespace_oid
binary_upgrade_set_next_pg_type_oid | | binary_upgrade_set_next_pg_type_oid
binary_upgrade_set_next_toast_pg_class_oid | 1 | binary_upgrade_set_next_toast_pg_class_oid
binary_upgrade_set_next_toast_relfilenode | | binary_upgrade_set_next_toast_relfilenode
(13 rows)
-- Verify clean failure when INCLUDE'd columns result in overlength tuple
-- The error message details are platform-dependent, so show only SQLSTATE
\set VERBOSITY sqlstate
insert into t values(repeat('xyzzy', 12), 42, repeat('xyzzy', 4000));
ERROR: 54000
\set VERBOSITY default
drop index t_f1_f2_f3_idx;
create index on t using spgist(f1 name_ops_old) include(f2, f3);
\d+ t_f1_f2_f3_idx
Index "public.t_f1_f2_f3_idx"
Column | Type | Key? | Definition | Storage | Stats target
--------+---------+------+------------+----------+--------------
f1 | name | yes | f1 | plain |
f2 | integer | no | f2 | plain |
f3 | text | no | f3 | extended |
spgist, for table "public.t"
explain (costs off)
select * from t
where f1 > 'binary_upgrade_set_n' and f1 < 'binary_upgrade_set_p'
order by 1;
QUERY PLAN
---------------------------------------------------------------------------------------------------
Sort
Sort Key: f1
-> Index Only Scan using t_f1_f2_f3_idx on t
Index Cond: ((f1 > 'binary_upgrade_set_n'::name) AND (f1 < 'binary_upgrade_set_p'::name))
(4 rows)
select * from t
where f1 > 'binary_upgrade_set_n' and f1 < 'binary_upgrade_set_p'
order by 1;
f1 | f2 | f3
------------------------------------------------------+----+------------------------------------------------------
binary_upgrade_set_next_array_pg_type_oid | | binary_upgrade_set_next_array_pg_type_oid
binary_upgrade_set_next_heap_pg_class_oid | | binary_upgrade_set_next_heap_pg_class_oid
binary_upgrade_set_next_heap_relfilenode | 1 | binary_upgrade_set_next_heap_relfilenode
binary_upgrade_set_next_index_pg_class_oid | 1 | binary_upgrade_set_next_index_pg_class_oid
binary_upgrade_set_next_index_relfilenode | | binary_upgrade_set_next_index_relfilenode
binary_upgrade_set_next_multirange_array_pg_type_oid | 1 | binary_upgrade_set_next_multirange_array_pg_type_oid
binary_upgrade_set_next_multirange_pg_type_oid | 1 | binary_upgrade_set_next_multirange_pg_type_oid
binary_upgrade_set_next_pg_authid_oid | | binary_upgrade_set_next_pg_authid_oid
binary_upgrade_set_next_pg_enum_oid | | binary_upgrade_set_next_pg_enum_oid
binary_upgrade_set_next_pg_tablespace_oid | | binary_upgrade_set_next_pg_tablespace_oid
binary_upgrade_set_next_pg_type_oid | | binary_upgrade_set_next_pg_type_oid
binary_upgrade_set_next_toast_pg_class_oid | 1 | binary_upgrade_set_next_toast_pg_class_oid
binary_upgrade_set_next_toast_relfilenode | | binary_upgrade_set_next_toast_relfilenode
(13 rows)
\set VERBOSITY sqlstate
insert into t values(repeat('xyzzy', 12), 42, repeat('xyzzy', 4000));
ERROR: 54000
\set VERBOSITY default
|
