Allow CTE in continuous aggregates

[gma2th]

Relevant system information:

• OS: OSX
• PostgreSQL version (output of postgres --version): 12.2
• TimescaleDB version (output of \dx in psql): 1.7.1
• Installation method: source

Describe the bug Using a cte in a continuous aggregate will raise the following error:

ERROR:  0A000: invalid SELECT query for continuous aggregate
LOCATION:  cagg_validate_query, create.c:745

To Reproduce

create table t (ts timestamptz, a int);
select create_hypertable ('t' , 'ts');
insert into t values (now() , 1) , (now() , 2);

create view v with (timescaledb.continuous) as
with my_t as (select * from t)
select a, time_bucket(interval '1 minute', ts) bucket from t group by a, bucket ;

create view v with (timescaledb.continuous) as
with my_bucket as (select a, time_bucket(interval '1 minute', ts) bucket from t group by a, bucket)
select * from my_bucket 

Expected behavior Is there a reason to not support cte in continuous aggregate? Is it planned to support them in the future?

[k-rus]

Currently only one hypertable can be used in FROM clause in cagg definition. If I don't mistake, any query with CTE confirming this requirement can be easily converted to a query without CTE. @gma2th Do you have any specific reason why you need such simple CTEs? It seems there is no point to have them with the current limitation on caggs.

[gma2th]

I query only one hypertable. I can do it without a cte, but using a cte avoid to repeat myself and simplify the query as I make several operations between the columns and their aggregated value.

[k-rus]

@gma2th Thank you for clarifying! Can you provide an example(s) where you demonstrate that CTE with single hypertable helps to define simple query? E.g., the same cagg definition with CTE and without CTE where it is easy to see your point. The examples in OP don't really support well your request.

[Wintermute79]

Here's my current issue as an example for a CTE:

I want to create 7 very similar continuous aggregate which differ only in their categories from 1 to 7.

... WHERE id IN (SELECT id FROM sensors_to_categories WHERE cat = 7)

Table sensors_to_categories holds many thousand entries, assigning each sensor to either of the 7 categories.

I would not store the category id in the hypertable along with every single sensor value, so there is no version without CTE.

Is there a different way to achieve the same goal? Otherwise original sensor data is deleted after 30 days via retention_policy.

[sdebruyn]

Given that CTEs are part of ANSI SQL, the lack of support for it should be mentioned as a clear warning on the documentation pages for continuous aggregates. Even the simple CTE in OP shows how CTEs can help you to properly make your SQL clearer and more understandable. Why you should care about CTEs

[0xForerunner]

+1, Would also be very useful.