[Bug]: incorrect interval_length value in _timescaledb_catalog.dimension

[salisbury-espinosa]

What type of bug is this?

Other

What subsystems and features are affected?

Other

What happened?

I have cagg 'caggTestMonth' with this time_bucket:

time_bucket('1 month', "bucketDaily") AS "bucketMonthly"

=> find hypertable_id for this cagg:

db=# select * from _timescaledb_catalog.continuous_agg where user_view_name = 'caggTestMonth';
-[ RECORD 1 ]------------+-------------------------
mat_hypertable_id        | 88
raw_hypertable_id        | 87
parent_mat_hypertable_id | 87
user_view_schema         | public
user_view_name           | caggTestMonth
partial_view_schema      | _timescaledb_internal
partial_view_name        | _partial_view_88
direct_view_schema       | _timescaledb_internal
direct_view_name         | _direct_view_88
materialized_only        | t
finalized                | t

=> find dimension for this hypertable_id 88 and 87

db=# select * from _timescaledb_catalog.dimension where hypertable_id IN (87, 88);
-[ RECORD 1 ]------------+-------------------------
id                       | 87
hypertable_id            | 87
column_name              | bucketDaily
column_type              | timestamp with time zone
aligned                  | t
num_slices               | 
partitioning_func_schema | 
partitioning_func        | 
interval_length          | 864000000000
compress_interval_length | 
integer_now_func_schema  | 
integer_now_func         | 
-[ RECORD 2 ]------------+-------------------------
id                       | 88
hypertable_id            | 88
column_name              | bucketMonthly
column_type              | timestamp with time zone
aligned                  | t
num_slices               | 
partitioning_func_schema | 
partitioning_func        | 
interval_length          | 864000000000
compress_interval_length | 
integer_now_func_schema  | 
integer_now_func         | 

why is the 'interval_length' in both hypertable set to 864000000000 ?
this behavior for all intervals that I tested (1 hour, 1 year, 1 month, 1 day)

TimescaleDB version affected

2.17.1

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?

On prem/Self-hosted

Relevant log output and stack trace

How can we reproduce the bug?

create hierarchical caggs with different intervals (time_bucket) and look at the table _timescaledb_catalog.dimension

[antekresic]

Unfortunately, time bucket interval used for caggs currently has nothing to do with chunk interval on underlying hypertables. They are calculated based on the raw data hypertable they reference (I think 10x the size of that hypertable).

[salisbury-espinosa]

Unfortunately, time bucket interval used for caggs currently has nothing to do with chunk interval on underlying hypertables. They are calculated based on the raw data hypertable they reference (I think 10x the size of that hypertable).

and how to get the interval size from tables with metainformation (like _timescaledb_catalog.dimension) for cagss ?

[antekresic]

Not sure what you are asking, you already showed it in your initial post:

select * from _timescaledb_catalog.dimension where hypertable_id IN (87, 88);

[salisbury-espinosa]

Not sure what you are asking, you already showed it in your initial post:

select * from _timescaledb_catalog.dimension where hypertable_id IN (87, 88);

but it doesn't work as you say for CAGGS...

[antekresic]

Caggs have an undelying materialized hypertable (id 88 in your example). You can inspect that just like a regular hypertable.

[salisbury-espinosa]

Caggs have an undelying materialized hypertable (id 88 in your example). You can inspect that just like a regular hypertable.

I did just that.
but here is the issue title for caggs: incorrect interval_length value in _timescaledb_catalog.dimension.
how can i get it interval_length for caggs ?

[antekresic]

I do apologize, I misunderstood what you are looking for. You are talking about the interval you have set in your aggregation, namely time bucket. I'm not aware of that being stored anywhere in our catalog tables except directly in the cagg view definition.