/
alter.out
543 lines (500 loc) · 23.7 KB
/
alter.out
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
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
-- DROP a table's column before making it a hypertable
CREATE TABLE alter_before(id serial, time timestamp, temp float, colorid integer, notes text, notes_2 text);
ALTER TABLE alter_before DROP COLUMN id;
ALTER TABLE alter_before ALTER COLUMN temp SET (n_distinct = 10);
ALTER TABLE alter_before ALTER COLUMN colorid SET (n_distinct = 11);
ALTER TABLE alter_before ALTER COLUMN colorid RESET (n_distinct);
ALTER TABLE alter_before ALTER COLUMN temp SET STATISTICS 100;
ALTER TABLE alter_before ALTER COLUMN notes SET STORAGE EXTERNAL;
SELECT create_hypertable('alter_before', 'time', chunk_time_interval => 2628000000000);
NOTICE: adding not-null constraint to column "time"
create_hypertable
---------------------------
(1,public,alter_before,t)
(1 row)
INSERT INTO alter_before VALUES ('2017-03-22T09:18:22', 23.5, 1);
SELECT * FROM alter_before;
time | temp | colorid | notes | notes_2
--------------------------+------+---------+-------+---------
Wed Mar 22 09:18:22 2017 | 23.5 | 1 | |
(1 row)
-- Show that deleted column is marked as dropped and that attnums are
-- now different for the root table and the chunk
SELECT c.relname, a.attname, a.attnum, a.attoptions, a.attstattarget, a.attstorage FROM pg_attribute a, pg_class c
WHERE a.attrelid = c.oid
AND (c.relname LIKE '_hyper_1%_chunk' OR c.relname = 'alter_before')
AND a.attnum > 0
ORDER BY c.relname, a.attnum;
relname | attname | attnum | attoptions | attstattarget | attstorage
------------------+------------------------------+--------+-----------------+---------------+------------
_hyper_1_1_chunk | time | 1 | | -1 | p
_hyper_1_1_chunk | temp | 2 | {n_distinct=10} | 100 | p
_hyper_1_1_chunk | colorid | 3 | | -1 | p
_hyper_1_1_chunk | notes | 4 | | -1 | e
_hyper_1_1_chunk | notes_2 | 5 | | -1 | x
alter_before | ........pg.dropped.1........ | 1 | | 0 | p
alter_before | time | 2 | | -1 | p
alter_before | temp | 3 | {n_distinct=10} | 100 | p
alter_before | colorid | 4 | | -1 | p
alter_before | notes | 5 | | -1 | e
alter_before | notes_2 | 6 | | -1 | x
(11 rows)
-- DROP a table's column after making it a hypertable and having data
CREATE TABLE alter_after(id serial, time timestamp, temp float, colorid integer, notes text, notes_2 text);
SELECT create_hypertable('alter_after', 'time', chunk_time_interval => 2628000000000);
NOTICE: adding not-null constraint to column "time"
create_hypertable
--------------------------
(2,public,alter_after,t)
(1 row)
-- Create first chunk
INSERT INTO alter_after (time, temp, colorid) VALUES ('2017-03-22T09:18:22', 23.5, 1);
ALTER TABLE alter_after DROP COLUMN id;
ALTER TABLE alter_after ALTER COLUMN temp SET (n_distinct = 10);
ALTER TABLE alter_after ALTER COLUMN colorid SET (n_distinct = 11);
ALTER TABLE alter_after ALTER COLUMN colorid RESET (n_distinct);
ALTER TABLE alter_after ALTER COLUMN colorid SET STATISTICS 101;
ALTER TABLE alter_after ALTER COLUMN notes_2 SET STORAGE EXTERNAL;
-- Creating new chunks after dropping a column should work just fine
INSERT INTO alter_after VALUES ('2017-03-22T09:18:23', 21.5, 1),
('2017-05-22T09:18:22', 36.2, 2),
('2017-05-22T09:18:23', 15.2, 2);
-- Make sure tuple conversion also works with COPY
\COPY alter_after FROM 'data/alter.tsv' NULL AS '';
-- Data should look OK
SELECT * FROM alter_after;
time | temp | colorid | notes | notes_2
--------------------------+------+---------+-------+---------
Wed Mar 22 09:18:22 2017 | 23.5 | 1 | |
Wed Mar 22 09:18:23 2017 | 21.5 | 1 | |
Mon May 22 09:18:22 2017 | 36.2 | 2 | |
Mon May 22 09:18:23 2017 | 15.2 | 2 | |
Tue Aug 22 09:19:22 2017 | 21.4 | 3 | nr1 | n2r1
Wed Aug 23 09:20:17 2017 | 31.5 | 2 | nr2 | n2r2
(6 rows)
-- Show that attnums are different for chunks created after DROP
-- column
SELECT c.relname, a.attname, a.attnum FROM pg_attribute a, pg_class c
WHERE a.attrelid = c.oid
AND (c.relname LIKE '_hyper_2%_chunk' OR c.relname = 'alter_after')
AND a.attnum > 0
ORDER BY c.relname, a.attnum;
relname | attname | attnum
------------------+------------------------------+--------
_hyper_2_2_chunk | ........pg.dropped.1........ | 1
_hyper_2_2_chunk | time | 2
_hyper_2_2_chunk | temp | 3
_hyper_2_2_chunk | colorid | 4
_hyper_2_2_chunk | notes | 5
_hyper_2_2_chunk | notes_2 | 6
_hyper_2_3_chunk | time | 1
_hyper_2_3_chunk | temp | 2
_hyper_2_3_chunk | colorid | 3
_hyper_2_3_chunk | notes | 4
_hyper_2_3_chunk | notes_2 | 5
_hyper_2_4_chunk | time | 1
_hyper_2_4_chunk | temp | 2
_hyper_2_4_chunk | colorid | 3
_hyper_2_4_chunk | notes | 4
_hyper_2_4_chunk | notes_2 | 5
alter_after | ........pg.dropped.1........ | 1
alter_after | time | 2
alter_after | temp | 3
alter_after | colorid | 4
alter_after | notes | 5
alter_after | notes_2 | 6
(22 rows)
-- Add an ID column again
ALTER TABLE alter_after ADD COLUMN id serial;
INSERT INTO alter_after (time, temp, colorid) VALUES ('2017-08-22T09:19:14', 12.5, 3);
--test thing that we are allowed to do on chunks
ALTER TABLE _timescaledb_internal._hyper_2_3_chunk ALTER COLUMN temp RESET (n_distinct);
ALTER TABLE _timescaledb_internal._hyper_2_4_chunk ALTER COLUMN temp SET (n_distinct = 20);
ALTER TABLE _timescaledb_internal._hyper_2_4_chunk ALTER COLUMN temp SET STATISTICS 201;
ALTER TABLE _timescaledb_internal._hyper_2_4_chunk ALTER COLUMN notes SET STORAGE EXTERNAL;
SELECT c.relname, a.attname, a.attnum, a.attoptions, a.attstattarget, a.attstorage FROM pg_attribute a, pg_class c
WHERE a.attrelid = c.oid
AND (c.relname LIKE '_hyper_2%_chunk' OR c.relname = 'alter_after')
AND a.attnum > 0
ORDER BY c.relname, a.attnum;
relname | attname | attnum | attoptions | attstattarget | attstorage
------------------+------------------------------+--------+-----------------+---------------+------------
_hyper_2_2_chunk | ........pg.dropped.1........ | 1 | | 0 | p
_hyper_2_2_chunk | time | 2 | | -1 | p
_hyper_2_2_chunk | temp | 3 | {n_distinct=10} | -1 | p
_hyper_2_2_chunk | colorid | 4 | | 101 | p
_hyper_2_2_chunk | notes | 5 | | -1 | x
_hyper_2_2_chunk | notes_2 | 6 | | -1 | e
_hyper_2_2_chunk | id | 7 | | -1 | p
_hyper_2_3_chunk | time | 1 | | -1 | p
_hyper_2_3_chunk | temp | 2 | | -1 | p
_hyper_2_3_chunk | colorid | 3 | | 101 | p
_hyper_2_3_chunk | notes | 4 | | -1 | x
_hyper_2_3_chunk | notes_2 | 5 | | -1 | e
_hyper_2_3_chunk | id | 6 | | -1 | p
_hyper_2_4_chunk | time | 1 | | -1 | p
_hyper_2_4_chunk | temp | 2 | {n_distinct=20} | 201 | p
_hyper_2_4_chunk | colorid | 3 | | 101 | p
_hyper_2_4_chunk | notes | 4 | | -1 | e
_hyper_2_4_chunk | notes_2 | 5 | | -1 | e
_hyper_2_4_chunk | id | 6 | | -1 | p
alter_after | ........pg.dropped.1........ | 1 | | 0 | p
alter_after | time | 2 | | -1 | p
alter_after | temp | 3 | {n_distinct=10} | -1 | p
alter_after | colorid | 4 | | 101 | p
alter_after | notes | 5 | | -1 | x
alter_after | notes_2 | 6 | | -1 | e
alter_after | id | 7 | | -1 | p
(26 rows)
SELECT * FROM alter_after;
time | temp | colorid | notes | notes_2 | id
--------------------------+------+---------+-------+---------+----
Wed Mar 22 09:18:22 2017 | 23.5 | 1 | | | 1
Wed Mar 22 09:18:23 2017 | 21.5 | 1 | | | 2
Mon May 22 09:18:22 2017 | 36.2 | 2 | | | 3
Mon May 22 09:18:23 2017 | 15.2 | 2 | | | 4
Tue Aug 22 09:19:22 2017 | 21.4 | 3 | nr1 | n2r1 | 5
Wed Aug 23 09:20:17 2017 | 31.5 | 2 | nr2 | n2r2 | 6
Tue Aug 22 09:19:14 2017 | 12.5 | 3 | | | 7
(7 rows)
-- Need superuser to ALTER chunks in _timescaledb_internal schema
\c single :ROLE_SUPERUSER
SELECT * FROM _timescaledb_catalog.chunk WHERE id = 2;
id | hypertable_id | schema_name | table_name
----+---------------+-----------------------+------------------
2 | 2 | _timescaledb_internal | _hyper_2_2_chunk
(1 row)
-- Rename chunk
ALTER TABLE _timescaledb_internal._hyper_2_2_chunk RENAME TO new_chunk_name;
SELECT * FROM _timescaledb_catalog.chunk WHERE id = 2;
id | hypertable_id | schema_name | table_name
----+---------------+-----------------------+----------------
2 | 2 | _timescaledb_internal | new_chunk_name
(1 row)
-- Set schema
ALTER TABLE _timescaledb_internal.new_chunk_name SET SCHEMA public;
SELECT * FROM _timescaledb_catalog.chunk WHERE id = 2;
id | hypertable_id | schema_name | table_name
----+---------------+-------------+----------------
2 | 2 | public | new_chunk_name
(1 row)
-- Test that we cannot rename chunk columns
\set ON_ERROR_STOP 0
ALTER TABLE public.new_chunk_name RENAME COLUMN time TO newtime;
ERROR: cannot rename column "time" of hypertable chunk "new_chunk_name"
\set ON_ERROR_STOP 1
-- Test that we can set tablespace of a hypertable
\c single :ROLE_SUPERUSER
SET client_min_messages = ERROR;
DROP TABLESPACE IF EXISTS tablespace1;
DROP TABLESPACE IF EXISTS tablespace2;
SET client_min_messages = NOTICE;
--test hypertable with tables space
CREATE TABLESPACE tablespace1 OWNER :ROLE_DEFAULT_PERM_USER LOCATION :TEST_TABLESPACE1_PATH;
CREATE TABLESPACE tablespace2 OWNER :ROLE_DEFAULT_PERM_USER LOCATION :TEST_TABLESPACE2_PATH;
\c single :ROLE_DEFAULT_PERM_USER
-- Test that we cannot directly change chunk tablespace
\set ON_ERROR_STOP 0
ALTER TABLE public.new_chunk_name SET TABLESPACE tablespace1;
ERROR: operation not supported on chunk tables
\set ON_ERROR_STOP 1
-- drop all tables to make checking the tests below easier
DROP TABLE alter_before;
DROP TABLE alter_after;
-- should return 0 rows
SELECT tablename, tablespace FROM pg_tables
WHERE tablename = 'hyper_in_space' OR tablename LIKE '\_hyper\__\__\_chunk' ORDER BY tablename;
tablename | tablespace
-----------+------------
(0 rows)
CREATE TABLE hyper_in_space(time bigint, temp float, device int);
SELECT create_hypertable('hyper_in_space', 'time', 'device', 4, chunk_time_interval=>1);
NOTICE: adding not-null constraint to column "time"
create_hypertable
-----------------------------
(3,public,hyper_in_space,t)
(1 row)
INSERT INTO hyper_in_space(time, temp, device) VALUES (1, 20, 1);
INSERT INTO hyper_in_space(time, temp, device) VALUES (3, 21, 2);
INSERT INTO hyper_in_space(time, temp, device) VALUES (5, 23, 1);
SELECT tablename FROM pg_tables WHERE tablespace = 'tablespace1' ORDER BY tablename;
tablename
-----------
(0 rows)
SET default_tablespace = tablespace1;
-- should be inserted in tablespace1 which is now default
INSERT INTO hyper_in_space(time, temp, device) VALUES (11, 24, 3);
SELECT tablename, tablespace FROM pg_tables
WHERE tablename = 'hyper_in_space' OR tablename LIKE '\_hyper\__\__\_chunk' ORDER BY tablename;
tablename | tablespace
------------------+-------------
_hyper_3_5_chunk |
_hyper_3_6_chunk |
_hyper_3_7_chunk |
_hyper_3_8_chunk | tablespace1
hyper_in_space |
(5 rows)
SET default_tablespace TO DEFAULT;
ALTER TABLE hyper_in_space SET TABLESPACE tablespace1;
SELECT tablename FROM pg_tables WHERE tablespace = 'tablespace1' ORDER BY tablename;
tablename
------------------
_hyper_3_5_chunk
_hyper_3_6_chunk
_hyper_3_7_chunk
_hyper_3_8_chunk
hyper_in_space
(5 rows)
-- should be inserted in an existing chunk in the new tablespace,
-- no new chunks
INSERT INTO hyper_in_space(time, temp, device) VALUES (5, 27, 1);
-- the new chunk should be create in the new tablespace
INSERT INTO hyper_in_space(time, temp, device) VALUES (8, 24, 2);
SELECT tablename, tablespace FROM pg_tables
WHERE tablename = 'hyper_in_space' OR tablename LIKE '\_hyper\__\__\_chunk' ORDER BY tablename;
tablename | tablespace
------------------+-------------
_hyper_3_5_chunk | tablespace1
_hyper_3_6_chunk | tablespace1
_hyper_3_7_chunk | tablespace1
_hyper_3_8_chunk | tablespace1
_hyper_3_9_chunk | tablespace1
hyper_in_space | tablespace1
(6 rows)
-- should not fail (unlike attach_tablespace)
ALTER TABLE hyper_in_space SET TABLESPACE tablespace1;
\set ON_ERROR_STOP 0
-- not an empty tablespace
DROP TABLESPACE tablespace1;
ERROR: tablespace "tablespace1" is still attached to 1 hypertables
\set ON_ERROR_STOP 1
SELECT drop_chunks(20, 'hyper_in_space');
drop_chunks
-------------
(1 row)
SELECT tablename, tablespace FROM pg_tables WHERE tablespace = 'tablespace1' ORDER BY tablename;
tablename | tablespace
----------------+-------------
hyper_in_space | tablespace1
(1 row)
\set ON_ERROR_STOP 0
-- should not be able to drop tablespace if a hypertable depends on it
-- even when there are no chunks
DROP TABLESPACE tablespace1;
ERROR: tablespace "tablespace1" is still attached to 1 hypertables
\set ON_ERROR_STOP 1
DROP TABLE hyper_in_space;
CREATE TABLE hyper_in_space(time bigint, temp float, device int) TABLESPACE tablespace1;
SELECT create_hypertable('hyper_in_space', 'time', 'device', 4, chunk_time_interval=>1);
NOTICE: adding not-null constraint to column "time"
create_hypertable
-----------------------------
(4,public,hyper_in_space,t)
(1 row)
INSERT INTO hyper_in_space(time, temp, device) VALUES (1, 20, 1);
INSERT INTO hyper_in_space(time, temp, device) VALUES (3, 21, 2);
INSERT INTO hyper_in_space(time, temp, device) VALUES (5, 23, 1);
SELECT tablename, tablespace FROM pg_tables
WHERE tablename = 'hyper_in_space' OR tablename ~ '_hyper_\d+_\d+_chunk' ORDER BY tablename;
tablename | tablespace
-------------------+-------------
_hyper_4_10_chunk | tablespace1
_hyper_4_11_chunk | tablespace1
_hyper_4_12_chunk | tablespace1
hyper_in_space | tablespace1
(4 rows)
SELECT attach_tablespace('tablespace2', 'hyper_in_space');
attach_tablespace
-------------------
(1 row)
\set ON_ERROR_STOP 0
-- should fail as >1 tablespaces are attached
ALTER TABLE hyper_in_space SET TABLESPACE tablespace1;
ERROR: cannot set new tablespace when multiple tablespaces are attached to hypertable "hyper_in_space"
\set ON_ERROR_STOP 1
SELECT detach_tablespace('tablespace2', 'hyper_in_space');
detach_tablespace
-------------------
1
(1 row)
SELECT * FROM _timescaledb_catalog.tablespace;
id | hypertable_id | tablespace_name
----+---------------+-----------------
3 | 4 | tablespace1
(1 row)
-- make sure when using ALTER TABLE, table spaces are not accumulated
-- as in case of attach_tablespace
-- should have one result
SELECT * FROM _timescaledb_catalog.tablespace;
id | hypertable_id | tablespace_name
----+---------------+-----------------
3 | 4 | tablespace1
(1 row)
ALTER TABLE hyper_in_space SET TABLESPACE tablespace2;
-- should have one result
SELECT * FROM _timescaledb_catalog.tablespace;
id | hypertable_id | tablespace_name
----+---------------+-----------------
5 | 4 | tablespace2
(1 row)
ALTER TABLE hyper_in_space SET TABLESPACE tablespace1;
-- should have one result, (same as the first in the block)
SELECT * FROM _timescaledb_catalog.tablespace;
id | hypertable_id | tablespace_name
----+---------------+-----------------
6 | 4 | tablespace1
(1 row)
SELECT tablename, tablespace FROM pg_tables
WHERE tablename = 'hyper_in_space' OR tablename ~ '_hyper_\d+_\d+_chunk' ORDER BY tablename;
tablename | tablespace
-------------------+-------------
_hyper_4_10_chunk | tablespace1
_hyper_4_11_chunk | tablespace1
_hyper_4_12_chunk | tablespace1
hyper_in_space | tablespace1
(4 rows)
-- attach tb2 <-> ALTER SET tb1 <-> detach tb1 should work
SELECT detach_tablespace('tablespace1', 'hyper_in_space');
detach_tablespace
-------------------
1
(1 row)
INSERT INTO hyper_in_space(time, temp, device) VALUES (5, 23, 1);
INSERT INTO hyper_in_space(time, temp, device) VALUES (7, 23, 1);
SELECT tablename, tablespace FROM pg_tables
WHERE tablename = 'hyper_in_space' OR tablename ~ '_hyper_\d+_\d+_chunk' ORDER BY tablename;
tablename | tablespace
-------------------+-------------
_hyper_4_10_chunk | tablespace1
_hyper_4_11_chunk | tablespace1
_hyper_4_12_chunk | tablespace1
_hyper_4_13_chunk |
hyper_in_space | tablespace1
(5 rows)
SELECT * FROM _timescaledb_catalog.tablespace;
id | hypertable_id | tablespace_name
----+---------------+-----------------
(0 rows)
DROP TABLE hyper_in_space;
DROP TABLESPACE tablespace1;
DROP TABLESPACE tablespace2;
-- Make sure we handle ALTER SCHEMA RENAME for hypertable schemas
\c single :ROLE_SUPERUSER
CREATE SCHEMA IF NOT EXISTS original_name;
CREATE TABLE original_name.my_table (
date timestamp with time zone NOT NULL,
quantity double precision
);
SELECT create_hypertable('original_name.my_table','date');
create_hypertable
------------------------------
(5,original_name,my_table,t)
(1 row)
INSERT INTO original_name.my_table (date, quantity) VALUES ('2018-07-04T21:00:00+00:00', 8);
ALTER SCHEMA original_name RENAME TO new_name;
DROP TABLE new_name.my_table;
DROP SCHEMA new_name;
-- Now make sure schema is renamed for multiple hypertables, but not hypertables not in the schema
CREATE SCHEMA IF NOT EXISTS original_name;
CREATE TABLE original_name.my_table (
date timestamp with time zone NOT NULL,
quantity double precision
);
CREATE TABLE original_name.my_table2 (
date timestamp with time zone NOT NULL,
quantity double precision
);
CREATE TABLE regular_table (
date timestamp with time zone NOT NULL,
quantity double precision
);
SELECT create_hypertable('original_name.my_table','date');
create_hypertable
------------------------------
(6,original_name,my_table,t)
(1 row)
SELECT create_hypertable('original_name.my_table2','date');
create_hypertable
-------------------------------
(7,original_name,my_table2,t)
(1 row)
SELECT create_hypertable('regular_table','date');
create_hypertable
----------------------------
(8,public,regular_table,t)
(1 row)
INSERT INTO original_name.my_table (date, quantity) VALUES ('2018-07-04T21:00:00+00:00', 8);
INSERT INTO original_name.my_table2 (date, quantity) VALUES ('2018-07-04T21:00:00+00:00', 8);
INSERT INTO regular_table (date, quantity) VALUES ('2018-07-04T21:00:00+00:00', 8);
ALTER SCHEMA original_name RENAME TO new_name;
DROP TABLE new_name.my_table;
DROP TABLE new_name.my_table2;
DROP TABLE regular_table;
DROP SCHEMA new_name;
-- These tables should also drop when we drop the whole schema
CREATE SCHEMA IF NOT EXISTS original_name;
CREATE TABLE original_name.my_table (
date timestamp with time zone NOT NULL,
quantity double precision
);
CREATE TABLE original_name.my_table2 (
date timestamp with time zone NOT NULL,
quantity double precision
);
SELECT create_hypertable('original_name.my_table','date');
create_hypertable
------------------------------
(9,original_name,my_table,t)
(1 row)
SELECT create_hypertable('original_name.my_table2','date');
create_hypertable
--------------------------------
(10,original_name,my_table2,t)
(1 row)
INSERT INTO original_name.my_table (date, quantity) VALUES ('2018-07-04T21:00:00+00:00', 8);
INSERT INTO original_name.my_table2 (date, quantity) VALUES ('2018-07-04T21:00:00+00:00', 8);
ALTER SCHEMA original_name RENAME TO new_name;
DROP SCHEMA new_name CASCADE;
NOTICE: drop cascades to 4 other objects
\dt new_name.*;
List of relations
Schema | Name | Type | Owner
--------+------+------+-------
(0 rows)
-- Make sure we can't rename internal schemas
\set ON_ERROR_STOP 0
ALTER SCHEMA _timescaledb_internal RENAME TO my_new_schema_name;
ERROR: cannot rename schemas used by the TimescaleDB extension
ALTER SCHEMA _timescaledb_catalog RENAME TO my_new_schema_name;
ERROR: cannot rename schemas used by the TimescaleDB extension
ALTER SCHEMA _timescaledb_cache RENAME TO my_new_schema_name;
ERROR: cannot rename schemas used by the TimescaleDB extension
ALTER SCHEMA _timescaledb_config RENAME TO my_new_schema_name;
ERROR: cannot rename schemas used by the TimescaleDB extension
\set ON_ERROR_STOP 1
-- Make sure we can rename associated schemas
CREATE TABLE my_table (
date timestamp with time zone NOT NULL,
quantity double precision
);
SELECT create_hypertable('my_table','date', associated_schema_name => 'my_associated_schema');
create_hypertable
------------------------
(11,public,my_table,t)
(1 row)
INSERT INTO my_table (date, quantity) VALUES ('2018-07-04T21:00:00+00:00', 8);
ALTER SCHEMA my_associated_schema RENAME TO new_associated_schema;
INSERT INTO my_table (date, quantity) VALUES ('2018-08-10T23:00:00+00:00', 20);
-- Make sure the schema name is changed in both catalog tables
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
----+-------------+------------+------------------------+-------------------------+----------------+--------------------------+--------------------------+-------------------
11 | public | my_table | new_associated_schema | _hyper_11 | 1 | _timescaledb_internal | calculate_chunk_interval | 0
(1 row)
SELECT * from _timescaledb_catalog.chunk;
id | hypertable_id | schema_name | table_name
----+---------------+-----------------------+--------------------
20 | 11 | new_associated_schema | _hyper_11_20_chunk
21 | 11 | new_associated_schema | _hyper_11_21_chunk
(2 rows)
DROP TABLE my_table;