--- title: Troubleshoot continuous aggregates | Tiger Data Docs description: Troubleshoot common problems with continuous aggregates --- ## Continuous aggregate watermark is in the future Continuous aggregates use a watermark to indicate which time buckets have already been materialized. When you query a continuous aggregate, your query returns materialized data from before the watermark. It returns real-time, non-materialized data from after the watermark. In certain cases, the watermark might be in the future. If this happens, all buckets, including the most recent bucket, are materialized and below the watermark. No real-time data is returned. This might happen if you refresh your continuous aggregate over the time window `, NULL`, which materializes all recent data. It might also happen if you create a continuous aggregate using the `WITH DATA` option. This also implicitly refreshes your continuous aggregate with a window of `NULL, NULL`. To fix this, create a new continuous aggregate using the `WITH NO DATA` option. Then use a policy to refresh this continuous aggregate over an explicit time window. ### Creating a new continuous aggregate with an explicit refresh window 1. **Create the continuous aggregate with WITH NO DATA** ``` CREATE MATERIALIZED VIEW WITH (timescaledb.continuous) AS SELECT time_bucket('', ), , ... FROM GROUP BY bucket, WITH NO DATA; ``` 2. **Add a refresh policy with an explicit `end_offset`** ``` SELECT add_continuous_aggregate_policy('', start_offset => INTERVAL '30 day', end_offset => INTERVAL '1 hour', schedule_interval => INTERVAL '1 hour'); ``` 3. **Check the watermark** Get the ID for the materialization hypertable that contains the actual continuous aggregate data: ``` SELECT id FROM _timescaledb_catalog.hypertable WHERE table_name=( SELECT materialization_hypertable_name FROM timescaledb_information.continuous_aggregates WHERE view_name='' ); ``` 4. **Query for the watermark timestamp** Use the returned ID to query for the watermark’s timestamp: For TimescaleDB >= 2.12: ``` SELECT COALESCE( _timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark()), '-infinity'::timestamp with time zone ); ``` For TimescaleDB < 2.12: ``` SELECT COALESCE( _timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark()), '-infinity'::timestamp with time zone ); ``` Warning If you choose to delete your old continuous aggregate after creating a new one, beware of historical data loss. If your old continuous aggregate contained data that you dropped from your original hypertable, for example through a data retention policy, the dropped data is not included in your new continuous aggregate. ## Hierarchical continuous aggregate fails with incompatible bucket width ``` ERROR: cannot create continuous aggregate with incompatible bucket width DETAIL: Time bucket width of "" [1 year] should be multiple of the time bucket width of "" [1 day]. ``` If you attempt to create a hierarchical continuous aggregate, you must use compatible time buckets. You can’t create a continuous aggregate with a fixed-width time bucket on top of a continuous aggregate with a variable-width time bucket. For more information, see the restrictions section in [hierarchical continuous aggregates](/docs/learn/continuous-aggregates/hierarchical-continuous-aggregates#restrictions/index.md). ## Hypertable retention policy isn’t applying to continuous aggregates A retention policy set on a hypertable does not apply to any continuous aggregates made from the hypertable. This allows you to set different retention periods for raw and summarized data. To apply a retention policy to a continuous aggregate, set the policy on the continuous aggregate itself. ## Continuous aggregate doesn’t refresh with newly inserted historical data Materialized views are generally used with ordered data. If you insert historic data, or data that is not related to the current time, you need to refresh policies and reevaluate the values that are dragging from past to present. You can set up an after insert rule for your hypertable or upsert to trigger something that can validate what needs to be refreshed as the data is merged. Let’s say you inserted ordered timeframes named A, B, D, and F, and you already have a continuous aggregation looking for this data. If you now insert E, you need to refresh E and F. However, if you insert C you need to refresh C, D, E and F. For example: 1. A, B, D, and F are already materialized in a view with all data. 2. To insert C, split the data into `AB` and `DEF` subsets. 3. `AB` are consistent and the materialized data is too; you only need to reuse it. 4. Insert C, `DEF`, and refresh policies after C. This can use a lot of resources to process, especially if you have any important data in the past that also needs to be brought to the present. Consider an example where you have 300 columns on a single hypertable and use, for example, five of them in a continuous aggregation. In this case, it could be hard to refresh and would make more sense to isolate these columns in another hypertable. Alternatively, you might create one hypertable per metric and refresh them independently. ## Permissions error when migrating a continuous aggregate You might get a permissions error when migrating a continuous aggregate from old to new format using `cagg_migrate`. The user performing the migration must have the following permissions: - Select, insert, and update permissions on the tables `_timescale_catalog.continuous_agg_migrate_plan` and `_timescale_catalog.continuous_agg_migrate_plan_step` - Usage permissions on the sequence `_timescaledb_catalog.continuous_agg_migrate_plan_step_step_id_seq` To solve the problem, change to a user capable of granting permissions, and grant the following permissions to the user performing the migration: ``` GRANT SELECT, INSERT, UPDATE ON TABLE _timescaledb_catalog.continuous_agg_migrate_plan TO ; GRANT SELECT, INSERT, UPDATE ON TABLE _timescaledb_catalog.continuous_agg_migrate_plan_step TO ; GRANT USAGE ON SEQUENCE _timescaledb_catalog.continuous_agg_migrate_plan_step_step_id_seq TO ; ``` ## Queries fail when defining continuous aggregates but work on regular tables Continuous aggregates do not work on all queries. For example, TimescaleDB does not support window functions on continuous aggregates. If you use an unsupported function, you see the following error: ``` ERROR: invalid continuous aggregate view SQL state: 0A000 ``` The following table summarizes the aggregate functions supported in continuous aggregates: | Function, clause, or feature | TimescaleDB 2.6 and earlier | TimescaleDB 2.7, 2.8, and 2.9 | TimescaleDB 2.10 and later | | --------------------------------------------------------------------------------------------------------------------- | --------------------------- | ----------------------------- | -------------------------- | | Parallelizable aggregate functions | ✅ | ✅ | ✅ | | [Non-parallelizable SQL aggregates](https://www.postgresql.org/docs/current/parallel-plans.html#PARALLEL-AGGREGATION) | ❌ | ✅ | ✅ | | `ORDER BY` | ❌ | ✅ | ✅ | | Ordered-set aggregates | ❌ | ✅ | ✅ | | Hypothetical-set aggregates | ❌ | ✅ | ✅ | | `DISTINCT` in aggregate functions | ❌ | ✅ | ✅ | | `FILTER` in aggregate functions | ❌ | ✅ | ✅ | | `FROM` clause supports `JOINS` | ❌ | ❌ | ✅ | DISTINCT works in aggregate functions, not in the query definition. For example, for the table: ``` CREATE TABLE public.candle( symbol_id uuid NOT NULL, symbol text NOT NULL, "time" timestamp with time zone NOT NULL, open double precision NOT NULL, high double precision NOT NULL, low double precision NOT NULL, close double precision NOT NULL, volume double precision NOT NULL ); ``` - The following works: ``` CREATE MATERIALIZED VIEW candles_start_end WITH (timescaledb.continuous) AS SELECT time_bucket('1 hour', "time"), COUNT(DISTINCT symbol), first(time, time) as first_candle, last(time, time) as last_candle FROM candle GROUP BY 1; ``` - This does not: ``` CREATE MATERIALIZED VIEW candles_start_end WITH (timescaledb.continuous) AS SELECT DISTINCT ON (symbol) symbol,symbol_id, first(time, time) as first_candle, last(time, time) as last_candle FROM candle GROUP BY symbol_id; ``` ## Updates to previously materialized regions aren’t shown in real-time aggregates Real-time aggregates automatically add the most recent data when you query your continuous aggregate. In other words, they include data *more recent than* your last materialized bucket. If you add new *historical* data to an already-materialized bucket, it won’t be reflected in a real-time aggregate. You should wait for the next scheduled refresh, or manually refresh by calling `refresh_continuous_aggregate`. You can think of real-time aggregates as being eventually consistent for historical data. The following example shows how this works: 1. **Create the hypertable** ``` CREATE TABLE conditions( day DATE NOT NULL, city text NOT NULL, temperature INT NOT NULL ) WITH ( tsdb.hypertable, tsdb.partition_column='day', tsdb.chunk_interval='1 day' ); ``` For TimescaleDB [v2.23.0](https://github.com/timescale/timescaledb/releases/tag/2.23.0) and higher, the table is automatically partitioned on the first column in the table with a timestamp data type. If multiple columns are suitable candidates as a partitioning column, TimescaleDB throws an error and asks for an explicit definition. For earlier versions, set `partition_column` to a time column. If you are self-hosting TimescaleDB [v2.20.0](https://github.com/timescale/timescaledb/releases/tag/2.23.0) to [v2.22.1](https://github.com/timescale/timescaledb/releases/tag/2.23.0), to convert your data to the columnstore after a specific time interval, you have to call [add\_columnstore\_policy](/docs/reference/timescaledb/hypercore/add_columnstore_policy/index.md) after you call [CREATE TABLE](/docs/reference/timescaledb/hypertables/create_table/index.md) If you are self-hosting TimescaleDB [v2.19.3](https://github.com/timescale/timescaledb/releases/tag/2.19.3) and below, create a [PostgreSQL relational table](https://www.postgresql.org/docs/current/sql-createtable.html), then convert it using [create\_hypertable](/docs/reference/timescaledb/hypertables/create_hypertable/index.md). You then enable hypercore with a call to [ALTER TABLE](/docs/reference/timescaledb/hypercore/alter_table/index.md). 2. **Add data** ``` INSERT INTO conditions (day, city, temperature) VALUES ('2021-06-14', 'Moscow', 26), ('2021-06-15', 'Moscow', 22), ('2021-06-16', 'Moscow', 24), ('2021-06-17', 'Moscow', 24), ('2021-06-18', 'Moscow', 27), ('2021-06-19', 'Moscow', 28), ('2021-06-20', 'Moscow', 30), ('2021-06-21', 'Moscow', 31), ('2021-06-22', 'Moscow', 34), ('2021-06-23', 'Moscow', 34), ('2021-06-24', 'Moscow', 34), ('2021-06-25', 'Moscow', 32), ('2021-06-26', 'Moscow', 32), ('2021-06-27', 'Moscow', 31); ``` 3. **Create a continuous aggregate with no materialized data** 1. Create the continuous aggregate: ``` CREATE MATERIALIZED VIEW conditions_summary WITH (timescaledb.continuous, timescaledb.materialized_only=false) AS SELECT city, time_bucket('7 days', day) AS bucket, MIN(temperature), MAX(temperature) FROM conditions GROUP BY city, bucket WITH NO DATA; ``` 2. Check your data: ``` SELECT * FROM conditions_summary ORDER BY bucket; ``` The query fetches data directly from the hypertable: | city | bucket | min | max | | ------ | ---------- | --- | --- | | Moscow | 2021-06-14 | 22 | 30 | | Moscow | 2021-06-21 | 31 | 34 | 4. **Materialize the data** 1. Refresh the continuous aggregate: ``` CALL refresh_continuous_aggregate('conditions_summary', '2021-06-14', '2021-06-21'); ``` 2. Check your data: ``` SELECT * FROM conditions_summary ORDER BY bucket; ``` The same data is returned, but this time from the underlying materialized table: | city | bucket | min | max | | ------ | ---------- | --- | --- | | Moscow | 2021-06-14 | 22 | 30 | | Moscow | 2021-06-21 | 31 | 34 | 5. **Update data in a previously materialized bucket** 1. Update the data in your hypertable: ``` UPDATE conditions SET temperature = 35 WHERE day = '2021-06-14' and city = 'Moscow'; ``` 2. Check your data: ``` SELECT * FROM conditions_summary ORDER BY bucket; ``` The updated data is not yet visible. This is because the changes have not been materialized: | city | bucket | min | max | | ------ | ---------- | --- | --- | | Moscow | 2021-06-14 | 22 | 30 | | Moscow | 2021-06-21 | 31 | 34 | 6. **Refresh to update the materialized region** 1. Refresh the data: ``` CALL refresh_continuous_aggregate('conditions_summary', '2021-06-14', '2021-06-21'); ``` 2. Check your data: ``` SELECT * FROM conditions_summary ORDER BY bucket; ``` The update is now visible: | city | bucket | min | max | | ------ | ---------- | --- | --- | | Moscow | 2021-06-14 | 22 | 35 | | Moscow | 2021-06-21 | 31 | 34 | ## Queries using `locf()` don’t treat NULL values as missing When you have a query that uses a last observation carried forward (locf) function, the query carries forward NULL values by default. If you want the function to ignore NULL values instead, you can set `treat_null_as_missing=TRUE` as the second parameter in the query. For example: ``` dev=# select * FROM (select time_bucket_gapfill(4, time,-5,13), locf(avg(v)::int,treat_null_as_missing:=true) FROM (VALUES (0,0),(8,NULL)) v(time, v) WHERE time BETWEEN 0 AND 10 GROUP BY 1) i ORDER BY 1 DESC; time_bucket_gapfill | locf ---------------------+------ 12 | 0 8 | 0 4 | 0 0 | 0 -4 | -8 | (6 rows) ``` ## Scheduled jobs stop running Your scheduled jobs might stop running for various reasons. On self-hosted TimescaleDB, you can fix this by restarting background workers: - [TimescaleDB >= 2.12](#tab-panel-450) - [TimescaleDB < 2.12](#tab-panel-451) ``` SELECT _timescaledb_functions.start_background_workers(); ``` ``` SELECT _timescaledb_internal.start_background_workers(); ``` On Tiger Cloud and Managed Service for TimescaleDB, restart background workers by doing one of the following: - Run `SELECT timescaledb_pre_restore()`, followed by `SELECT timescaledb_post_restore()`. - Power the service off and on again. This might cause a downtime of a few minutes while the service restores from backup and replays the write-ahead log.