[Bug]: Creating a C-agg with time_bucket(1 month) returns an error around unsupported interval, however, the view is still created

[hardikm10]

What type of bug is this?

Incorrect result

What subsystems and features are affected?

Continuous aggregate

What happened?

Creating a C-agg with time_bucket(1 month) returns an error around unsupported interval, however, the view is still created. If you then query the view, it errors out : 'ERROR: interval defined in terms of month, year, century etc. not supported'

I guess the view shouldn't be even created.

TimescaleDB version affected

2.7.2

PostgreSQL version used

14.4

What operating system did you use?

Docker

What installation method did you use?

Docker

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

tsdb=> select * from sensor_summary_1month order by bucket limit 1;
ERROR:  interval defined in terms of month, year, century etc. not supported

How can we reproduce the bug?

drop table sensor_data cascade;


create table sensor_data(
time timestamptz not null,
sensor_id integer not null,
cpu double precision null,
temperature double precision null );

select from create_hypertable('sensor_data','time');


INSERT INTO sensor_data
SELECT
time + (INTERVAL '1 minute' * random()) AS time,
sensor_id,
random() AS cpu,
random()* 100 AS temperature
FROM
generate_series(now() - INTERVAL '1 week', now() - INTERVAL '1 day', INTERVAL '10 minute') AS g1(time),
generate_series(1, 100, 1 ) AS g2(sensor_id)
ORDER BY time;

create materialized view sensor_summary_1month with (timescaledb.continuous)
            as select time_bucket('1 month', time) bucket,
            sensor_id, avg(cpu) as avg_cpu, count(temperature), sum(temperature )
            from sensor_data group by bucket, sensor_id;
NOTICE:  refreshing continuous aggregate "sensor_summary_1month"
HINT:  Use WITH NO DATA if you do not want to refresh the continuous aggregate on creation.
ERROR:  interval defined in terms of month, year, century etc. not supported
CONTEXT:  SQL statement "INSERT INTO _timescaledb_internal._materialized_hypertable_2 SELECT * FROM _timescaledb_internal._partial_view_2 AS I WHERE I.bucket >= '-infinity' AND I.bucket < '2022-09-01 00:00:00+00' ;"

tsdb=> select * from sensor_summary_1month order by bucket limit 1;
ERROR:  interval defined in terms of month, year, century etc. not supported

[fabriziomello]

@hardikm10 currently we check the time_bucket interval during the execution of this function, it means that during the CAgg creation we didn't check it, just when try to execute it, for example:

fabrizio=# create materialized view sensor_summary_1month with (timescaledb.continuous)
            as select time_bucket('1 month', time) bucket,
            sensor_id, avg(cpu) as avg_cpu, count(temperature), sum(temperature )
            from sensor_data group by bucket, sensor_id 
            with no data;
CREATE MATERIALIZED VIEW

fabrizio=# select * from sensor_summary_1month ;
ERROR:  0A000: interval defined in terms of month, year, century etc. not supported

And this behavior is this since forever... we have regression tests around this error just when execute the time_bucket function.

You pointed out that the view is created even if the automatic refresh raise an error. It happen because we execute two separated transactions for CAgg creation and automatic refresh and just the second transaction failed.

[hardikm10]

Thanks @fabriziomello. I guess my perspective was from the UX side i.e if the view isn't workable, why let it create.

[mfundul]

The creation of the continuous aggregate is a multi-transactional process, which may explain why it's not very easy to catch all the errors atomically. I feel like fixing this might require more effort than it's worth.

[svenklemm]

Fixed by #4641