[Bug]: CAGG chunk interval resolves to source_table interval multiplied by 10

[noctarius]

What type of bug is this?

Performance issue, Unexpected error

What subsystems and features are affected?

Continuous aggregate

What happened?

When creating an continuous aggregate, the chunk interval taken for it is the chunk interval of the source table multiplied by 10, which increases way to fast. That is true for cagg on hypertable and cagg on cagg.

Example:
raw hypertable chunk interval: 7 days
cagg on hypertable: 70 days
cagg on cagg: 700 days

TimescaleDB version affected

2.9.3

PostgreSQL version used

14.7

What operating system did you use?

Cloud

What installation method did you use?

Not applicable

What platform did you run on?

Timescale Cloud

Relevant log output and stack trace

tsdb=> create table test (
    ts timestamptz primary key,
    value int not null
);
CREATE TABLE

tsdb=> select create_hypertable('test', 'ts');
 create_hypertable
--------------------
 (43,public,test,t)
(1 row)

tsdb=> create materialized view test1
    with (timescaledb.continuous) as
    select
        time_bucket('10 min', ts) bucket,
        avg(value) value
    from test
    group by 1;
NOTICE:  continuous aggregate "test1" is already up-to-date
CREATE MATERIALIZED VIEW

tsdb=> create materialized view test2
    with (timescaledb.continuous) as
    select
        time_bucket('1 h', bucket) bucket,
        avg(value) value
    from test1
    group by 1;
NOTICE:  continuous aggregate "test2" is already up-to-date
CREATE MATERIALIZED VIEW

tsdb=> select h.table_name name, _timescaledb_internal.to_interval(d.interval_length)
from _timescaledb_catalog.hypertable h
left join _timescaledb_catalog.dimension d on d.hypertable_id = h.id
where h.table_name = 'test'
union all
select c.user_view_name name, _timescaledb_internal.to_interval(d.interval_length)
from _timescaledb_catalog.continuous_agg c
left join _timescaledb_catalog.dimension d on d.hypertable_id = c.mat_hypertable_id
where c.user_view_name = 'test1'
union all
select c.user_view_name name, _timescaledb_internal.to_interval(d.interval_length)
from _timescaledb_catalog.continuous_agg c
left join _timescaledb_catalog.dimension d on d.hypertable_id = c.mat_hypertable_id
where c.user_view_name = 'test2';
 name  | to_interval
-------+-------------
 test  | 7 days
 test1 | 70 days
 test2 | 700 days
(3 rows)

### How can we reproduce the bug?

```bash
create table test (
    ts timestamptz primary key,
    value int not null
);

select create_hypertable('test', 'ts');

create materialized view test1
    with (timescaledb.continuous) as
    select
        time_bucket('10 min', ts) bucket,
        avg(value) value
    from test
    group by 1;

create materialized view test2
    with (timescaledb.continuous) as
    select
        time_bucket('1 h', bucket) bucket,
        avg(value) value
    from test1
    group by 1;

select h.table_name name, _timescaledb_internal.to_interval(d.interval_length)
from _timescaledb_catalog.hypertable h
left join _timescaledb_catalog.dimension d on d.hypertable_id = h.id
where h.table_name = 'test'
union all
select c.user_view_name name, _timescaledb_internal.to_interval(d.interval_length)
from _timescaledb_catalog.continuous_agg c
left join _timescaledb_catalog.dimension d on d.hypertable_id = c.mat_hypertable_id
where c.user_view_name = 'test1'
union all
select c.user_view_name name, _timescaledb_internal.to_interval(d.interval_length)
from _timescaledb_catalog.continuous_agg c
left join _timescaledb_catalog.dimension d on d.hypertable_id = c.mat_hypertable_id
where c.user_view_name = 'test2';

[jnidzwetzki]

Hello @noctarius,

Thank you for the detailed steps to reproduce the problem. I could see the same behavior in TimescaleDB 2.10.0 and PostgreSQL 14. The interval_length for the hybertable / caggs is different in the catalog table.

test2=# select * from _timescaledb_catalog.dimension;
 id | hypertable_id | column_name |       column_type        | aligned | num_slices | partitioning_func_schema | partitioning_func | interval_length | compress_interv
al_length | integer_now_func_schema | integer_now_func 
----+---------------+-------------+--------------------------+---------+------------+--------------------------+-------------------+-----------------+----------------
----------+-------------------------+------------------
  1 |             1 | ts          | timestamp with time zone | t       |            |                          |                   |    604800000000 |                
          |                         | 
  2 |             2 | bucket      | timestamp with time zone | t       |            |                          |                   |   6048000000000 |                
          |                         | 
  3 |             3 | bucket      | timestamp with time zone | t       |            |                          |                   |  60480000000000 |                
          |                         | 
(3 rows)