Cannot create materialized view with ORDER BY clause in TimescaleDb 2.7.0-CodePudding

The timescale docs seem to suggest that since 2.7.0 it should be possible to make materialized views which include an order by clause. (See "timescale.finalized" option here and "function support" here).

However, I have not been able to get this to work for me. When I try to create my materialized view I get:

ERROR:  invalid continuous aggregate query
DETAIL:  ORDER BY is not supported in queries defining continuous aggregates.
HINT:  Use ORDER BY clauses in SELECTS from the continuous aggregate view instead.

Is there something fundamental I'm misunderstanding about how this should work?

Here is the full script:

> select extname, extversion from pg_extension where extname = 'timescaledb';

   extname   | extversion
------------- ------------
 timescaledb | 2.7.0
(1 row)


> CREATE TABLE stocks_real_time (
  time TIMESTAMPTZ NOT NULL,
  price DOUBLE PRECISION NULL
  );

CREATE TABLE


> SELECT create_hypertable('stocks_real_time','time');

       create_hypertable
-------------------------------
 (7,public,stocks_real_time,t)
(1 row)


> CREATE MATERIALIZED VIEW mat_view_stocks_real_time
  WITH (timescaledb.continuous)
  AS (
    SELECT
      time_bucket('60 minutes', time) as bucketed_time,
      AVG(price) as price
    FROM stocks_real_time
    GROUP BY bucketed_time
    ORDER BY bucketed_time
  );

ERROR:  invalid continuous aggregate query
DETAIL:  ORDER BY is not supported in queries defining continuous aggregates.
HINT: Use ORDER BY clauses in SELECTS from the continuous aggregate view instead.

I still get the same error if I explicitly add "timescaledb.finalized=true" to the with clause.

CodePudding user response：

(NB: I work at Timescale!)

We have an open issue to support this, and I think the confusion is because we now support aggregates with order by clauses in them, this means things like: SELECT percentile_cont(price) WITHIN GROUP (ORDER BY time) or SELECT array_agg(foo ORDER BY time)

So I think that is probably where the confusion is coming from, but like I said, we have an open issue to support that sort of order by. You can also apply the order by in the SELECT from the continuous aggregate though: ie SELECT * FROM mat_view_stocks_real_time ORDER BY bucketed_time and that should work just fine.