DFFF
April 8, 2024, 10:30am
1
I have a hypertable: measurements, which is materialized into: measurements_hourly. I accidentally dropped chunks from meaurement_hourly instead measurements by running:
SELECT drop_chunks('measurements_hourly', INTERVAL '1 month');
Now, I want to fix my mistake and recalculate measurements_hourly aggregation table. To do this I ran:
CALL refresh_continuous_aggregate('meaurement_hourly', NULL, NULL);
but in response I got: continuous aggregate "measurements_hourly" is already up-to-date. I guess this is beacause no chunks from measurements has been modified, so timescaledb thinks there is nothing to materialize. To fix this I simulated a change in measurements table by running:
UPDATE measurements
set value = value;
After this I could successfully rematerialize all measurements data by running:
CALL refresh_continuous_aggregate('meaurement_hourly', NULL, NULL);
I am wondering if there is any built-in way of doing what I just did? Something like “refresh all buckets, not only those that are modified”.
FYI: my timescaledb version is: 2.14.2.
That’s a great question and I’d like to suggest you to bring it as a new interface for the extension.
We have a table that controls it that is the concept behind the invalidation_logs but I don’t think we have a public interface to change it.
Here’s another related issue in some extent:
opened 11:23PM - 27 Nov 23 UTC
bug
documentation
continuous_aggregate
### What type of bug is this?
Other
### What subsystems and features are affec… ted?
Continuous aggregate
### What happened?
I have a Continuous Aggregate that selects from a hypertable and joins a second table. When **only the second table** is updated and I call `refresh_continuous_aggregate`, the Continuous Aggregate is not refreshed. But, if I "touch" my hypertable with an update setting `time = time` and call `refresh_continuous_aggregate` again, then the Continuous Aggregate is refreshed for the same bucket of the "updated" records.
I know that refresh policies don't track secondary tables used in `JOIN`s, but I expect that intentionally calling `refresh_continuous_aggregate` would refresh the data. So it looks like a bug to me, but I understend if you tell me it's not - in this case, I would ask if there's a recommended way to make the refresh happen.
### TimescaleDB version affected
2.12.2
### PostgreSQL version used
14
### What operating system did you use?
Debian 6.1.38-4 x86_64 GNU/Linux
### What installation method did you use?
Not applicable
### What platform did you run on?
On prem/Self-hosted
### Relevant log output and stack trace
_No response_
### How can we reproduce the bug?
```bash
CREATE TABLE metrics (
time timestamp NOT NULL,
value int NOT NULL,
device_id int NOT NULL
);
SELECT create_hypertable('metrics', 'time');
CREATE TABLE devices (
id serial,
is_working boolean
);
CREATE MATERIALIZED VIEW agg_metrics
WITH (timescaledb.continuous)
AS
SELECT
time_bucket('1 day', m.time) AS bucket,
AVG(m.value) AS metric
FROM metrics m
INNER JOIN devices d ON m.device_id = d.id
WHERE d.is_working = 't'
GROUP BY time_bucket('1 day', m.time)
WITH NO DATA;
INSERT INTO devices (is_working) values(true);
INSERT INTO devices (is_working) values(true);
INSERT INTO devices (is_working) values(false);
INSERT INTO metrics (time, device_id, value) values('2023-01-01 00:00:00', 1, 1000);
INSERT INTO metrics (time, device_id, value) values('2023-01-01 00:00:00', 2, 2000);
INSERT INTO metrics (time, device_id, value) values('2023-01-01 00:00:00', 3, 3000);
CALL refresh_continuous_aggregate('agg_metrics', '2022-12-01', '2023-02-28');
SELECT * FROM agg_metrics;
-- |bucket |metric |
-- +-----------------------+---------------------+
-- |2023-01-01 00:00:00.000|1500.0000000000000000|
UPDATE devices SET is_working = 't' WHERE id = 3
CALL refresh_continuous_aggregate('agg_metrics', '2022-12-01', '2023-02-28');
SELECT * FROM agg_metrics;
-- |bucket |metric |
-- +-----------------------+---------------------+
-- |2023-01-01 00:00:00.000|1500.0000000000000000|
UPDATE metrics SET time = time WHERE device_id = 1
CALL refresh_continuous_aggregate('agg_metrics', '2022-12-01', '2023-02-28');
SELECT * FROM agg_metrics;
-- |bucket |metric |
-- +-----------------------+---------------------+
-- |2023-01-01 00:00:00.000|2000.0000000000000000|
```