[Bug]: Re-creating a continuous aggregate table with an existing hyper table -- not updating

[Auricmondal]

What type of bug is this?

Unexpected error

What subsystems and features are affected?

Continuous aggregate

What happened?

If I try to drop an existing continuous aggregate to modify and recreate it, the newly formed view does not update at all whenever a new data is added to the hyper table.

TimescaleDB version affected

I have tried this on versions: 2.16.1 and 2.10.2

PostgreSQL version used

16.4

What operating system did you use?

Ubuntu 24.04.1 LTS

What installation method did you use?

Docker

What platform did you run on?

Other

Relevant log output and stack trace

No response

How can we reproduce the bug?

--- Command Lines ---
-- Create ---

CREATE TABLE my_table (
  id VARCHAR NOT NULL,
  data INTEGER DEFAULT 1,
  timestamp TIMESTAMPTZ NOT NULL UNIQUE DEFAULT NOW(),
	 PRIMARY KEY (id, timestamp)
);

CREATE TABLE my_table2 (
  id VARCHAR Primary key unique,
  install_at DATE
);

CREATE MATERIALIZED VIEW IF NOT EXISTS test2
      WITH (timescaledb.continuous) AS
SELECT
	a.id,
	time_bucket('100 years', a.timestamp) AS bucket,
    SUM(a.data) as Total_data,
	MAX(timestamp) as max_timestamp,
b.install_at
FROM my_table a
left join my_table2 b on a.id = b.id
GROUP BY a.id, bucket, b.install_at

SELECT create_hypertable('my_table', 'timestamp');


-- Get ---

select * from my_table;
select * from my_table2;

SELECT hypertable_name
FROM timescaledb_information.hypertables;


-- Alter ---

ALTER TABLE my_table
  ALTER COLUMN id TYPE VARCHAR;


--drop ---

drop table my_table
drop table my_table2
DROP MATERIALIZED VIEW test2;


--Insert ---

INSERT INTO my_table (id, data, timestamp)
VALUES ('5', 4, NOW());

INSERT INTO my_table2 (id,install_at)
VALUES ('4', '2024-10-26 19:15:00+05:30' );

--Alter---

ALTER MATERIALIZED VIEW test2 set (timescaledb.materialized_only = false);

Step 1: create the tables
step 2: create the hyper table
step 3: create the continuous aggregate
step 4: run the alter command
step 5: add data to the tables

---It should work fine till now --

step 6: Drop the view
step 7: recreate the view
step 8: try inserting data to the hyper-table

--- you will see that the view is not updating ---

[akuzm]

Sounds like you're encountering the situation described here: https://docs.timescale.com/use-timescale/latest/continuous-aggregates/real-time-aggregates/#real-time-aggregates-and-refreshing-historical-data

Does manually calling the refresh_continuous_aggregate fix this?

[Auricmondal]

Hey @akuzm, I'm not sure if the data can be called historical data, for I was adding data from after the present date and time each time. Moreover, the same settings were working fine the first time but not the second. Also, manually running refresh_continuous_aggregate does solve the issue for the newly added data until it is called, but again, if new data is inserted, the issue remains.

[fabriziomello]

@Auricmondal after step 8 you should either:

• change the cagg to realtime (step 4), or
• manually refresh the cagg, or
• setup a refresh policy to process it in an scheduled way (https://docs.timescale.com/use-timescale/latest/continuous-aggregates/refresh-policies/)

You can also set the cagg realtime during it creating using the timescaledb.materialized_only=false in the definition. I.e.:

CREATE MATERIALIZED VIEW IF NOT EXISTS test2
      WITH (timescaledb.continuous, timescaledb.materialized_only=false) AS
SELECT
	a.id,
	time_bucket('100 years', a.timestamp) AS bucket,
    SUM(a.data) as Total_data,
	MAX(timestamp) as max_timestamp,
b.install_at
FROM my_table a
left join my_table2 b on a.id = b.id
GROUP BY a.id, bucket, b.install_at;