TimescaleDB multiple continuous aggregates not filling up with data

Viewed 654

I'm trying to create several TimescaleDB continuous aggregate tables, like in the examples at the following link, but there is no data in the views when I query them...

https://docs.timescale.com/latest/using-timescaledb/continuous-aggregates

Here's my code for creating the continuous aggregate view. I created a 1-hour interval, a 2-hour interval, and a 6-hour interval (i.e. 3 continuous aggregate views, which is apparently allowed in TimescaleDB >= v1.4). I'm using TimescaleDB v1.7.3 in a Docker container on PostgreSQL v11.9.

CREATE VIEW time_series_mv_1_hour_interval
WITH (timescaledb.continuous) AS
SELECT 
    gateway,
    time_bucket('01:00:00'::interval, timestamp_utc) AS timestamp_utc,
    avg(spm) AS spm,
    avg(hyd::integer) AS hyd
FROM public.time_series
GROUP BY 
    gateway, 
    time_bucket('01:00:00'::interval, timestamp_utc);

Here's a picture of my three views "successfully created": enter image description here

The underlying table was created as follows, and it's constantly having new IoT data inserted:

create table if not exists public.time_series (
    timestamp_utc timestamp without time zone NOT NULL,
    gateway text NOT NULL,
    spm real NULL,
    hyd bool NULL
    UNIQUE (timestamp_utc, gateway)
);

After creating the table, I ran the following to create the TimescaleDB hypertable: https://docs.timescale.com/latest/api#create_hypertable

SELECT create_hypertable('public.time_series', 'timestamp_utc');

Then I created a smart index on gateway first, then time:

https://blog.timescale.com/blog/use-composite-indexes-to-speed-up-time-series-queries-sql-8ca2df6b3aaa/ https://docs.timescale.com/latest/using-timescaledb/schema-management#indexing

CREATE INDEX ON public.time_series (gateway, timestamp_utc DESC);

Why is there no data in my newly-created continuous aggregate tables/views? I've waited about 16 hours, and still no data.

I ran the following simple query, and no records were returned...

SELECT * from time_series_mv_1_hour_interval;
1 Answers

Solution: restart the database, then drop-cascade the existing continuous aggregate views that weren't filling up with rows, and re-create them using the exact same steps...

I wish I knew what was causing the problem the first time around, but at least it's working now.

-Sean

Related