cagg_watermark-16.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 EXPLAIN_ANALYZE 'EXPLAIN (analyze,costs off,timing off,summary off)'
CREATE TABLE continuous_agg_test(time int, data int);
select create_hypertable('continuous_agg_test', 'time', chunk_time_interval=> 10);
NOTICE:  adding not-null constraint to column "time"
        create_hypertable         
----------------------------------
 (1,public,continuous_agg_test,t)
(1 row)

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

-- watermark tabels start out empty
SELECT * FROM _timescaledb_catalog.continuous_aggs_invalidation_threshold;
 hypertable_id | watermark 
---------------+-----------
(0 rows)

SELECT * from _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log;
 hypertable_id | lowest_modified_value | greatest_modified_value 
---------------+-----------------------+-------------------------
(0 rows)

-- inserting into a table that does not have continuous_agg_insert_trigger doesn't change the watermark
INSERT INTO continuous_agg_test VALUES (10, 1), (11, 2), (21, 3), (22, 4);
SELECT * FROM _timescaledb_catalog.continuous_aggs_invalidation_threshold;
 hypertable_id | watermark 
---------------+-----------
(0 rows)

SELECT * from _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log;
 hypertable_id | lowest_modified_value | greatest_modified_value 
---------------+-----------------------+-------------------------
(0 rows)

\c :TEST_DBNAME :ROLE_SUPERUSER
CREATE TABLE continuous_agg_test_mat(time int);
select create_hypertable('continuous_agg_test_mat', 'time', chunk_time_interval=> 10);
NOTICE:  adding not-null constraint to column "time"
          create_hypertable           
--------------------------------------
 (2,public,continuous_agg_test_mat,t)
(1 row)

INSERT INTO _timescaledb_catalog.continuous_agg VALUES (2, 1, NULL, '','','','',0,'','');
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER
-- create the trigger
CREATE TRIGGER continuous_agg_insert_trigger
    AFTER INSERT ON continuous_agg_test
    FOR EACH ROW EXECUTE FUNCTION _timescaledb_functions.continuous_agg_invalidation_trigger(1);
-- inserting into the table still doesn't change the watermark since there's no
-- continuous_aggs_invalidation_threshold. We treat that case as a invalidation_watermark of
-- BIG_INT_MIN, since the first run of the aggregation will need to scan the
-- entire table anyway.
INSERT INTO continuous_agg_test VALUES (10, 1), (11, 2), (21, 3), (22, 4);
SELECT * FROM _timescaledb_catalog.continuous_aggs_invalidation_threshold;
 hypertable_id | watermark 
---------------+-----------
(0 rows)

SELECT * from _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log;
 hypertable_id | lowest_modified_value | greatest_modified_value 
---------------+-----------------------+-------------------------
(0 rows)

-- set the continuous_aggs_invalidation_threshold to 15, any insertions below that value need an invalidation
\c :TEST_DBNAME :ROLE_SUPERUSER
INSERT INTO _timescaledb_catalog.continuous_aggs_invalidation_threshold VALUES (1, 15);
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER
INSERT INTO continuous_agg_test VALUES (10, 1), (11, 2), (21, 3), (22, 4);
SELECT * FROM _timescaledb_catalog.continuous_aggs_invalidation_threshold;
 hypertable_id | watermark 
---------------+-----------
             1 |        15
(1 row)

SELECT * from _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log;
 hypertable_id | lowest_modified_value | greatest_modified_value 
---------------+-----------------------+-------------------------
             1 |                    10 |                      22
(1 row)

-- INSERTs only above the continuous_aggs_invalidation_threshold won't change the continuous_aggs_hypertable_invalidation_log
INSERT INTO continuous_agg_test VALUES (21, 3), (22, 4);
SELECT * FROM _timescaledb_catalog.continuous_aggs_invalidation_threshold;
 hypertable_id | watermark 
---------------+-----------
             1 |        15
(1 row)

SELECT * from _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log;
 hypertable_id | lowest_modified_value | greatest_modified_value 
---------------+-----------------------+-------------------------
             1 |                    10 |                      22
(1 row)

-- INSERTs only below the continuous_aggs_invalidation_threshold will change the continuous_aggs_hypertable_invalidation_log
INSERT INTO continuous_agg_test VALUES (10, 1), (11, 2);
SELECT * FROM _timescaledb_catalog.continuous_aggs_invalidation_threshold;
 hypertable_id | watermark 
---------------+-----------
             1 |        15
(1 row)

SELECT * from _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log;
 hypertable_id | lowest_modified_value | greatest_modified_value 
---------------+-----------------------+-------------------------
             1 |                    10 |                      22
             1 |                    10 |                      11
(2 rows)

-- test INSERTing other values
INSERT INTO continuous_agg_test VALUES (1, 7), (12, 6), (24, 5), (51, 4);
SELECT * FROM _timescaledb_catalog.continuous_aggs_invalidation_threshold;
 hypertable_id | watermark 
---------------+-----------
             1 |        15
(1 row)

SELECT * from _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log;
 hypertable_id | lowest_modified_value | greatest_modified_value 
---------------+-----------------------+-------------------------
             1 |                    10 |                      22
             1 |                    10 |                      11
             1 |                     1 |                      51
(3 rows)

-- INSERT after dropping a COLUMN
ALTER TABLE continuous_agg_test DROP COLUMN data;
INSERT INTO continuous_agg_test VALUES (-1), (-2), (-3), (-4);
SELECT * FROM _timescaledb_catalog.continuous_aggs_invalidation_threshold;
 hypertable_id | watermark 
---------------+-----------
             1 |        15
(1 row)

SELECT * from _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log;
 hypertable_id | lowest_modified_value | greatest_modified_value 
---------------+-----------------------+-------------------------
             1 |                    10 |                      22
             1 |                    10 |                      11
             1 |                     1 |                      51
             1 |                    -4 |                      -1
(4 rows)

INSERT INTO continuous_agg_test VALUES (100);
SELECT * FROM _timescaledb_catalog.continuous_aggs_invalidation_threshold;
 hypertable_id | watermark 
---------------+-----------
             1 |        15
(1 row)

SELECT * from _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log;
 hypertable_id | lowest_modified_value | greatest_modified_value 
---------------+-----------------------+-------------------------
             1 |                    10 |                      22
             1 |                    10 |                      11
             1 |                     1 |                      51
             1 |                    -4 |                      -1
(4 rows)

-- INSERT after adding a COLUMN
ALTER TABLE continuous_agg_test ADD COLUMN d BOOLEAN;
INSERT INTO continuous_agg_test VALUES (-6, true), (-7, false), (-3, true), (-4, false);
SELECT * FROM _timescaledb_catalog.continuous_aggs_invalidation_threshold;
 hypertable_id | watermark 
---------------+-----------
             1 |        15
(1 row)

SELECT * from _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log;
 hypertable_id | lowest_modified_value | greatest_modified_value 
---------------+-----------------------+-------------------------
             1 |                    10 |                      22
             1 |                    10 |                      11
             1 |                     1 |                      51
             1 |                    -4 |                      -1
             1 |                    -7 |                      -3
(5 rows)

INSERT INTO continuous_agg_test VALUES (120, false), (200, true);
SELECT * FROM _timescaledb_catalog.continuous_aggs_invalidation_threshold;
 hypertable_id | watermark 
---------------+-----------
             1 |        15
(1 row)

SELECT * from _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log;
 hypertable_id | lowest_modified_value | greatest_modified_value 
---------------+-----------------------+-------------------------
             1 |                    10 |                      22
             1 |                    10 |                      11
             1 |                     1 |                      51
             1 |                    -4 |                      -1
             1 |                    -7 |                      -3
(5 rows)

\c :TEST_DBNAME :ROLE_SUPERUSER
DELETE FROM _timescaledb_catalog.continuous_agg where mat_hypertable_id =  2;
DELETE FROM _timescaledb_config.bgw_job WHERE id = 2;
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER
DROP TABLE continuous_agg_test CASCADE;
\c :TEST_DBNAME :ROLE_SUPERUSER
TRUNCATE _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log;
TRUNCATE _timescaledb_catalog.continuous_aggs_invalidation_threshold;
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER
-- CREATE VIEW creates the invalidation trigger correctly
CREATE TABLE ca_inval_test(time int);
SELECT create_hypertable('ca_inval_test', 'time', chunk_time_interval=> 10);
NOTICE:  adding not-null constraint to column "time"
     create_hypertable      
----------------------------
 (3,public,ca_inval_test,t)
(1 row)

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

CREATE MATERIALIZED VIEW cit_view
    WITH (timescaledb.continuous, timescaledb.materialized_only=false)
    AS SELECT time_bucket('5', time), COUNT(time)
        FROM ca_inval_test
        GROUP BY 1 WITH NO DATA;
INSERT INTO ca_inval_test SELECT generate_series(0, 5);
SELECT * FROM _timescaledb_catalog.continuous_aggs_invalidation_threshold;
 hypertable_id |  watermark  
---------------+-------------
             3 | -2147483648
(1 row)

SELECT * from _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log;
 hypertable_id | lowest_modified_value | greatest_modified_value 
---------------+-----------------------+-------------------------
(0 rows)

\c :TEST_DBNAME :ROLE_SUPERUSER
UPDATE _timescaledb_catalog.continuous_aggs_invalidation_threshold
SET watermark = 15
WHERE hypertable_id = 3;
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER
INSERT INTO ca_inval_test SELECT generate_series(5, 15);
SELECT * FROM _timescaledb_catalog.continuous_aggs_invalidation_threshold;
 hypertable_id | watermark 
---------------+-----------
             3 |        15
(1 row)

SELECT * from _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log;
 hypertable_id | lowest_modified_value | greatest_modified_value 
---------------+-----------------------+-------------------------
             3 |                     5 |                      15
(1 row)

INSERT INTO ca_inval_test SELECT generate_series(16, 20);
SELECT * FROM _timescaledb_catalog.continuous_aggs_invalidation_threshold;
 hypertable_id | watermark 
---------------+-----------
             3 |        15
(1 row)

SELECT * from _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log;
 hypertable_id | lowest_modified_value | greatest_modified_value 
---------------+-----------------------+-------------------------
             3 |                     5 |                      15
(1 row)

\c :TEST_DBNAME :ROLE_SUPERUSER
TRUNCATE _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log;
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER
-- updates below the threshold update both the old and new values
UPDATE ca_inval_test SET time = 5 WHERE time = 6;
UPDATE ca_inval_test SET time = 7 WHERE time = 5;
UPDATE ca_inval_test SET time = 17 WHERE time = 14;
UPDATE ca_inval_test SET time = 12 WHERE time = 16;
-- updates purely above the threshold are not logged
UPDATE ca_inval_test SET time = 19 WHERE time = 18;
UPDATE ca_inval_test SET time = 17 WHERE time = 19;
SELECT * FROM _timescaledb_catalog.continuous_aggs_invalidation_threshold;
 hypertable_id | watermark 
---------------+-----------
             3 |        15
(1 row)

SELECT * from _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log;
 hypertable_id | lowest_modified_value | greatest_modified_value 
---------------+-----------------------+-------------------------
             3 |                     5 |                       6
             3 |                     5 |                       7
             3 |                    14 |                      17
             3 |                    12 |                      16
(4 rows)

DROP TABLE ca_inval_test CASCADE;
NOTICE:  drop cascades to 3 other objects
\c :TEST_DBNAME :ROLE_SUPERUSER
TRUNCATE _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log;
TRUNCATE _timescaledb_catalog.continuous_aggs_invalidation_threshold;
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER
-- invalidation trigger is created correctly on chunks that existed before
-- the view was created
CREATE TABLE ts_continuous_test(time INTEGER, location INTEGER);
    SELECT create_hypertable('ts_continuous_test', 'time', chunk_time_interval => 10);
NOTICE:  adding not-null constraint to column "time"
        create_hypertable        
---------------------------------
 (5,public,ts_continuous_test,t)
(1 row)

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

INSERT INTO ts_continuous_test SELECT i, i FROM
    (SELECT generate_series(0, 29) AS i) AS i;
CREATE MATERIALIZED VIEW continuous_view
    WITH (timescaledb.continuous, timescaledb.materialized_only=false)
    AS SELECT time_bucket('5', time), COUNT(location)
        FROM ts_continuous_test
        GROUP BY 1 WITH NO DATA;
SELECT * FROM _timescaledb_catalog.continuous_aggs_invalidation_threshold;
 hypertable_id |  watermark  
---------------+-------------
             5 | -2147483648
(1 row)

SELECT * from _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log;
 hypertable_id | lowest_modified_value | greatest_modified_value 
---------------+-----------------------+-------------------------
(0 rows)

\c :TEST_DBNAME :ROLE_SUPERUSER
UPDATE _timescaledb_catalog.continuous_aggs_invalidation_threshold
SET watermark = 2
WHERE hypertable_id = 5;
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER
INSERT INTO ts_continuous_test VALUES (1, 1);
SELECT * FROM _timescaledb_catalog.continuous_aggs_invalidation_threshold;
 hypertable_id | watermark 
---------------+-----------
             5 |         2
(1 row)

SELECT * from _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log;
 hypertable_id | lowest_modified_value | greatest_modified_value 
---------------+-----------------------+-------------------------
             5 |                     1 |                       1
(1 row)

-- aborts don't get written
BEGIN;
    INSERT INTO ts_continuous_test VALUES (-20, -20);
ABORT;
SELECT * FROM _timescaledb_catalog.continuous_aggs_invalidation_threshold;
 hypertable_id | watermark 
---------------+-----------
             5 |         2
(1 row)

SELECT * from _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log;
 hypertable_id | lowest_modified_value | greatest_modified_value 
---------------+-----------------------+-------------------------
             5 |                     1 |                       1
(1 row)

DROP TABLE ts_continuous_test CASCADE;
NOTICE:  drop cascades to 3 other objects
----
-- Test watermark invalidation and chunk exclusion with prepared and ad-hoc queries
----
CREATE TABLE chunks(time timestamptz, device int, value float);
SELECT FROM create_hypertable('chunks','time',chunk_time_interval:='1d'::interval);
NOTICE:  adding not-null constraint to column "time"
--
(1 row)

CREATE MATERIALIZED VIEW chunks_1h WITH (timescaledb.continuous, timescaledb.materialized_only = false)
    AS SELECT time_bucket('1 hour', time) AS bucket, device, max(value) AS max FROM chunks GROUP BY 1, 2;
NOTICE:  continuous aggregate "chunks_1h" is already up-to-date
-- Get id of the materialization hypertable
SELECT id AS "MAT_HT_ID_1H" FROM _timescaledb_catalog.hypertable
    WHERE table_name=(
        SELECT materialization_hypertable_name
            FROM timescaledb_information.continuous_aggregates
            WHERE view_name='chunks_1h'
    ) \gset
SELECT materialization_hypertable_schema || '.' || materialization_hypertable_name AS "MAT_HT_NAME_1H"
    FROM timescaledb_information.continuous_aggregates
    WHERE view_name='chunks_1h'
\gset
-- Prepared scan on hypertable (identical to the query of a real-time CAgg)
PREPARE ht_scan_realtime_1h AS
   SELECT bucket, device, max
   FROM :MAT_HT_NAME_1H
  WHERE bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(:MAT_HT_ID_1H)), '-infinity'::timestamp with time zone)
UNION ALL
 SELECT time_bucket('01:00:00'::interval, chunks."time") AS bucket,
    chunks.device,
    max(chunks.value) AS max
   FROM chunks
  WHERE chunks."time" >= COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(:MAT_HT_ID_1H)), '-infinity'::timestamp with time zone)
  GROUP BY (time_bucket('01:00:00'::interval, chunks."time")), chunks.device;
PREPARE cagg_scan_1h AS SELECT * FROM chunks_1h;
:EXPLAIN_ANALYZE EXECUTE ht_scan_realtime_1h;
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 HashAggregate (actual rows=0 loops=1)
   Group Key: time_bucket('@ 1 hour'::interval, chunks."time"), chunks.device
   Batches: 1 
   ->  Result (actual rows=0 loops=1)
         One-Time Filter: false
(5 rows)

INSERT INTO chunks VALUES ('1901-08-01 01:01:01+01', 1, 2);
CALL refresh_continuous_aggregate('chunks_1h', '1900-01-01', '2021-06-01');
SELECT * FROM _timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(:MAT_HT_ID_1H));
         to_timestamp         
------------------------------
 Wed Jul 31 17:00:00 1901 PST
(1 row)

:EXPLAIN_ANALYZE EXECUTE ht_scan_realtime_1h;
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Append (actual rows=1 loops=1)
   ->  Index Scan using _hyper_8_17_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_17_chunk (actual rows=1 loops=1)
         Index Cond: (bucket < 'Wed Jul 31 17:00:00 1901 PST'::timestamp with time zone)
   ->  HashAggregate (actual rows=0 loops=1)
         Group Key: time_bucket('@ 1 hour'::interval, _hyper_7_16_chunk."time"), _hyper_7_16_chunk.device
         Batches: 1 
         ->  Result (actual rows=0 loops=1)
               ->  Index Scan using _hyper_7_16_chunk_chunks_time_idx on _hyper_7_16_chunk (actual rows=0 loops=1)
                     Index Cond: ("time" >= 'Wed Jul 31 17:00:00 1901 PST'::timestamp with time zone)
(9 rows)

-- Compare prepared statement with ad-hoc query
EXECUTE cagg_scan_1h;
            bucket            | device | max 
------------------------------+--------+-----
 Wed Jul 31 16:00:00 1901 PST |      1 |   2
(1 row)

SELECT * FROM chunks_1h;
            bucket            | device | max 
------------------------------+--------+-----
 Wed Jul 31 16:00:00 1901 PST |      1 |   2
(1 row)

-- Add new chunks to the non materialized part of the CAgg
INSERT INTO chunks VALUES ('1910-08-01 01:01:01+01', 1, 2);
:EXPLAIN_ANALYZE EXECUTE cagg_scan_1h;
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Append (actual rows=2 loops=1)
   ->  Index Scan using _hyper_8_17_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_17_chunk (actual rows=1 loops=1)
         Index Cond: (bucket < 'Wed Jul 31 17:00:00 1901 PST'::timestamp with time zone)
   ->  HashAggregate (actual rows=1 loops=1)
         Group Key: time_bucket('@ 1 hour'::interval, _hyper_7_16_chunk."time"), _hyper_7_16_chunk.device
         Batches: 1 
         ->  Result (actual rows=1 loops=1)
               ->  Append (actual rows=1 loops=1)
                     ->  Index Scan using _hyper_7_16_chunk_chunks_time_idx on _hyper_7_16_chunk (actual rows=0 loops=1)
                           Index Cond: ("time" >= 'Wed Jul 31 17:00:00 1901 PST'::timestamp with time zone)
                     ->  Index Scan using _hyper_7_18_chunk_chunks_time_idx on _hyper_7_18_chunk (actual rows=1 loops=1)
                           Index Cond: ("time" >= 'Wed Jul 31 17:00:00 1901 PST'::timestamp with time zone)
(12 rows)

:EXPLAIN_ANALYZE SELECT * FROM chunks_1h;
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Append (actual rows=2 loops=1)
   ->  Index Scan using _hyper_8_17_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_17_chunk (actual rows=1 loops=1)
         Index Cond: (bucket < 'Wed Jul 31 17:00:00 1901 PST'::timestamp with time zone)
   ->  HashAggregate (actual rows=1 loops=1)
         Group Key: time_bucket('@ 1 hour'::interval, _hyper_7_16_chunk."time"), _hyper_7_16_chunk.device
         Batches: 1 
         ->  Result (actual rows=1 loops=1)
               ->  Append (actual rows=1 loops=1)
                     ->  Index Scan using _hyper_7_16_chunk_chunks_time_idx on _hyper_7_16_chunk (actual rows=0 loops=1)
                           Index Cond: ("time" >= 'Wed Jul 31 17:00:00 1901 PST'::timestamp with time zone)
                     ->  Index Scan using _hyper_7_18_chunk_chunks_time_idx on _hyper_7_18_chunk (actual rows=1 loops=1)
                           Index Cond: ("time" >= 'Wed Jul 31 17:00:00 1901 PST'::timestamp with time zone)
(12 rows)

INSERT INTO chunks VALUES ('1911-08-01 01:01:01+01', 1, 2);
:EXPLAIN_ANALYZE EXECUTE cagg_scan_1h;
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Append (actual rows=3 loops=1)
   ->  Index Scan using _hyper_8_17_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_17_chunk (actual rows=1 loops=1)
         Index Cond: (bucket < 'Wed Jul 31 17:00:00 1901 PST'::timestamp with time zone)
   ->  HashAggregate (actual rows=2 loops=1)
         Group Key: time_bucket('@ 1 hour'::interval, _hyper_7_16_chunk."time"), _hyper_7_16_chunk.device
         Batches: 1 
         ->  Result (actual rows=2 loops=1)
               ->  Append (actual rows=2 loops=1)
                     ->  Index Scan using _hyper_7_16_chunk_chunks_time_idx on _hyper_7_16_chunk (actual rows=0 loops=1)
                           Index Cond: ("time" >= 'Wed Jul 31 17:00:00 1901 PST'::timestamp with time zone)
                     ->  Index Scan using _hyper_7_18_chunk_chunks_time_idx on _hyper_7_18_chunk (actual rows=1 loops=1)
                           Index Cond: ("time" >= 'Wed Jul 31 17:00:00 1901 PST'::timestamp with time zone)
                     ->  Index Scan using _hyper_7_19_chunk_chunks_time_idx on _hyper_7_19_chunk (actual rows=1 loops=1)
                           Index Cond: ("time" >= 'Wed Jul 31 17:00:00 1901 PST'::timestamp with time zone)
(14 rows)

:EXPLAIN_ANALYZE SELECT * FROM chunks_1h;
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Append (actual rows=3 loops=1)
   ->  Index Scan using _hyper_8_17_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_17_chunk (actual rows=1 loops=1)
         Index Cond: (bucket < 'Wed Jul 31 17:00:00 1901 PST'::timestamp with time zone)
   ->  HashAggregate (actual rows=2 loops=1)
         Group Key: time_bucket('@ 1 hour'::interval, _hyper_7_16_chunk."time"), _hyper_7_16_chunk.device
         Batches: 1 
         ->  Result (actual rows=2 loops=1)
               ->  Append (actual rows=2 loops=1)
                     ->  Index Scan using _hyper_7_16_chunk_chunks_time_idx on _hyper_7_16_chunk (actual rows=0 loops=1)
                           Index Cond: ("time" >= 'Wed Jul 31 17:00:00 1901 PST'::timestamp with time zone)
                     ->  Index Scan using _hyper_7_18_chunk_chunks_time_idx on _hyper_7_18_chunk (actual rows=1 loops=1)
                           Index Cond: ("time" >= 'Wed Jul 31 17:00:00 1901 PST'::timestamp with time zone)
                     ->  Index Scan using _hyper_7_19_chunk_chunks_time_idx on _hyper_7_19_chunk (actual rows=1 loops=1)
                           Index Cond: ("time" >= 'Wed Jul 31 17:00:00 1901 PST'::timestamp with time zone)
(14 rows)

-- Materialize CAgg and check for plan time chunk exclusion
CALL refresh_continuous_aggregate('chunks_1h', '1900-01-01', '2021-06-01');
:EXPLAIN_ANALYZE EXECUTE cagg_scan_1h;
                                                            QUERY PLAN                                                             
-----------------------------------------------------------------------------------------------------------------------------------
 Append (actual rows=3 loops=1)
   ->  Append (actual rows=3 loops=1)
         ->  Index Scan using _hyper_8_17_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_17_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Mon Jul 31 17:00:00 1911 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_20_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_20_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Mon Jul 31 17:00:00 1911 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_21_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_21_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Mon Jul 31 17:00:00 1911 PST'::timestamp with time zone)
   ->  HashAggregate (actual rows=0 loops=1)
         Group Key: time_bucket('@ 1 hour'::interval, _hyper_7_19_chunk."time"), _hyper_7_19_chunk.device
         Batches: 1 
         ->  Result (actual rows=0 loops=1)
               ->  Index Scan using _hyper_7_19_chunk_chunks_time_idx on _hyper_7_19_chunk (actual rows=0 loops=1)
                     Index Cond: ("time" >= 'Mon Jul 31 17:00:00 1911 PST'::timestamp with time zone)
(14 rows)

:EXPLAIN_ANALYZE SELECT * FROM chunks_1h;
                                                            QUERY PLAN                                                             
-----------------------------------------------------------------------------------------------------------------------------------
 Append (actual rows=3 loops=1)
   ->  Append (actual rows=3 loops=1)
         ->  Index Scan using _hyper_8_17_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_17_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Mon Jul 31 17:00:00 1911 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_20_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_20_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Mon Jul 31 17:00:00 1911 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_21_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_21_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Mon Jul 31 17:00:00 1911 PST'::timestamp with time zone)
   ->  HashAggregate (actual rows=0 loops=1)
         Group Key: time_bucket('@ 1 hour'::interval, _hyper_7_19_chunk."time"), _hyper_7_19_chunk.device
         Batches: 1 
         ->  Result (actual rows=0 loops=1)
               ->  Index Scan using _hyper_7_19_chunk_chunks_time_idx on _hyper_7_19_chunk (actual rows=0 loops=1)
                     Index Cond: ("time" >= 'Mon Jul 31 17:00:00 1911 PST'::timestamp with time zone)
(14 rows)

-- Check plan when chunk_append and constraint_aware_append cannot be used
-- There should be no plans for scans of chunks that are materialized in the CAgg
-- on the underlying hypertable
SET timescaledb.enable_chunk_append = OFF;
SET timescaledb.enable_constraint_aware_append = OFF;
:EXPLAIN_ANALYZE SELECT * FROM chunks_1h;
                                                            QUERY PLAN                                                             
-----------------------------------------------------------------------------------------------------------------------------------
 Append (actual rows=3 loops=1)
   ->  Append (actual rows=3 loops=1)
         ->  Index Scan using _hyper_8_17_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_17_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Mon Jul 31 17:00:00 1911 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_20_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_20_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Mon Jul 31 17:00:00 1911 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_21_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_21_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Mon Jul 31 17:00:00 1911 PST'::timestamp with time zone)
   ->  HashAggregate (actual rows=0 loops=1)
         Group Key: time_bucket('@ 1 hour'::interval, _hyper_7_19_chunk."time"), _hyper_7_19_chunk.device
         Batches: 1 
         ->  Result (actual rows=0 loops=1)
               ->  Index Scan using _hyper_7_19_chunk_chunks_time_idx on _hyper_7_19_chunk (actual rows=0 loops=1)
                     Index Cond: ("time" >= 'Mon Jul 31 17:00:00 1911 PST'::timestamp with time zone)
(14 rows)

RESET timescaledb.enable_chunk_append;
RESET timescaledb.enable_constraint_aware_append;
-- Insert new values and check watermark changes
INSERT INTO chunks VALUES ('1920-08-01 01:01:01+01', 1, 2);
CALL refresh_continuous_aggregate('chunks_1h', '1900-01-01', '2021-06-01');
SELECT * FROM _timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(:MAT_HT_ID_1H));
         to_timestamp         
------------------------------
 Sat Jul 31 17:00:00 1920 PST
(1 row)

:EXPLAIN_ANALYZE EXECUTE ht_scan_realtime_1h;
                                                            QUERY PLAN                                                             
-----------------------------------------------------------------------------------------------------------------------------------
 Append (actual rows=4 loops=1)
   ->  Append (actual rows=4 loops=1)
         ->  Index Scan using _hyper_8_17_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_17_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Sat Jul 31 17:00:00 1920 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_20_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_20_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Sat Jul 31 17:00:00 1920 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_21_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_21_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Sat Jul 31 17:00:00 1920 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_23_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_23_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Sat Jul 31 17:00:00 1920 PST'::timestamp with time zone)
   ->  HashAggregate (actual rows=0 loops=1)
         Group Key: time_bucket('@ 1 hour'::interval, _hyper_7_22_chunk."time"), _hyper_7_22_chunk.device
         Batches: 1 
         ->  Result (actual rows=0 loops=1)
               ->  Index Scan using _hyper_7_22_chunk_chunks_time_idx on _hyper_7_22_chunk (actual rows=0 loops=1)
                     Index Cond: ("time" >= 'Sat Jul 31 17:00:00 1920 PST'::timestamp with time zone)
(16 rows)

-- Compare prepared statement with ad-hoc query
EXECUTE cagg_scan_1h;
            bucket            | device | max 
------------------------------+--------+-----
 Wed Jul 31 16:00:00 1901 PST |      1 |   2
 Sun Jul 31 16:00:00 1910 PST |      1 |   2
 Mon Jul 31 16:00:00 1911 PST |      1 |   2
 Sat Jul 31 16:00:00 1920 PST |      1 |   2
(4 rows)

SELECT * FROM chunks_1h;
            bucket            | device | max 
------------------------------+--------+-----
 Wed Jul 31 16:00:00 1901 PST |      1 |   2
 Sun Jul 31 16:00:00 1910 PST |      1 |   2
 Mon Jul 31 16:00:00 1911 PST |      1 |   2
 Sat Jul 31 16:00:00 1920 PST |      1 |   2
(4 rows)

INSERT INTO chunks VALUES ('1930-08-01 01:01:01+01', 1, 2);
CALL refresh_continuous_aggregate('chunks_1h', '1900-01-01', '2021-06-01');
SELECT * FROM _timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(:MAT_HT_ID_1H));
         to_timestamp         
------------------------------
 Thu Jul 31 17:00:00 1930 PST
(1 row)

:EXPLAIN_ANALYZE EXECUTE ht_scan_realtime_1h;
                                                            QUERY PLAN                                                             
-----------------------------------------------------------------------------------------------------------------------------------
 Append (actual rows=5 loops=1)
   ->  Append (actual rows=5 loops=1)
         ->  Index Scan using _hyper_8_17_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_17_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Thu Jul 31 17:00:00 1930 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_20_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_20_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Thu Jul 31 17:00:00 1930 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_21_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_21_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Thu Jul 31 17:00:00 1930 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_23_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_23_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Thu Jul 31 17:00:00 1930 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_25_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_25_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Thu Jul 31 17:00:00 1930 PST'::timestamp with time zone)
   ->  HashAggregate (actual rows=0 loops=1)
         Group Key: time_bucket('@ 1 hour'::interval, _hyper_7_24_chunk."time"), _hyper_7_24_chunk.device
         Batches: 1 
         ->  Result (actual rows=0 loops=1)
               ->  Index Scan using _hyper_7_24_chunk_chunks_time_idx on _hyper_7_24_chunk (actual rows=0 loops=1)
                     Index Cond: ("time" >= 'Thu Jul 31 17:00:00 1930 PST'::timestamp with time zone)
(18 rows)

-- Two invalidations without prepared statement execution between
INSERT INTO chunks VALUES ('1931-08-01 01:01:01+01', 1, 2);
CALL refresh_continuous_aggregate('chunks_1h', '1900-01-01', '2021-06-01');
INSERT INTO chunks VALUES ('1932-08-01 01:01:01+01', 1, 2);
CALL refresh_continuous_aggregate('chunks_1h', '1900-01-01', '2021-06-01');
SELECT * FROM _timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(:MAT_HT_ID_1H));
         to_timestamp         
------------------------------
 Sun Jul 31 17:00:00 1932 PST
(1 row)

:EXPLAIN_ANALYZE EXECUTE ht_scan_realtime_1h;
                                                            QUERY PLAN                                                             
-----------------------------------------------------------------------------------------------------------------------------------
 Append (actual rows=7 loops=1)
   ->  Append (actual rows=7 loops=1)
         ->  Index Scan using _hyper_8_17_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_17_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Sun Jul 31 17:00:00 1932 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_20_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_20_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Sun Jul 31 17:00:00 1932 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_21_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_21_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Sun Jul 31 17:00:00 1932 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_23_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_23_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Sun Jul 31 17:00:00 1932 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_25_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_25_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Sun Jul 31 17:00:00 1932 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_27_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_27_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Sun Jul 31 17:00:00 1932 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_29_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_29_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Sun Jul 31 17:00:00 1932 PST'::timestamp with time zone)
   ->  HashAggregate (actual rows=0 loops=1)
         Group Key: time_bucket('@ 1 hour'::interval, _hyper_7_28_chunk."time"), _hyper_7_28_chunk.device
         Batches: 1 
         ->  Result (actual rows=0 loops=1)
               ->  Index Scan using _hyper_7_28_chunk_chunks_time_idx on _hyper_7_28_chunk (actual rows=0 loops=1)
                     Index Cond: ("time" >= 'Sun Jul 31 17:00:00 1932 PST'::timestamp with time zone)
(22 rows)

-- Multiple prepared statement executions followed by one invalidation
:EXPLAIN_ANALYZE EXECUTE ht_scan_realtime_1h;
                                                            QUERY PLAN                                                             
-----------------------------------------------------------------------------------------------------------------------------------
 Append (actual rows=7 loops=1)
   ->  Append (actual rows=7 loops=1)
         ->  Index Scan using _hyper_8_17_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_17_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Sun Jul 31 17:00:00 1932 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_20_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_20_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Sun Jul 31 17:00:00 1932 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_21_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_21_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Sun Jul 31 17:00:00 1932 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_23_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_23_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Sun Jul 31 17:00:00 1932 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_25_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_25_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Sun Jul 31 17:00:00 1932 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_27_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_27_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Sun Jul 31 17:00:00 1932 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_29_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_29_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Sun Jul 31 17:00:00 1932 PST'::timestamp with time zone)
   ->  HashAggregate (actual rows=0 loops=1)
         Group Key: time_bucket('@ 1 hour'::interval, _hyper_7_28_chunk."time"), _hyper_7_28_chunk.device
         Batches: 1 
         ->  Result (actual rows=0 loops=1)
               ->  Index Scan using _hyper_7_28_chunk_chunks_time_idx on _hyper_7_28_chunk (actual rows=0 loops=1)
                     Index Cond: ("time" >= 'Sun Jul 31 17:00:00 1932 PST'::timestamp with time zone)
(22 rows)

:EXPLAIN_ANALYZE EXECUTE ht_scan_realtime_1h;
                                                            QUERY PLAN                                                             
-----------------------------------------------------------------------------------------------------------------------------------
 Append (actual rows=7 loops=1)
   ->  Append (actual rows=7 loops=1)
         ->  Index Scan using _hyper_8_17_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_17_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Sun Jul 31 17:00:00 1932 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_20_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_20_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Sun Jul 31 17:00:00 1932 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_21_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_21_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Sun Jul 31 17:00:00 1932 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_23_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_23_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Sun Jul 31 17:00:00 1932 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_25_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_25_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Sun Jul 31 17:00:00 1932 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_27_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_27_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Sun Jul 31 17:00:00 1932 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_29_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_29_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Sun Jul 31 17:00:00 1932 PST'::timestamp with time zone)
   ->  HashAggregate (actual rows=0 loops=1)
         Group Key: time_bucket('@ 1 hour'::interval, _hyper_7_28_chunk."time"), _hyper_7_28_chunk.device
         Batches: 1 
         ->  Result (actual rows=0 loops=1)
               ->  Index Scan using _hyper_7_28_chunk_chunks_time_idx on _hyper_7_28_chunk (actual rows=0 loops=1)
                     Index Cond: ("time" >= 'Sun Jul 31 17:00:00 1932 PST'::timestamp with time zone)
(22 rows)

INSERT INTO chunks VALUES ('1940-08-01 01:01:01+01', 1, 2);
CALL refresh_continuous_aggregate('chunks_1h', '1900-01-01', '2021-06-01');
:EXPLAIN_ANALYZE EXECUTE ht_scan_realtime_1h;
                                                            QUERY PLAN                                                             
-----------------------------------------------------------------------------------------------------------------------------------
 Append (actual rows=8 loops=1)
   ->  Append (actual rows=8 loops=1)
         ->  Index Scan using _hyper_8_17_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_17_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Wed Jul 31 17:00:00 1940 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_20_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_20_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Wed Jul 31 17:00:00 1940 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_21_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_21_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Wed Jul 31 17:00:00 1940 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_23_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_23_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Wed Jul 31 17:00:00 1940 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_25_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_25_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Wed Jul 31 17:00:00 1940 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_27_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_27_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Wed Jul 31 17:00:00 1940 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_29_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_29_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Wed Jul 31 17:00:00 1940 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_31_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_31_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Wed Jul 31 17:00:00 1940 PST'::timestamp with time zone)
   ->  HashAggregate (actual rows=0 loops=1)
         Group Key: time_bucket('@ 1 hour'::interval, _hyper_7_30_chunk."time"), _hyper_7_30_chunk.device
         Batches: 1 
         ->  Result (actual rows=0 loops=1)
               ->  Index Scan using _hyper_7_30_chunk_chunks_time_idx on _hyper_7_30_chunk (actual rows=0 loops=1)
                     Index Cond: ("time" >= 'Wed Jul 31 17:00:00 1940 PST'::timestamp with time zone)
(24 rows)

-- Compare prepared statement with ad-hoc query
EXECUTE cagg_scan_1h;
            bucket            | device | max 
------------------------------+--------+-----
 Wed Jul 31 16:00:00 1901 PST |      1 |   2
 Sun Jul 31 16:00:00 1910 PST |      1 |   2
 Mon Jul 31 16:00:00 1911 PST |      1 |   2
 Sat Jul 31 16:00:00 1920 PST |      1 |   2
 Thu Jul 31 16:00:00 1930 PST |      1 |   2
 Fri Jul 31 16:00:00 1931 PST |      1 |   2
 Sun Jul 31 16:00:00 1932 PST |      1 |   2
 Wed Jul 31 16:00:00 1940 PST |      1 |   2
(8 rows)

SELECT * FROM chunks_1h;
            bucket            | device | max 
------------------------------+--------+-----
 Wed Jul 31 16:00:00 1901 PST |      1 |   2
 Sun Jul 31 16:00:00 1910 PST |      1 |   2
 Mon Jul 31 16:00:00 1911 PST |      1 |   2
 Sat Jul 31 16:00:00 1920 PST |      1 |   2
 Thu Jul 31 16:00:00 1930 PST |      1 |   2
 Fri Jul 31 16:00:00 1931 PST |      1 |   2
 Sun Jul 31 16:00:00 1932 PST |      1 |   2
 Wed Jul 31 16:00:00 1940 PST |      1 |   2
(8 rows)

-- Delete data from hypertable - data is only present in cagg after this point. If the watermark in the prepared
-- statement is not moved to the most-recent watermark, we would see an empty result.
TRUNCATE chunks;
EXECUTE cagg_scan_1h;
            bucket            | device | max 
------------------------------+--------+-----
 Wed Jul 31 16:00:00 1901 PST |      1 |   2
 Sun Jul 31 16:00:00 1910 PST |      1 |   2
 Mon Jul 31 16:00:00 1911 PST |      1 |   2
 Sat Jul 31 16:00:00 1920 PST |      1 |   2
 Thu Jul 31 16:00:00 1930 PST |      1 |   2
 Fri Jul 31 16:00:00 1931 PST |      1 |   2
 Sun Jul 31 16:00:00 1932 PST |      1 |   2
 Wed Jul 31 16:00:00 1940 PST |      1 |   2
(8 rows)

SELECT * FROM chunks_1h;
            bucket            | device | max 
------------------------------+--------+-----
 Wed Jul 31 16:00:00 1901 PST |      1 |   2
 Sun Jul 31 16:00:00 1910 PST |      1 |   2
 Mon Jul 31 16:00:00 1911 PST |      1 |   2
 Sat Jul 31 16:00:00 1920 PST |      1 |   2
 Thu Jul 31 16:00:00 1930 PST |      1 |   2
 Fri Jul 31 16:00:00 1931 PST |      1 |   2
 Sun Jul 31 16:00:00 1932 PST |      1 |   2
 Wed Jul 31 16:00:00 1940 PST |      1 |   2
(8 rows)

-- Refresh the CAgg
CALL refresh_continuous_aggregate('chunks_1h', NULL, NULL);
EXECUTE cagg_scan_1h;
 bucket | device | max 
--------+--------+-----
(0 rows)

SELECT * FROM chunks_1h;
 bucket | device | max 
--------+--------+-----
(0 rows)

-- Check new watermark
SELECT * FROM _timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(:MAT_HT_ID_1H));
         to_timestamp         
------------------------------
 Wed Jul 31 17:00:00 1940 PST
(1 row)

:EXPLAIN_ANALYZE EXECUTE ht_scan_realtime_1h;
                                                            QUERY PLAN                                                             
-----------------------------------------------------------------------------------------------------------------------------------
 Append (actual rows=0 loops=1)
   ->  Append (actual rows=0 loops=1)
         ->  Index Scan using _hyper_8_17_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_17_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < 'Wed Jul 31 17:00:00 1940 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_20_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_20_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < 'Wed Jul 31 17:00:00 1940 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_21_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_21_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < 'Wed Jul 31 17:00:00 1940 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_23_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_23_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < 'Wed Jul 31 17:00:00 1940 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_25_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_25_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < 'Wed Jul 31 17:00:00 1940 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_27_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_27_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < 'Wed Jul 31 17:00:00 1940 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_29_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_29_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < 'Wed Jul 31 17:00:00 1940 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_31_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_31_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < 'Wed Jul 31 17:00:00 1940 PST'::timestamp with time zone)
   ->  HashAggregate (actual rows=0 loops=1)
         Group Key: time_bucket('@ 1 hour'::interval, "time"), device
         Batches: 1 
         ->  Result (actual rows=0 loops=1)
               One-Time Filter: false
(23 rows)

-- Update after truncate
INSERT INTO chunks VALUES ('1950-08-01 01:01:01+01', 1, 2);
CALL refresh_continuous_aggregate('chunks_1h', '1900-01-01', '2021-06-01');
SELECT * FROM _timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(:MAT_HT_ID_1H));
         to_timestamp         
------------------------------
 Mon Jul 31 17:00:00 1950 PST
(1 row)

:EXPLAIN_ANALYZE EXECUTE ht_scan_realtime_1h;
                                                            QUERY PLAN                                                             
-----------------------------------------------------------------------------------------------------------------------------------
 Append (actual rows=1 loops=1)
   ->  Append (actual rows=1 loops=1)
         ->  Index Scan using _hyper_8_17_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_17_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < 'Mon Jul 31 17:00:00 1950 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_20_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_20_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < 'Mon Jul 31 17:00:00 1950 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_21_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_21_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < 'Mon Jul 31 17:00:00 1950 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_23_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_23_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < 'Mon Jul 31 17:00:00 1950 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_25_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_25_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < 'Mon Jul 31 17:00:00 1950 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_27_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_27_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < 'Mon Jul 31 17:00:00 1950 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_29_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_29_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < 'Mon Jul 31 17:00:00 1950 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_31_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_31_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < 'Mon Jul 31 17:00:00 1950 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_33_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_33_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Mon Jul 31 17:00:00 1950 PST'::timestamp with time zone)
   ->  HashAggregate (actual rows=0 loops=1)
         Group Key: time_bucket('@ 1 hour'::interval, _hyper_7_32_chunk."time"), _hyper_7_32_chunk.device
         Batches: 1 
         ->  Result (actual rows=0 loops=1)
               ->  Index Scan using _hyper_7_32_chunk_chunks_time_idx on _hyper_7_32_chunk (actual rows=0 loops=1)
                     Index Cond: ("time" >= 'Mon Jul 31 17:00:00 1950 PST'::timestamp with time zone)
(26 rows)

-- Test with CAgg on CAgg
CREATE MATERIALIZED VIEW chunks_1d WITH (timescaledb.continuous, timescaledb.materialized_only = false)
    AS SELECT time_bucket('1 days', bucket) AS bucket, device, max(max) AS max FROM chunks_1h GROUP BY 1, 2;
NOTICE:  refreshing continuous aggregate "chunks_1d"
SELECT id AS "MAT_HT_ID_1D" FROM _timescaledb_catalog.hypertable
    WHERE table_name=(
        SELECT materialization_hypertable_name
            FROM timescaledb_information.continuous_aggregates
            WHERE view_name='chunks_1d'
    ) \gset
SELECT materialization_hypertable_schema || '.' || materialization_hypertable_name AS "MAT_HT_NAME_1D"
    FROM timescaledb_information.continuous_aggregates
    WHERE view_name='chunks_1d'
\gset
-- Prepared scan on hypertable (identical to the query of a real-time CAgg)
PREPARE ht_scan_realtime_1d AS
 SELECT bucket, device, max
   FROM :MAT_HT_NAME_1D
  WHERE bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(:MAT_HT_ID_1D)), '-infinity'::timestamp with time zone)
UNION ALL
 SELECT time_bucket('@ 1 day'::interval, chunks_1h.bucket) AS bucket,
    chunks_1h.device,
    max(chunks_1h.max) AS max
   FROM chunks_1h
  WHERE chunks_1h.bucket >= COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(:MAT_HT_ID_1D)), '-infinity'::timestamp with time zone)
  GROUP BY (time_bucket('@ 1 day'::interval, chunks_1h.bucket)), chunks_1h.device;
PREPARE cagg_scan_1d AS SELECT * FROM chunks_1d;
:EXPLAIN_ANALYZE EXECUTE ht_scan_realtime_1d;
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Append (actual rows=1 loops=1)
   ->  Index Scan using _hyper_9_34_chunk__materialized_hypertable_9_bucket_idx on _hyper_9_34_chunk (actual rows=1 loops=1)
         Index Cond: (bucket < 'Tue Aug 01 16:00:00 1950 PST'::timestamp with time zone)
   ->  HashAggregate (actual rows=0 loops=1)
         Group Key: time_bucket('@ 1 day'::interval, (time_bucket('@ 1 hour'::interval, "time"))), device
         Batches: 1 
         ->  Result (actual rows=0 loops=1)
               ->  HashAggregate (actual rows=0 loops=1)
                     Group Key: time_bucket('@ 1 hour'::interval, "time"), device
                     Batches: 1 
                     ->  Result (actual rows=0 loops=1)
                           One-Time Filter: false
(12 rows)

INSERT INTO chunks VALUES ('2000-08-01 01:01:01+01', 1, 2);
CALL refresh_continuous_aggregate('chunks_1h', '1900-01-01', '2021-06-01');
CALL refresh_continuous_aggregate('chunks_1d', '1900-01-01', '2021-06-01');
:EXPLAIN_ANALYZE EXECUTE ht_scan_realtime_1d;
                                                            QUERY PLAN                                                             
-----------------------------------------------------------------------------------------------------------------------------------
 Append (actual rows=2 loops=1)
   ->  Append (actual rows=2 loops=1)
         ->  Index Scan using _hyper_9_34_chunk__materialized_hypertable_9_bucket_idx on _hyper_9_34_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Tue Aug 01 17:00:00 2000 PDT'::timestamp with time zone)
         ->  Index Scan using _hyper_9_37_chunk__materialized_hypertable_9_bucket_idx on _hyper_9_37_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Tue Aug 01 17:00:00 2000 PDT'::timestamp with time zone)
   ->  HashAggregate (actual rows=0 loops=1)
         Group Key: time_bucket('@ 1 day'::interval, (time_bucket('@ 1 hour'::interval, "time"))), device
         Batches: 1 
         ->  Result (actual rows=0 loops=1)
               ->  HashAggregate (actual rows=0 loops=1)
                     Group Key: time_bucket('@ 1 hour'::interval, "time"), device
                     Batches: 1 
                     ->  Result (actual rows=0 loops=1)
                           One-Time Filter: false
(15 rows)

INSERT INTO chunks VALUES ('2010-08-01 01:01:01+01', 1, 2);
CALL refresh_continuous_aggregate('chunks_1h', '1900-01-01', '2021-06-01');
CALL refresh_continuous_aggregate('chunks_1d', '1900-01-01', '2021-06-01');
:EXPLAIN_ANALYZE EXECUTE ht_scan_realtime_1d;
                                                            QUERY PLAN                                                             
-----------------------------------------------------------------------------------------------------------------------------------
 Append (actual rows=3 loops=1)
   ->  Append (actual rows=3 loops=1)
         ->  Index Scan using _hyper_9_34_chunk__materialized_hypertable_9_bucket_idx on _hyper_9_34_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Sun Aug 01 17:00:00 2010 PDT'::timestamp with time zone)
         ->  Index Scan using _hyper_9_37_chunk__materialized_hypertable_9_bucket_idx on _hyper_9_37_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Sun Aug 01 17:00:00 2010 PDT'::timestamp with time zone)
         ->  Index Scan using _hyper_9_40_chunk__materialized_hypertable_9_bucket_idx on _hyper_9_40_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Sun Aug 01 17:00:00 2010 PDT'::timestamp with time zone)
   ->  HashAggregate (actual rows=0 loops=1)
         Group Key: time_bucket('@ 1 day'::interval, (time_bucket('@ 1 hour'::interval, "time"))), device
         Batches: 1 
         ->  Result (actual rows=0 loops=1)
               ->  HashAggregate (actual rows=0 loops=1)
                     Group Key: time_bucket('@ 1 hour'::interval, "time"), device
                     Batches: 1 
                     ->  Result (actual rows=0 loops=1)
                           One-Time Filter: false
(17 rows)

-- Stored procedure - watermark
CREATE FUNCTION cur_watermark_plsql(mat_table int) RETURNS timestamptz
AS $$
DECLARE
cur_watermark_value timestamptz;
BEGIN
    SELECT _timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(mat_table)) INTO cur_watermark_value;
    RETURN cur_watermark_value;
END$$ LANGUAGE plpgsql;
SELECT * FROM cur_watermark_plsql(:MAT_HT_ID_1H);
     cur_watermark_plsql      
------------------------------
 Sat Jul 31 18:00:00 2010 PDT
(1 row)

INSERT INTO chunks VALUES ('2011-08-01 01:01:01+01', 1, 2);
CALL refresh_continuous_aggregate('chunks_1h', '1900-01-01', '2021-06-01');
SELECT * FROM cur_watermark_plsql(:MAT_HT_ID_1H);
     cur_watermark_plsql      
------------------------------
 Sun Jul 31 18:00:00 2011 PDT
(1 row)

INSERT INTO chunks VALUES ('2012-08-01 01:01:01+01', 1, 2);
CALL refresh_continuous_aggregate('chunks_1h', '1900-01-01', '2021-06-01');
SELECT * FROM cur_watermark_plsql(:MAT_HT_ID_1H);
     cur_watermark_plsql      
------------------------------
 Tue Jul 31 18:00:00 2012 PDT
(1 row)

-- Stored procedure - result
CREATE FUNCTION cur_cagg_result_count() RETURNS int
AS $$
DECLARE
count_value int;
BEGIN
    SELECT count(*) FROM chunks_1h INTO count_value;
    RETURN count_value;
END$$ LANGUAGE plpgsql;
-- Cache function value
SELECT * FROM cur_cagg_result_count();
 cur_cagg_result_count 
-----------------------
                     5
(1 row)

-- Add to non-materialized part
INSERT INTO chunks VALUES ('2013-08-01 01:01:01+01', 1, 2);
SELECT * FROM cur_cagg_result_count();
 cur_cagg_result_count 
-----------------------
                     6
(1 row)

-- Materialize
CALL refresh_continuous_aggregate('chunks_1h', '1900-01-01', '2021-06-01');
SELECT * FROM cur_cagg_result_count();
 cur_cagg_result_count 
-----------------------
                     6
(1 row)

-- Ensure all elements are materialized (i.e., watermark is moved properly)
TRUNCATE chunks;
SELECT * FROM cur_cagg_result_count();
 cur_cagg_result_count 
-----------------------
                     6
(1 row)

SELECT count(*) FROM chunks_1h;
 count 
-------
     6
(1 row)

-- Test watermark call directly
PREPARE watermark_query AS
    SELECT _timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(:MAT_HT_ID_1H));
SELECT _timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(:MAT_HT_ID_1H));
         to_timestamp         
------------------------------
 Wed Jul 31 18:00:00 2013 PDT
(1 row)

EXECUTE watermark_query;
         to_timestamp         
------------------------------
 Wed Jul 31 18:00:00 2013 PDT
(1 row)

INSERT INTO chunks VALUES ('2013-09-01 01:01:01+01', 1, 2);
CALL refresh_continuous_aggregate('chunks_1h', '1900-01-01', '2021-06-01');
SELECT _timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(:MAT_HT_ID_1H));
         to_timestamp         
------------------------------
 Sat Aug 31 18:00:00 2013 PDT
(1 row)

EXECUTE watermark_query;
         to_timestamp         
------------------------------
 Sat Aug 31 18:00:00 2013 PDT
(1 row)

-- Disable constification of watermark values
SET timescaledb.enable_cagg_watermark_constify = OFF;
INSERT INTO chunks VALUES ('2014-01-01 01:01:01+01', 1, 2);
CALL refresh_continuous_aggregate('chunks_1h', '1900-01-01', '2021-06-01');
:EXPLAIN_ANALYZE EXECUTE ht_scan_realtime_1h;
                                                                                    QUERY PLAN                                                                                    
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Append (actual rows=2 loops=1)
   ->  Custom Scan (ChunkAppend) on _materialized_hypertable_8 (actual rows=2 loops=1)
         Chunks excluded during startup: 0
         ->  Index Scan using _hyper_8_17_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_17_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_20_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_20_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_21_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_21_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_23_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_23_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_25_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_25_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_27_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_27_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_29_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_29_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_31_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_31_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_33_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_33_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_36_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_36_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_39_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_39_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_42_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_42_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_44_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_44_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_46_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_46_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_48_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_48_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_50_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_50_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
   ->  HashAggregate (actual rows=0 loops=1)
         Group Key: time_bucket('@ 1 hour'::interval, chunks."time"), chunks.device
         Batches: 1 
         ->  Result (actual rows=0 loops=1)
               ->  Custom Scan (ChunkAppend) on chunks (actual rows=0 loops=1)
                     Chunks excluded during startup: 1
                     ->  Index Scan using _hyper_7_49_chunk_chunks_time_idx on _hyper_7_49_chunk (actual rows=0 loops=1)
                           Index Cond: ("time" >= COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
(43 rows)

RESET timescaledb.enable_cagg_watermark_constify;
-- Select with projection
INSERT INTO chunks VALUES ('2015-01-01 01:01:01+01', 1, 2);
CALL refresh_continuous_aggregate('chunks_1h', '1900-01-01', '2021-06-01');
:EXPLAIN_ANALYZE SELECT device FROM chunks_1h;
                                                                  QUERY PLAN                                                                   
-----------------------------------------------------------------------------------------------------------------------------------------------
 Append (actual rows=3 loops=1)
   ->  Subquery Scan on "*SELECT* 1" (actual rows=3 loops=1)
         ->  Result (actual rows=3 loops=1)
               ->  Append (actual rows=3 loops=1)
                     ->  Index Scan using _hyper_8_17_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_17_chunk (actual rows=0 loops=1)
                           Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
                     ->  Index Scan using _hyper_8_20_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_20_chunk (actual rows=0 loops=1)
                           Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
                     ->  Index Scan using _hyper_8_21_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_21_chunk (actual rows=0 loops=1)
                           Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
                     ->  Index Scan using _hyper_8_23_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_23_chunk (actual rows=0 loops=1)
                           Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
                     ->  Index Scan using _hyper_8_25_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_25_chunk (actual rows=0 loops=1)
                           Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
                     ->  Index Scan using _hyper_8_27_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_27_chunk (actual rows=0 loops=1)
                           Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
                     ->  Index Scan using _hyper_8_29_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_29_chunk (actual rows=0 loops=1)
                           Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
                     ->  Index Scan using _hyper_8_31_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_31_chunk (actual rows=0 loops=1)
                           Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
                     ->  Index Scan using _hyper_8_33_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_33_chunk (actual rows=0 loops=1)
                           Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
                     ->  Index Scan using _hyper_8_36_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_36_chunk (actual rows=0 loops=1)
                           Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
                     ->  Index Scan using _hyper_8_39_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_39_chunk (actual rows=0 loops=1)
                           Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
                     ->  Index Scan using _hyper_8_42_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_42_chunk (actual rows=0 loops=1)
                           Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
                     ->  Index Scan using _hyper_8_44_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_44_chunk (actual rows=0 loops=1)
                           Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
                     ->  Index Scan using _hyper_8_46_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_46_chunk (actual rows=0 loops=1)
                           Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
                     ->  Index Scan using _hyper_8_48_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_48_chunk (actual rows=1 loops=1)
                           Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
                     ->  Index Scan using _hyper_8_50_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_50_chunk (actual rows=1 loops=1)
                           Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
                     ->  Index Scan using _hyper_8_52_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_52_chunk (actual rows=1 loops=1)
                           Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
   ->  Subquery Scan on "*SELECT* 2" (actual rows=0 loops=1)
         ->  HashAggregate (actual rows=0 loops=1)
               Group Key: time_bucket('@ 1 hour'::interval, _hyper_7_51_chunk."time"), _hyper_7_51_chunk.device
               Batches: 1 
               ->  Result (actual rows=0 loops=1)
                     ->  Index Scan using _hyper_7_51_chunk_chunks_time_idx on _hyper_7_51_chunk (actual rows=0 loops=1)
                           Index Cond: ("time" >= 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
(45 rows)

-- Watermark function use other tables in WHERE condition (should not be constified)
CREATE TABLE continuous_agg_test(time int, data int);
:EXPLAIN_ANALYZE (SELECT * FROM continuous_agg_test AS t1) UNION ALL (SELECT * from continuous_agg_test AS t2 WHERE COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(:MAT_HT_ID_1H)), '-infinity'::timestamp with time zone) IS NOT NULL);
                                                                              QUERY PLAN                                                                               
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Append (actual rows=0 loops=1)
   ->  Seq Scan on continuous_agg_test t1 (actual rows=0 loops=1)
   ->  Result (actual rows=0 loops=1)
         One-Time Filter: (COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone) IS NOT NULL)
         ->  Seq Scan on continuous_agg_test t2 (actual rows=0 loops=1)
(5 rows)

-- Query without COALESCE - should not be optimized
:EXPLAIN_ANALYZE (SELECT * FROM chunks_1h AS t1) UNION ALL (SELECT * from chunks_1h AS t2 WHERE _timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(:MAT_HT_ID_1H)) IS NOT NULL);
                                                                                          QUERY PLAN                                                                                          
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Append (actual rows=6 loops=1)
   ->  Custom Scan (ChunkAppend) on _materialized_hypertable_8 (actual rows=3 loops=1)
         Chunks excluded during startup: 0
         ->  Index Scan using _hyper_8_17_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_17_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_20_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_20_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_21_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_21_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_23_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_23_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_25_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_25_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_27_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_27_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_29_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_29_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_31_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_31_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_33_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_33_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_36_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_36_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_39_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_39_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_42_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_42_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_44_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_44_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_46_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_46_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_48_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_48_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_50_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_50_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_52_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_52_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
   ->  HashAggregate (actual rows=0 loops=1)
         Group Key: time_bucket('@ 1 hour'::interval, chunks."time"), chunks.device
         Batches: 1 
         ->  Result (actual rows=0 loops=1)
               ->  Custom Scan (ChunkAppend) on chunks (actual rows=0 loops=1)
                     Chunks excluded during startup: 2
                     ->  Index Scan using _hyper_7_51_chunk_chunks_time_idx on _hyper_7_51_chunk (actual rows=0 loops=1)
                           Index Cond: ("time" >= COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
   ->  Append (actual rows=3 loops=1)
         ->  Result (actual rows=3 loops=1)
               One-Time Filter: (_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)) IS NOT NULL)
               ->  Custom Scan (ChunkAppend) on _materialized_hypertable_8 _materialized_hypertable_8_1 (actual rows=3 loops=1)
                     Chunks excluded during startup: 0
                     ->  Index Scan using _hyper_8_17_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_17_chunk _hyper_8_17_chunk_1 (actual rows=0 loops=1)
                           Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
                     ->  Index Scan using _hyper_8_20_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_20_chunk _hyper_8_20_chunk_1 (actual rows=0 loops=1)
                           Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
                     ->  Index Scan using _hyper_8_21_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_21_chunk _hyper_8_21_chunk_1 (actual rows=0 loops=1)
                           Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
                     ->  Index Scan using _hyper_8_23_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_23_chunk _hyper_8_23_chunk_1 (actual rows=0 loops=1)
                           Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
                     ->  Index Scan using _hyper_8_25_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_25_chunk _hyper_8_25_chunk_1 (actual rows=0 loops=1)
                           Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
                     ->  Index Scan using _hyper_8_27_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_27_chunk _hyper_8_27_chunk_1 (actual rows=0 loops=1)
                           Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
                     ->  Index Scan using _hyper_8_29_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_29_chunk _hyper_8_29_chunk_1 (actual rows=0 loops=1)
                           Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
                     ->  Index Scan using _hyper_8_31_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_31_chunk _hyper_8_31_chunk_1 (actual rows=0 loops=1)
                           Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
                     ->  Index Scan using _hyper_8_33_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_33_chunk _hyper_8_33_chunk_1 (actual rows=0 loops=1)
                           Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
                     ->  Index Scan using _hyper_8_36_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_36_chunk _hyper_8_36_chunk_1 (actual rows=0 loops=1)
                           Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
                     ->  Index Scan using _hyper_8_39_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_39_chunk _hyper_8_39_chunk_1 (actual rows=0 loops=1)
                           Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
                     ->  Index Scan using _hyper_8_42_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_42_chunk _hyper_8_42_chunk_1 (actual rows=0 loops=1)
                           Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
                     ->  Index Scan using _hyper_8_44_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_44_chunk _hyper_8_44_chunk_1 (actual rows=0 loops=1)
                           Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
                     ->  Index Scan using _hyper_8_46_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_46_chunk _hyper_8_46_chunk_1 (actual rows=0 loops=1)
                           Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
                     ->  Index Scan using _hyper_8_48_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_48_chunk _hyper_8_48_chunk_1 (actual rows=1 loops=1)
                           Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
                     ->  Index Scan using _hyper_8_50_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_50_chunk _hyper_8_50_chunk_1 (actual rows=1 loops=1)
                           Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
                     ->  Index Scan using _hyper_8_52_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_52_chunk _hyper_8_52_chunk_1 (actual rows=1 loops=1)
                           Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Result (actual rows=0 loops=1)
               One-Time Filter: (_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)) IS NOT NULL)
               ->  HashAggregate (actual rows=0 loops=1)
                     Group Key: time_bucket('@ 1 hour'::interval, chunks_1."time"), chunks_1.device
                     Batches: 1 
                     ->  Result (actual rows=0 loops=1)
                           ->  Custom Scan (ChunkAppend) on chunks chunks_1 (actual rows=0 loops=1)
                                 Chunks excluded during startup: 2
                                 ->  Index Scan using _hyper_7_51_chunk_chunks_time_idx on _hyper_7_51_chunk _hyper_7_51_chunk_1 (actual rows=0 loops=1)
                                       Index Cond: ("time" >= COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
(94 rows)

-- Aggregation query over CAgg should be constified
:EXPLAIN_ANALYZE SELECT max(device) from chunks_1h;
                                                                     QUERY PLAN                                                                      
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate (actual rows=1 loops=1)
   ->  Append (actual rows=3 loops=1)
         ->  Subquery Scan on "*SELECT* 1" (actual rows=3 loops=1)
               ->  Result (actual rows=3 loops=1)
                     ->  Append (actual rows=3 loops=1)
                           ->  Index Scan using _hyper_8_17_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_17_chunk (actual rows=0 loops=1)
                                 Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
                           ->  Index Scan using _hyper_8_20_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_20_chunk (actual rows=0 loops=1)
                                 Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
                           ->  Index Scan using _hyper_8_21_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_21_chunk (actual rows=0 loops=1)
                                 Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
                           ->  Index Scan using _hyper_8_23_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_23_chunk (actual rows=0 loops=1)
                                 Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
                           ->  Index Scan using _hyper_8_25_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_25_chunk (actual rows=0 loops=1)
                                 Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
                           ->  Index Scan using _hyper_8_27_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_27_chunk (actual rows=0 loops=1)
                                 Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
                           ->  Index Scan using _hyper_8_29_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_29_chunk (actual rows=0 loops=1)
                                 Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
                           ->  Index Scan using _hyper_8_31_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_31_chunk (actual rows=0 loops=1)
                                 Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
                           ->  Index Scan using _hyper_8_33_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_33_chunk (actual rows=0 loops=1)
                                 Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
                           ->  Index Scan using _hyper_8_36_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_36_chunk (actual rows=0 loops=1)
                                 Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
                           ->  Index Scan using _hyper_8_39_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_39_chunk (actual rows=0 loops=1)
                                 Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
                           ->  Index Scan using _hyper_8_42_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_42_chunk (actual rows=0 loops=1)
                                 Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
                           ->  Index Scan using _hyper_8_44_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_44_chunk (actual rows=0 loops=1)
                                 Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
                           ->  Index Scan using _hyper_8_46_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_46_chunk (actual rows=0 loops=1)
                                 Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
                           ->  Index Scan using _hyper_8_48_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_48_chunk (actual rows=1 loops=1)
                                 Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
                           ->  Index Scan using _hyper_8_50_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_50_chunk (actual rows=1 loops=1)
                                 Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
                           ->  Index Scan using _hyper_8_52_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_52_chunk (actual rows=1 loops=1)
                                 Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
         ->  Subquery Scan on "*SELECT* 2" (actual rows=0 loops=1)
               ->  HashAggregate (actual rows=0 loops=1)
                     Group Key: time_bucket('@ 1 hour'::interval, _hyper_7_51_chunk."time"), _hyper_7_51_chunk.device
                     Batches: 1 
                     ->  Result (actual rows=0 loops=1)
                           ->  Index Scan using _hyper_7_51_chunk_chunks_time_idx on _hyper_7_51_chunk (actual rows=0 loops=1)
                                 Index Cond: ("time" >= 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
(46 rows)

-- Test with integer partitioning
CREATE TABLE integer_ht(time int, data int);
SELECT create_hypertable('integer_ht', 'time', chunk_time_interval => 10);
NOTICE:  adding not-null constraint to column "time"
    create_hypertable     
--------------------------
 (10,public,integer_ht,t)
(1 row)

CREATE FUNCTION integer_now_integer_ht() RETURNS INTEGER LANGUAGE SQL STABLE AS $$ SELECT coalesce(max(time), 0) FROM integer_ht $$;
SELECT set_integer_now_func('integer_ht', 'integer_now_integer_ht');
 set_integer_now_func 
----------------------
 
(1 row)

INSERT INTO integer_ht SELECT i, i FROM generate_series(0, 25) AS i;
CREATE MATERIALIZED VIEW integer_ht_cagg
    WITH (timescaledb.continuous, timescaledb.materialized_only=false)
    AS SELECT time_bucket('5', time), COUNT(time)
        FROM integer_ht
        GROUP BY 1;
NOTICE:  refreshing continuous aggregate "integer_ht_cagg"
SELECT * FROM integer_ht_cagg;
 time_bucket | count 
-------------+-------
          25 |     1
          20 |     5
          15 |     5
          10 |     5
           5 |     5
           0 |     5
(6 rows)

:EXPLAIN_ANALYZE SELECT * FROM integer_ht_cagg;
                                                             QUERY PLAN                                                              
-------------------------------------------------------------------------------------------------------------------------------------
 Append (actual rows=6 loops=1)
   ->  Index Scan using _hyper_11_56_chunk__materialized_hypertable_11_time_bucket_idx on _hyper_11_56_chunk (actual rows=6 loops=1)
         Index Cond: (time_bucket < 30)
   ->  HashAggregate (actual rows=0 loops=1)
         Group Key: time_bucket(5, "time")
         Batches: 1 
         ->  Result (actual rows=0 loops=1)
               One-Time Filter: false
(8 rows)

-- Test with big integer partitioning
CREATE TABLE big_integer_ht(time bigint, data bigint);
SELECT create_hypertable('big_integer_ht', 'time', chunk_time_interval => 10);
NOTICE:  adding not-null constraint to column "time"
      create_hypertable       
------------------------------
 (12,public,big_integer_ht,t)
(1 row)

CREATE FUNCTION integer_now_big_integer_ht() RETURNS BIGINT LANGUAGE SQL STABLE AS $$ SELECT coalesce(max(time), 0) FROM big_integer_ht $$;
SELECT set_integer_now_func('big_integer_ht', 'integer_now_big_integer_ht');
 set_integer_now_func 
----------------------
 
(1 row)

INSERT INTO big_integer_ht SELECT i, i FROM generate_series(0, 25) AS i;
CREATE MATERIALIZED VIEW big_integer_ht_cagg
    WITH (timescaledb.continuous, timescaledb.materialized_only=false)
    AS SELECT time_bucket('5', time), COUNT(time)
        FROM big_integer_ht
        GROUP BY 1;
NOTICE:  refreshing continuous aggregate "big_integer_ht_cagg"
SELECT * FROM big_integer_ht_cagg;
 time_bucket | count 
-------------+-------
          25 |     1
          20 |     5
          15 |     5
          10 |     5
           5 |     5
           0 |     5
(6 rows)

:EXPLAIN_ANALYZE SELECT * FROM big_integer_ht_cagg;
                                                             QUERY PLAN                                                              
-------------------------------------------------------------------------------------------------------------------------------------
 Append (actual rows=6 loops=1)
   ->  Index Scan using _hyper_13_60_chunk__materialized_hypertable_13_time_bucket_idx on _hyper_13_60_chunk (actual rows=6 loops=1)
         Index Cond: (time_bucket < '30'::bigint)
   ->  HashAggregate (actual rows=0 loops=1)
         Group Key: time_bucket('5'::bigint, "time")
         Batches: 1 
         ->  Result (actual rows=0 loops=1)
               One-Time Filter: false
(8 rows)

-- Test with small integer partitioning
CREATE TABLE small_integer_ht(time bigint, data bigint);
SELECT create_hypertable('small_integer_ht', 'time', chunk_time_interval => 10);
NOTICE:  adding not-null constraint to column "time"
       create_hypertable        
--------------------------------
 (14,public,small_integer_ht,t)
(1 row)

CREATE FUNCTION integer_now_small_integer_ht() RETURNS BIGINT LANGUAGE SQL STABLE AS $$ SELECT coalesce(max(time), 0) FROM small_integer_ht $$;
SELECT set_integer_now_func('small_integer_ht', 'integer_now_small_integer_ht');
 set_integer_now_func 
----------------------
 
(1 row)

INSERT INTO small_integer_ht SELECT i, i FROM generate_series(0, 25) AS i;
CREATE MATERIALIZED VIEW small_integer_ht_cagg
    WITH (timescaledb.continuous, timescaledb.materialized_only=false)
    AS SELECT time_bucket('5', time), COUNT(time)
        FROM small_integer_ht
        GROUP BY 1;
NOTICE:  refreshing continuous aggregate "small_integer_ht_cagg"
SELECT * FROM small_integer_ht_cagg;
 time_bucket | count 
-------------+-------
          25 |     1
          20 |     5
          15 |     5
          10 |     5
           5 |     5
           0 |     5
(6 rows)

:EXPLAIN_ANALYZE SELECT * FROM small_integer_ht_cagg;
                                                             QUERY PLAN                                                              
-------------------------------------------------------------------------------------------------------------------------------------
 Append (actual rows=6 loops=1)
   ->  Index Scan using _hyper_15_64_chunk__materialized_hypertable_15_time_bucket_idx on _hyper_15_64_chunk (actual rows=6 loops=1)
         Index Cond: (time_bucket < '30'::bigint)
   ->  HashAggregate (actual rows=0 loops=1)
         Group Key: time_bucket('5'::bigint, "time")
         Batches: 1 
         ->  Result (actual rows=0 loops=1)
               One-Time Filter: false
(8 rows)

-- Test handling of multiple watermark functions on integer based hypertables
-- This is not a usual CAgg query. So, no constification should be done. However,
-- the constification code should detect this and do nothing.
SELECT id AS "MAT_HT_ID_SMALL_INTEGER" FROM _timescaledb_catalog.hypertable
    WHERE table_name=(
        SELECT materialization_hypertable_name
            FROM timescaledb_information.continuous_aggregates
            WHERE view_name='small_integer_ht_cagg'
    ) \gset
:EXPLAIN_ANALYZE SELECT time_bucket(5, time) AS time_bucket,
    count(time) AS count
   FROM small_integer_ht
  WHERE small_integer_ht."time" >= COALESCE(_timescaledb_functions.cagg_watermark(:MAT_HT_ID_SMALL_INTEGER)::integer, _timescaledb_functions.cagg_watermark(:MAT_HT_ID_SMALL_INTEGER)::integer)
  GROUP BY (time_bucket(5, small_integer_ht."time"))
UNION ALL
  SELECT time_bucket(5, time) AS time_bucket,
    count(time) AS count
   FROM small_integer_ht
  WHERE small_integer_ht."time" < COALESCE(_timescaledb_functions.cagg_watermark(:MAT_HT_ID_SMALL_INTEGER)::integer, _timescaledb_functions.cagg_watermark(:MAT_HT_ID_SMALL_INTEGER)::integer)
  GROUP BY (time_bucket(5, small_integer_ht."time"));
                                                                              QUERY PLAN                                                                               
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Append (actual rows=6 loops=1)
   ->  HashAggregate (actual rows=0 loops=1)
         Group Key: time_bucket('5'::bigint, small_integer_ht."time")
         Batches: 1 
         ->  Result (actual rows=0 loops=1)
               ->  Custom Scan (ChunkAppend) on small_integer_ht (actual rows=0 loops=1)
                     Chunks excluded during startup: 3
   ->  HashAggregate (actual rows=6 loops=1)
         Group Key: time_bucket('5'::bigint, small_integer_ht_1."time")
         Batches: 1 
         ->  Result (actual rows=26 loops=1)
               ->  Custom Scan (ChunkAppend) on small_integer_ht small_integer_ht_1 (actual rows=26 loops=1)
                     Chunks excluded during startup: 0
                     ->  Index Only Scan Backward using _hyper_14_61_chunk_small_integer_ht_time_idx on _hyper_14_61_chunk (actual rows=10 loops=1)
                           Index Cond: ("time" < COALESCE((_timescaledb_functions.cagg_watermark(15))::integer, (_timescaledb_functions.cagg_watermark(15))::integer))
                           Heap Fetches: 10
                     ->  Index Only Scan Backward using _hyper_14_62_chunk_small_integer_ht_time_idx on _hyper_14_62_chunk (actual rows=10 loops=1)
                           Index Cond: ("time" < COALESCE((_timescaledb_functions.cagg_watermark(15))::integer, (_timescaledb_functions.cagg_watermark(15))::integer))
                           Heap Fetches: 10
                     ->  Index Only Scan Backward using _hyper_14_63_chunk_small_integer_ht_time_idx on _hyper_14_63_chunk (actual rows=6 loops=1)
                           Index Cond: ("time" < COALESCE((_timescaledb_functions.cagg_watermark(15))::integer, (_timescaledb_functions.cagg_watermark(15))::integer))
                           Heap Fetches: 6
(22 rows)

-- test with non constant value of the watermark function (should not be constified)
:EXPLAIN_ANALYZE  SELECT bucket, device, max
   FROM :MAT_HT_NAME_1H
  WHERE bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(:MAT_HT_ID_1H)), '-infinity'::timestamp with time zone)
UNION ALL
 SELECT time_bucket('@ 1 day'::interval, chunks_1h.bucket) AS bucket,
    chunks_1h.device,
    max(chunks_1h.max) AS max
   FROM chunks_1h
  WHERE chunks_1h.bucket >= COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(COALESCE(:MAT_HT_ID_1H, :MAT_HT_ID_1H))), '-infinity'::timestamp with time zone)
  GROUP BY (time_bucket('@ 1 day'::interval, chunks_1h.bucket)), chunks_1h.device;
                                                                                                                                                               QUERY PLAN                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Append (actual rows=3 loops=1)
   ->  Custom Scan (ChunkAppend) on _materialized_hypertable_8 (actual rows=3 loops=1)
         Chunks excluded during startup: 0
         ->  Index Scan using _hyper_8_17_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_17_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_20_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_20_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_21_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_21_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_23_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_23_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_25_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_25_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_27_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_27_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_29_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_29_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_31_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_31_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_33_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_33_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_36_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_36_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_39_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_39_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_42_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_42_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_44_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_44_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_46_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_46_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_48_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_48_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_50_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_50_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_52_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_52_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
   ->  HashAggregate (actual rows=0 loops=1)
         Group Key: time_bucket('@ 1 day'::interval, _materialized_hypertable_8_1.bucket), _materialized_hypertable_8_1.device
         Batches: 1 
         ->  Result (actual rows=0 loops=1)
               ->  Append (actual rows=0 loops=1)
                     ->  Custom Scan (ChunkAppend) on _materialized_hypertable_8 _materialized_hypertable_8_1 (actual rows=0 loops=1)
                           Chunks excluded during startup: 16
                           ->  Index Scan using _hyper_8_52_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_52_chunk _hyper_8_52_chunk_1 (actual rows=0 loops=1)
                                 Index Cond: ((bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone)) AND (bucket >= COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone)))
                     ->  HashAggregate (actual rows=0 loops=1)
                           Group Key: time_bucket('@ 1 hour'::interval, chunks."time"), chunks.device
                           Batches: 1 
                           ->  Result (actual rows=0 loops=1)
                                 ->  Custom Scan (ChunkAppend) on chunks (actual rows=0 loops=1)
                                       Chunks excluded during startup: 2
                                       ->  Index Scan using _hyper_7_51_chunk_chunks_time_idx on _hyper_7_51_chunk (actual rows=0 loops=1)
                                             Index Cond: ("time" >= COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
                                             Filter: (time_bucket('@ 1 hour'::interval, "time") >= COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
(55 rows)

-- test with NULL constant value of the watermark function (should not be constified)
:EXPLAIN_ANALYZE  SELECT bucket, device, max
   FROM :MAT_HT_NAME_1H
  WHERE bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(:MAT_HT_ID_1H)), '-infinity'::timestamp with time zone)
UNION ALL
 SELECT time_bucket('@ 1 day'::interval, chunks_1h.bucket) AS bucket,
    chunks_1h.device,
    max(chunks_1h.max) AS max
   FROM chunks_1h
  WHERE chunks_1h.bucket >= COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(COALESCE(:MAT_HT_ID_1H, :MAT_HT_ID_1H))), '-infinity'::timestamp with time zone)
  GROUP BY (time_bucket('@ 1 day'::interval, chunks_1h.bucket)), chunks_1h.device;
                                                                                                                                                               QUERY PLAN                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Append (actual rows=3 loops=1)
   ->  Custom Scan (ChunkAppend) on _materialized_hypertable_8 (actual rows=3 loops=1)
         Chunks excluded during startup: 0
         ->  Index Scan using _hyper_8_17_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_17_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_20_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_20_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_21_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_21_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_23_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_23_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_25_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_25_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_27_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_27_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_29_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_29_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_31_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_31_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_33_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_33_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_36_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_36_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_39_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_39_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_42_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_42_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_44_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_44_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_46_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_46_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_48_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_48_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_50_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_50_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
         ->  Index Scan using _hyper_8_52_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_52_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
   ->  HashAggregate (actual rows=0 loops=1)
         Group Key: time_bucket('@ 1 day'::interval, _materialized_hypertable_8_1.bucket), _materialized_hypertable_8_1.device
         Batches: 1 
         ->  Result (actual rows=0 loops=1)
               ->  Append (actual rows=0 loops=1)
                     ->  Custom Scan (ChunkAppend) on _materialized_hypertable_8 _materialized_hypertable_8_1 (actual rows=0 loops=1)
                           Chunks excluded during startup: 16
                           ->  Index Scan using _hyper_8_52_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_52_chunk _hyper_8_52_chunk_1 (actual rows=0 loops=1)
                                 Index Cond: ((bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone)) AND (bucket >= COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone)))
                     ->  HashAggregate (actual rows=0 loops=1)
                           Group Key: time_bucket('@ 1 hour'::interval, chunks."time"), chunks.device
                           Batches: 1 
                           ->  Result (actual rows=0 loops=1)
                                 ->  Custom Scan (ChunkAppend) on chunks (actual rows=0 loops=1)
                                       Chunks excluded during startup: 2
                                       ->  Index Scan using _hyper_7_51_chunk_chunks_time_idx on _hyper_7_51_chunk (actual rows=0 loops=1)
                                             Index Cond: ("time" >= COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
                                             Filter: (time_bucket('@ 1 hour'::interval, "time") >= COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(8)), '-infinity'::timestamp with time zone))
(55 rows)

-- test with double COALESCE function (should be constified)
:EXPLAIN_ANALYZE  SELECT bucket, device, max
   FROM :MAT_HT_NAME_1H
  WHERE bucket < COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(:MAT_HT_ID_1H)), '-infinity'::timestamp with time zone)
UNION ALL
 SELECT time_bucket('@ 1 day'::interval, chunks_1h.bucket) AS bucket,
    chunks_1h.device,
    max(chunks_1h.max) AS max
   FROM chunks_1h
  WHERE chunks_1h.bucket >= COALESCE(COALESCE(_timescaledb_functions.to_timestamp(_timescaledb_functions.cagg_watermark(:MAT_HT_ID_1H)), '-infinity'::timestamp with time zone), '-infinity'::timestamp with time zone)
  GROUP BY (time_bucket('@ 1 day'::interval, chunks_1h.bucket)), chunks_1h.device;
                                                                                         QUERY PLAN                                                                                          
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Append (actual rows=3 loops=1)
   ->  Append (actual rows=3 loops=1)
         ->  Index Scan using _hyper_8_17_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_17_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_20_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_20_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_21_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_21_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_23_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_23_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_25_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_25_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_27_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_27_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_29_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_29_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_31_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_31_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_33_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_33_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_36_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_36_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_39_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_39_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_42_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_42_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_44_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_44_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_46_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_46_chunk (actual rows=0 loops=1)
               Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_48_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_48_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_50_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_50_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
         ->  Index Scan using _hyper_8_52_chunk__materialized_hypertable_8_bucket_idx on _hyper_8_52_chunk (actual rows=1 loops=1)
               Index Cond: (bucket < 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
   ->  HashAggregate (actual rows=0 loops=1)
         Group Key: time_bucket('@ 1 day'::interval, (time_bucket('@ 1 hour'::interval, _hyper_7_51_chunk."time"))), _hyper_7_51_chunk.device
         Batches: 1 
         ->  Result (actual rows=0 loops=1)
               ->  HashAggregate (actual rows=0 loops=1)
                     Group Key: time_bucket('@ 1 hour'::interval, _hyper_7_51_chunk."time"), _hyper_7_51_chunk.device
                     Batches: 1 
                     ->  Result (actual rows=0 loops=1)
                           ->  Index Scan using _hyper_7_51_chunk_chunks_time_idx on _hyper_7_51_chunk (actual rows=0 loops=1)
                                 Index Cond: (("time" >= 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone) AND ("time" >= 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone))
                                 Filter: (time_bucket('@ 1 hour'::interval, "time") >= 'Wed Dec 31 17:00:00 2014 PST'::timestamp with time zone)
(47 rows)

\c :TEST_DBNAME :ROLE_SUPERUSER
TRUNCATE _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log;
TRUNCATE _timescaledb_catalog.continuous_aggs_invalidation_threshold;
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER
-- Issue #6722: constify cagg_watermark using window func when querying a cagg
:EXPLAIN_ANALYZE
SELECT time_bucket, lead(count) OVER (ORDER BY time_bucket) FROM small_integer_ht_cagg;
                                                                   QUERY PLAN                                                                    
-------------------------------------------------------------------------------------------------------------------------------------------------
 WindowAgg (actual rows=6 loops=1)
   ->  Sort (actual rows=6 loops=1)
         Sort Key: _hyper_15_64_chunk.time_bucket
         Sort Method: quicksort 
         ->  Append (actual rows=6 loops=1)
               ->  Index Scan using _hyper_15_64_chunk__materialized_hypertable_15_time_bucket_idx on _hyper_15_64_chunk (actual rows=6 loops=1)
                     Index Cond: (time_bucket < '30'::bigint)
               ->  HashAggregate (actual rows=0 loops=1)
                     Group Key: time_bucket('5'::bigint, "time")
                     Batches: 1 
                     ->  Result (actual rows=0 loops=1)
                           One-Time Filter: false
(12 rows)