1-- This file and its contents are licensed under the Timescale License. 2-- Please see the included NOTICE for copyright information and 3-- LICENSE-TIMESCALE for a copy of the license. 4 5-------------------------------------------------------------------------- 6-- show_chunks and drop_chunks functions on a compressed table 7-- (issue https://github.com/timescale/timescaledb/issues/1535) 8 9-- create a table that will not be compressed 10CREATE TABLE public.uncompressed_table(time date NOT NULL, temp float8, device_id text); 11CREATE INDEX ON public.uncompressed_table(time DESC); 12SELECT create_hypertable('public.uncompressed_table', 'time', chunk_time_interval => interval '1 day'); 13 14INSERT INTO public.uncompressed_table VALUES('2020-03-01', 1.0, 'dev1'); 15INSERT INTO public.uncompressed_table VALUES('2020-03-05', 2.0, 'dev1'); 16INSERT INTO public.uncompressed_table VALUES('2020-03-07', 3.0, 'dev1'); 17INSERT INTO public.uncompressed_table VALUES('2020-03-08', 4.0, 'dev7'); 18INSERT INTO public.uncompressed_table VALUES('2020-03-09', 5.0, 'dev7'); 19INSERT INTO public.uncompressed_table VALUES('2020-03-10', 6.0, 'dev7'); 20 21-- create next table that is going to be compressed: 22CREATE TABLE public.table_to_compress (time date NOT NULL, acq_id bigint, value bigint); 23CREATE INDEX idx_table_to_compress_acq_id ON public.table_to_compress(acq_id); 24SELECT create_hypertable('public.table_to_compress', 'time', chunk_time_interval => interval '1 day'); 25ALTER TABLE public.table_to_compress SET (timescaledb.compress, timescaledb.compress_segmentby = 'acq_id'); 26INSERT INTO public.table_to_compress VALUES ('2020-01-01', 1234567, 777888); 27INSERT INTO public.table_to_compress VALUES ('2020-02-01', 567567, 890890); 28INSERT INTO public.table_to_compress VALUES ('2020-02-10', 1234, 5678); 29SELECT show_chunks('public.uncompressed_table'); 30SELECT show_chunks('public.table_to_compress'); 31SELECT show_chunks('public.table_to_compress', older_than=>'1 day'::interval); 32SELECT show_chunks('public.table_to_compress', newer_than=>'1 day'::interval); 33-- compress all chunks of the table: 34SELECT compress_chunk(show_chunks('public.table_to_compress')); 35-- and run the queries again to make sure results are the same 36SELECT show_chunks('public.uncompressed_table'); 37SELECT show_chunks('public.table_to_compress'); 38SELECT show_chunks('public.table_to_compress', older_than=>'1 day'::interval); 39SELECT show_chunks('public.table_to_compress', newer_than=>'1 day'::interval); 40-- drop all hypertables' old chunks 41SELECT drop_chunks(table_name::regclass, older_than=>'1 day'::interval) 42 FROM _timescaledb_catalog.hypertable 43 WHERE schema_name = current_schema() 44ORDER BY table_name DESC; 45SELECT show_chunks('public.uncompressed_table'); 46SELECT show_chunks('public.table_to_compress'); 47