cagg_errors_deprecated.out

-- This file and its contents are licensed under the Timescale License.
-- Please see the included NOTICE for copyright information and
-- LICENSE-TIMESCALE for a copy of the license.
\set ON_ERROR_STOP 0
\set VERBOSITY default
--negative tests for query validation
create table mat_t1( a integer, b integer,c TEXT);
CREATE TABLE conditions (
      timec        TIMESTAMPTZ       NOT NULL,
      location    TEXT              NOT NULL,
      temperature integer  NULL,
      humidity    DOUBLE PRECISION  NULL,
      timemeasure TIMESTAMPTZ,
      timeinterval INTERVAL
    );
select table_name from create_hypertable( 'conditions', 'timec');
 table_name 
------------
 conditions
(1 row)

CREATE MATERIALIZED VIEW mat_m1 WITH (timescaledb.continuous, timescaledb.finalized = false, timescaledb.myfill = 1)
as
select location , min(temperature)
from conditions
group by time_bucket('1d', timec), location WITH NO DATA;
ERROR:  unrecognized parameter "timescaledb.myfill"
--valid PG option
CREATE MATERIALIZED VIEW mat_m1 WITH (timescaledb.continuous, timescaledb.finalized = false, check_option = LOCAL )
as
select * from conditions , mat_t1 WITH NO DATA;
ERROR:  unsupported combination of storage parameters
DETAIL:  A continuous aggregate does not support standard storage parameters.
HINT:  Use only parameters with the "timescaledb." prefix when creating a continuous aggregate.
--non-hypertable
CREATE MATERIALIZED VIEW mat_m1 WITH (timescaledb.continuous, timescaledb.finalized = false)
as
select a, count(*) from mat_t1
group by a WITH NO DATA;
ERROR:  table "mat_t1" is not a hypertable
-- no group by
CREATE MATERIALIZED VIEW mat_m1 WITH (timescaledb.continuous, timescaledb.finalized = false)
as
select count(*) from conditions  WITH NO DATA;
ERROR:  invalid continuous aggregate query
HINT:  Include at least one aggregate function and a GROUP BY clause with time bucket.
-- no time_bucket in group by
CREATE MATERIALIZED VIEW mat_m1 WITH (timescaledb.continuous, timescaledb.finalized = false)
as
select count(*) from conditions group by location WITH NO DATA;
ERROR:  continuous aggregate view must include a valid time bucket function
-- with valid query in a CTE
CREATE MATERIALIZED VIEW mat_m1 WITH (timescaledb.continuous, timescaledb.finalized = false)
AS
with m1 as (
Select location, count(*) from conditions
 group by time_bucket('1week', timec) , location)
select * from m1 WITH NO DATA;
ERROR:  invalid continuous aggregate query
DETAIL:  CTEs, subqueries and set-returning functions are not supported by continuous aggregates.
--with DISTINCT ON
CREATE MATERIALIZED VIEW mat_m1 WITH (timescaledb.continuous, timescaledb.finalized = false)
as
 select distinct on ( location ) count(*)  from conditions group by location, time_bucket('1week', timec)  WITH NO DATA;
ERROR:  invalid continuous aggregate query
DETAIL:  DISTINCT / DISTINCT ON queries are not supported by continuous aggregates.
--aggregate with DISTINCT
CREATE MATERIALIZED VIEW mat_m1 WITH (timescaledb.continuous, timescaledb.finalized = false)
AS
Select time_bucket('1week', timec),
 count(location) , sum(distinct temperature) from conditions
 group by time_bucket('1week', timec) , location WITH NO DATA;
ERROR:  aggregates with FILTER / DISTINCT / ORDER BY are not supported
--aggregate with FILTER
CREATE MATERIALIZED VIEW mat_m1 WITH (timescaledb.continuous, timescaledb.finalized = false)
AS
Select time_bucket('1week', timec),
 sum(temperature) filter ( where humidity > 20 ) from conditions
 group by time_bucket('1week', timec) , location WITH NO DATA;
ERROR:  aggregates with FILTER / DISTINCT / ORDER BY are not supported
-- aggregate with filter in having clause
CREATE MATERIALIZED VIEW mat_m1 WITH (timescaledb.continuous, timescaledb.finalized = false)
AS
Select time_bucket('1week', timec), max(temperature)
from conditions
 group by time_bucket('1week', timec) , location
 having sum(temperature) filter ( where humidity > 20 ) > 50 WITH NO DATA;
ERROR:  aggregates with FILTER / DISTINCT / ORDER BY are not supported
-- time_bucket on non partitioning column of hypertable
CREATE MATERIALIZED VIEW mat_m1 WITH (timescaledb.continuous, timescaledb.finalized = false)
AS
Select max(temperature)
from conditions
 group by time_bucket('1week', timemeasure) , location WITH NO DATA;
ERROR:  time bucket function must reference a hypertable dimension column
--time_bucket on expression
CREATE MATERIALIZED VIEW mat_m1 WITH (timescaledb.continuous, timescaledb.finalized = false)
AS
Select max(temperature)
from conditions
 group by time_bucket('1week', timec+ '10 minutes'::interval) , location WITH NO DATA;
ERROR:  time bucket function must reference a hypertable dimension column
--multiple time_bucket functions
CREATE MATERIALIZED VIEW mat_m1 WITH (timescaledb.continuous, timescaledb.finalized = false)
AS
Select max(temperature)
from conditions
 group by time_bucket('1week', timec) , time_bucket('1month', timec), location WITH NO DATA;
ERROR:  continuous aggregate view cannot contain multiple time bucket functions
--time_bucket using additional args
CREATE MATERIALIZED VIEW mat_m1 WITH (timescaledb.continuous, timescaledb.finalized = false)
AS
Select max(temperature)
from conditions
 group by time_bucket( INTERVAL '5 minutes', timec, INTERVAL '-2.5 minutes') , location WITH NO DATA;
ERROR:  continuous aggregate view must include a valid time bucket function
--time_bucket using non-const for first argument
CREATE MATERIALIZED VIEW mat_m1 WITH (timescaledb.continuous, timescaledb.finalized = false)
AS
Select max(temperature)
from conditions
 group by time_bucket( timeinterval, timec) , location WITH NO DATA;
ERROR:  only immutable expressions allowed in time bucket function
HINT:  Use an immutable expression as first argument to the time bucket function.
-- ordered set aggr
CREATE MATERIALIZED VIEW mat_m1 WITH (timescaledb.continuous, timescaledb.finalized = false)
AS
Select mode() within group( order by humidity)
from conditions
 group by time_bucket('1week', timec)  WITH NO DATA;
ERROR:  aggregates with FILTER / DISTINCT / ORDER BY are not supported
--window function
CREATE MATERIALIZED VIEW mat_m1 WITH (timescaledb.continuous, timescaledb.finalized = false)
AS
Select avg(temperature) over( order by humidity)
from conditions
 WITH NO DATA;
ERROR:  invalid continuous aggregate query
DETAIL:  Window functions are not supported by continuous aggregates.
--aggregate without combine function
CREATE MATERIALIZED VIEW mat_m1 WITH (timescaledb.continuous, timescaledb.finalized = false)
AS
Select json_agg(location)
from conditions
 group by time_bucket('1week', timec) , location WITH NO DATA;
ERROR:  aggregates which are not parallelizable are not supported
;
CREATE MATERIALIZED VIEW mat_m1 WITH (timescaledb.continuous, timescaledb.finalized = false)
AS
Select sum(humidity), avg(temperature), array_agg(location)
from conditions
 group by time_bucket('1week', timec) , location WITH NO DATA;
ERROR:  aggregates which are not parallelizable are not supported
;
-- userdefined aggregate without combine function
CREATE AGGREGATE newavg (
   sfunc = int4_avg_accum, basetype = int4, stype = _int8,
   finalfunc = int8_avg,
   initcond1 = '{0,0}'
);
CREATE MATERIALIZED VIEW mat_m1 WITH (timescaledb.continuous, timescaledb.finalized = false)
AS
Select sum(humidity), newavg(temperature::int4)
from conditions
 group by time_bucket('1week', timec) , location WITH NO DATA;
ERROR:  aggregates which are not parallelizable are not supported
;
-- using subqueries
CREATE MATERIALIZED VIEW mat_m1 WITH (timescaledb.continuous, timescaledb.finalized = false)
AS
Select sum(humidity), avg(temperature::int4)
from
( select humidity, temperature, location, timec
from conditions ) q
 group by time_bucket('1week', timec) , location  WITH NO DATA;
ERROR:  invalid continuous aggregate view
CREATE MATERIALIZED VIEW mat_m1 WITH (timescaledb.continuous, timescaledb.finalized = false)
AS
select * from
( Select sum(humidity), avg(temperature::int4)
from conditions
 group by time_bucket('1week', timec) , location )  q WITH NO DATA;
ERROR:  invalid continuous aggregate query
HINT:  Include at least one aggregate function and a GROUP BY clause with time bucket.
--using limit /limit offset
CREATE MATERIALIZED VIEW mat_m1 WITH (timescaledb.continuous, timescaledb.finalized = false)
AS
Select sum(humidity), avg(temperature::int4)
from conditions
 group by time_bucket('1week', timec) , location
limit 10  WITH NO DATA;
ERROR:  invalid continuous aggregate query
DETAIL:  LIMIT and LIMIT OFFSET are not supported in queries defining continuous aggregates.
HINT:  Use LIMIT and LIMIT OFFSET in SELECTS from the continuous aggregate view instead.
CREATE MATERIALIZED VIEW mat_m1 WITH (timescaledb.continuous, timescaledb.finalized = false)
AS
Select sum(humidity), avg(temperature::int4)
from conditions
 group by time_bucket('1week', timec) , location
offset 10 WITH NO DATA;
ERROR:  invalid continuous aggregate query
DETAIL:  LIMIT and LIMIT OFFSET are not supported in queries defining continuous aggregates.
HINT:  Use LIMIT and LIMIT OFFSET in SELECTS from the continuous aggregate view instead.
--using ORDER BY in view defintion
CREATE MATERIALIZED VIEW mat_m1 WITH (timescaledb.continuous, timescaledb.finalized = false)
AS
Select sum(humidity), avg(temperature::int4)
from conditions
 group by time_bucket('1week', timec) , location
ORDER BY 1 WITH NO DATA;
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.
--using FETCH
CREATE MATERIALIZED VIEW mat_m1 WITH (timescaledb.continuous, timescaledb.finalized = false)
AS
Select sum(humidity), avg(temperature::int4)
from conditions
 group by time_bucket('1week', timec) , location
fetch first 10 rows only WITH NO DATA;
ERROR:  invalid continuous aggregate query
DETAIL:  LIMIT and LIMIT OFFSET are not supported in queries defining continuous aggregates.
HINT:  Use LIMIT and LIMIT OFFSET in SELECTS from the continuous aggregate view instead.
--using locking clauses FOR clause
--all should be disabled. we cannot guarntee locks on the hypertable
CREATE MATERIALIZED VIEW mat_m1 WITH (timescaledb.continuous, timescaledb.finalized = false)
AS
Select sum(humidity), avg(temperature::int4)
from conditions
 group by time_bucket('1week', timec) , location
FOR KEY SHARE WITH NO DATA;
ERROR:  FOR KEY SHARE is not allowed with GROUP BY clause
CREATE MATERIALIZED VIEW mat_m1 WITH (timescaledb.continuous, timescaledb.finalized = false)
AS
Select sum(humidity), avg(temperature::int4)
from conditions
 group by time_bucket('1week', timec) , location
FOR SHARE WITH NO DATA;
ERROR:  FOR SHARE is not allowed with GROUP BY clause
CREATE MATERIALIZED VIEW mat_m1 WITH (timescaledb.continuous, timescaledb.finalized = false)
AS
Select sum(humidity), avg(temperature::int4)
from conditions
 group by time_bucket('1week', timec) , location
FOR UPDATE WITH NO DATA;
ERROR:  FOR UPDATE is not allowed with GROUP BY clause
CREATE MATERIALIZED VIEW mat_m1 WITH (timescaledb.continuous, timescaledb.finalized = false)
AS
Select sum(humidity), avg(temperature::int4)
from conditions
 group by time_bucket('1week', timec) , location
FOR NO KEY UPDATE WITH NO DATA;
ERROR:  FOR NO KEY UPDATE is not allowed with GROUP BY clause
--tablesample clause
CREATE MATERIALIZED VIEW mat_m1 WITH (timescaledb.continuous, timescaledb.finalized = false)
AS
Select sum(humidity), avg(temperature::int4)
from conditions tablesample bernoulli(0.2)
 group by time_bucket('1week', timec) , location
 WITH NO DATA;
ERROR:  invalid continuous aggregate view
-- ONLY in from clause
CREATE MATERIALIZED VIEW mat_m1 WITH (timescaledb.continuous, timescaledb.finalized = false)
AS
Select sum(humidity), avg(temperature::int4)
from ONLY conditions
 group by time_bucket('1week', timec) , location  WITH NO DATA;
ERROR:  invalid continuous aggregate view
--grouping sets and variants
CREATE MATERIALIZED VIEW mat_m1 WITH (timescaledb.continuous, timescaledb.finalized = false)
AS
Select sum(humidity), avg(temperature::int4)
from conditions
 group by grouping sets(time_bucket('1week', timec) , location )  WITH NO DATA;
ERROR:  invalid continuous aggregate query
DETAIL:  GROUP BY GROUPING SETS, ROLLUP and CUBE are not supported by continuous aggregates
HINT:  Define multiple continuous aggregates with different grouping levels.
CREATE MATERIALIZED VIEW mat_m1 WITH (timescaledb.continuous, timescaledb.finalized = false)
AS
Select sum(humidity), avg(temperature::int4)
from conditions
group by rollup(time_bucket('1week', timec) , location )  WITH NO DATA;
ERROR:  invalid continuous aggregate query
DETAIL:  GROUP BY GROUPING SETS, ROLLUP and CUBE are not supported by continuous aggregates
HINT:  Define multiple continuous aggregates with different grouping levels.
--NO immutable functions -- check all clauses
CREATE FUNCTION test_stablefunc(int) RETURNS int LANGUAGE 'sql'
       STABLE AS 'SELECT $1 + 10';
CREATE MATERIALIZED VIEW mat_m1 WITH (timescaledb.continuous, timescaledb.finalized = false)
AS
Select sum(humidity), max(timec + INTERVAL '1h')
from conditions
group by time_bucket('1week', timec) , location   WITH NO DATA;
ERROR:  only immutable functions supported in continuous aggregate view
HINT:  Make sure all functions in the continuous aggregate definition have IMMUTABLE volatility. Note that functions or expressions may be IMMUTABLE for one data type, but STABLE or VOLATILE for another.
CREATE MATERIALIZED VIEW mat_m1 WITH (timescaledb.continuous, timescaledb.finalized = false)
AS
Select sum(humidity), min(location)
from conditions
group by time_bucket('1week', timec)
having  max(timec + INTERVAL '1h') > '2010-01-01 09:00:00-08' WITH NO DATA;
ERROR:  only immutable functions supported in continuous aggregate view
HINT:  Make sure all functions in the continuous aggregate definition have IMMUTABLE volatility. Note that functions or expressions may be IMMUTABLE for one data type, but STABLE or VOLATILE for another.
CREATE MATERIALIZED VIEW mat_m1 WITH (timescaledb.continuous, timescaledb.finalized = false)
AS
Select sum( test_stablefunc(humidity::int) ), min(location)
from conditions
group by time_bucket('1week', timec) WITH NO DATA;
ERROR:  only immutable functions supported in continuous aggregate view
HINT:  Make sure all functions in the continuous aggregate definition have IMMUTABLE volatility. Note that functions or expressions may be IMMUTABLE for one data type, but STABLE or VOLATILE for another.
CREATE MATERIALIZED VIEW mat_m1 WITH (timescaledb.continuous, timescaledb.finalized = false)
AS
Select sum( temperature ), min(location)
from conditions
group by time_bucket('1week', timec), test_stablefunc(humidity::int) WITH NO DATA;
ERROR:  only immutable functions supported in continuous aggregate view
HINT:  Make sure all functions in the continuous aggregate definition have IMMUTABLE volatility. Note that functions or expressions may be IMMUTABLE for one data type, but STABLE or VOLATILE for another.
-- Should use CREATE MATERIALIZED VIEW to create continuous aggregates
CREATE VIEW continuous_aggs_errors_tbl1 WITH (timescaledb.continuous, timescaledb.finalized = false) AS
SELECT time_bucket('1 week', timec)
  FROM conditions
GROUP BY time_bucket('1 week', timec);
ERROR:  cannot create continuous aggregate with CREATE VIEW
HINT:  Use CREATE MATERIALIZED VIEW to create a continuous aggregate.
-- row security on table
create table rowsec_tab( a bigint, b integer, c integer);
select table_name from create_hypertable( 'rowsec_tab', 'a', chunk_time_interval=>10);
NOTICE:  adding not-null constraint to column "a"
DETAIL:  Time dimensions cannot have NULL values.
 table_name 
------------
 rowsec_tab
(1 row)

CREATE OR REPLACE FUNCTION integer_now_test() returns bigint LANGUAGE SQL STABLE as $$ SELECT coalesce(max(a), 0)::bigint FROM rowsec_tab $$;
SELECT set_integer_now_func('rowsec_tab', 'integer_now_test');
 set_integer_now_func 
----------------------
 
(1 row)

alter table rowsec_tab ENABLE ROW LEVEL SECURITY;
create policy rowsec_tab_allview ON rowsec_tab FOR SELECT USING(true);
CREATE MATERIALIZED VIEW mat_m1 WITH (timescaledb.continuous, timescaledb.finalized = false)
AS
Select sum( b), min(c)
from rowsec_tab
group by time_bucket('1', a) WITH NO DATA;
ERROR:  cannot create continuous aggregate on hypertable with row security
-- cagg on cagg not allowed
CREATE MATERIALIZED VIEW mat_m1 WITH (timescaledb.continuous, timescaledb.finalized = false)
AS
SELECT time_bucket('1 day', timec) AS bucket
  FROM conditions
GROUP BY time_bucket('1 day', timec);
NOTICE:  continuous aggregate "mat_m1" is already up-to-date
CREATE MATERIALIZED VIEW mat_m2_on_mat_m1 WITH (timescaledb.continuous)
AS
SELECT time_bucket('1 week', bucket) AS bucket
  FROM mat_m1
GROUP BY time_bucket('1 week', bucket);
ERROR:  old format of continuous aggregate is not supported
HINT:  Run "CALL cagg_migrate('public.mat_m1');" to migrate to the new format.
drop table conditions cascade;
NOTICE:  drop cascades to 3 other objects
DETAIL:  drop cascades to view mat_m1
drop cascades to view _timescaledb_internal._partial_view_3
drop cascades to view _timescaledb_internal._direct_view_3
--negative tests for WITH options
CREATE TABLE conditions (
      timec       TIMESTAMPTZ       NOT NULL,
      location    TEXT              NOT NULL,
      temperature DOUBLE PRECISION  NULL,
      humidity    DOUBLE PRECISION  NULL,
      lowp        double precision NULL,
      highp       double precision null,
      allnull     double precision null
    );
select table_name from create_hypertable( 'conditions', 'timec');
 table_name 
------------
 conditions
(1 row)

create materialized view mat_with_test( timec, minl, sumt , sumh)
WITH (timescaledb.continuous, timescaledb.finalized = false)
as
select time_bucket('1day', timec), min(location), sum(temperature),sum(humidity)
from conditions
group by time_bucket('1day', timec) WITH NO DATA;
SELECT  h.schema_name AS "MAT_SCHEMA_NAME",
       h.table_name AS "MAT_TABLE_NAME",
       partial_view_name as "PART_VIEW_NAME",
       partial_view_schema as "PART_VIEW_SCHEMA"
FROM _timescaledb_catalog.continuous_agg ca
INNER JOIN _timescaledb_catalog.hypertable h ON(h.id = ca.mat_hypertable_id)
WHERE user_view_name = 'mat_with_test'
\gset
\set ON_ERROR_STOP 0
ALTER MATERIALIZED VIEW mat_with_test SET(timescaledb.create_group_indexes = 'false');
ERROR:  cannot alter create_group_indexes option for continuous aggregates
ALTER MATERIALIZED VIEW mat_with_test SET(timescaledb.create_group_indexes = 'true');
ERROR:  cannot alter create_group_indexes option for continuous aggregates
ALTER MATERIALIZED VIEW mat_with_test ALTER timec DROP default;
ERROR:  cannot alter only SET options of a continuous aggregate
\set ON_ERROR_STOP 1
\set VERBOSITY terse
DROP TABLE conditions CASCADE;
NOTICE:  drop cascades to 3 other objects
--test WITH using a hypertable with an integer time dimension
CREATE TABLE conditions (
      timec       SMALLINT       NOT NULL,
      location    TEXT              NOT NULL,
      temperature DOUBLE PRECISION  NULL,
      humidity    DOUBLE PRECISION  NULL,
      lowp        double precision NULL,
      highp       double precision null,
      allnull     double precision null
    );
select table_name from create_hypertable( 'conditions', 'timec', chunk_time_interval=> 100);
 table_name 
------------
 conditions
(1 row)

CREATE OR REPLACE FUNCTION integer_now_test_s() returns smallint LANGUAGE SQL STABLE as $$ SELECT coalesce(max(timec), 0)::smallint FROM conditions $$;
SELECT set_integer_now_func('conditions', 'integer_now_test_s');
 set_integer_now_func 
----------------------
 
(1 row)

\set ON_ERROR_STOP 0
create materialized view mat_with_test( timec, minl, sumt , sumh)
WITH (timescaledb.continuous, timescaledb.finalized = false)
as
select time_bucket(100, timec), min(location), sum(temperature),sum(humidity)
from conditions
group by time_bucket(100, timec) WITH NO DATA;
ERROR:  time bucket function must reference a hypertable dimension column
create materialized view mat_with_test( timec, minl, sumt , sumh)
WITH (timescaledb.continuous, timescaledb.finalized = false)
as
select time_bucket(100, timec), min(location), sum(temperature),sum(humidity)
from conditions
group by time_bucket(100, timec) WITH NO DATA;
ERROR:  time bucket function must reference a hypertable dimension column
ALTER TABLE conditions ALTER timec type int;
create materialized view mat_with_test( timec, minl, sumt , sumh)
WITH (timescaledb.continuous, timescaledb.finalized = false)
as
select time_bucket(100, timec), min(location), sum(temperature),sum(humidity)
from conditions
group by time_bucket(100, timec) WITH NO DATA;
\set ON_ERROR_STOP 1
DROP TABLE conditions cascade;
NOTICE:  drop cascades to 3 other objects
CREATE TABLE conditions (
      timec       BIGINT       NOT NULL,
      location    TEXT              NOT NULL,
      temperature DOUBLE PRECISION  NULL,
      humidity    DOUBLE PRECISION  NULL,
      lowp        double precision NULL,
      highp       double precision null,
      allnull     double precision null
    );
select table_name from create_hypertable( 'conditions', 'timec', chunk_time_interval=> 100);
 table_name 
------------
 conditions
(1 row)

CREATE OR REPLACE FUNCTION integer_now_test_b() returns bigint LANGUAGE SQL STABLE as $$ SELECT coalesce(max(timec), 0)::bigint FROM conditions $$;
SELECT set_integer_now_func('conditions', 'integer_now_test_b');
 set_integer_now_func 
----------------------
 
(1 row)

create materialized view mat_with_test( timec, minl, sumt , sumh)
WITH (timescaledb.continuous, timescaledb.finalized = false)
as
select time_bucket(BIGINT '100', timec), min(location), sum(temperature),sum(humidity)
from conditions
group by 1 WITH NO DATA;
-- custom time partition functions are not supported with invalidations
CREATE FUNCTION text_part_func(TEXT) RETURNS BIGINT
    AS $$ SELECT length($1)::BIGINT $$
    LANGUAGE SQL IMMUTABLE;
CREATE TABLE text_time(time TEXT);
    SELECT create_hypertable('text_time', 'time', chunk_time_interval => 10, time_partitioning_func => 'text_part_func');
NOTICE:  adding not-null constraint to column "time"
    create_hypertable    
-------------------------
 (10,public,text_time,t)
(1 row)

\set VERBOSITY default
\set ON_ERROR_STOP 0
CREATE MATERIALIZED VIEW text_view
    WITH (timescaledb.continuous, timescaledb.finalized = false)
    AS SELECT time_bucket('5', text_part_func(time)), COUNT(time)
        FROM text_time
        GROUP BY 1 WITH NO DATA;
ERROR:  custom partitioning functions not supported with continuous aggregates
\set ON_ERROR_STOP 1
-- Check that we get an error when mixing normal materialized views
-- and continuous aggregates.
CREATE MATERIALIZED VIEW normal_mat_view AS
SELECT time_bucket('5', text_part_func(time)), COUNT(time)
  FROM text_time
GROUP BY 1 WITH NO DATA;
\set VERBOSITY terse
\set ON_ERROR_STOP 0
DROP MATERIALIZED VIEW normal_mat_view, mat_with_test;
ERROR:  mixing continuous aggregates and other objects not allowed
\set ON_ERROR_STOP 1
DROP TABLE text_time CASCADE;
NOTICE:  drop cascades to materialized view normal_mat_view
CREATE TABLE measurements (time TIMESTAMPTZ NOT NULL, device INT, value FLOAT);
SELECT create_hypertable('measurements', 'time');
     create_hypertable      
----------------------------
 (11,public,measurements,t)
(1 row)

INSERT INTO measurements VALUES ('2019-03-04 13:30', 1, 1.3);
-- Add a continuous aggregate on the measurements table and a policy
-- to be able to test error cases for the add_job function.
CREATE MATERIALIZED VIEW measurements_summary WITH (timescaledb.continuous, timescaledb.finalized = false) AS
SELECT time_bucket('1 day', time), COUNT(time)
  FROM measurements
GROUP BY 1 WITH NO DATA;
-- First test that add_job checks the config. It is currently possible
-- to add non-custom jobs using the add_job function so we need to
-- test that the function actually checks the config parameters. These
-- should all generate errors, for different reasons...
\set ON_ERROR_STOP 0
-- ... this one because it is missing a field.
SELECT add_job(
       '_timescaledb_internal.policy_refresh_continuous_aggregate'::regproc,
       '1 hour'::interval,
       check_config => '_timescaledb_internal.policy_refresh_continuous_aggregate_check'::regproc,
       config => '{"end_offset": null, "start_offset": null}');
ERROR:  could not find "mat_hypertable_id" in config for job
-- ... this one because it has a bad value for start_offset
SELECT add_job(
       '_timescaledb_internal.policy_refresh_continuous_aggregate'::regproc,
       '1 hour'::interval,
       check_config => '_timescaledb_internal.policy_refresh_continuous_aggregate_check'::regproc,
       config => '{"end_offset": null, "start_offset": "1 fortnight", "mat_hypertable_id": 11}');
ERROR:  invalid input syntax for type interval: "1 fortnight"
-- ... this one because it has a bad value for end_offset
SELECT add_job(
       '_timescaledb_internal.policy_refresh_continuous_aggregate'::regproc,
       '1 hour'::interval,
       check_config => '_timescaledb_internal.policy_refresh_continuous_aggregate_check'::regproc,
       config => '{"end_offset": "chicken", "start_offset": null, "mat_hypertable_id": 11}');
ERROR:  invalid input syntax for type interval: "chicken"
\set ON_ERROR_STOP 1
SELECT add_continuous_aggregate_policy('measurements_summary', NULL, NULL, '1 h'::interval) AS job_id
\gset
\x on
SELECT * FROM _timescaledb_config.bgw_job WHERE id = :job_id;
-[ RECORD 1 ]-----+--------------------------------------------------------------------
id                | 1000
application_name  | Refresh Continuous Aggregate Policy [1000]
schedule_interval | @ 1 hour
max_runtime       | @ 0
max_retries       | -1
retry_period      | @ 1 hour
proc_schema       | _timescaledb_internal
proc_name         | policy_refresh_continuous_aggregate
owner             | default_perm_user
scheduled         | t
fixed_schedule    | f
initial_start     | 
hypertable_id     | 12
config            | {"end_offset": null, "start_offset": null, "mat_hypertable_id": 12}
check_schema      | _timescaledb_internal
check_name        | policy_refresh_continuous_aggregate_check
timezone          | 

\x off
-- These are all weird values for the parameters for the continuous
-- aggregate jobs and should generate an error. Since the config will
-- be replaced, we will also generate error for missing arguments.
\set ON_ERROR_STOP 0
SELECT alter_job(:job_id, config => '{"end_offset": "1 week", "start_offset": "2 fortnights"}');
ERROR:  could not find "mat_hypertable_id" in config for job
SELECT alter_job(:job_id,
       config => '{"mat_hypertable_id": 11, "end_offset": "chicken", "start_offset": "1 fortnights"}');
ERROR:  invalid input syntax for type interval: "1 fortnights"
SELECT alter_job(:job_id,
       config => '{"mat_hypertable_id": 11, "end_offset": "chicken", "start_offset": "1 week"}');
ERROR:  invalid input syntax for type interval: "chicken"
\set ON_ERROR_STOP 1
DROP TABLE measurements CASCADE;
NOTICE:  drop cascades to 3 other objects
DROP TABLE conditions CASCADE;
NOTICE:  drop cascades to 3 other objects
-- test handling of invalid mat_hypertable_id
create table i2980(time timestamptz not null);
select create_hypertable('i2980','time');
  create_hypertable  
---------------------
 (13,public,i2980,t)
(1 row)

create materialized view i2980_cagg with (timescaledb.continuous, timescaledb.finalized = false) AS SELECT time_bucket('1h',time), avg(7) FROM i2980 GROUP BY 1;
NOTICE:  continuous aggregate "i2980_cagg" is already up-to-date
select add_continuous_aggregate_policy('i2980_cagg',NULL,NULL,'4h') AS job_id \gset
\set ON_ERROR_STOP 0
select alter_job(:job_id,config:='{"end_offset": null, "start_offset": null, "mat_hypertable_id": 1000}');
ERROR:  configuration materialization hypertable id 1000 not found
--test creating continuous aggregate with compression enabled --
CREATE MATERIALIZED VIEW  i2980_cagg2 with (timescaledb.continuous, timescaledb.compress, timescaledb.finalized = false)
AS SELECT time_bucket('1h',time), avg(7) FROM i2980 GROUP BY 1;
ERROR:  cannot enable compression while creating a continuous aggregate
--this one succeeds
CREATE MATERIALIZED VIEW  i2980_cagg2 with (timescaledb.continuous, timescaledb.finalized = false)
AS SELECT time_bucket('1h',time) as bucket, avg(7) FROM i2980 GROUP BY 1;
NOTICE:  continuous aggregate "i2980_cagg2" is already up-to-date
--now enable compression with invalid parameters
ALTER MATERIALIZED VIEW i2980_cagg2 SET ( timescaledb.compress,
timescaledb.compress_segmentby = 'bucket');
ERROR:  cannot use column "bucket" for both ordering and segmenting
ALTER MATERIALIZED VIEW i2980_cagg2 SET ( timescaledb.compress,
timescaledb.compress_orderby = 'bucket');
--enable compression and test re-enabling compression
ALTER MATERIALIZED VIEW i2980_cagg2 SET ( timescaledb.compress);
insert into i2980 select now();
call refresh_continuous_aggregate('i2980_cagg2', NULL, NULL);
SELECT compress_chunk(ch) FROM show_chunks('i2980_cagg2') ch;
             compress_chunk              
-----------------------------------------
 _timescaledb_internal._hyper_15_3_chunk
(1 row)

ALTER MATERIALIZED VIEW i2980_cagg2 SET ( timescaledb.compress = 'false');
ERROR:  cannot change configuration on already compressed chunks
ALTER MATERIALIZED VIEW i2980_cagg2 SET ( timescaledb.compress = 'true');
ERROR:  cannot change configuration on already compressed chunks
ALTER MATERIALIZED VIEW i2980_cagg2 SET ( timescaledb.compress, timescaledb.compress_segmentby = 'bucket');
ERROR:  cannot change configuration on already compressed chunks
--Errors with compression policy on caggs--
select add_continuous_aggregate_policy('i2980_cagg2', interval '10 day', interval '2 day' ,'4h') AS job_id ;
 job_id 
--------
   1002
(1 row)

SELECT add_compression_policy('i2980_cagg', '8 day'::interval);
ERROR:  compression not enabled on continuous aggregate "i2980_cagg"
ALTER MATERIALIZED VIEW i2980_cagg SET ( timescaledb.compress );
SELECT add_compression_policy('i2980_cagg', '8 day'::interval);
ERROR:  compress_after value for compression policy should be greater than the start of the refresh window of continuous aggregate policy for i2980_cagg
SELECT add_continuous_aggregate_policy('i2980_cagg2', '10 day'::interval, '6 day'::interval);
ERROR:  function add_continuous_aggregate_policy(unknown, interval, interval) does not exist at character 8
SELECT add_compression_policy('i2980_cagg2', '3 day'::interval);
ERROR:  compress_after value for compression policy should be greater than the start of the refresh window of continuous aggregate policy for i2980_cagg2
SELECT add_compression_policy('i2980_cagg2', '1 day'::interval);
ERROR:  compress_after value for compression policy should be greater than the start of the refresh window of continuous aggregate policy for i2980_cagg2
SELECT add_compression_policy('i2980_cagg2', '3'::integer);
ERROR:  unsupported compress_after argument type, expected type : interval
SELECT add_compression_policy('i2980_cagg2', 13::integer);
ERROR:  unsupported compress_after argument type, expected type : interval
SELECT materialization_hypertable_schema || '.' || materialization_hypertable_name AS "MAT_TABLE_NAME"
FROM timescaledb_information.continuous_aggregates
WHERE view_name = 'i2980_cagg2'
\gset
SELECT add_compression_policy( :'MAT_TABLE_NAME', 13::integer);
ERROR:  cannot add compression policy to materialized hypertable "_materialized_hypertable_15" 
--TEST compressing cagg chunks without enabling compression
SELECT count(*) FROM (select decompress_chunk(ch) FROM show_chunks('i2980_cagg2') ch ) q;
 count 
-------
     1
(1 row)

ALTER MATERIALIZED VIEW i2980_cagg2 SET (timescaledb.compress = 'false');
SELECT compress_chunk(ch) FROM show_chunks('i2980_cagg2') ch;
ERROR:  compression not enabled on "i2980_cagg2"
-- test error handling when trying to create cagg on internal hypertable
CREATE TABLE comp_ht_test(time timestamptz NOT NULL);
SELECT table_name FROM create_hypertable('comp_ht_test','time');
  table_name  
--------------
 comp_ht_test
(1 row)

ALTER TABLE comp_ht_test SET (timescaledb.compress);
SELECT
  format('%I.%I', ht.schema_name, ht.table_name) AS "INTERNALTABLE"
FROM
  _timescaledb_catalog.hypertable ht
  INNER JOIN _timescaledb_catalog.hypertable uncompress ON (ht.id = uncompress.compressed_hypertable_id
      AND uncompress.table_name = 'comp_ht_test') \gset
CREATE MATERIALIZED VIEW cagg1 WITH(timescaledb.continuous, timescaledb.finalized = false) AS SELECT time_bucket('1h',_ts_meta_min_1) FROM :INTERNALTABLE GROUP BY 1;
ERROR:  hypertable is an internal compressed hypertable