1-- This file and its contents are licensed under the Apache License 2.0. 2-- Please see the included NOTICE for copyright information and 3-- LICENSE-APACHE for a copy of the license. 4-- 5-- This test will create chunks in two dimenisions, time (x) and 6-- space (y), where the time dimension is aligned. The figure below 7-- shows the expected result. The chunk number in the figure 8-- indicates the creation order. 9-- 10-- + 11-- + 12-- + +-----+ +-----+ 13-- + | 2 | | 3 | 14-- + | +---+-+ | 15-- + +-----+ 5 |6+-----+ 16-- + | 1 +---+-+-----+ +---------+ 17-- + | | |4| 7 | | 8 | 18-- +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+- 19-- 0 5 10 15 20 20-- 21-- Partitioning: 22-- 23-- Chunk # | time | space 24-- 1 | 3 | 2 25-- 4 | 1 | 3 26-- 5 | 5 | 3 27-- 28CREATE TABLE chunk_test(time integer, temp float8, tag integer, color integer); 29SELECT create_hypertable('chunk_test', 'time', 'tag', 2, chunk_time_interval => 3); 30NOTICE: adding not-null constraint to column "time" 31 create_hypertable 32------------------------- 33 (1,public,chunk_test,t) 34(1 row) 35 36INSERT INTO chunk_test VALUES (4, 24.3, 1, 1); 37SELECT * FROM _timescaledb_catalog.dimension_slice; 38 id | dimension_id | range_start | range_end 39----+--------------+----------------------+------------ 40 1 | 1 | 3 | 6 41 2 | 2 | -9223372036854775808 | 1073741823 42(2 rows) 43 44INSERT INTO chunk_test VALUES (4, 24.3, 2, 1); 45INSERT INTO chunk_test VALUES (10, 24.3, 2, 1); 46SELECT c.table_name AS chunk_name, d.id AS dimension_id, ds.id AS slice_id, range_start, range_end FROM _timescaledb_catalog.chunk c 47LEFT JOIN _timescaledb_catalog.chunk_constraint cc ON (c.id = cc.chunk_id) 48LEFT JOIN _timescaledb_catalog.dimension_slice ds ON (ds.id = cc.dimension_slice_id) 49LEFT JOIN _timescaledb_catalog.dimension d ON (d.id = ds.dimension_id) 50LEFT JOIN _timescaledb_catalog.hypertable h ON (d.hypertable_id = h.id) 51WHERE h.schema_name = 'public' AND h.table_name = 'chunk_test' 52ORDER BY c.id, d.id; 53 chunk_name | dimension_id | slice_id | range_start | range_end 54------------------+--------------+----------+----------------------+--------------------- 55 _hyper_1_1_chunk | 1 | 1 | 3 | 6 56 _hyper_1_1_chunk | 2 | 2 | -9223372036854775808 | 1073741823 57 _hyper_1_2_chunk | 1 | 1 | 3 | 6 58 _hyper_1_2_chunk | 2 | 3 | 1073741823 | 9223372036854775807 59 _hyper_1_3_chunk | 1 | 4 | 9 | 12 60 _hyper_1_3_chunk | 2 | 3 | 1073741823 | 9223372036854775807 61(6 rows) 62 63\c :TEST_DBNAME :ROLE_SUPERUSER 64SELECT set_number_partitions('chunk_test', 3); 65 set_number_partitions 66----------------------- 67 68(1 row) 69 70\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER 71SELECT set_chunk_time_interval('chunk_test', 1::bigint); 72 set_chunk_time_interval 73------------------------- 74 75(1 row) 76 77INSERT INTO chunk_test VALUES (8, 24.3, 11233, 1); 78SELECT set_chunk_time_interval('chunk_test', 5::bigint); 79 set_chunk_time_interval 80------------------------- 81 82(1 row) 83 84SELECT * FROM _timescaledb_catalog.dimension; 85 id | hypertable_id | column_name | column_type | aligned | num_slices | partitioning_func_schema | partitioning_func | interval_length | integer_now_func_schema | integer_now_func 86----+---------------+-------------+-------------+---------+------------+--------------------------+--------------------+-----------------+-------------------------+------------------ 87 2 | 1 | tag | integer | f | 3 | _timescaledb_internal | get_partition_hash | | | 88 1 | 1 | time | integer | t | | | | 5 | | 89(2 rows) 90 91INSERT INTO chunk_test VALUES (7, 24.3, 79669, 1); 92INSERT INTO chunk_test VALUES (8, 24.3, 79669, 1); 93INSERT INTO chunk_test VALUES (10, 24.3, 11233, 1); 94INSERT INTO chunk_test VALUES (16, 24.3, 11233, 1); 95SELECT c.table_name AS chunk_name, d.id AS dimension_id, ds.id AS slice_id, range_start, range_end FROM _timescaledb_catalog.chunk c 96LEFT JOIN _timescaledb_catalog.chunk_constraint cc ON (c.id = cc.chunk_id) 97LEFT JOIN _timescaledb_catalog.dimension_slice ds ON (ds.id = cc.dimension_slice_id) 98LEFT JOIN _timescaledb_catalog.dimension d ON (d.id = ds.dimension_id) 99LEFT JOIN _timescaledb_catalog.hypertable h ON (d.hypertable_id = h.id) 100WHERE h.schema_name = 'public' AND h.table_name = 'chunk_test' 101ORDER BY c.id, d.id; 102 chunk_name | dimension_id | slice_id | range_start | range_end 103------------------+--------------+----------+----------------------+--------------------- 104 _hyper_1_1_chunk | 1 | 1 | 3 | 6 105 _hyper_1_1_chunk | 2 | 2 | -9223372036854775808 | 1073741823 106 _hyper_1_2_chunk | 1 | 1 | 3 | 6 107 _hyper_1_2_chunk | 2 | 3 | 1073741823 | 9223372036854775807 108 _hyper_1_3_chunk | 1 | 4 | 9 | 12 109 _hyper_1_3_chunk | 2 | 3 | 1073741823 | 9223372036854775807 110 _hyper_1_4_chunk | 1 | 5 | 8 | 9 111 _hyper_1_4_chunk | 2 | 6 | -9223372036854775808 | 715827882 112 _hyper_1_5_chunk | 1 | 7 | 6 | 8 113 _hyper_1_5_chunk | 2 | 8 | 715827882 | 1431655764 114 _hyper_1_6_chunk | 1 | 5 | 8 | 9 115 _hyper_1_6_chunk | 2 | 8 | 715827882 | 1431655764 116 _hyper_1_7_chunk | 1 | 4 | 9 | 12 117 _hyper_1_7_chunk | 2 | 6 | -9223372036854775808 | 715827882 118 _hyper_1_8_chunk | 1 | 9 | 15 | 20 119 _hyper_1_8_chunk | 2 | 6 | -9223372036854775808 | 715827882 120(16 rows) 121 122--test the edges of an open partition -- INT_64_MAX and INT_64_MIN. 123CREATE TABLE chunk_test_ends(time bigint, temp float8, tag integer, color integer); 124SELECT create_hypertable('chunk_test_ends', 'time', chunk_time_interval => 5); 125NOTICE: adding not-null constraint to column "time" 126 create_hypertable 127------------------------------ 128 (2,public,chunk_test_ends,t) 129(1 row) 130 131INSERT INTO chunk_test_ends VALUES ((-9223372036854775808)::bigint, 23.1, 11233, 1); 132INSERT INTO chunk_test_ends VALUES (9223372036854775807::bigint, 24.1, 11233, 1); 133--try to hit cache 134INSERT INTO chunk_test_ends VALUES (9223372036854775807::bigint, 24.2, 11233, 1); 135INSERT INTO chunk_test_ends VALUES (9223372036854775807::bigint, 24.3, 11233, 1), (9223372036854775807::bigint, 24.4, 11233, 1); 136INSERT INTO chunk_test_ends VALUES ((-9223372036854775808)::bigint, 23.2, 11233, 1); 137INSERT INTO chunk_test_ends VALUES ((-9223372036854775808)::bigint, 23.3, 11233, 1), ((-9223372036854775808)::bigint, 23.4, 11233, 1); 138SELECT * FROM chunk_test_ends ORDER BY time asc, tag, temp; 139 time | temp | tag | color 140----------------------+------+-------+------- 141 -9223372036854775808 | 23.1 | 11233 | 1 142 -9223372036854775808 | 23.2 | 11233 | 1 143 -9223372036854775808 | 23.3 | 11233 | 1 144 -9223372036854775808 | 23.4 | 11233 | 1 145 9223372036854775807 | 24.1 | 11233 | 1 146 9223372036854775807 | 24.2 | 11233 | 1 147 9223372036854775807 | 24.3 | 11233 | 1 148 9223372036854775807 | 24.4 | 11233 | 1 149(8 rows) 150 151--further tests of set_chunk_time_interval 152CREATE TABLE chunk_test2(time TIMESTAMPTZ, temp float8, tag integer, color integer); 153SELECT create_hypertable('chunk_test2', 'time', 'tag', 2, chunk_time_interval => 3); 154WARNING: unexpected interval: smaller than one second 155NOTICE: adding not-null constraint to column "time" 156 create_hypertable 157-------------------------- 158 (3,public,chunk_test2,t) 159(1 row) 160 161SELECT interval_length 162FROM _timescaledb_catalog.dimension d 163LEFT JOIN _timescaledb_catalog.hypertable h ON (d.hypertable_id = h.id) 164WHERE h.schema_name = 'public' AND h.table_name = 'chunk_test2' 165ORDER BY d.id; 166 interval_length 167----------------- 168 3 169 170(2 rows) 171 172-- should work since time column is non-INT 173SELECT set_chunk_time_interval('chunk_test2', INTERVAL '1 minute'); 174 set_chunk_time_interval 175------------------------- 176 177(1 row) 178 179SELECT interval_length 180FROM _timescaledb_catalog.dimension d 181LEFT JOIN _timescaledb_catalog.hypertable h ON (d.hypertable_id = h.id) 182WHERE h.schema_name = 'public' AND h.table_name = 'chunk_test2' 183ORDER BY d.id; 184 interval_length 185----------------- 186 60000000 187 188(2 rows) 189 190-- should still work for non-INT time columns 191SELECT set_chunk_time_interval('chunk_test2', 1000000); 192 set_chunk_time_interval 193------------------------- 194 195(1 row) 196 197SELECT interval_length 198FROM _timescaledb_catalog.dimension d 199LEFT JOIN _timescaledb_catalog.hypertable h ON (d.hypertable_id = h.id) 200WHERE h.schema_name = 'public' AND h.table_name = 'chunk_test2' 201ORDER BY d.id; 202 interval_length 203----------------- 204 1000000 205 206(2 rows) 207 208\set ON_ERROR_STOP 0 209select set_chunk_time_interval(NULL,NULL::interval); 210ERROR: hypertable cannot be NULL 211-- should fail since time column is an int 212SELECT set_chunk_time_interval('chunk_test', INTERVAL '1 minute'); 213ERROR: invalid interval type for integer dimension 214-- should fail since its not a valid way to represent time 215SELECT set_chunk_time_interval('chunk_test', 'foo'::TEXT); 216ERROR: invalid interval type for integer dimension 217SELECT set_chunk_time_interval('chunk_test', NULL::BIGINT); 218ERROR: invalid interval: an explicit interval must be specified 219SELECT set_chunk_time_interval('chunk_test2', NULL::BIGINT); 220ERROR: invalid interval: an explicit interval must be specified 221SELECT set_chunk_time_interval('chunk_test2', NULL::INTERVAL); 222ERROR: invalid interval: an explicit interval must be specified 223\set ON_ERROR_STOP 1 224