continuous_aggs.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.
-- initialize the bgw mock state to prevent the materialization workers from running
\c :TEST_DBNAME :ROLE_SUPERUSER
CREATE OR REPLACE FUNCTION ts_bgw_params_create() RETURNS VOID
AS :MODULE_PATHNAME LANGUAGE C VOLATILE;
\set WAIT_ON_JOB 0
\set IMMEDIATELY_SET_UNTIL 1
\set WAIT_FOR_OTHER_TO_ADVANCE 2
-- remove any default jobs, e.g., telemetry so bgw_job isn't polluted
DELETE FROM _timescaledb_config.bgw_job WHERE TRUE;
SET ROLE :ROLE_DEFAULT_PERM_USER;
SELECT * FROM _timescaledb_config.bgw_job;
 id | application_name | schedule_interval | max_runtime | max_retries | retry_period | proc_schema | proc_name | owner | scheduled | hypertable_id | config 
----+------------------+-------------------+-------------+-------------+--------------+-------------+-----------+-------+-----------+---------------+--------
(0 rows)

--TEST1 ---
--basic test with count
create table foo (a integer, b integer, c integer);
select table_name from create_hypertable('foo', 'a', chunk_time_interval=> 10);
NOTICE:  adding not-null constraint to column "a"
 table_name 
------------
 foo
(1 row)

insert into foo values( 3 , 16 , 20);
insert into foo values( 1 , 10 , 20);
insert into foo values( 1 , 11 , 20);
insert into foo values( 1 , 12 , 20);
insert into foo values( 1 , 13 , 20);
insert into foo values( 1 , 14 , 20);
insert into foo values( 2 , 14 , 20);
insert into foo values( 2 , 15 , 20);
insert into foo values( 2 , 16 , 20);
CREATE OR REPLACE FUNCTION integer_now_foo() returns int LANGUAGE SQL STABLE as $$ SELECT coalesce(max(a), 0) FROM foo $$;
SELECT set_integer_now_func('foo', 'integer_now_foo');
 set_integer_now_func 
----------------------
 
(1 row)

CREATE MATERIALIZED VIEW mat_m1(a, countb)
WITH (timescaledb.continuous, timescaledb.materialized_only=true)
as
select a, count(b)
from foo
group by time_bucket(1, a), a WITH NO DATA;
NOTICE:  adding index _materialized_hypertable_2_a_time_partition_col_idx ON _timescaledb_internal._materialized_hypertable_2 USING BTREE(a, time_partition_col)
SELECT add_continuous_aggregate_policy('mat_m1', NULL, 2::integer, '12 h'::interval);
 add_continuous_aggregate_policy 
---------------------------------
                            1000
(1 row)

SELECT * FROM _timescaledb_config.bgw_job;
  id  |              application_name              | schedule_interval | max_runtime | max_retries | retry_period |      proc_schema      |              proc_name              |       owner       | scheduled | hypertable_id |                               config                                
------+--------------------------------------------+-------------------+-------------+-------------+--------------+-----------------------+-------------------------------------+-------------------+-----------+---------------+---------------------------------------------------------------------
 1000 | Refresh Continuous Aggregate Policy [1000] | @ 12 hours        | @ 0         |          -1 | @ 12 hours   | _timescaledb_internal | policy_refresh_continuous_aggregate | default_perm_user | t         |             2 | {"end_interval": 2, "start_interval": null, "mat_hypertable_id": 2}
(1 row)

SELECT ca.raw_hypertable_id as "RAW_HYPERTABLE_ID",
       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_m1'
\gset
insert into :"MAT_SCHEMA_NAME".:"MAT_TABLE_NAME"
select a, _timescaledb_internal.partialize_agg(count(b)),
time_bucket(1, a)
,1
from foo
group by time_bucket(1, a) , a ;
select * from mat_m1 order by a ;
 a | countb 
---+--------
 1 |      5
 2 |      3
 3 |      1
(3 rows)

--check triggers on user hypertable --
SET ROLE :ROLE_SUPERUSER;
select tgname, tgtype, tgenabled , relname from pg_trigger, pg_class
where tgrelid = pg_class.oid and pg_class.relname like 'foo'
order by tgname;
            tgname            | tgtype | tgenabled | relname 
------------------------------+--------+-----------+---------
 ts_cagg_invalidation_trigger |     29 | O         | foo
 ts_insert_blocker            |      7 | O         | foo
(2 rows)

SET ROLE :ROLE_DEFAULT_PERM_USER;
-- TEST2 ---
DROP MATERIALIZED VIEW mat_m1;
NOTICE:  drop cascades to table _timescaledb_internal._hyper_2_2_chunk
SELECT * FROM _timescaledb_config.bgw_job;
 id | application_name | schedule_interval | max_runtime | max_retries | retry_period | proc_schema | proc_name | owner | scheduled | hypertable_id | config 
----+------------------+-------------------+-------------+-------------+--------------+-------------+-----------+-------+-----------+---------------+--------
(0 rows)

CREATE TABLE conditions (
      timec        TIMESTAMPTZ       NOT NULL,
      location    TEXT              NOT NULL,
      temperature DOUBLE PRECISION  NULL,
      humidity    DOUBLE PRECISION  NULL
    );
select table_name from create_hypertable( 'conditions', 'timec');
 table_name 
------------
 conditions
(1 row)

insert into conditions values ( '2010-01-01 09:00:00-08', 'SFO', 55, 45);
insert into conditions values ( '2010-01-02 09:00:00-08', 'por', 100, 100);
insert into conditions values ( '2010-01-02 09:00:00-08', 'SFO', 65, 45);
insert into conditions values ( '2010-01-02 09:00:00-08', 'NYC', 65, 45);
insert into conditions values ( '2018-11-01 09:00:00-08', 'NYC', 45, 35);
insert into conditions values ( '2018-11-02 09:00:00-08', 'NYC', 35, 15);
CREATE MATERIALIZED VIEW mat_m1( timec, minl, sumt , sumh)
WITH (timescaledb.continuous, timescaledb.materialized_only=true)
as
select time_bucket('1day', timec), min(location), sum(temperature),sum(humidity)
from conditions
group by time_bucket('1day', timec) WITH NO DATA;
SELECT ca.raw_hypertable_id as "RAW_HYPERTABLE_ID",
       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_m1'
\gset
SET ROLE :ROLE_SUPERUSER;
insert into  :"MAT_SCHEMA_NAME".:"MAT_TABLE_NAME"
select
 time_bucket('1day', timec), _timescaledb_internal.partialize_agg( min(location)), _timescaledb_internal.partialize_agg( sum(temperature)) , _timescaledb_internal.partialize_agg( sum(humidity))
,1
from conditions
group by time_bucket('1day', timec) ;
SET ROLE :ROLE_DEFAULT_PERM_USER;
--should have same results --
select timec, minl, sumt, sumh
from mat_m1
order by timec;
            timec             | minl | sumt | sumh 
------------------------------+------+------+------
 Thu Dec 31 16:00:00 2009 PST | SFO  |   55 |   45
 Fri Jan 01 16:00:00 2010 PST | NYC  |  230 |  190
 Wed Oct 31 17:00:00 2018 PDT | NYC  |   45 |   35
 Thu Nov 01 17:00:00 2018 PDT | NYC  |   35 |   15
(4 rows)

select time_bucket('1day', timec), min(location), sum(temperature), sum(humidity)
from conditions
group by time_bucket('1day', timec)
order by 1;
         time_bucket          | min | sum | sum 
------------------------------+-----+-----+-----
 Thu Dec 31 16:00:00 2009 PST | SFO |  55 |  45
 Fri Jan 01 16:00:00 2010 PST | NYC | 230 | 190
 Wed Oct 31 17:00:00 2018 PDT | NYC |  45 |  35
 Thu Nov 01 17:00:00 2018 PDT | NYC |  35 |  15
(4 rows)

-- TEST3 --
-- drop on table conditions should cascade to materialized mat_v1
drop table conditions cascade;
NOTICE:  drop cascades to 2 other objects
NOTICE:  drop cascades to 2 other objects
CREATE TABLE conditions (
      timec        TIMESTAMPTZ       NOT NULL,
      location    TEXT              NOT NULL,
      temperature DOUBLE PRECISION  NULL,
      humidity    DOUBLE PRECISION  NULL
    );
select table_name from create_hypertable( 'conditions', 'timec');
 table_name 
------------
 conditions
(1 row)

insert into conditions values ( '2010-01-01 09:00:00-08', 'SFO', 55, 45);
insert into conditions values ( '2010-01-02 09:00:00-08', 'por', 100, 100);
insert into conditions values ( '2010-01-02 09:00:00-08', 'NYC', 65, 45);
insert into conditions values ( '2010-01-02 09:00:00-08', 'SFO', 65, 45);
insert into conditions values ( '2010-01-03 09:00:00-08', 'NYC', 45, 55);
insert into conditions values ( '2010-01-05 09:00:00-08', 'SFO', 75, 100);
insert into conditions values ( '2018-11-01 09:00:00-08', 'NYC', 45, 35);
insert into conditions values ( '2018-11-02 09:00:00-08', 'NYC', 35, 15);
insert into conditions values ( '2018-11-03 09:00:00-08', 'NYC', 35, 25);
CREATE MATERIALIZED VIEW mat_m1( timec, minl, sumth, stddevh)
WITH (timescaledb.continuous, timescaledb.materialized_only=true)
as
select time_bucket('1week', timec) ,
min(location), sum(temperature)+sum(humidity), stddev(humidity)
from conditions
group by time_bucket('1week', timec)  WITH NO DATA;
SELECT ca.raw_hypertable_id as "RAW_HYPERTABLE_ID",
       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_m1'
\gset
SET ROLE :ROLE_SUPERUSER;
insert into  :"MAT_SCHEMA_NAME".:"MAT_TABLE_NAME"
select
 time_bucket('1week', timec),  _timescaledb_internal.partialize_agg( min(location)), _timescaledb_internal.partialize_agg( sum(temperature)) , _timescaledb_internal.partialize_agg( sum(humidity)), _timescaledb_internal.partialize_agg(stddev(humidity))
,1
from conditions
group by time_bucket('1week', timec) ;
SET ROLE :ROLE_DEFAULT_PERM_USER;
--should have same results --
select timec, minl, sumth, stddevh
from mat_m1
order by timec;
            timec             | minl | sumth |     stddevh      
------------------------------+------+-------+------------------
 Sun Dec 27 16:00:00 2009 PST | NYC  |   620 | 23.8746727726266
 Sun Jan 03 16:00:00 2010 PST | SFO  |   175 |                 
 Sun Oct 28 17:00:00 2018 PDT | NYC  |   190 |               10
(3 rows)

select time_bucket('1week', timec) ,
min(location), sum(temperature)+ sum(humidity), stddev(humidity)
from conditions
group by time_bucket('1week', timec)
order by time_bucket('1week', timec);
         time_bucket          | min | ?column? |      stddev      
------------------------------+-----+----------+------------------
 Sun Dec 27 16:00:00 2009 PST | NYC |      620 | 23.8746727726266
 Sun Jan 03 16:00:00 2010 PST | SFO |      175 |                 
 Sun Oct 28 17:00:00 2018 PDT | NYC |      190 |               10
(3 rows)

-- TEST4 --
--materialized view with group by clause + expression in SELECT
-- use previous data from conditions
--drop only the view.
-- apply where clause on result of mat_m1 --
DROP MATERIALIZED VIEW mat_m1;
NOTICE:  drop cascades to 2 other objects
CREATE MATERIALIZED VIEW mat_m1( timec, minl, sumth, stddevh)
WITH (timescaledb.continuous, timescaledb.materialized_only=true)
as
select time_bucket('1week', timec) ,
min(location), sum(temperature)+sum(humidity), stddev(humidity)
from conditions
where location = 'NYC'
group by time_bucket('1week', timec)
 WITH NO DATA;
SELECT ca.raw_hypertable_id as "RAW_HYPERTABLE_ID",
       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_m1'
\gset
SET ROLE :ROLE_SUPERUSER;
insert into  :"MAT_SCHEMA_NAME".:"MAT_TABLE_NAME"
select
 time_bucket('1week', timec),  _timescaledb_internal.partialize_agg( min(location)), _timescaledb_internal.partialize_agg( sum(temperature)) , _timescaledb_internal.partialize_agg( sum(humidity)), _timescaledb_internal.partialize_agg(stddev(humidity))
,1
from conditions
where location = 'NYC'
group by time_bucket('1week', timec) ;
SET ROLE :ROLE_DEFAULT_PERM_USER;
--should have same results --
select timec, minl, sumth, stddevh
from mat_m1
where stddevh is not null
order by timec;
            timec             | minl | sumth |     stddevh      
------------------------------+------+-------+------------------
 Sun Dec 27 16:00:00 2009 PST | NYC  |   210 | 7.07106781186548
 Sun Oct 28 17:00:00 2018 PDT | NYC  |   190 |               10
(2 rows)

select time_bucket('1week', timec) ,
min(location), sum(temperature)+ sum(humidity), stddev(humidity)
from conditions
where location = 'NYC'
group by time_bucket('1week', timec)
order by time_bucket('1week', timec);
         time_bucket          | min | ?column? |      stddev      
------------------------------+-----+----------+------------------
 Sun Dec 27 16:00:00 2009 PST | NYC |      210 | 7.07106781186548
 Sun Oct 28 17:00:00 2018 PDT | NYC |      190 |               10
(2 rows)

-- TEST5 --
---------test with having clause ----------------------
DROP MATERIALIZED VIEW mat_m1;
NOTICE:  drop cascades to 2 other objects
create materialized view mat_m1( timec, minl, sumth, stddevh)
WITH (timescaledb.continuous, timescaledb.materialized_only=true)
as
select time_bucket('1week', timec) ,
min(location), sum(temperature)+sum(humidity), stddev(humidity)
from conditions
group by time_bucket('1week', timec)
having stddev(humidity) is not null WITH NO DATA;
;
SELECT ca.raw_hypertable_id as "RAW_HYPERTABLE_ID",
       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_m1'
\gset
SET ROLE :ROLE_SUPERUSER;
insert into  :"MAT_SCHEMA_NAME".:"MAT_TABLE_NAME"
select
 time_bucket('1week', timec),  _timescaledb_internal.partialize_agg( min(location)), _timescaledb_internal.partialize_agg( sum(temperature)) , _timescaledb_internal.partialize_agg( sum(humidity)), _timescaledb_internal.partialize_agg(stddev(humidity))
,1
from conditions
group by time_bucket('1week', timec) ;
SET ROLE :ROLE_DEFAULT_PERM_USER;
-- should have same results --
select * from mat_m1
order by sumth;
            timec             | minl | sumth |     stddevh      
------------------------------+------+-------+------------------
 Sun Oct 28 17:00:00 2018 PDT | NYC  |   190 |               10
 Sun Dec 27 16:00:00 2009 PST | NYC  |   620 | 23.8746727726266
(2 rows)

select time_bucket('1week', timec) ,
min(location), sum(temperature)+sum(humidity), stddev(humidity)
from conditions
group by time_bucket('1week', timec)
having stddev(humidity) is not null
order by sum(temperature)+sum(humidity);
         time_bucket          | min | ?column? |      stddev      
------------------------------+-----+----------+------------------
 Sun Oct 28 17:00:00 2018 PDT | NYC |      190 |               10
 Sun Dec 27 16:00:00 2009 PST | NYC |      620 | 23.8746727726266
(2 rows)

-- TEST6 --
--group by with more than 1 group column
-- having clause with a mix of columns from select list + others
drop table conditions cascade;
NOTICE:  drop cascades to 2 other objects
NOTICE:  drop cascades to 2 other objects
CREATE TABLE conditions (
      timec       TIMESTAMPTZ       NOT NULL,
      location    TEXT              NOT NULL,
      temperature DOUBLE PRECISION  NULL,
      humidity    DOUBLE PRECISION  NULL,
      lowp        numeric NULL,
      highp       numeric null
    );
select table_name from create_hypertable( 'conditions', 'timec');
 table_name 
------------
 conditions
(1 row)

insert into conditions
select generate_series('2018-12-01 00:00'::timestamp, '2018-12-31 00:00'::timestamp, '1 day'), 'POR', 55, 75, 40, 70;
insert into conditions
select generate_series('2018-11-01 00:00'::timestamp, '2018-12-31 00:00'::timestamp, '1 day'), 'NYC', 35, 45, 50, 40;
insert into conditions
select generate_series('2018-11-01 00:00'::timestamp, '2018-12-15 00:00'::timestamp, '1 day'), 'LA', 73, 55, 71, 28;
--naming with AS clauses
CREATE MATERIALIZED VIEW mat_naming
WITH (timescaledb.continuous, timescaledb.materialized_only=true)
as
select time_bucket('1week', timec) as bucket, location as loc, sum(temperature)+sum(humidity), stddev(humidity)
from conditions
group by bucket, loc
having min(location) >= 'NYC' and avg(temperature) > 20 WITH NO DATA;
NOTICE:  adding index _materialized_hypertable_10_loc_bucket_idx ON _timescaledb_internal._materialized_hypertable_10 USING BTREE(loc, bucket)
SELECT ca.raw_hypertable_id as "RAW_HYPERTABLE_ID",
       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_naming'
\gset
select attnum , attname from pg_attribute
where attnum > 0 and attrelid =
(Select oid from pg_class where relname like :'MAT_TABLE_NAME')
order by attnum, attname;
 attnum | attname  
--------+----------
      1 | bucket
      2 | loc
      3 | agg_3_3
      4 | agg_3_4
      5 | agg_4_5
      6 | agg_0_6
      7 | agg_0_7
      8 | chunk_id
(8 rows)

DROP MATERIALIZED VIEW mat_naming;
--naming with default names
CREATE MATERIALIZED VIEW mat_naming
WITH (timescaledb.continuous, timescaledb.materialized_only=true)
as
select time_bucket('1week', timec), location, sum(temperature)+sum(humidity), stddev(humidity)
from conditions
group by 1,2
having min(location) >= 'NYC' and avg(temperature) > 20 WITH NO DATA;
NOTICE:  adding index _materialized_hypertable_11_location_time_bucket_idx ON _timescaledb_internal._materialized_hypertable_11 USING BTREE(location, time_bucket)
SELECT ca.raw_hypertable_id as "RAW_HYPERTABLE_ID",
       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_naming'
\gset
select attnum , attname from pg_attribute
where attnum > 0 and attrelid =
(Select oid from pg_class where relname like :'MAT_TABLE_NAME')
order by attnum, attname;
 attnum |   attname   
--------+-------------
      1 | time_bucket
      2 | location
      3 | agg_3_3
      4 | agg_3_4
      5 | agg_4_5
      6 | agg_0_6
      7 | agg_0_7
      8 | chunk_id
(8 rows)

DROP MATERIALIZED VIEW mat_naming;
--naming with view col names
CREATE MATERIALIZED VIEW mat_naming(bucket, loc, sum_t_h, stdd)
WITH (timescaledb.continuous, timescaledb.materialized_only=true)
as
select time_bucket('1week', timec), location, sum(temperature)+sum(humidity), stddev(humidity)
from conditions
group by 1,2
having min(location) >= 'NYC' and avg(temperature) > 20 WITH NO DATA;
NOTICE:  adding index _materialized_hypertable_12_loc_bucket_idx ON _timescaledb_internal._materialized_hypertable_12 USING BTREE(loc, bucket)
SELECT ca.raw_hypertable_id as "RAW_HYPERTABLE_ID",
       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_naming'
\gset
select attnum , attname from pg_attribute
where attnum > 0 and attrelid =
(Select oid from pg_class where relname like :'MAT_TABLE_NAME')
order by attnum, attname;
 attnum | attname  
--------+----------
      1 | bucket
      2 | loc
      3 | agg_3_3
      4 | agg_3_4
      5 | agg_4_5
      6 | agg_0_6
      7 | agg_0_7
      8 | chunk_id
(8 rows)

DROP MATERIALIZED VIEW mat_naming;
CREATE MATERIALIZED VIEW mat_m1(timec, minl, sumth, stddevh)
WITH (timescaledb.continuous, timescaledb.materialized_only=true)
as
select time_bucket('1week', timec) ,
min(location), sum(temperature)+sum(humidity), stddev(humidity)
from conditions
group by  time_bucket('1week', timec)
having min(location) >= 'NYC' and avg(temperature) > 20 WITH NO DATA;
SELECT ca.raw_hypertable_id as "RAW_HYPERTABLE_ID",
       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_m1'
\gset
select attnum , attname from pg_attribute
where attnum > 0 and attrelid =
(Select oid from pg_class where relname like :'MAT_TABLE_NAME')
order by attnum, attname;
 attnum | attname  
--------+----------
      1 | timec
      2 | agg_2_2
      3 | agg_3_3
      4 | agg_3_4
      5 | agg_4_5
      6 | agg_0_6
      7 | chunk_id
(7 rows)

SET ROLE :ROLE_SUPERUSER;
insert into  :"MAT_SCHEMA_NAME".:"MAT_TABLE_NAME"
select
 time_bucket('1week', timec),  _timescaledb_internal.partialize_agg( min(location)), _timescaledb_internal.partialize_agg( sum(temperature)) , _timescaledb_internal.partialize_agg( sum(humidity)), _timescaledb_internal.partialize_agg(stddev(humidity))
,_timescaledb_internal.partialize_agg( avg(temperature))
,1
from conditions
group by time_bucket('1week', timec) ;
SET ROLE :ROLE_DEFAULT_PERM_USER;
--should have same results --
select timec, minl, sumth, stddevh
from mat_m1
order by timec, minl;
            timec             | minl | sumth |     stddevh      
------------------------------+------+-------+------------------
 Sun Dec 16 16:00:00 2018 PST | NYC  |  1470 | 15.5662356498831
 Sun Dec 23 16:00:00 2018 PST | NYC  |  1470 | 15.5662356498831
 Sun Dec 30 16:00:00 2018 PST | NYC  |   210 | 21.2132034355964
(3 rows)

select time_bucket('1week', timec) ,
min(location), sum(temperature)+sum(humidity), stddev(humidity)
from conditions
group by  time_bucket('1week', timec)
having min(location) >= 'NYC' and avg(temperature) > 20 and avg(lowp) > 10
order by time_bucket('1week', timec), min(location);
         time_bucket          | min | ?column? |      stddev      
------------------------------+-----+----------+------------------
 Sun Dec 16 16:00:00 2018 PST | NYC |     1470 | 15.5662356498831
 Sun Dec 23 16:00:00 2018 PST | NYC |     1470 | 15.5662356498831
 Sun Dec 30 16:00:00 2018 PST | NYC |      210 | 21.2132034355964
(3 rows)

--check view defintion in information views
select view_name, view_definition from timescaledb_information.continuous_aggregates
where view_name::text like 'mat_m1';
 view_name |                                                 view_definition                                                 
-----------+-----------------------------------------------------------------------------------------------------------------
 mat_m1    |  SELECT time_bucket('@ 7 days'::interval, conditions.timec) AS timec,                                          +
           |     min(conditions.location) AS minl,                                                                          +
           |     (sum(conditions.temperature) + sum(conditions.humidity)) AS sumth,                                         +
           |     stddev(conditions.humidity) AS stddevh                                                                     +
           |    FROM conditions                                                                                             +
           |   GROUP BY (time_bucket('@ 7 days'::interval, conditions.timec))                                               +
           |  HAVING ((min(conditions.location) >= 'NYC'::text) AND (avg(conditions.temperature) > (20)::double precision));
(1 row)

--TEST6 -- select from internal view
SET ROLE :ROLE_SUPERUSER;
insert into :"MAT_SCHEMA_NAME".:"MAT_TABLE_NAME"
select * from :"PART_VIEW_SCHEMA".:"PART_VIEW_NAME";
SET ROLE :ROLE_DEFAULT_PERM_USER;
--lets drop the view and check
DROP MATERIALIZED VIEW mat_m1;
NOTICE:  drop cascades to 2 other objects
drop table conditions;
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)

insert into conditions
select generate_series('2018-12-01 00:00'::timestamp, '2018-12-31 00:00'::timestamp, '1 day'), 'POR', 55, 75, 40, 70, NULL;
insert into conditions
select generate_series('2018-11-01 00:00'::timestamp, '2018-12-31 00:00'::timestamp, '1 day'), 'NYC', 35, 45, 50, 40, NULL;
insert into conditions
select generate_series('2018-11-01 00:00'::timestamp, '2018-12-15 00:00'::timestamp, '1 day'), 'LA', 73, 55, NULL, 28, NULL;
SELECT
  $$
  select time_bucket('1week', timec) ,
  min(location) as col1, sum(temperature)+sum(humidity) as col2, stddev(humidity) as col3, min(allnull) as col4
  from conditions
  group by  time_bucket('1week', timec)
  having min(location) >= 'NYC' and avg(temperature) > 20
  $$ AS "QUERY"
\gset
\set ECHO errors
psql:include/cont_agg_equal.sql:8: NOTICE:  materialized view "mat_test" does not exist, skipping
                           ?column?                            | count 
---------------------------------------------------------------+-------
 Number of rows different between view and original (expect 0) |     0
(1 row)

SELECT
  $$
  select time_bucket('1week', timec), location,
  sum(temperature)+sum(humidity) as col2, stddev(humidity) as col3, min(allnull) as col4
  from conditions
  group by location, time_bucket('1week', timec)
  $$ AS "QUERY"
\gset
\set ECHO errors
psql:include/cont_agg_equal.sql:8: NOTICE:  drop cascades to 2 other objects
psql:include/cont_agg_equal.sql:13: NOTICE:  adding index _materialized_hypertable_16_location_time_bucket_idx ON _timescaledb_internal._materialized_hypertable_16 USING BTREE(location, time_bucket)
                           ?column?                            | count 
---------------------------------------------------------------+-------
 Number of rows different between view and original (expect 0) |     0
(1 row)

--TEST7 -- drop tests for view and hypertable
--DROP tests
\set ON_ERROR_STOP 0
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",
       direct_view_name as "DIR_VIEW_NAME",
       direct_view_schema as "DIR_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_test'
\gset
DROP TABLE :"MAT_SCHEMA_NAME".:"MAT_TABLE_NAME";
ERROR:  cannot drop table _timescaledb_internal._materialized_hypertable_16 because other objects depend on it
DROP VIEW :"PART_VIEW_SCHEMA".:"PART_VIEW_NAME";
ERROR:  cannot drop the partial/direct view because it is required by a continuous aggregate
DROP VIEW :"DIR_VIEW_SCHEMA".:"DIR_VIEW_NAME";
ERROR:  cannot drop the partial/direct view because it is required by a continuous aggregate
\set ON_ERROR_STOP 1
--catalog entry still there;
SELECT count(*)
FROM _timescaledb_catalog.continuous_agg ca
WHERE user_view_name = 'mat_test';
 count 
-------
     1
(1 row)

--mat table, user_view, direct view and partial view all there
select count(*) from pg_class where relname = :'PART_VIEW_NAME';
 count 
-------
     1
(1 row)

select count(*) from pg_class where relname = :'MAT_TABLE_NAME';
 count 
-------
     1
(1 row)

select count(*) from pg_class where relname = :'DIR_VIEW_NAME';
 count 
-------
     1
(1 row)

select count(*) from pg_class where relname = 'mat_test';
 count 
-------
     1
(1 row)

DROP MATERIALIZED VIEW mat_test;
NOTICE:  drop cascades to 2 other objects
--catalog entry should be gone
SELECT count(*)
FROM _timescaledb_catalog.continuous_agg ca
WHERE user_view_name = 'mat_test';
 count 
-------
     0
(1 row)

--mat table, user_view, direct view and partial view all gone
select count(*) from pg_class where relname = :'PART_VIEW_NAME';
 count 
-------
     0
(1 row)

select count(*) from pg_class where relname = :'MAT_TABLE_NAME';
 count 
-------
     0
(1 row)

select count(*) from pg_class where relname = :'DIR_VIEW_NAME';
 count 
-------
     0
(1 row)

select count(*) from pg_class where relname = 'mat_test';
 count 
-------
     0
(1 row)

--test dropping raw table
DROP TABLE conditions;
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)

--no data in hyper table on purpose so that CASCADE is not required because of chunks
CREATE MATERIALIZED VIEW mat_drop_test(timec, minl, sumt , sumh)
WITH (timescaledb.continuous, timescaledb.materialized_only=true)
as
select time_bucket('1day', timec), min(location), sum(temperature),sum(humidity)
from conditions
group by time_bucket('1day', timec) WITH NO DATA;
\set ON_ERROR_STOP 0
DROP TABLE conditions;
ERROR:  cannot drop table conditions because other objects depend on it
\set ON_ERROR_STOP 1
--insert data now
insert into conditions
select generate_series('2018-12-01 00:00'::timestamp, '2018-12-31 00:00'::timestamp, '1 day'), 'POR', 55, 75, 40, 70, NULL;
insert into conditions
select generate_series('2018-11-01 00:00'::timestamp, '2018-12-31 00:00'::timestamp, '1 day'), 'NYC', 35, 45, 50, 40, NULL;
insert into conditions
select generate_series('2018-11-01 00:00'::timestamp, '2018-12-15 00:00'::timestamp, '1 day'), 'LA', 73, 55, NULL, 28, NULL;
SELECT ca.raw_hypertable_id as "RAW_HYPERTABLE_ID",
       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_drop_test'
\gset
SET client_min_messages TO LOG;
SET timescaledb.current_timestamp_mock = '2018-12-31 00:00';
REFRESH MATERIALIZED VIEW mat_drop_test;
LOG:  new materialization range for public.conditions (time column timec) larger than allowed in one run, truncating Fri Dec 28 16:00:00 2018 PST to Tue Nov 20 16:00:00 2018 PST
LOG:  materializing continuous aggregate public.mat_drop_test: nothing to invalidate, new range up to Tue Nov 20 16:00:00 2018 PST
WARNING:  REFRESH did not materialize the entire range since it was limited by the max_interval_per_job setting
--force invalidation
insert into conditions
select generate_series('2017-11-01 00:00'::timestamp, '2017-12-15 00:00'::timestamp, '1 day'), 'LA', 73, 55, NULL, 28, NULL;
select count(*) from _timescaledb_catalog.continuous_aggs_invalidation_threshold;
 count 
-------
     1
(1 row)

select count(*) from _timescaledb_catalog.continuous_aggs_completed_threshold;
 count 
-------
     1
(1 row)

select count(*) from _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log;
 count 
-------
     1
(1 row)

DROP TABLE conditions CASCADE;
NOTICE:  drop cascades to 2 other objects
NOTICE:  drop cascades to 2 other objects
--catalog entry should be gone
SELECT count(*)
FROM _timescaledb_catalog.continuous_agg ca
WHERE user_view_name = 'mat_drop_test';
 count 
-------
     0
(1 row)

select count(*) from _timescaledb_catalog.continuous_aggs_invalidation_threshold;
 count 
-------
     0
(1 row)

select count(*) from _timescaledb_catalog.continuous_aggs_completed_threshold;
 count 
-------
     0
(1 row)

select count(*) from _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log;
 count 
-------
     0
(1 row)

select count(*) from _timescaledb_catalog.continuous_aggs_materialization_invalidation_log;
 count 
-------
     0
(1 row)

SELECT * FROM _timescaledb_config.bgw_job;
 id | application_name | schedule_interval | max_runtime | max_retries | retry_period | proc_schema | proc_name | owner | scheduled | hypertable_id | config 
----+------------------+-------------------+-------------+-------------+--------------+-------------+-----------+-------+-----------+---------------+--------
(0 rows)

--mat table, user_view, and partial view all gone
select count(*) from pg_class where relname = :'PART_VIEW_NAME';
 count 
-------
     0
(1 row)

select count(*) from pg_class where relname = :'MAT_TABLE_NAME';
 count 
-------
     0
(1 row)

select count(*) from pg_class where relname = 'mat_drop_test';
 count 
-------
     0
(1 row)

--TEST 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.materialized_only=true, timescaledb.refresh_lag = '5 hours')
as
select time_bucket('1day', timec), min(location), sum(temperature),sum(humidity)
from conditions
group by time_bucket('1day', timec), location, humidity, temperature WITH NO DATA;
NOTICE:  adding index _materialized_hypertable_20_grp_5_5_timec_idx ON _timescaledb_internal._materialized_hypertable_20 USING BTREE(grp_5_5, timec)
NOTICE:  adding index _materialized_hypertable_20_grp_6_6_timec_idx ON _timescaledb_internal._materialized_hypertable_20 USING BTREE(grp_6_6, timec)
NOTICE:  adding index _materialized_hypertable_20_grp_7_7_timec_idx ON _timescaledb_internal._materialized_hypertable_20 USING BTREE(grp_7_7, timec)
SELECT add_continuous_aggregate_policy('mat_with_test', NULL, '5 h'::interval, '12 h'::interval);
 add_continuous_aggregate_policy 
---------------------------------
                            1001
(1 row)

SELECT alter_job(id, schedule_interval => '1h') FROM _timescaledb_config.bgw_job;
                                                                  alter_job                                                                   
----------------------------------------------------------------------------------------------------------------------------------------------
 (1001,"@ 1 hour","@ 0",-1,"@ 12 hours",t,"{""end_interval"": ""@ 5 hours"", ""start_interval"": null, ""mat_hypertable_id"": 20}",-infinity)
(1 row)

SELECT schedule_interval FROM _timescaledb_config.bgw_job;
 schedule_interval 
-------------------
 @ 1 hour
(1 row)

SELECT _timescaledb_internal.to_interval(refresh_lag) FROM _timescaledb_catalog.continuous_agg WHERE user_view_name = 'mat_with_test';
 to_interval 
-------------
 @ 5 hours
(1 row)

ALTER MATERIALIZED VIEW mat_with_test SET(timescaledb.refresh_lag = '6 h');
SELECT alter_job(id, schedule_interval => '2h') FROM _timescaledb_config.bgw_job;
                                                                   alter_job                                                                   
-----------------------------------------------------------------------------------------------------------------------------------------------
 (1001,"@ 2 hours","@ 0",-1,"@ 12 hours",t,"{""end_interval"": ""@ 5 hours"", ""start_interval"": null, ""mat_hypertable_id"": 20}",-infinity)
(1 row)

SELECT _timescaledb_internal.to_interval(refresh_lag) FROM _timescaledb_catalog.continuous_agg WHERE user_view_name = 'mat_with_test';
 to_interval 
-------------
 @ 6 hours
(1 row)

SELECT schedule_interval FROM _timescaledb_config.bgw_job;
 schedule_interval 
-------------------
 @ 2 hours
(1 row)

select indexname, indexdef from pg_indexes where tablename =
(SELECT h.table_name
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')
order by indexname;
                   indexname                   |                                                                     indexdef                                                                      
-----------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------
 _materialized_hypertable_20_grp_5_5_timec_idx | CREATE INDEX _materialized_hypertable_20_grp_5_5_timec_idx ON _timescaledb_internal._materialized_hypertable_20 USING btree (grp_5_5, timec DESC)
 _materialized_hypertable_20_grp_6_6_timec_idx | CREATE INDEX _materialized_hypertable_20_grp_6_6_timec_idx ON _timescaledb_internal._materialized_hypertable_20 USING btree (grp_6_6, timec DESC)
 _materialized_hypertable_20_grp_7_7_timec_idx | CREATE INDEX _materialized_hypertable_20_grp_7_7_timec_idx ON _timescaledb_internal._materialized_hypertable_20 USING btree (grp_7_7, timec DESC)
 _materialized_hypertable_20_timec_idx         | CREATE INDEX _materialized_hypertable_20_timec_idx ON _timescaledb_internal._materialized_hypertable_20 USING btree (timec DESC)
(4 rows)

DROP MATERIALIZED VIEW mat_with_test;
--no additional indexes
CREATE MATERIALIZED VIEW mat_with_test(timec, minl, sumt , sumh)
WITH (timescaledb.continuous, timescaledb.materialized_only=true, timescaledb.refresh_lag = '5 hours', timescaledb.create_group_indexes=false)
as
select time_bucket('1day', timec), min(location), sum(temperature),sum(humidity)
from conditions
group by time_bucket('1day', timec), location, humidity, temperature WITH NO DATA;
select indexname, indexdef from pg_indexes where tablename =
(SELECT h.table_name
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');
               indexname               |                                                             indexdef                                                             
---------------------------------------+----------------------------------------------------------------------------------------------------------------------------------
 _materialized_hypertable_21_timec_idx | CREATE INDEX _materialized_hypertable_21_timec_idx ON _timescaledb_internal._materialized_hypertable_21 USING btree (timec DESC)
(1 row)

DROP TABLE conditions CASCADE;
NOTICE:  drop cascades to 2 other objects
--test WITH using a hypertable with an integer time dimension
CREATE TABLE conditions (
      timec       INT       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_conditions() returns int LANGUAGE SQL STABLE as $$ SELECT coalesce(max(timec), 0) FROM conditions $$;
SELECT set_integer_now_func('conditions', 'integer_now_conditions');
 set_integer_now_func 
----------------------
 
(1 row)

CREATE MATERIALIZED VIEW mat_with_test(timec, minl, sumt , sumh)
WITH (timescaledb.continuous, timescaledb.materialized_only=true, timescaledb.refresh_lag = '500')
as
select time_bucket(100, timec), min(location), sum(temperature),sum(humidity)
from conditions
group by time_bucket(100, timec) WITH NO DATA;
SELECT add_continuous_aggregate_policy('mat_with_test', NULL, 500::integer, '12 h'::interval);
 add_continuous_aggregate_policy 
---------------------------------
                            1002
(1 row)

SELECT alter_job(id, schedule_interval => '2h') FROM _timescaledb_config.bgw_job;
                                                              alter_job                                                              
-------------------------------------------------------------------------------------------------------------------------------------
 (1002,"@ 2 hours","@ 0",-1,"@ 12 hours",t,"{""end_interval"": 500, ""start_interval"": null, ""mat_hypertable_id"": 23}",-infinity)
(1 row)

SELECT schedule_interval FROM _timescaledb_config.bgw_job;
 schedule_interval 
-------------------
 @ 2 hours
(1 row)

SELECT refresh_lag FROM _timescaledb_catalog.continuous_agg WHERE user_view_name = 'mat_with_test';
 refresh_lag 
-------------
         500
(1 row)

ALTER MATERIALIZED VIEW mat_with_test SET(timescaledb.refresh_lag = '100');
SELECT refresh_lag FROM _timescaledb_catalog.continuous_agg WHERE user_view_name = 'mat_with_test';
 refresh_lag 
-------------
         100
(1 row)

-- we can SET multiple options in one commad
ALTER MATERIALIZED VIEW mat_with_test SET (timescaledb.refresh_lag = '100', timescaledb.max_interval_per_job='400');
SELECT refresh_lag, max_interval_per_job FROM _timescaledb_catalog.continuous_agg WHERE user_view_name = 'mat_with_test';
 refresh_lag | max_interval_per_job 
-------------+----------------------
         100 |                  400
(1 row)

DROP TABLE conditions CASCADE;
NOTICE:  drop cascades to 2 other objects
--test space partitions
CREATE TABLE space_table (
    time BIGINT,
    dev  BIGINT,
    data BIGINT
);
SELECT create_hypertable(
    'space_table',
    'time',
    chunk_time_interval => 10,
    partitioning_column => 'dev',
    number_partitions => 3);
NOTICE:  adding not-null constraint to column "time"
     create_hypertable     
---------------------------
 (24,public,space_table,t)
(1 row)

CREATE OR REPLACE FUNCTION integer_now_space_table() returns BIGINT LANGUAGE SQL STABLE as $$ SELECT coalesce(max(time), BIGINT '0') FROM space_table $$;
SELECT set_integer_now_func('space_table', 'integer_now_space_table');
 set_integer_now_func 
----------------------
 
(1 row)

CREATE MATERIALIZED VIEW space_view
WITH (timescaledb.continuous, timescaledb.materialized_only=true, timescaledb.refresh_lag = '-2')
AS SELECT time_bucket('4', time), COUNT(data)
   FROM space_table
   GROUP BY 1 WITH NO DATA;
INSERT INTO space_table VALUES
  (0, 1, 1), (0, 2, 1), (1, 1, 1), (1, 2, 1),
  (10, 1, 1), (10, 2, 1), (11, 1, 1), (11, 2, 1);
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",
       direct_view_name as "DIR_VIEW_NAME",
       direct_view_schema as "DIR_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 = 'space_view'
\gset
SELECT * FROM :"MAT_SCHEMA_NAME".:"MAT_TABLE_NAME"
  ORDER BY time_bucket, chunk_id;
 time_bucket | agg_2_2 | chunk_id 
-------------+---------+----------
(0 rows)

REFRESH MATERIALIZED VIEW space_view;
LOG:  materializing continuous aggregate public.space_view: nothing to invalidate, new range up to 12
SELECT * FROM space_view ORDER BY 1;
 time_bucket | count 
-------------+-------
           0 |     4
           8 |     4
(2 rows)

SELECT * FROM :"MAT_SCHEMA_NAME".:"MAT_TABLE_NAME"
  ORDER BY time_bucket, chunk_id;
 time_bucket |      agg_2_2       | chunk_id 
-------------+--------------------+----------
           0 | \x0000000000000002 |       58
           0 | \x0000000000000002 |       59
           8 | \x0000000000000002 |       60
           8 | \x0000000000000002 |       61
(4 rows)

INSERT INTO space_table VALUES (3, 2, 1);
REFRESH MATERIALIZED VIEW space_view;
LOG:  new materialization range not found for public.space_table (time column time): not enough new data past completion threshold of 12 as of 11
LOG:  materializing continuous aggregate public.space_view: processing invalidations, no new range
SELECT * FROM space_view ORDER BY 1;
 time_bucket | count 
-------------+-------
           0 |     5
           8 |     4
(2 rows)

SELECT * FROM :"MAT_SCHEMA_NAME".:"MAT_TABLE_NAME"
  ORDER BY time_bucket, chunk_id;
 time_bucket |      agg_2_2       | chunk_id 
-------------+--------------------+----------
           0 | \x0000000000000002 |       58
           0 | \x0000000000000003 |       59
           8 | \x0000000000000002 |       60
           8 | \x0000000000000002 |       61
(4 rows)

INSERT INTO space_table VALUES (2, 3, 1);
REFRESH MATERIALIZED VIEW space_view;
LOG:  new materialization range not found for public.space_table (time column time): not enough new data past completion threshold of 12 as of 11
LOG:  materializing continuous aggregate public.space_view: processing invalidations, no new range
SELECT * FROM space_view ORDER BY 1;
 time_bucket | count 
-------------+-------
           0 |     6
           8 |     4
(2 rows)

SELECT * FROM :"MAT_SCHEMA_NAME".:"MAT_TABLE_NAME"
  ORDER BY time_bucket, chunk_id;
 time_bucket |      agg_2_2       | chunk_id 
-------------+--------------------+----------
           0 | \x0000000000000002 |       58
           0 | \x0000000000000003 |       59
           0 | \x0000000000000001 |       63
           8 | \x0000000000000002 |       60
           8 | \x0000000000000002 |       61
(5 rows)

DROP TABLE space_table CASCADE;
NOTICE:  drop cascades to 2 other objects
NOTICE:  drop cascades to table _timescaledb_internal._hyper_25_62_chunk
--
-- TEST FINALIZEFUNC_EXTRA
--
-- create special aggregate to test ffunc_extra
-- Raise warning with the actual type being passed in
CREATE OR REPLACE FUNCTION fake_ffunc(a int8, b int, c int, d int, x anyelement)
RETURNS anyelement AS $$
BEGIN
 RAISE WARNING 'type % %', pg_typeof(d), pg_typeof(x);
 RETURN x;
END;
$$
LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION fake_sfunc(a int8, b int, c int, d int, x anyelement)
RETURNS int8 AS $$
BEGIN
 RETURN b;
END; $$
LANGUAGE plpgsql;
CREATE AGGREGATE aggregate_to_test_ffunc_extra(int, int, int, anyelement) (
    SFUNC = fake_sfunc,
    STYPE = int8,
    COMBINEFUNC = int8pl,
    FINALFUNC = fake_ffunc,
    PARALLEL = SAFE,
    FINALFUNC_EXTRA
);
CREATE TABLE conditions (
      timec       INT       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_conditions() returns int LANGUAGE SQL STABLE as $$ SELECT coalesce(max(timec), 0) FROM conditions $$;
SELECT set_integer_now_func('conditions', 'integer_now_conditions');
 set_integer_now_func 
----------------------
 
(1 row)

insert into conditions
select generate_series(0, 200, 10), 'POR', 55, 75, 40, 70, NULL;
CREATE MATERIALIZED VIEW mat_ffunc_test
WITH (timescaledb.continuous, timescaledb.materialized_only=true, timescaledb.refresh_lag = '-200')
as
select time_bucket(100, timec), aggregate_to_test_ffunc_extra(timec, 1, 3, 'test'::text)
from conditions
group by time_bucket(100, timec) WITH NO DATA;
REFRESH MATERIALIZED VIEW mat_ffunc_test;
LOG:  materializing continuous aggregate public.mat_ffunc_test: nothing to invalidate, new range up to 300
SELECT * FROM mat_ffunc_test;
WARNING:  type integer text
WARNING:  type integer text
WARNING:  type integer text
 time_bucket | aggregate_to_test_ffunc_extra 
-------------+-------------------------------
         200 | 
           0 | 
         100 | 
(3 rows)

DROP MATERIALIZED view mat_ffunc_test;
NOTICE:  drop cascades to table _timescaledb_internal._hyper_27_67_chunk
CREATE MATERIALIZED VIEW mat_ffunc_test
WITH (timescaledb.continuous, timescaledb.materialized_only=true, timescaledb.refresh_lag = '-200')
as
select time_bucket(100, timec), aggregate_to_test_ffunc_extra(timec, 4, 5, bigint '123')
from conditions
group by time_bucket(100, timec) WITH NO DATA;
REFRESH MATERIALIZED VIEW mat_ffunc_test;
LOG:  materializing continuous aggregate public.mat_ffunc_test: nothing to invalidate, new range up to 300
SELECT * FROM mat_ffunc_test;
WARNING:  type integer bigint
WARNING:  type integer bigint
WARNING:  type integer bigint
 time_bucket | aggregate_to_test_ffunc_extra 
-------------+-------------------------------
         200 |                              
           0 |                              
         100 |                              
(3 rows)

--refresh mat view test when time_bucket is not projected --
DROP MATERIALIZED VIEW mat_ffunc_test;
NOTICE:  drop cascades to table _timescaledb_internal._hyper_28_68_chunk
CREATE MATERIALIZED VIEW mat_refresh_test
WITH (timescaledb.continuous, timescaledb.materialized_only=true, timescaledb.refresh_lag = '-200')
as
select location, max(humidity)
from conditions
group by time_bucket(100, timec), location WITH NO DATA;
NOTICE:  adding index _materialized_hypertable_29_location_time_partition_col_idx ON _timescaledb_internal._materialized_hypertable_29 USING BTREE(location, time_partition_col)
insert into conditions
select generate_series(0, 50, 10), 'NYC', 55, 75, 40, 70, NULL;
REFRESH MATERIALIZED VIEW mat_refresh_test;
LOG:  materializing continuous aggregate public.mat_refresh_test: nothing to invalidate, new range up to 300
SELECT * FROM mat_refresh_test order by 1,2 ;
 location | max 
----------+-----
 NYC      |  75
 POR      |  75
 POR      |  75
 POR      |  75
(4 rows)

-- test for bug when group by is not in project list
CREATE MATERIALIZED VIEW conditions_grpby_view with (timescaledb.continuous, timescaledb.refresh_lag = '-200') as 
select time_bucket(100, timec),  sum(humidity) 
from conditions 
group by time_bucket(100, timec), location WITH NO DATA;
NOTICE:  adding index _materialized_hypertable_30_grp_3_3_time_bucket_idx ON _timescaledb_internal._materialized_hypertable_30 USING BTREE(grp_3_3, time_bucket)
REFRESH MATERIALIZED VIEW conditions_grpby_view;
LOG:  materializing continuous aggregate public.conditions_grpby_view: nothing to invalidate, new range up to 300
select * from conditions_grpby_view order by 1, 2;
 time_bucket | sum 
-------------+-----
           0 | 450
           0 | 750
         100 | 750
         200 |  75
(4 rows)

CREATE MATERIALIZED VIEW conditions_grpby_view2 with (timescaledb.continuous, timescaledb.refresh_lag = '-200') as
select time_bucket(100, timec), sum(humidity)
from conditions
group by time_bucket(100, timec), location
having avg(temperature) > 0
 WITH NO DATA;
NOTICE:  adding index _materialized_hypertable_31_grp_3_3_time_bucket_idx ON _timescaledb_internal._materialized_hypertable_31 USING BTREE(grp_3_3, time_bucket)
REFRESH MATERIALIZED VIEW conditions_grpby_view2;
LOG:  materializing continuous aggregate public.conditions_grpby_view2: nothing to invalidate, new range up to 300
select * from conditions_grpby_view2 order by 1, 2;
 time_bucket | sum 
-------------+-----
           0 | 450
           0 | 750
         100 | 750
         200 |  75
(4 rows)

--test ignore_invalidation_older_than on timestamps-based ht
DROP TABLE conditions CASCADE;
NOTICE:  drop cascades to 8 other objects
NOTICE:  drop cascades to table _timescaledb_internal._hyper_29_69_chunk
NOTICE:  drop cascades to table _timescaledb_internal._hyper_30_70_chunk
NOTICE:  drop cascades to table _timescaledb_internal._hyper_31_71_chunk
CREATE TABLE conditions (
                            time       TIMESTAMPTZ       NOT NULL,
                            temperature DOUBLE PRECISION  NULL
);
select table_name from create_hypertable( 'conditions', 'time');
 table_name 
------------
 conditions
(1 row)

create materialized view mat_test5( timec, maxt)
        WITH ( timescaledb.continuous, timescaledb.ignore_invalidation_older_than='30 day', timescaledb.refresh_lag='-1day',
               timescaledb.max_interval_per_job='260 day')
as
select time_bucket('1day', time), max(temperature)
from conditions
group by time_bucket('1day', time) WITH NO DATA;
SET timescaledb.current_timestamp_mock = '2001-03-11';
insert into conditions values('2001-03-10', '1');
insert into conditions values('2001-03-10', '2');
REFRESH MATERIALIZED VIEW mat_test5;
LOG:  materializing continuous aggregate public.mat_test5: nothing to invalidate, new range up to Sat Mar 10 16:00:00 2001 PST
SELECT * FROM mat_test5;
            timec             | maxt 
------------------------------+------
 Fri Mar 09 16:00:00 2001 PST |    2
(1 row)

insert into conditions values('2001-02-15', '1');
insert into conditions values('2001-01-15', '1');
REFRESH MATERIALIZED VIEW mat_test5;
LOG:  new materialization range not found for public.conditions (time column time): not enough new data past completion threshold of Sat Mar 10 16:00:00 2001 PST as of Sun Mar 11 00:00:00 2001 PST
LOG:  materializing continuous aggregate public.mat_test5: processing invalidations, no new range
--will see the feb but not the january change
SELECT * FROM mat_test5;
            timec             | maxt 
------------------------------+------
 Fri Mar 09 16:00:00 2001 PST |    2
 Wed Feb 14 16:00:00 2001 PST |    1
(2 rows)

--what matters is the time at insertion not materialization
SET timescaledb.current_timestamp_mock = '2001-03-11';
--will see this change
insert into conditions values('2001-02-15', '3');
SET timescaledb.current_timestamp_mock = '2001-05-11';
--but not this one
insert into conditions values('2001-02-20', '4');
REFRESH MATERIALIZED VIEW mat_test5;
LOG:  new materialization range not found for public.conditions (time column time): not enough new data past completion threshold of Sat Mar 10 16:00:00 2001 PST as of Fri May 11 00:00:00 2001 PDT
LOG:  materializing continuous aggregate public.mat_test5: processing invalidations, no new range
SELECT * FROM mat_test5;
            timec             | maxt 
------------------------------+------
 Fri Mar 09 16:00:00 2001 PST |    2
 Wed Feb 14 16:00:00 2001 PST |    3
(2 rows)

--verify that watermark is limited by max value and not by
-- the current time (now value)--
SET timescaledb.current_timestamp_mock = '2018-05-11';
SELECT view_name, completed_threshold, invalidation_threshold
FROM timescaledb_information.continuous_aggregate_stats
where view_name::text like 'mat_test5';
 view_name |     completed_threshold      |    invalidation_threshold    
-----------+------------------------------+------------------------------
 mat_test5 | Sat Mar 10 16:00:00 2001 PST | Sat Mar 10 16:00:00 2001 PST
(1 row)

REFRESH MATERIALIZED VIEW mat_test5;
LOG:  new materialization range not found for public.conditions (time column time): not enough new data past completion threshold of Sat Mar 10 16:00:00 2001 PST as of Fri May 11 00:00:00 2018 PDT
LOG:  materializing continuous aggregate public.mat_test5: nothing to invalidate, no new range
LOG:  materializing continuous aggregate public.mat_test5: no new range to materialize or invalidations found, exiting early
SELECT view_name, completed_threshold, invalidation_threshold
FROM timescaledb_information.continuous_aggregate_stats
where view_name::text like 'mat_test5';
 view_name |     completed_threshold      |    invalidation_threshold    
-----------+------------------------------+------------------------------
 mat_test5 | Sat Mar 10 16:00:00 2001 PST | Sat Mar 10 16:00:00 2001 PST
(1 row)