How to delete aggregated data

TimescaleDB and PostgreSQL
1

Hello everyone,

recently I started to use timescale with postgresql and I am loving it! I have some newbie question though. How do I delete specific rows from the aggregated tables? If I issue a simple delete query I receive error

SQL Error [55000]: ERROR: cannot delete from view "TemperaturesDaily"
  Detail: Views containing UNION, INTERSECT, or EXCEPT are not automatically updatable.

Here are some details: Raw data are first saved to a hypertable defined as:

SELECT create_hypertable('""Temperatures""', 'Timestamp');
SELECT add_retention_policy('""Temperatures""', INTERVAL '1 week 1 day');

Temperatures table is pretty simple, it has following columns: Timestamp, LocationId, DatasourceId and Value.

Data is then aggregated to another table defined as follows:

CREATE MATERIALIZED VIEW ""TemperaturesDaily""
WITH (timescaledb.continuous)
AS
  SELECT time_bucket(INTERVAL '1d', ""Timestamp"") as ""Timestamp"", 
     ""LocationId"",
     ""DataSourceId"", 
     CAST(AVG(""Value"") AS real) AS ""AverageTemp"", 
     MIN(""Value"") AS ""MinTemp"",
     MAX(""Value"") AS ""MaxTemp""
   FROM ""Temperatures""
   GROUP BY 1, ""LocationId"", ""DataSourceId"";

SELECT add_continuous_aggregate_policy('""TemperaturesDaily""',
   start_offset => INTERVAL '1 week',
   end_offset => INTERVAL '1 hour',
   schedule_interval => INTERVAL '1 hour');

Now sometimes I need to delete all aggregated data that has e.g. LocationId = 123. I can delete data from the last week by deleting it from the raw hypertable and refreshing the aggregate, but sometimes I also need to delete older data. What am I doing wrong?

1 Like
2

Hello @Dyce welcome to the community. Super happy that you’re enjoying our work!

Just chatting with a colleague, we think that this is a feature we don’t currently support. That being the case it would be GREAT if you would be able to raise a GitHub issue? We have some notes to help you along here.

You could try deleting from the materialized view that’s “underneath” the continuous aggregate (it may work). We also think it’s possible that an upcoming version might support it, but raising the GH issue may well get you the answer on that.

It would be useful to understand the use case. Is it just that you don’t want the old data hanging around or is it essential for it to be “gone”? That’s just for interest, but it also helps support a feature request too.

3

Hello,

thank you for the information. I will open github issue later (if similar does not exist). It is not really essential, I could find some workaround, but in my use case the data is basically useless at that point and it would just take up space. I will provide details in the GH issue. Thank you for helping.

1 Like
4

Thanks for the solution.

6

The suggested solution works, however @LorraineP’s comment doesn’t inspire a lot of confidence. Will this have adverse effects down the line, or involve any risks? If not, then why does TSDB/Postgres explicitly prohibit this and error out?

ERROR:  cannot delete from view "summary"
DETAIL:  Views containing UNION, INTERSECT, or EXCEPT are not automatically updatable.
HINT:  To enable deleting from the view, provide an INSTEAD OF DELETE trigger or an unconditional ON DELETE DO INSTEAD rule.

Some clarity would be appreciated.

Thanks!