Improve the error message when a time_bucket with offset/origin parameter is used with continuous aggregates

[lkshminarayanan]

What type of bug is this?

Other

What subsystems and features are affected?

Continuous aggregate

What happened?

Using time_bucket with origin/offset parameters in a continuous aggregate is currently not supported. (Refer to #2265 for further discussion on this.) When such an attempt is made by the user, the following error is thrown : continuous aggregate view must include a valid time bucket function, which is misleading as the time_bucket with an offset/origin parameter is a valid function outside the context of a continuous aggregate. A better error like continuous aggregate view doesn't support time bucket functions with origin/offset parameters must be thrown instead for these cases.

TimescaleDB version affected

2.11.0

PostgreSQL version used

15.3

What operating system did you use?

Ubuntu

What installation method did you use?

Source

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

-- Create the table
CREATE TABLE temperature (
  time timestamptz NOT NULL,
  value float
);

-- Create hypertable
SELECT create_hypertable('temperature', 'time');

-- Populate it
INSERT INTO temperature
  SELECT time, ceil(random() * 100)::int
    FROM generate_series('2000-01-01 0:00:00+0'::timestamptz,
                         '2000-01-01 23:59:59+0','1m') time;

-- Try creating continuous aggregate with a time_bucket that has an offset
CREATE MATERIALIZED VIEW temp_4_hours
  WITH  (timescaledb.continuous) AS
  SELECT time_bucket('4 hour', time, '30m'::interval), avg(value)
    FROM temperature
    GROUP BY 1 ORDER BY 1;
CREATE TABLE
Time: 15.492 ms
    create_hypertable     
--------------------------
 (1,public,temperature,t)
(1 row)

Time: 4.939 ms
INSERT 0 1440
Time: 13.086 ms
ERROR:  continuous aggregate view must include a valid time bucket function
Time: 0.876 ms

How can we reproduce the bug?

-- Create the table
CREATE TABLE temperature (
  time timestamptz NOT NULL,
  value float
);

-- Create hypertable
SELECT create_hypertable('temperature', 'time');

-- Populate it
INSERT INTO temperature
  SELECT time, ceil(random() * 100)::int
    FROM generate_series('2000-01-01 0:00:00+0'::timestamptz,
                         '2000-01-01 23:59:59+0','1m') time;

-- Try creating continuous aggregate with a time_bucket that has an offset
CREATE MATERIALIZED VIEW temp_4_hours
  WITH  (timescaledb.continuous) AS
  SELECT time_bucket('4 hour', time, '30m'::interval), avg(value)
    FROM temperature
    GROUP BY 1 ORDER BY 1;