-
Notifications
You must be signed in to change notification settings - Fork 847
/
cagg_ddl.out
1941 lines (1808 loc) · 76.1 KB
/
cagg_ddl.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
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
-- 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.
\set IS_DISTRIBUTED FALSE
\ir include/cagg_ddl_common.sql
-- 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.
-- Set this variable to avoid using a hard-coded path each time query
-- results are compared
\set QUERY_RESULT_TEST_EQUAL_RELPATH '../../../../test/sql/include/query_result_test_equal.sql'
\if :IS_DISTRIBUTED
\echo 'Running distributed hypertable tests'
\else
\echo 'Running local hypertable tests'
Running local hypertable tests
\endif
SET ROLE :ROLE_DEFAULT_PERM_USER;
--DDL commands on continuous aggregates
CREATE TABLE conditions (
timec TIMESTAMPTZ NOT NULL,
location TEXT NOT NULL,
temperature integer NULL,
humidity DOUBLE PRECISION NULL,
timemeasure TIMESTAMPTZ,
timeinterval INTERVAL
);
\if :IS_DISTRIBUTED
SELECT table_name FROM create_distributed_hypertable('conditions', 'timec', replication_factor => 2);
\else
SELECT table_name FROM create_hypertable('conditions', 'timec');
table_name
------------
conditions
(1 row)
\endif
-- schema tests
\c :TEST_DBNAME :ROLE_CLUSTER_SUPERUSER
CREATE TABLESPACE tablespace1 OWNER :ROLE_DEFAULT_PERM_USER LOCATION :TEST_TABLESPACE1_PATH;
CREATE TABLESPACE tablespace2 OWNER :ROLE_DEFAULT_PERM_USER LOCATION :TEST_TABLESPACE2_PATH;
CREATE SCHEMA rename_schema;
GRANT ALL ON SCHEMA rename_schema TO :ROLE_DEFAULT_PERM_USER;
SET ROLE :ROLE_DEFAULT_PERM_USER;
CREATE TABLE foo(time TIMESTAMPTZ NOT NULL, data INTEGER);
\if :IS_DISTRIBUTED
SELECT create_distributed_hypertable('foo', 'time', replication_factor => 2);
\else
SELECT create_hypertable('foo', 'time');
create_hypertable
-------------------
(2,public,foo,t)
(1 row)
\endif
CREATE MATERIALIZED VIEW rename_test
WITH ( timescaledb.continuous, timescaledb.materialized_only=true)
AS SELECT time_bucket('1week', time), COUNT(data)
FROM foo
GROUP BY 1 WITH NO DATA;
SELECT user_view_schema, user_view_name, partial_view_schema, partial_view_name
FROM _timescaledb_catalog.continuous_agg;
user_view_schema | user_view_name | partial_view_schema | partial_view_name
------------------+----------------+-----------------------+-------------------
public | rename_test | _timescaledb_internal | _partial_view_3
(1 row)
ALTER MATERIALIZED VIEW rename_test SET SCHEMA rename_schema;
SELECT user_view_schema, user_view_name, partial_view_schema, partial_view_name
FROM _timescaledb_catalog.continuous_agg;
user_view_schema | user_view_name | partial_view_schema | partial_view_name
------------------+----------------+-----------------------+-------------------
rename_schema | rename_test | _timescaledb_internal | _partial_view_3
(1 row)
SELECT ca.raw_hypertable_id as "RAW_HYPERTABLE_ID",
h.schema_name AS "MAT_SCHEMA_NAME",
h.table_name AS "MAT_TABLE_NAME",
partial_view_name as "PART_VIEW_NAME",
partial_view_schema as "PART_VIEW_SCHEMA",
direct_view_name as "DIR_VIEW_NAME",
direct_view_schema as "DIR_VIEW_SCHEMA"
FROM _timescaledb_catalog.continuous_agg ca
INNER JOIN _timescaledb_catalog.hypertable h ON(h.id = ca.mat_hypertable_id)
WHERE user_view_name = 'rename_test'
\gset
RESET ROLE;
SELECT current_user;
current_user
--------------------
cluster_super_user
(1 row)
ALTER VIEW :"PART_VIEW_SCHEMA".:"PART_VIEW_NAME" SET SCHEMA public;
SET ROLE :ROLE_DEFAULT_PERM_USER;
SELECT user_view_schema, user_view_name, partial_view_schema, partial_view_name
FROM _timescaledb_catalog.continuous_agg;
user_view_schema | user_view_name | partial_view_schema | partial_view_name
------------------+----------------+---------------------+-------------------
rename_schema | rename_test | public | _partial_view_3
(1 row)
--alter direct view schema
SELECT user_view_schema, user_view_name, direct_view_schema, direct_view_name
FROM _timescaledb_catalog.continuous_agg;
user_view_schema | user_view_name | direct_view_schema | direct_view_name
------------------+----------------+-----------------------+------------------
rename_schema | rename_test | _timescaledb_internal | _direct_view_3
(1 row)
RESET ROLE;
SELECT current_user;
current_user
--------------------
cluster_super_user
(1 row)
ALTER VIEW :"DIR_VIEW_SCHEMA".:"DIR_VIEW_NAME" SET SCHEMA public;
SET ROLE :ROLE_DEFAULT_PERM_USER;
SELECT user_view_schema, user_view_name, partial_view_schema, partial_view_name,
direct_view_schema, direct_view_name
FROM _timescaledb_catalog.continuous_agg;
user_view_schema | user_view_name | partial_view_schema | partial_view_name | direct_view_schema | direct_view_name
------------------+----------------+---------------------+-------------------+--------------------+------------------
rename_schema | rename_test | public | _partial_view_3 | public | _direct_view_3
(1 row)
RESET ROLE;
SELECT current_user;
current_user
--------------------
cluster_super_user
(1 row)
ALTER SCHEMA rename_schema RENAME TO new_name_schema;
SET ROLE :ROLE_DEFAULT_PERM_USER;
SELECT user_view_schema, user_view_name, partial_view_schema, partial_view_name,
direct_view_schema, direct_view_name
FROM _timescaledb_catalog.continuous_agg;
user_view_schema | user_view_name | partial_view_schema | partial_view_name | direct_view_schema | direct_view_name
------------------+----------------+---------------------+-------------------+--------------------+------------------
new_name_schema | rename_test | public | _partial_view_3 | public | _direct_view_3
(1 row)
ALTER VIEW :"PART_VIEW_NAME" SET SCHEMA new_name_schema;
ALTER VIEW :"DIR_VIEW_NAME" SET SCHEMA new_name_schema;
SELECT user_view_schema, user_view_name, partial_view_schema, partial_view_name,
direct_view_schema, direct_view_name
FROM _timescaledb_catalog.continuous_agg;
user_view_schema | user_view_name | partial_view_schema | partial_view_name | direct_view_schema | direct_view_name
------------------+----------------+---------------------+-------------------+--------------------+------------------
new_name_schema | rename_test | new_name_schema | _partial_view_3 | new_name_schema | _direct_view_3
(1 row)
RESET ROLE;
SELECT current_user;
current_user
--------------------
cluster_super_user
(1 row)
ALTER SCHEMA new_name_schema RENAME TO foo_name_schema;
SET ROLE :ROLE_DEFAULT_PERM_USER;
SELECT user_view_schema, user_view_name, partial_view_schema, partial_view_name
FROM _timescaledb_catalog.continuous_agg;
user_view_schema | user_view_name | partial_view_schema | partial_view_name
------------------+----------------+---------------------+-------------------
foo_name_schema | rename_test | foo_name_schema | _partial_view_3
(1 row)
ALTER MATERIALIZED VIEW foo_name_schema.rename_test SET SCHEMA public;
SELECT user_view_schema, user_view_name, partial_view_schema, partial_view_name
FROM _timescaledb_catalog.continuous_agg;
user_view_schema | user_view_name | partial_view_schema | partial_view_name
------------------+----------------+---------------------+-------------------
public | rename_test | foo_name_schema | _partial_view_3
(1 row)
RESET ROLE;
SELECT current_user;
current_user
--------------------
cluster_super_user
(1 row)
ALTER SCHEMA foo_name_schema RENAME TO rename_schema;
SET ROLE :ROLE_DEFAULT_PERM_USER;
SET client_min_messages TO NOTICE;
SELECT user_view_schema, user_view_name, partial_view_schema, partial_view_name
FROM _timescaledb_catalog.continuous_agg;
user_view_schema | user_view_name | partial_view_schema | partial_view_name
------------------+----------------+---------------------+-------------------
public | rename_test | rename_schema | _partial_view_3
(1 row)
ALTER MATERIALIZED VIEW rename_test RENAME TO rename_c_aggregate;
SELECT user_view_schema, user_view_name, partial_view_schema, partial_view_name
FROM _timescaledb_catalog.continuous_agg;
user_view_schema | user_view_name | partial_view_schema | partial_view_name
------------------+--------------------+---------------------+-------------------
public | rename_c_aggregate | rename_schema | _partial_view_3
(1 row)
SELECT * FROM rename_c_aggregate;
time_bucket | count
-------------+-------
(0 rows)
ALTER VIEW rename_schema.:"PART_VIEW_NAME" RENAME TO partial_view;
SELECT user_view_schema, user_view_name, partial_view_schema, partial_view_name,
direct_view_schema, direct_view_name
FROM _timescaledb_catalog.continuous_agg;
user_view_schema | user_view_name | partial_view_schema | partial_view_name | direct_view_schema | direct_view_name
------------------+--------------------+---------------------+-------------------+--------------------+------------------
public | rename_c_aggregate | rename_schema | partial_view | rename_schema | _direct_view_3
(1 row)
--rename direct view
ALTER VIEW rename_schema.:"DIR_VIEW_NAME" RENAME TO direct_view;
SELECT user_view_schema, user_view_name, partial_view_schema, partial_view_name,
direct_view_schema, direct_view_name
FROM _timescaledb_catalog.continuous_agg;
user_view_schema | user_view_name | partial_view_schema | partial_view_name | direct_view_schema | direct_view_name
------------------+--------------------+---------------------+-------------------+--------------------+------------------
public | rename_c_aggregate | rename_schema | partial_view | rename_schema | direct_view
(1 row)
-- drop_chunks tests
DROP TABLE conditions CASCADE;
DROP TABLE foo CASCADE;
psql:include/cagg_ddl_common.sql:161: NOTICE: drop cascades to 2 other objects
CREATE TABLE drop_chunks_table(time BIGINT NOT NULL, data INTEGER);
\if :IS_DISTRIBUTED
SELECT hypertable_id AS drop_chunks_table_id
FROM create_distributed_hypertable('drop_chunks_table', 'time', chunk_time_interval => 10, replication_factor => 2) \gset
\else
SELECT hypertable_id AS drop_chunks_table_id
FROM create_hypertable('drop_chunks_table', 'time', chunk_time_interval => 10) \gset
\endif
CREATE OR REPLACE FUNCTION integer_now_test() returns bigint LANGUAGE SQL STABLE as $$ SELECT coalesce(max(time), bigint '0') FROM drop_chunks_table $$;
\if :IS_DISTRIBUTED
CALL distributed_exec($DIST$
CREATE OR REPLACE FUNCTION integer_now_test() returns bigint LANGUAGE SQL STABLE as $$ SELECT coalesce(max(time), bigint '0') FROM drop_chunks_table $$;
$DIST$);
\endif
SELECT set_integer_now_func('drop_chunks_table', 'integer_now_test');
set_integer_now_func
----------------------
(1 row)
CREATE MATERIALIZED VIEW drop_chunks_view
WITH (
timescaledb.continuous,
timescaledb.materialized_only=true
)
AS SELECT time_bucket('5', time), COUNT(data)
FROM drop_chunks_table
GROUP BY 1 WITH NO DATA;
SELECT format('%I.%I', schema_name, table_name) AS drop_chunks_mat_table,
schema_name AS drop_chunks_mat_schema,
table_name AS drop_chunks_mat_table_name
FROM _timescaledb_catalog.hypertable, _timescaledb_catalog.continuous_agg
WHERE _timescaledb_catalog.continuous_agg.raw_hypertable_id = :drop_chunks_table_id
AND _timescaledb_catalog.hypertable.id = _timescaledb_catalog.continuous_agg.mat_hypertable_id \gset
-- create 3 chunks, with 3 time bucket
INSERT INTO drop_chunks_table SELECT i, i FROM generate_series(0, 29) AS i;
-- Only refresh up to bucket 15 initially. Matches the old refresh
-- behavior that didn't materialize everything
CALL refresh_continuous_aggregate('drop_chunks_view', 0, 15);
SELECT count(c) FROM show_chunks('drop_chunks_table') AS c;
count
-------
3
(1 row)
SELECT count(c) FROM show_chunks('drop_chunks_view') AS c;
count
-------
1
(1 row)
SELECT * FROM drop_chunks_view ORDER BY 1;
time_bucket | count
-------------+-------
0 | 5
5 | 5
10 | 5
(3 rows)
-- cannot drop directly from the materialization table without specifying
-- cont. aggregate view name explicitly
\set ON_ERROR_STOP 0
SELECT drop_chunks(:'drop_chunks_mat_table',
newer_than => -20,
verbose => true);
psql:include/cagg_ddl_common.sql:213: ERROR: operation not supported on materialized hypertable
\set ON_ERROR_STOP 1
SELECT count(c) FROM show_chunks('drop_chunks_table') AS c;
count
-------
3
(1 row)
SELECT count(c) FROM show_chunks('drop_chunks_view') AS c;
count
-------
1
(1 row)
SELECT * FROM drop_chunks_view ORDER BY 1;
time_bucket | count
-------------+-------
0 | 5
5 | 5
10 | 5
(3 rows)
-- drop chunks when the chunksize and time_bucket aren't aligned
DROP TABLE drop_chunks_table CASCADE;
psql:include/cagg_ddl_common.sql:222: NOTICE: drop cascades to 2 other objects
psql:include/cagg_ddl_common.sql:222: NOTICE: drop cascades to table _timescaledb_internal._hyper_5_4_chunk
CREATE TABLE drop_chunks_table_u(time BIGINT NOT NULL, data INTEGER);
\if :IS_DISTRIBUTED
SELECT hypertable_id AS drop_chunks_table_u_id
FROM create_distributed_hypertable('drop_chunks_table_u', 'time', chunk_time_interval => 7, replication_factor => 2) \gset
\else
SELECT hypertable_id AS drop_chunks_table_u_id
FROM create_hypertable('drop_chunks_table_u', 'time', chunk_time_interval => 7) \gset
\endif
CREATE OR REPLACE FUNCTION integer_now_test1() returns bigint LANGUAGE SQL STABLE as $$ SELECT coalesce(max(time), bigint '0') FROM drop_chunks_table_u $$;
\if :IS_DISTRIBUTED
CALL distributed_exec($DIST$
CREATE OR REPLACE FUNCTION integer_now_test1() returns bigint LANGUAGE SQL STABLE as $$ SELECT coalesce(max(time), bigint '0') FROM drop_chunks_table_u $$;
$DIST$);
\endif
SELECT set_integer_now_func('drop_chunks_table_u', 'integer_now_test1');
set_integer_now_func
----------------------
(1 row)
CREATE MATERIALIZED VIEW drop_chunks_view
WITH (
timescaledb.continuous,
timescaledb.materialized_only=true
)
AS SELECT time_bucket('3', time), COUNT(data)
FROM drop_chunks_table_u
GROUP BY 1 WITH NO DATA;
SELECT format('%I.%I', schema_name, table_name) AS drop_chunks_mat_table_u,
schema_name AS drop_chunks_mat_schema,
table_name AS drop_chunks_mat_table_u_name
FROM _timescaledb_catalog.hypertable, _timescaledb_catalog.continuous_agg
WHERE _timescaledb_catalog.continuous_agg.raw_hypertable_id = :drop_chunks_table_u_id
AND _timescaledb_catalog.hypertable.id = _timescaledb_catalog.continuous_agg.mat_hypertable_id \gset
-- create 3 chunks, with 3 time bucket
INSERT INTO drop_chunks_table_u SELECT i, i FROM generate_series(0, 21) AS i;
-- Refresh up to bucket 15 to match old materializer behavior
CALL refresh_continuous_aggregate('drop_chunks_view', 0, 15);
SELECT count(c) FROM show_chunks('drop_chunks_table_u') AS c;
count
-------
4
(1 row)
SELECT count(c) FROM show_chunks('drop_chunks_view') AS c;
count
-------
1
(1 row)
SELECT * FROM drop_chunks_view ORDER BY 1;
time_bucket | count
-------------+-------
0 | 3
3 | 3
6 | 3
9 | 3
12 | 3
(5 rows)
-- TRUNCATE test
-- Can truncate regular hypertables that have caggs
TRUNCATE drop_chunks_table_u;
\set ON_ERROR_STOP 0
-- Can't truncate materialized hypertables directly
TRUNCATE :drop_chunks_mat_table_u;
psql:include/cagg_ddl_common.sql:271: ERROR: cannot TRUNCATE a hypertable underlying a continuous aggregate
\set ON_ERROR_STOP 1
-- Check that we don't interfere with TRUNCATE of normal table and
-- partitioned table
CREATE TABLE truncate (value int);
INSERT INTO truncate VALUES (1), (2);
TRUNCATE truncate;
SELECT * FROM truncate;
value
-------
(0 rows)
CREATE TABLE truncate_partitioned (value int)
PARTITION BY RANGE(value);
CREATE TABLE truncate_p1 PARTITION OF truncate_partitioned
FOR VALUES FROM (1) TO (3);
INSERT INTO truncate_partitioned VALUES (1), (2);
TRUNCATE truncate_partitioned;
SELECT * FROM truncate_partitioned;
value
-------
(0 rows)
-- ALTER TABLE tests
\set ON_ERROR_STOP 0
-- test a variety of ALTER TABLE statements
ALTER TABLE :drop_chunks_mat_table_u RENAME time_bucket TO bad_name;
psql:include/cagg_ddl_common.sql:291: ERROR: renaming columns on materialization tables is not supported
ALTER TABLE :drop_chunks_mat_table_u ADD UNIQUE(time_bucket);
psql:include/cagg_ddl_common.sql:292: ERROR: operation not supported on materialization tables
ALTER TABLE :drop_chunks_mat_table_u SET UNLOGGED;
psql:include/cagg_ddl_common.sql:293: ERROR: operation not supported on materialization tables
ALTER TABLE :drop_chunks_mat_table_u ENABLE ROW LEVEL SECURITY;
psql:include/cagg_ddl_common.sql:294: ERROR: operation not supported on materialization tables
ALTER TABLE :drop_chunks_mat_table_u ADD COLUMN fizzle INTEGER;
psql:include/cagg_ddl_common.sql:295: ERROR: operation not supported on materialization tables
ALTER TABLE :drop_chunks_mat_table_u DROP COLUMN time_bucket;
psql:include/cagg_ddl_common.sql:296: ERROR: operation not supported on materialization tables
ALTER TABLE :drop_chunks_mat_table_u ALTER COLUMN time_bucket DROP NOT NULL;
psql:include/cagg_ddl_common.sql:297: ERROR: operation not supported on materialization tables
ALTER TABLE :drop_chunks_mat_table_u ALTER COLUMN time_bucket SET DEFAULT 1;
psql:include/cagg_ddl_common.sql:298: ERROR: operation not supported on materialization tables
ALTER TABLE :drop_chunks_mat_table_u ALTER COLUMN time_bucket SET STORAGE EXTERNAL;
psql:include/cagg_ddl_common.sql:299: ERROR: operation not supported on materialization tables
ALTER TABLE :drop_chunks_mat_table_u DISABLE TRIGGER ALL;
psql:include/cagg_ddl_common.sql:300: ERROR: operation not supported on materialization tables
ALTER TABLE :drop_chunks_mat_table_u SET TABLESPACE foo;
psql:include/cagg_ddl_common.sql:301: ERROR: operation not supported on materialization tables
ALTER TABLE :drop_chunks_mat_table_u NOT OF;
psql:include/cagg_ddl_common.sql:302: ERROR: operation not supported on materialization tables
ALTER TABLE :drop_chunks_mat_table_u OWNER TO CURRENT_USER;
psql:include/cagg_ddl_common.sql:303: ERROR: operation not supported on materialization tables
\set ON_ERROR_STOP 1
ALTER TABLE :drop_chunks_mat_table_u SET SCHEMA public;
ALTER TABLE :drop_chunks_mat_table_u_name RENAME TO new_name;
SET ROLE :ROLE_DEFAULT_PERM_USER;
SET client_min_messages TO NOTICE;
SELECT * FROM new_name;
time_bucket | count
-------------+-------
0 | 3
3 | 3
6 | 3
9 | 3
12 | 3
(5 rows)
SELECT * FROM drop_chunks_view ORDER BY 1;
time_bucket | count
-------------+-------
0 | 3
3 | 3
6 | 3
9 | 3
12 | 3
(5 rows)
\set ON_ERROR_STOP 0
-- no continuous aggregates on a continuous aggregate materialization table
CREATE MATERIALIZED VIEW new_name_view
WITH (
timescaledb.continuous,
timescaledb.materialized_only=true
)
AS SELECT time_bucket('6', time_bucket), COUNT("count")
FROM new_name
GROUP BY 1 WITH NO DATA;
psql:include/cagg_ddl_common.sql:326: ERROR: hypertable is a continuous aggregate materialization table
\set ON_ERROR_STOP 1
CREATE TABLE metrics(time timestamptz NOT NULL, device_id int, v1 float, v2 float);
\if :IS_DISTRIBUTED
SELECT create_distributed_hypertable('metrics', 'time', replication_factor => 2);
\else
SELECT create_hypertable('metrics','time');
create_hypertable
----------------------
(8,public,metrics,t)
(1 row)
\endif
INSERT INTO metrics SELECT generate_series('2000-01-01'::timestamptz,'2000-01-10','1m'),1,0.25,0.75;
-- check expressions in view definition
CREATE MATERIALIZED VIEW cagg_expr
WITH (timescaledb.continuous, timescaledb.materialized_only=true)
AS
SELECT
time_bucket('1d', time) AS time,
'Const'::text AS Const,
4.3::numeric AS "numeric",
first(metrics,time),
CASE WHEN true THEN 'foo' ELSE 'bar' END,
COALESCE(NULL,'coalesce'),
avg(v1) + avg(v2) AS avg1,
avg(v1+v2) AS avg2
FROM metrics
GROUP BY 1 WITH NO DATA;
CALL refresh_continuous_aggregate('cagg_expr', NULL, NULL);
SELECT * FROM cagg_expr ORDER BY time LIMIT 5;
time | const | numeric | first | case | coalesce | avg1 | avg2
------------------------------+-------+---------+----------------------------------------------+------+----------+------+------
Fri Dec 31 16:00:00 1999 PST | Const | 4.3 | ("Sat Jan 01 00:00:00 2000 PST",1,0.25,0.75) | foo | coalesce | 1 | 1
Sat Jan 01 16:00:00 2000 PST | Const | 4.3 | ("Sat Jan 01 16:00:00 2000 PST",1,0.25,0.75) | foo | coalesce | 1 | 1
Sun Jan 02 16:00:00 2000 PST | Const | 4.3 | ("Sun Jan 02 16:00:00 2000 PST",1,0.25,0.75) | foo | coalesce | 1 | 1
Mon Jan 03 16:00:00 2000 PST | Const | 4.3 | ("Mon Jan 03 16:00:00 2000 PST",1,0.25,0.75) | foo | coalesce | 1 | 1
Tue Jan 04 16:00:00 2000 PST | Const | 4.3 | ("Tue Jan 04 16:00:00 2000 PST",1,0.25,0.75) | foo | coalesce | 1 | 1
(5 rows)
--test materialization of invalidation before drop
DROP TABLE IF EXISTS drop_chunks_table CASCADE;
psql:include/cagg_ddl_common.sql:358: NOTICE: table "drop_chunks_table" does not exist, skipping
DROP TABLE IF EXISTS drop_chunks_table_u CASCADE;
psql:include/cagg_ddl_common.sql:359: NOTICE: drop cascades to 2 other objects
psql:include/cagg_ddl_common.sql:359: NOTICE: drop cascades to table _timescaledb_internal._hyper_7_9_chunk
CREATE TABLE drop_chunks_table(time BIGINT NOT NULL, data INTEGER);
\if :IS_DISTRIBUTED
SELECT hypertable_id AS drop_chunks_table_nid
FROM create_distributed_hypertable('drop_chunks_table', 'time', chunk_time_interval => 10, replication_factor => 2) \gset
\else
SELECT hypertable_id AS drop_chunks_table_nid
FROM create_hypertable('drop_chunks_table', 'time', chunk_time_interval => 10) \gset
\endif
CREATE OR REPLACE FUNCTION integer_now_test2() returns bigint LANGUAGE SQL STABLE as $$ SELECT coalesce(max(time), bigint '0') FROM drop_chunks_table $$;
\if :IS_DISTRIBUTED
CALL distributed_exec($DIST$
CREATE OR REPLACE FUNCTION integer_now_test2() returns bigint LANGUAGE SQL STABLE as $$ SELECT coalesce(max(time), bigint '0') FROM drop_chunks_table $$;
$DIST$);
\endif
SELECT set_integer_now_func('drop_chunks_table', 'integer_now_test2');
set_integer_now_func
----------------------
(1 row)
CREATE MATERIALIZED VIEW drop_chunks_view
WITH (
timescaledb.continuous,
timescaledb.materialized_only=true
)
AS SELECT time_bucket('5', time), max(data)
FROM drop_chunks_table
GROUP BY 1 WITH NO DATA;
INSERT INTO drop_chunks_table SELECT i, i FROM generate_series(0, 20) AS i;
--dropping chunks will process the invalidations
SELECT drop_chunks('drop_chunks_table', older_than => (integer_now_test2()-9));
drop_chunks
------------------------------------------
_timescaledb_internal._hyper_10_13_chunk
(1 row)
SELECT * FROM drop_chunks_table ORDER BY time ASC limit 1;
time | data
------+------
10 | 10
(1 row)
INSERT INTO drop_chunks_table SELECT i, i FROM generate_series(20, 35) AS i;
CALL refresh_continuous_aggregate('drop_chunks_view', 10, 40);
--this will be seen after the drop its within the invalidation window and will be dropped
INSERT INTO drop_chunks_table VALUES (26, 100);
--this will not be processed by the drop since chunk 30-39 is not dropped but will be seen after refresh
--shows that the drop doesn't do more work than necessary
INSERT INTO drop_chunks_table VALUES (31, 200);
--move the time up to 39
INSERT INTO drop_chunks_table SELECT i, i FROM generate_series(35, 39) AS i;
--the chunks and ranges we have thus far
SELECT chunk_name, range_start_integer, range_end_integer
FROM timescaledb_information.chunks
WHERE hypertable_name = 'drop_chunks_table';
chunk_name | range_start_integer | range_end_integer
--------------------+---------------------+-------------------
_hyper_10_14_chunk | 10 | 20
_hyper_10_15_chunk | 20 | 30
_hyper_10_16_chunk | 30 | 40
(3 rows)
--the invalidation on 25 not yet seen
SELECT * FROM drop_chunks_view ORDER BY time_bucket DESC;
time_bucket | max
-------------+-----
35 | 35
30 | 34
25 | 29
20 | 24
15 | 19
10 | 14
(6 rows)
--refresh to process the invalidations and then drop
CALL refresh_continuous_aggregate('drop_chunks_view', NULL, (integer_now_test2()-9));
SELECT drop_chunks('drop_chunks_table', older_than => (integer_now_test2()-9));
drop_chunks
------------------------------------------
_timescaledb_internal._hyper_10_14_chunk
_timescaledb_internal._hyper_10_15_chunk
(2 rows)
--new values on 25 now seen in view
SELECT * FROM drop_chunks_view ORDER BY time_bucket DESC;
time_bucket | max
-------------+-----
35 | 35
30 | 34
25 | 100
20 | 24
15 | 19
10 | 14
(6 rows)
--earliest datapoint now in table
SELECT * FROM drop_chunks_table ORDER BY time ASC limit 1;
time | data
------+------
30 | 30
(1 row)
--we see the chunks row with the dropped flags set;
SELECT * FROM _timescaledb_catalog.chunk where dropped;
id | hypertable_id | schema_name | table_name | compressed_chunk_id | dropped | status | osm_chunk
----+---------------+-----------------------+--------------------+---------------------+---------+--------+-----------
13 | 10 | _timescaledb_internal | _hyper_10_13_chunk | | t | 0 | f
14 | 10 | _timescaledb_internal | _hyper_10_14_chunk | | t | 0 | f
15 | 10 | _timescaledb_internal | _hyper_10_15_chunk | | t | 0 | f
(3 rows)
--still see data in the view
SELECT * FROM drop_chunks_view WHERE time_bucket < (integer_now_test2()-9) ORDER BY time_bucket DESC;
time_bucket | max
-------------+-----
25 | 100
20 | 24
15 | 19
10 | 14
(4 rows)
--no data but covers dropped chunks
SELECT * FROM drop_chunks_table WHERE time < (integer_now_test2()-9) ORDER BY time DESC;
time | data
------+------
(0 rows)
--recreate the dropped chunk
INSERT INTO drop_chunks_table SELECT i, i FROM generate_series(0, 20) AS i;
--see data from recreated region
SELECT * FROM drop_chunks_table WHERE time < (integer_now_test2()-9) ORDER BY time DESC;
time | data
------+------
20 | 20
19 | 19
18 | 18
17 | 17
16 | 16
15 | 15
14 | 14
13 | 13
12 | 12
11 | 11
10 | 10
9 | 9
8 | 8
7 | 7
6 | 6
5 | 5
4 | 4
3 | 3
2 | 2
1 | 1
0 | 0
(21 rows)
--should show chunk with old name and old ranges
SELECT chunk_name, range_start_integer, range_end_integer
FROM timescaledb_information.chunks
WHERE hypertable_name = 'drop_chunks_table'
ORDER BY range_start_integer;
chunk_name | range_start_integer | range_end_integer
--------------------+---------------------+-------------------
_hyper_10_13_chunk | 0 | 10
_hyper_10_14_chunk | 10 | 20
_hyper_10_15_chunk | 20 | 30
_hyper_10_16_chunk | 30 | 40
(4 rows)
--We dropped everything up to the bucket starting at 30 and then
--inserted new data up to and including time 20. Therefore, the
--dropped data should stay the same as long as we only refresh
--buckets that have non-dropped data.
CALL refresh_continuous_aggregate('drop_chunks_view', 30, 40);
SELECT * FROM drop_chunks_view ORDER BY time_bucket DESC;
time_bucket | max
-------------+-----
35 | 39
30 | 200
25 | 100
20 | 24
15 | 19
10 | 14
(6 rows)
SELECT format('%I.%I', schema_name, table_name) AS drop_chunks_mat_tablen,
schema_name AS drop_chunks_mat_schema,
table_name AS drop_chunks_mat_table_name
FROM _timescaledb_catalog.hypertable, _timescaledb_catalog.continuous_agg
WHERE _timescaledb_catalog.continuous_agg.raw_hypertable_id = :drop_chunks_table_nid
AND _timescaledb_catalog.hypertable.id = _timescaledb_catalog.continuous_agg.mat_hypertable_id \gset
-- TEST drop chunks from continuous aggregates by specifying view name
SELECT drop_chunks('drop_chunks_view',
newer_than => -20,
verbose => true);
psql:include/cagg_ddl_common.sql:454: INFO: dropping chunk _timescaledb_internal._hyper_11_17_chunk
drop_chunks
------------------------------------------
_timescaledb_internal._hyper_11_17_chunk
(1 row)
-- Test that we cannot drop chunks when specifying materialized
-- hypertable
INSERT INTO drop_chunks_table SELECT generate_series(45, 55), 500;
CALL refresh_continuous_aggregate('drop_chunks_view', 45, 55);
SELECT chunk_name, range_start_integer, range_end_integer
FROM timescaledb_information.chunks
WHERE hypertable_name = :'drop_chunks_mat_table_name' ORDER BY range_start_integer;
chunk_name | range_start_integer | range_end_integer
--------------------+---------------------+-------------------
_hyper_11_20_chunk | 0 | 100
(1 row)
\set ON_ERROR_STOP 0
\set VERBOSITY default
SELECT drop_chunks(:'drop_chunks_mat_tablen', older_than => 60);
psql:include/cagg_ddl_common.sql:466: ERROR: operation not supported on materialized hypertable
DETAIL: Hypertable "_materialized_hypertable_11" is a materialized hypertable.
HINT: Try the operation on the continuous aggregate instead.
\set VERBOSITY terse
\set ON_ERROR_STOP 1
-----------------------------------------------------------------
-- Test that refresh_continuous_aggregate on chunk will refresh,
-- but only in the regions covered by the show chunks.
-----------------------------------------------------------------
SELECT chunk_name, range_start_integer, range_end_integer
FROM timescaledb_information.chunks
WHERE hypertable_name = 'drop_chunks_table'
ORDER BY 2,3;
chunk_name | range_start_integer | range_end_integer
--------------------+---------------------+-------------------
_hyper_10_13_chunk | 0 | 10
_hyper_10_14_chunk | 10 | 20
_hyper_10_15_chunk | 20 | 30
_hyper_10_16_chunk | 30 | 40
_hyper_10_18_chunk | 40 | 50
_hyper_10_19_chunk | 50 | 60
(6 rows)
-- Pick the second chunk as the one to drop
WITH numbered_chunks AS (
SELECT row_number() OVER (ORDER BY range_start_integer), chunk_schema, chunk_name, range_start_integer, range_end_integer
FROM timescaledb_information.chunks
WHERE hypertable_name = 'drop_chunks_table'
ORDER BY 1
)
SELECT format('%I.%I', chunk_schema, chunk_name) AS chunk_to_drop, range_start_integer, range_end_integer
FROM numbered_chunks
WHERE row_number = 2 \gset
-- There's data in the table for the chunk/range we will drop
SELECT * FROM drop_chunks_table
WHERE time >= :range_start_integer
AND time < :range_end_integer
ORDER BY 1;
time | data
------+------
10 | 10
11 | 11
12 | 12
13 | 13
14 | 14
15 | 15
16 | 16
17 | 17
18 | 18
19 | 19
(10 rows)
-- Make sure there is also data in the continuous aggregate
-- CARE:
-- Note that this behaviour of dropping the materialization table chunks and expecting a refresh
-- that overlaps that time range to NOT update those chunks is undefined. Since CAGGs over
-- distributed hypertables merge the invalidations the refresh region is updated in the distributed
-- case, which may be different than what happens in the normal hypertable case. The command was:
-- SELECT drop_chunks('drop_chunks_view', newer_than => -20, verbose => true);
CALL refresh_continuous_aggregate('drop_chunks_view', 0, 50);
SELECT * FROM drop_chunks_view
ORDER BY 1;
time_bucket | max
-------------+-----
0 | 4
5 | 9
10 | 14
15 | 19
20 | 20
45 | 500
50 | 500
(7 rows)
-- Drop the second chunk, to leave a gap in the data
\if :IS_DISTRIBUTED
CALL distributed_exec(format('DROP TABLE IF EXISTS %s', :'chunk_to_drop'));
DROP FOREIGN TABLE :chunk_to_drop;
\else
DROP TABLE :chunk_to_drop;
\endif
-- Verify that the second chunk is dropped
SELECT chunk_name, range_start_integer, range_end_integer
FROM timescaledb_information.chunks
WHERE hypertable_name = 'drop_chunks_table'
ORDER BY 2,3;
chunk_name | range_start_integer | range_end_integer
--------------------+---------------------+-------------------
_hyper_10_13_chunk | 0 | 10
_hyper_10_15_chunk | 20 | 30
_hyper_10_16_chunk | 30 | 40
_hyper_10_18_chunk | 40 | 50
_hyper_10_19_chunk | 50 | 60
(5 rows)
-- Data is no longer in the table but still in the view
SELECT * FROM drop_chunks_table
WHERE time >= :range_start_integer
AND time < :range_end_integer
ORDER BY 1;
time | data
------+------
(0 rows)
SELECT * FROM drop_chunks_view
WHERE time_bucket >= :range_start_integer
AND time_bucket < :range_end_integer
ORDER BY 1;
time_bucket | max
-------------+-----
10 | 14
15 | 19
(2 rows)
-- Insert a large value in one of the chunks that will be dropped
INSERT INTO drop_chunks_table VALUES (:range_start_integer-1, 100);
-- Now refresh and drop the two adjecent chunks
CALL refresh_continuous_aggregate('drop_chunks_view', NULL, 30);
SELECT drop_chunks('drop_chunks_table', older_than=>30);
drop_chunks
------------------------------------------
_timescaledb_internal._hyper_10_13_chunk
_timescaledb_internal._hyper_10_15_chunk
(2 rows)
-- Verify that the chunks are dropped
SELECT chunk_name, range_start_integer, range_end_integer
FROM timescaledb_information.chunks
WHERE hypertable_name = 'drop_chunks_table'
ORDER BY 2,3;
chunk_name | range_start_integer | range_end_integer
--------------------+---------------------+-------------------
_hyper_10_16_chunk | 30 | 40
_hyper_10_18_chunk | 40 | 50
_hyper_10_19_chunk | 50 | 60
(3 rows)
-- The continuous aggregate should be refreshed in the regions covered
-- by the dropped chunks, but not in the "gap" region, i.e., the
-- region of the chunk that was dropped via DROP TABLE.
SELECT * FROM drop_chunks_view
ORDER BY 1;
time_bucket | max
-------------+-----
0 | 4
5 | 100
20 | 20
45 | 500
50 | 500
(5 rows)
-- Now refresh in the region of the first two dropped chunks
CALL refresh_continuous_aggregate('drop_chunks_view', 0, :range_end_integer);
-- Aggregate data in the refreshed range should no longer exist since
-- the underlying data was dropped.
SELECT * FROM drop_chunks_view
ORDER BY 1;
time_bucket | max
-------------+-----
20 | 20
45 | 500
50 | 500
(3 rows)
--------------------------------------------------------------------
-- Check that we can create a materialized table in a tablespace. We
-- create one with tablespace and one without and compare them.
CREATE VIEW cagg_info AS
WITH
caggs AS (
SELECT format('%I.%I', user_view_schema, user_view_name)::regclass AS user_view,
format('%I.%I', direct_view_schema, direct_view_name)::regclass AS direct_view,
format('%I.%I', partial_view_schema, partial_view_name)::regclass AS partial_view,
format('%I.%I', ht.schema_name, ht.table_name)::regclass AS mat_relid
FROM _timescaledb_catalog.hypertable ht,
_timescaledb_catalog.continuous_agg cagg
WHERE ht.id = cagg.mat_hypertable_id
)
SELECT user_view,
pg_get_userbyid(relowner) AS user_view_owner,
relname AS mat_table,
(SELECT pg_get_userbyid(relowner) FROM pg_class WHERE oid = mat_relid) AS mat_table_owner,
direct_view,
(SELECT pg_get_userbyid(relowner) FROM pg_class WHERE oid = direct_view) AS direct_view_owner,
partial_view,
(SELECT pg_get_userbyid(relowner) FROM pg_class WHERE oid = partial_view) AS partial_view_owner,
(SELECT spcname FROM pg_tablespace WHERE oid = reltablespace) AS tablespace
FROM pg_class JOIN caggs ON pg_class.oid = caggs.mat_relid;
GRANT SELECT ON cagg_info TO PUBLIC;
CREATE VIEW chunk_info AS
SELECT ht.schema_name, ht.table_name, relname AS chunk_name,
(SELECT spcname FROM pg_tablespace WHERE oid = reltablespace) AS tablespace
FROM pg_class c,
_timescaledb_catalog.hypertable ht,
_timescaledb_catalog.chunk ch
WHERE ch.table_name = c.relname AND ht.id = ch.hypertable_id;
CREATE TABLE whatever(time BIGINT NOT NULL, data INTEGER);
\if :IS_DISTRIBUTED
SELECT hypertable_id AS whatever_nid
FROM create_distributed_hypertable('whatever', 'time', chunk_time_interval => 10, replication_factor => 2)
\gset
\else
SELECT hypertable_id AS whatever_nid
FROM create_hypertable('whatever', 'time', chunk_time_interval => 10)
\gset
\endif
SELECT set_integer_now_func('whatever', 'integer_now_test');
set_integer_now_func
----------------------
(1 row)
CREATE MATERIALIZED VIEW whatever_view_1
WITH (timescaledb.continuous, timescaledb.materialized_only=true) AS
SELECT time_bucket('5', time), COUNT(data)
FROM whatever GROUP BY 1 WITH NO DATA;
CREATE MATERIALIZED VIEW whatever_view_2
WITH (timescaledb.continuous, timescaledb.materialized_only=true)
TABLESPACE tablespace1 AS
SELECT time_bucket('5', time), COUNT(data)
FROM whatever GROUP BY 1 WITH NO DATA;
INSERT INTO whatever SELECT i, i FROM generate_series(0, 29) AS i;
CALL refresh_continuous_aggregate('whatever_view_1', NULL, NULL);
CALL refresh_continuous_aggregate('whatever_view_2', NULL, NULL);
SELECT user_view,
mat_table,
cagg_info.tablespace AS mat_tablespace,
chunk_name,
chunk_info.tablespace AS chunk_tablespace
FROM cagg_info, chunk_info
WHERE mat_table::text = table_name
AND user_view::text LIKE 'whatever_view%';
user_view | mat_table | mat_tablespace | chunk_name | chunk_tablespace
-----------------+-----------------------------+----------------+--------------------+------------------
whatever_view_1 | _materialized_hypertable_13 | | _hyper_13_24_chunk |
whatever_view_2 | _materialized_hypertable_14 | tablespace1 | _hyper_14_25_chunk | tablespace1
(2 rows)
ALTER MATERIALIZED VIEW whatever_view_1 SET TABLESPACE tablespace2;
SELECT user_view,
mat_table,
cagg_info.tablespace AS mat_tablespace,
chunk_name,
chunk_info.tablespace AS chunk_tablespace
FROM cagg_info, chunk_info
WHERE mat_table::text = table_name
AND user_view::text LIKE 'whatever_view%';
user_view | mat_table | mat_tablespace | chunk_name | chunk_tablespace
-----------------+-----------------------------+----------------+--------------------+------------------
whatever_view_1 | _materialized_hypertable_13 | tablespace2 | _hyper_13_24_chunk | tablespace2
whatever_view_2 | _materialized_hypertable_14 | tablespace1 | _hyper_14_25_chunk | tablespace1
(2 rows)
DROP MATERIALIZED VIEW whatever_view_1;
psql:include/cagg_ddl_common.sql:644: NOTICE: drop cascades to table _timescaledb_internal._hyper_13_24_chunk
DROP MATERIALIZED VIEW whatever_view_2;
psql:include/cagg_ddl_common.sql:645: NOTICE: drop cascades to table _timescaledb_internal._hyper_14_25_chunk
-- test bucket width expressions on integer hypertables
CREATE TABLE metrics_int2 (
time int2 NOT NULL,
device_id int,
v1 float,
v2 float
);