-
Notifications
You must be signed in to change notification settings - Fork 1.1k
Expand file tree
/
Copy pathcompress_compbloom_config.out
More file actions
176 lines (161 loc) · 17.6 KB
/
Copy pathcompress_compbloom_config.out
File metadata and controls
176 lines (161 loc) · 17.6 KB
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
-- This file and its contents are licensed under the Timescale License.
-- Please see the included NOTICE for copyright information and
-- LICENSE-TIMESCALE for a copy of the license.
-------------------------------------------------------------------
-- Config tests
-------------------------------------------------------------------
CREATE VIEW settings AS SELECT * FROM _timescaledb_catalog.compression_settings ORDER BY upper(relid::text) COLLATE "C";
CREATE VIEW compressedcols AS select relname,attname,c.oid as reloid,attnum from pg_attribute a, pg_class c where c.oid=a.attrelid and relname like '%compress_hyper_%chunk' order by c.oid asc, a.attnum asc;
CREATE TABLE t(a int, b int, c int, d int, e int, f int, g int, h int, i int);
SELECT create_hypertable('t', 'a');
create_hypertable
-------------------
(1,public,t,t)
-- Should succeed (8 columns max)
ALTER TABLE t SET (timescaledb.compress, timescaledb.compress_orderby = 'b', timescaledb.compress_index = 'bloom("a","b","c","d","e","f","g","h")');
select relid,compress_relid,segmentby,orderby,orderby_desc,orderby_nullsfirst,index from settings
where relid = 't'::regclass and index is not null order by 1,2;
relid | compress_relid | segmentby | orderby | orderby_desc | orderby_nullsfirst | index
-------+----------------+-----------+---------+--------------+--------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
t | | | {b,a} | {f,t} | {f,t} | [{"type": "bloom", "column": ["a", "b", "c", "d", "e", "f", "g", "h"], "source": "config"}, {"type": "minmax", "column": "b", "source": "orderby"}, {"type": "minmax", "column": "a", "source": "orderby"}]
-- Should fail (9 columns)
\set ON_ERROR_STOP 0
ALTER TABLE t SET (timescaledb.compress, timescaledb.compress_orderby = 'b', timescaledb.compress_index = 'bloom("a","b","c","d","e","f","g","h","i")');
ERROR: bloom index has too many columns: 9 > max 8
\set ON_ERROR_STOP 1
-- The ordering of bloom columns in the composite bloom index should be determined by the order of the columns in the CREATE TABLE statement
ALTER TABLE t SET (timescaledb.compress, timescaledb.compress_orderby = 'b', timescaledb.compress_index = 'bloom("h","g","f","e","d","c","b")');
NOTICE: updated compression settings will only apply to future compressions
select relid,compress_relid,segmentby,orderby,orderby_desc,orderby_nullsfirst,index from settings
where relid = 't'::regclass and index is not null order by 1,2;
relid | compress_relid | segmentby | orderby | orderby_desc | orderby_nullsfirst | index
-------+----------------+-----------+---------+--------------+--------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
t | | | {b,a} | {f,t} | {f,t} | [{"type": "bloom", "column": ["b", "c", "d", "e", "f", "g", "h"], "source": "config"}, {"type": "minmax", "column": "b", "source": "orderby"}, {"type": "minmax", "column": "a", "source": "orderby"}]
-- Creating two composite bloom indexes with the same columns in different orders should fail
\set ON_ERROR_STOP 0
ALTER TABLE t SET (timescaledb.compress, timescaledb.compress_orderby = 'b', timescaledb.compress_index = 'bloom("h","g","f"),bloom("f","g","h")');
ERROR: duplicate sparse index configuration ('f','g','h')
\set ON_ERROR_STOP 1
-- Creating a composite bloom index with the same columns should fail
\set ON_ERROR_STOP 0
ALTER TABLE t SET (timescaledb.compress_index = 'bloom("a","b","a")');
ERROR: duplicate column name ('a') in composite bloom index configuration: ('a','b','a')
ALTER TABLE t SET (timescaledb.compress_index = 'bloom("g","g")');
ERROR: duplicate column name ('g') in composite bloom index configuration: ('g','g')
\set ON_ERROR_STOP 1
DROP TABLE t CASCADE;
-- Creating a composite bloom index based on a primary key, and create the same manually again
CREATE TABLE u(a int, b int, c int, d int, PRIMARY KEY (a, b, c));
SELECT create_hypertable('u', 'a');
create_hypertable
-------------------
(3,public,u,t)
ALTER TABLE u SET (timescaledb.compress, timescaledb.compress_orderby = 'b');
select index from settings where relid = 'u'::regclass and index is not null order by 1;
index
------------------------------------------------------------------------------------------------------------------
[{"type": "minmax", "column": "b", "source": "orderby"}, {"type": "minmax", "column": "a", "source": "orderby"}]
INSERT INTO u VALUES (1, 2, 3, 4), (5, 6, 7, 8);
select count(compress_chunk(x)) from show_chunks('u') x;
count
-------
1
select relid,compress_relid,segmentby,orderby,orderby_desc,orderby_nullsfirst,index from settings where index is not null order by 1,2;
relid | compress_relid | segmentby | orderby | orderby_desc | orderby_nullsfirst | index
----------------------------------------+------------------------------------------------+-----------+---------+--------------+--------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
u | | | {b,a} | {f,t} | {f,t} | [{"type": "minmax", "column": "b", "source": "orderby"}, {"type": "minmax", "column": "a", "source": "orderby"}]
_timescaledb_internal._hyper_3_1_chunk | _timescaledb_internal.compress_hyper_4_2_chunk | | {b,a} | {f,t} | {f,t} | [{"type": "bloom", "column": ["a", "b", "c"], "source": "default"}, {"type": "bloom", "column": "c", "source": "default"}, {"type": "minmax", "column": "b", "source": "orderby"}, {"type": "minmax", "column": "a", "source": "orderby"}]
-- Check the auto generated compressed columns
select relname,attname from compressedcols order by 1,2;
relname | attname
--------------------------+-------------------------------
compress_hyper_4_2_chunk | _ts_meta_count
compress_hyper_4_2_chunk | _ts_meta_max_1
compress_hyper_4_2_chunk | _ts_meta_max_2
compress_hyper_4_2_chunk | _ts_meta_min_1
compress_hyper_4_2_chunk | _ts_meta_min_2
compress_hyper_4_2_chunk | regress-test-bloom_c
compress_hyper_4_2_chunk | regress-test-bloom_ed4b_a_b_c
compress_hyper_4_2_chunk | a
compress_hyper_4_2_chunk | b
compress_hyper_4_2_chunk | c
compress_hyper_4_2_chunk | cmax
compress_hyper_4_2_chunk | cmin
compress_hyper_4_2_chunk | ctid
compress_hyper_4_2_chunk | d
compress_hyper_4_2_chunk | tableoid
compress_hyper_4_2_chunk | xmax
compress_hyper_4_2_chunk | xmin
ALTER TABLE u SET (timescaledb.compress_index = 'bloom("a","b","c")');
NOTICE: updated compression settings will only apply to future compressions
select relid,compress_relid,segmentby,orderby,orderby_desc,orderby_nullsfirst,index from settings where index is not null order by 1,2;
relid | compress_relid | segmentby | orderby | orderby_desc | orderby_nullsfirst | index
----------------------------------------+------------------------------------------------+-----------+---------+--------------+--------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
u | | | {b,a} | {f,t} | {f,t} | [{"type": "bloom", "column": ["a", "b", "c"], "source": "config"}, {"type": "minmax", "column": "b", "source": "orderby"}, {"type": "minmax", "column": "a", "source": "orderby"}]
_timescaledb_internal._hyper_3_1_chunk | _timescaledb_internal.compress_hyper_4_2_chunk | | {b,a} | {f,t} | {f,t} | [{"type": "bloom", "column": ["a", "b", "c"], "source": "default"}, {"type": "bloom", "column": "c", "source": "default"}, {"type": "minmax", "column": "b", "source": "orderby"}, {"type": "minmax", "column": "a", "source": "orderby"}]
-- Also in a different order
ALTER TABLE u SET (timescaledb.compress_index = 'bloom("c","b","a")');
NOTICE: updated compression settings will only apply to future compressions
select relid,compress_relid,segmentby,orderby,orderby_desc,orderby_nullsfirst,index from settings where index is not null order by 1,2;
relid | compress_relid | segmentby | orderby | orderby_desc | orderby_nullsfirst | index
----------------------------------------+------------------------------------------------+-----------+---------+--------------+--------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
u | | | {b,a} | {f,t} | {f,t} | [{"type": "bloom", "column": ["a", "b", "c"], "source": "config"}, {"type": "minmax", "column": "b", "source": "orderby"}, {"type": "minmax", "column": "a", "source": "orderby"}]
_timescaledb_internal._hyper_3_1_chunk | _timescaledb_internal.compress_hyper_4_2_chunk | | {b,a} | {f,t} | {f,t} | [{"type": "bloom", "column": ["a", "b", "c"], "source": "default"}, {"type": "bloom", "column": "c", "source": "default"}, {"type": "minmax", "column": "b", "source": "orderby"}, {"type": "minmax", "column": "a", "source": "orderby"}]
DROP TABLE u CASCADE;
-------------------------------------------------------------------
-- Long column names tests
-------------------------------------------------------------------
CREATE TABLE v(a_01234567890123456789 int, b_01234567890123456789 int, c_01234567890123456789 int, d_01234567890123456789 int, e_01234567890123456789 int, f_01234567890123456789 int, g_01234567890123456789 int, h_01234567890123456789 int, i_01234567890123456789 int, primary key (a_01234567890123456789, b_01234567890123456789, c_01234567890123456789, d_01234567890123456789, e_01234567890123456789));
SELECT create_hypertable('v', 'a_01234567890123456789');
create_hypertable
-------------------
(5,public,v,t)
ALTER TABLE v SET (timescaledb.compress, timescaledb.compress_orderby = 'b_01234567890123456789');
select relid,compress_relid,segmentby,orderby,orderby_desc,orderby_nullsfirst,index from settings where index is not null order by 1,2;
relid | compress_relid | segmentby | orderby | orderby_desc | orderby_nullsfirst | index
-------+----------------+-----------+-------------------------------------------------+--------------+--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------
v | | | {b_01234567890123456789,a_01234567890123456789} | {f,t} | {f,t} | [{"type": "minmax", "column": "b_01234567890123456789", "source": "orderby"}, {"type": "minmax", "column": "a_01234567890123456789", "source": "orderby"}]
INSERT INTO v VALUES (1, 2, 3, 4, 5, 6, 7, 8, 9);
select count(compress_chunk(x)) from show_chunks('v') x;
count
-------
1
-- Check the auto generated composite bloom index configuration and column names
select relid,compress_relid,segmentby,orderby,orderby_desc,orderby_nullsfirst,index from settings where index is not null order by 1,2;
relid | compress_relid | segmentby | orderby | orderby_desc | orderby_nullsfirst | index
----------------------------------------+------------------------------------------------+-----------+-------------------------------------------------+--------------+--------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
v | | | {b_01234567890123456789,a_01234567890123456789} | {f,t} | {f,t} | [{"type": "minmax", "column": "b_01234567890123456789", "source": "orderby"}, {"type": "minmax", "column": "a_01234567890123456789", "source": "orderby"}]
_timescaledb_internal._hyper_5_3_chunk | _timescaledb_internal.compress_hyper_6_4_chunk | | {b_01234567890123456789,a_01234567890123456789} | {f,t} | {f,t} | [{"type": "bloom", "column": ["a_01234567890123456789", "b_01234567890123456789", "c_01234567890123456789", "d_01234567890123456789", "e_01234567890123456789"], "source": "default"}, {"type": "bloom", "column": "c_01234567890123456789", "source": "default"}, {"type": "bloom", "column": "d_01234567890123456789", "source": "default"}, {"type": "bloom", "column": "e_01234567890123456789", "source": "default"}, {"type": "minmax", "column": "b_01234567890123456789", "source": "orderby"}, {"type": "minmax", "column": "a_01234567890123456789", "source": "orderby"}]
select relname,attname from compressedcols order by 1,2;
relname | attname
--------------------------+-----------------------------------------------------------------
compress_hyper_6_4_chunk | _ts_meta_count
compress_hyper_6_4_chunk | _ts_meta_max_1
compress_hyper_6_4_chunk | _ts_meta_max_2
compress_hyper_6_4_chunk | _ts_meta_min_1
compress_hyper_6_4_chunk | _ts_meta_min_2
compress_hyper_6_4_chunk | regress-test-bloom_76d9_a_01234567890123456789_b_01234567890123
compress_hyper_6_4_chunk | regress-test-bloom_c_01234567890123456789
compress_hyper_6_4_chunk | regress-test-bloom_d_01234567890123456789
compress_hyper_6_4_chunk | regress-test-bloom_e_01234567890123456789
compress_hyper_6_4_chunk | a_01234567890123456789
compress_hyper_6_4_chunk | b_01234567890123456789
compress_hyper_6_4_chunk | c_01234567890123456789
compress_hyper_6_4_chunk | cmax
compress_hyper_6_4_chunk | cmin
compress_hyper_6_4_chunk | ctid
compress_hyper_6_4_chunk | d_01234567890123456789
compress_hyper_6_4_chunk | e_01234567890123456789
compress_hyper_6_4_chunk | f_01234567890123456789
compress_hyper_6_4_chunk | g_01234567890123456789
compress_hyper_6_4_chunk | h_01234567890123456789
compress_hyper_6_4_chunk | i_01234567890123456789
compress_hyper_6_4_chunk | tableoid
compress_hyper_6_4_chunk | xmax
compress_hyper_6_4_chunk | xmin
-- Make sure the duplicate column detection works for long column names
\set ON_ERROR_STOP 0
ALTER TABLE v SET (timescaledb.compress_index = 'bloom("a_01234567890123456789","b_01234567890123456789","c_01234567890123456789","a_01234567890123456789")');
ERROR: duplicate column name ('a_01234567890123456789') in composite bloom index configuration: ('a_01234567890123456789','b_01234567890123456789','c_01234567890123456789','a_01234567890123456789')
\set ON_ERROR_STOP 1
DROP TABLE v CASCADE;