-
Notifications
You must be signed in to change notification settings - Fork 1.1k
Expand file tree
/
Copy pathdrop_rename_hypertable.out
More file actions
217 lines (208 loc) · 18.1 KB
/
Copy pathdrop_rename_hypertable.out
File metadata and controls
217 lines (208 loc) · 18.1 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
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
-- This file and its contents are licensed under the Apache License 2.0.
-- Please see the included NOTICE for copyright information and
-- LICENSE-APACHE for a copy of the license.
\o /dev/null
\ir include/insert_two_partitions.sql
-- This file and its contents are licensed under the Apache License 2.0.
-- Please see the included NOTICE for copyright information and
-- LICENSE-APACHE for a copy of the license.
CREATE TABLE PUBLIC."two_Partitions" (
"timeCustom" BIGINT NOT NULL,
device_id TEXT NOT NULL,
series_0 DOUBLE PRECISION NULL,
series_1 DOUBLE PRECISION NULL,
series_2 DOUBLE PRECISION NULL,
series_bool BOOLEAN NULL
);
CREATE INDEX ON PUBLIC."two_Partitions" (device_id, "timeCustom" DESC NULLS LAST) WHERE device_id IS NOT NULL;
CREATE INDEX ON PUBLIC."two_Partitions" ("timeCustom" DESC NULLS LAST, series_0) WHERE series_0 IS NOT NULL;
CREATE INDEX ON PUBLIC."two_Partitions" ("timeCustom" DESC NULLS LAST, series_1) WHERE series_1 IS NOT NULL;
CREATE INDEX ON PUBLIC."two_Partitions" ("timeCustom" DESC NULLS LAST, series_2) WHERE series_2 IS NOT NULL;
CREATE INDEX ON PUBLIC."two_Partitions" ("timeCustom" DESC NULLS LAST, series_bool) WHERE series_bool IS NOT NULL;
CREATE INDEX ON PUBLIC."two_Partitions" ("timeCustom" DESC NULLS LAST, device_id);
SELECT * FROM create_hypertable('"public"."two_Partitions"'::regclass, 'timeCustom'::name, 'device_id'::name, associated_schema_name=>'_timescaledb_internal'::text, number_partitions => 2, chunk_time_interval=>_timescaledb_functions.interval_to_usec('1 month'));
\set QUIET off
BEGIN;
\COPY public."two_Partitions" FROM 'data/ds1_dev1_1.tsv' NULL AS '';
COMMIT;
INSERT INTO public."two_Partitions"("timeCustom", device_id, series_0, series_1) VALUES
(1257987600000000000, 'dev1', 1.5, 1),
(1257987600000000000, 'dev1', 1.5, 2),
(1257894000000000000, 'dev2', 1.5, 1),
(1257894002000000000, 'dev1', 2.5, 3);
INSERT INTO "two_Partitions"("timeCustom", device_id, series_0, series_1) VALUES
(1257894000000000000, 'dev2', 1.5, 2);
\set QUIET on
\o
SELECT * FROM test.show_columnsp('_timescaledb_internal.%_hyper%');
Relation | Kind | Column | Column type | NotNull
------------------------------------------------------------------------------------+------+-------------+------------------+---------
_timescaledb_internal._hyper_1_1_chunk | r | timeCustom | bigint | t
_timescaledb_internal._hyper_1_1_chunk | r | device_id | text | t
_timescaledb_internal._hyper_1_1_chunk | r | series_0 | double precision | f
_timescaledb_internal._hyper_1_1_chunk | r | series_1 | double precision | f
_timescaledb_internal._hyper_1_1_chunk | r | series_2 | double precision | f
_timescaledb_internal._hyper_1_1_chunk | r | series_bool | boolean | f
_timescaledb_internal."_hyper_1_1_chunk_two_Partitions_device_id_timeCustom_idx" | i | device_id | text | f
_timescaledb_internal."_hyper_1_1_chunk_two_Partitions_device_id_timeCustom_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_1_chunk_two_Partitions_timeCustom_device_id_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_1_chunk_two_Partitions_timeCustom_device_id_idx" | i | device_id | text | f
_timescaledb_internal."_hyper_1_1_chunk_two_Partitions_timeCustom_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_1_chunk_two_Partitions_timeCustom_series_0_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_1_chunk_two_Partitions_timeCustom_series_0_idx" | i | series_0 | double precision | f
_timescaledb_internal."_hyper_1_1_chunk_two_Partitions_timeCustom_series_1_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_1_chunk_two_Partitions_timeCustom_series_1_idx" | i | series_1 | double precision | f
_timescaledb_internal."_hyper_1_1_chunk_two_Partitions_timeCustom_series_2_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_1_chunk_two_Partitions_timeCustom_series_2_idx" | i | series_2 | double precision | f
_timescaledb_internal."_hyper_1_1_chunk_two_Partitions_timeCustom_series_bool_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_1_chunk_two_Partitions_timeCustom_series_bool_idx" | i | series_bool | boolean | f
_timescaledb_internal._hyper_1_2_chunk | r | timeCustom | bigint | t
_timescaledb_internal._hyper_1_2_chunk | r | device_id | text | t
_timescaledb_internal._hyper_1_2_chunk | r | series_0 | double precision | f
_timescaledb_internal._hyper_1_2_chunk | r | series_1 | double precision | f
_timescaledb_internal._hyper_1_2_chunk | r | series_2 | double precision | f
_timescaledb_internal._hyper_1_2_chunk | r | series_bool | boolean | f
_timescaledb_internal."_hyper_1_2_chunk_two_Partitions_device_id_timeCustom_idx" | i | device_id | text | f
_timescaledb_internal."_hyper_1_2_chunk_two_Partitions_device_id_timeCustom_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_2_chunk_two_Partitions_timeCustom_device_id_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_2_chunk_two_Partitions_timeCustom_device_id_idx" | i | device_id | text | f
_timescaledb_internal."_hyper_1_2_chunk_two_Partitions_timeCustom_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_2_chunk_two_Partitions_timeCustom_series_0_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_2_chunk_two_Partitions_timeCustom_series_0_idx" | i | series_0 | double precision | f
_timescaledb_internal."_hyper_1_2_chunk_two_Partitions_timeCustom_series_1_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_2_chunk_two_Partitions_timeCustom_series_1_idx" | i | series_1 | double precision | f
_timescaledb_internal."_hyper_1_2_chunk_two_Partitions_timeCustom_series_2_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_2_chunk_two_Partitions_timeCustom_series_2_idx" | i | series_2 | double precision | f
_timescaledb_internal."_hyper_1_2_chunk_two_Partitions_timeCustom_series_bool_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_2_chunk_two_Partitions_timeCustom_series_bool_idx" | i | series_bool | boolean | f
_timescaledb_internal._hyper_1_3_chunk | r | timeCustom | bigint | t
_timescaledb_internal._hyper_1_3_chunk | r | device_id | text | t
_timescaledb_internal._hyper_1_3_chunk | r | series_0 | double precision | f
_timescaledb_internal._hyper_1_3_chunk | r | series_1 | double precision | f
_timescaledb_internal._hyper_1_3_chunk | r | series_2 | double precision | f
_timescaledb_internal._hyper_1_3_chunk | r | series_bool | boolean | f
_timescaledb_internal."_hyper_1_3_chunk_two_Partitions_device_id_timeCustom_idx" | i | device_id | text | f
_timescaledb_internal."_hyper_1_3_chunk_two_Partitions_device_id_timeCustom_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_3_chunk_two_Partitions_timeCustom_device_id_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_3_chunk_two_Partitions_timeCustom_device_id_idx" | i | device_id | text | f
_timescaledb_internal."_hyper_1_3_chunk_two_Partitions_timeCustom_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_3_chunk_two_Partitions_timeCustom_series_0_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_3_chunk_two_Partitions_timeCustom_series_0_idx" | i | series_0 | double precision | f
_timescaledb_internal."_hyper_1_3_chunk_two_Partitions_timeCustom_series_1_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_3_chunk_two_Partitions_timeCustom_series_1_idx" | i | series_1 | double precision | f
_timescaledb_internal."_hyper_1_3_chunk_two_Partitions_timeCustom_series_2_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_3_chunk_two_Partitions_timeCustom_series_2_idx" | i | series_2 | double precision | f
_timescaledb_internal."_hyper_1_3_chunk_two_Partitions_timeCustom_series_bool_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_3_chunk_two_Partitions_timeCustom_series_bool_idx" | i | series_bool | boolean | f
_timescaledb_internal._hyper_1_4_chunk | r | timeCustom | bigint | t
_timescaledb_internal._hyper_1_4_chunk | r | device_id | text | t
_timescaledb_internal._hyper_1_4_chunk | r | series_0 | double precision | f
_timescaledb_internal._hyper_1_4_chunk | r | series_1 | double precision | f
_timescaledb_internal._hyper_1_4_chunk | r | series_2 | double precision | f
_timescaledb_internal._hyper_1_4_chunk | r | series_bool | boolean | f
_timescaledb_internal."_hyper_1_4_chunk_two_Partitions_device_id_timeCustom_idx" | i | device_id | text | f
_timescaledb_internal."_hyper_1_4_chunk_two_Partitions_device_id_timeCustom_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_4_chunk_two_Partitions_timeCustom_device_id_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_4_chunk_two_Partitions_timeCustom_device_id_idx" | i | device_id | text | f
_timescaledb_internal."_hyper_1_4_chunk_two_Partitions_timeCustom_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_4_chunk_two_Partitions_timeCustom_series_0_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_4_chunk_two_Partitions_timeCustom_series_0_idx" | i | series_0 | double precision | f
_timescaledb_internal."_hyper_1_4_chunk_two_Partitions_timeCustom_series_1_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_4_chunk_two_Partitions_timeCustom_series_1_idx" | i | series_1 | double precision | f
_timescaledb_internal."_hyper_1_4_chunk_two_Partitions_timeCustom_series_2_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_4_chunk_two_Partitions_timeCustom_series_2_idx" | i | series_2 | double precision | f
_timescaledb_internal."_hyper_1_4_chunk_two_Partitions_timeCustom_series_bool_idx" | i | timeCustom | bigint | f
_timescaledb_internal."_hyper_1_4_chunk_two_Partitions_timeCustom_series_bool_idx" | i | series_bool | boolean | f
-- Test that renaming hypertable works
SELECT * FROM test.show_columns('_timescaledb_internal._hyper_1_1_chunk');
Column | Type | NotNull
-------------+------------------+---------
timeCustom | bigint | t
device_id | text | t
series_0 | double precision | f
series_1 | double precision | f
series_2 | double precision | f
series_bool | boolean | f
ALTER TABLE "two_Partitions" RENAME TO "newname";
SELECT * FROM "newname";
timeCustom | device_id | series_0 | series_1 | series_2 | series_bool
---------------------+-----------+----------+----------+----------+-------------
1257894000000000000 | dev1 | 1.5 | 1 | 2 | t
1257894000000000000 | dev1 | 1.5 | 2 | |
1257894000000001000 | dev1 | 2.5 | 3 | |
1257894001000000000 | dev1 | 3.5 | 4 | |
1257894002000000000 | dev1 | 5.5 | 6 | | t
1257894002000000000 | dev1 | 5.5 | 7 | | f
1257894002000000000 | dev1 | 2.5 | 3 | |
1257897600000000000 | dev1 | 4.5 | 5 | | f
1257987600000000000 | dev1 | 1.5 | 1 | |
1257987600000000000 | dev1 | 1.5 | 2 | |
1257894000000000000 | dev2 | 1.5 | 1 | |
1257894000000000000 | dev2 | 1.5 | 2 | |
SELECT * FROM _timescaledb_catalog.hypertable;
id | schema_name | table_name | associated_schema_name | associated_table_prefix | num_dimensions | chunk_sizing_func_schema | chunk_sizing_func_name | chunk_target_size | compression_state | compressed_hypertable_id | status
----+-------------+------------+------------------------+-------------------------+----------------+--------------------------+--------------------------+-------------------+-------------------+--------------------------+--------
1 | public | newname | _timescaledb_internal | _hyper_1 | 2 | _timescaledb_functions | calculate_chunk_interval | 0 | 0 | | 0
\c :TEST_DBNAME :ROLE_SUPERUSER
CREATE SCHEMA "newschema" AUTHORIZATION :ROLE_DEFAULT_PERM_USER;
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER
ALTER TABLE "newname" SET SCHEMA "newschema";
SELECT * FROM "newschema"."newname";
timeCustom | device_id | series_0 | series_1 | series_2 | series_bool
---------------------+-----------+----------+----------+----------+-------------
1257894000000000000 | dev1 | 1.5 | 1 | 2 | t
1257894000000000000 | dev1 | 1.5 | 2 | |
1257894000000001000 | dev1 | 2.5 | 3 | |
1257894001000000000 | dev1 | 3.5 | 4 | |
1257894002000000000 | dev1 | 5.5 | 6 | | t
1257894002000000000 | dev1 | 5.5 | 7 | | f
1257894002000000000 | dev1 | 2.5 | 3 | |
1257897600000000000 | dev1 | 4.5 | 5 | | f
1257987600000000000 | dev1 | 1.5 | 1 | |
1257987600000000000 | dev1 | 1.5 | 2 | |
1257894000000000000 | dev2 | 1.5 | 1 | |
1257894000000000000 | dev2 | 1.5 | 2 | |
SELECT * FROM _timescaledb_catalog.hypertable;
id | schema_name | table_name | associated_schema_name | associated_table_prefix | num_dimensions | chunk_sizing_func_schema | chunk_sizing_func_name | chunk_target_size | compression_state | compressed_hypertable_id | status
----+-------------+------------+------------------------+-------------------------+----------------+--------------------------+--------------------------+-------------------+-------------------+--------------------------+--------
1 | newschema | newname | _timescaledb_internal | _hyper_1 | 2 | _timescaledb_functions | calculate_chunk_interval | 0 | 0 | | 0
DROP TABLE "newschema"."newname";
SELECT * FROM _timescaledb_catalog.hypertable;
id | schema_name | table_name | associated_schema_name | associated_table_prefix | num_dimensions | chunk_sizing_func_schema | chunk_sizing_func_name | chunk_target_size | compression_state | compressed_hypertable_id | status
----+-------------+------------+------------------------+-------------------------+----------------+--------------------------+------------------------+-------------------+-------------------+--------------------------+--------
SELECT schema, name FROM test.relation WHERE schema IN ('public', '_timescaledb_catalog', '_timescaledb_internal');
schema | name
-----------------------+--------------------------------------------------
_timescaledb_catalog | bgw_job
_timescaledb_catalog | chunk
_timescaledb_catalog | chunk_column_stats
_timescaledb_catalog | chunk_constraint
_timescaledb_catalog | chunk_rewrite
_timescaledb_catalog | compression_algorithm
_timescaledb_catalog | compression_chunk_size
_timescaledb_catalog | compression_settings
_timescaledb_catalog | continuous_agg
_timescaledb_catalog | continuous_aggs_bucket_function
_timescaledb_catalog | continuous_aggs_hypertable_invalidation_log
_timescaledb_catalog | continuous_aggs_invalidation_threshold
_timescaledb_catalog | continuous_aggs_jobs_refresh_ranges
_timescaledb_catalog | continuous_aggs_materialization_invalidation_log
_timescaledb_catalog | continuous_aggs_materialization_ranges
_timescaledb_catalog | continuous_aggs_watermark
_timescaledb_catalog | dimension
_timescaledb_catalog | dimension_slice
_timescaledb_catalog | hypertable
_timescaledb_catalog | metadata
_timescaledb_catalog | tablespace
_timescaledb_catalog | telemetry_event
_timescaledb_internal | bgw_job_stat
_timescaledb_internal | bgw_job_stat_history
_timescaledb_internal | bgw_policy_chunk_stats
_timescaledb_internal | compressed_chunk_stats
_timescaledb_internal | hypertable_chunk_local_size
-- Test that renaming ordinary table works
CREATE TABLE renametable (foo int);
ALTER TABLE "renametable" RENAME TO "newname_none_ht";
SELECT * FROM "newname_none_ht";
foo
-----