chunk_utils_internal.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.
--  These tests work for PG14 or greater
-- Remember to corordinate any changes to functionality with the Cloud
-- Storage team. Tests for the following API:
-- * freeze_chunk
-- * drop_chunk
-- * attach_foreign_table_chunk
-- * hypertable_osm_range_update
\set EXPLAIN 'EXPLAIN (BUFFERS OFF, COSTS OFF)'
CREATE OR REPLACE VIEW chunk_view AS
  SELECT
    ht.table_name AS hypertable_name,
    srcch.schema_name AS schema_name,
    srcch.table_name AS chunk_name,
    _timescaledb_functions.to_timestamp(dimsl.range_start)
     AS range_start,
    dimsl.range_start AS range_start_int,
    dimsl.range_end AS range_end_int,
    _timescaledb_functions.to_timestamp(dimsl.range_end)
     AS range_end
  FROM _timescaledb_catalog.chunk srcch
    INNER JOIN _timescaledb_catalog.hypertable ht ON ht.id = srcch.hypertable_id
    INNER JOIN _timescaledb_catalog.dimension_slice dimsl ON dimsl.chunk_id = srcch.id
    INNER JOIN _timescaledb_catalog.dimension dim ON dim.id = dimsl.dimension_id;
GRANT SELECT on chunk_view TO PUBLIC;
\c :TEST_DBNAME :ROLE_SUPERUSER
CREATE SCHEMA test1;
GRANT CREATE ON SCHEMA test1 TO :ROLE_DEFAULT_PERM_USER;
GRANT USAGE ON SCHEMA test1 TO :ROLE_DEFAULT_PERM_USER;
GRANT USAGE ON SCHEMA test1 TO :ROLE_4;
-- mock hooks for OSM interaction with timescaledb
CREATE OR REPLACE FUNCTION ts_setup_osm_hook( ) RETURNS VOID
AS :TSL_MODULE_PATHNAME LANGUAGE C VOLATILE;
CREATE OR REPLACE FUNCTION ts_undo_osm_hook( ) RETURNS VOID
AS :TSL_MODULE_PATHNAME LANGUAGE C VOLATILE;
SET ROLE :ROLE_DEFAULT_PERM_USER;
CREATE TABLE test1.hyper1 (time bigint, temp float);
SELECT create_hypertable('test1.hyper1', 'time', chunk_time_interval => 10);
 create_hypertable  
--------------------
 (1,test1,hyper1,t)

INSERT INTO test1.hyper1 VALUES (10, 0.5);
INSERT INTO test1.hyper1 VALUES (30, 0.5);
SELECT chunk_schema as "CHSCHEMA",  chunk_name as "CHNAME",
       range_start_integer, range_end_integer
FROM timescaledb_information.chunks
WHERE hypertable_name = 'hyper1' and hypertable_schema = 'test1'
ORDER BY chunk_name ;
       CHSCHEMA        |      CHNAME      | range_start_integer | range_end_integer 
-----------------------+------------------+---------------------+-------------------
 _timescaledb_internal | _hyper_1_1_chunk |                  10 |                20
 _timescaledb_internal | _hyper_1_2_chunk |                  30 |                40

----- TESTS for freeze and unfreeze chunk ------------
--TEST internal api that freezes a chunk
--freeze one of the chunks
SELECT chunk_schema || '.' ||  chunk_name as "CHNAME", chunk_name as "CHUNK_NAME"
FROM timescaledb_information.chunks
WHERE hypertable_name = 'hyper1' and hypertable_schema = 'test1'
ORDER BY chunk_name LIMIT 1
\gset
-- Freeze
SELECT  _timescaledb_functions.freeze_chunk( :'CHNAME');
 freeze_chunk 
--------------
 t

SELECT * from test1.hyper1 ORDER BY 1;
 time | temp 
------+------
   10 |  0.5
   30 |  0.5

-- TEST updates and deletes on frozen chunk should fail
\set ON_ERROR_STOP 0
SELECT * from test1.hyper1 ORDER BY 1;
 time | temp 
------+------
   10 |  0.5
   30 |  0.5

-- Value (time = 20) does not exist
UPDATE test1.hyper1 SET temp = 40 WHERE time = 20;
-- Frozen chunk is affected
UPDATE test1.hyper1 SET temp = 40 WHERE temp = 0.5;
ERROR:  cannot update/delete rows from chunk "_hyper_1_1_chunk" as it is frozen
-- Frozen chunk is affected
UPDATE test1.hyper1 SET temp = 40 WHERE time = 10;
ERROR:  cannot update/delete rows from chunk "_hyper_1_1_chunk" as it is frozen
-- Frozen chunk is affected
DELETE FROM test1.hyper1 WHERE time = 10;
ERROR:  cannot update/delete rows from chunk "_hyper_1_1_chunk" as it is frozen
SELECT * from test1.hyper1 ORDER BY 1;
 time | temp 
------+------
   10 |  0.5
   30 |  0.5

BEGIN;
DELETE FROM test1.hyper1 WHERE time = 20;
DELETE FROM test1.hyper1 WHERE temp = 0.5;
ERROR:  cannot update/delete rows from chunk "_hyper_1_1_chunk" as it is frozen
ROLLBACK;
-- TEST update on unfrozen chunk should be possible
BEGIN;
SELECT * FROM test1.hyper1;
 time | temp 
------+------
   10 |  0.5
   30 |  0.5

UPDATE test1.hyper1 SET temp = 40 WHERE time = 30;
SELECT * FROM test1.hyper1;
 time | temp 
------+------
   10 |  0.5
   30 |   40

ROLLBACK;
-- Test with cast (chunk path pruning can not be done during query planning)
BEGIN;
SELECT * FROM test1.hyper1 WHERE time = 30;
 time | temp 
------+------
   30 |  0.5

UPDATE test1.hyper1 SET temp = 40 WHERE time = 30::text::float;
ERROR:  cannot update/delete rows from chunk "_hyper_1_1_chunk" as it is frozen
SELECT * FROM test1.hyper1 WHERE time = 30;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;
-- TEST delete on unfrozen chunks should be possible
BEGIN;
SELECT * from test1.hyper1 ORDER BY 1;
 time | temp 
------+------
   10 |  0.5
   30 |  0.5

DELETE FROM test1.hyper1 WHERE time = 30;
SELECT * from test1.hyper1 ORDER BY 1;
 time | temp 
------+------
   10 |  0.5

ROLLBACK;
-- Test with cast
BEGIN;
SELECT * FROM test1.hyper1 WHERE time = 30;
 time | temp 
------+------
   30 |  0.5

DELETE FROM test1.hyper1 WHERE time = 30::text::float;
ERROR:  cannot update/delete rows from chunk "_hyper_1_1_chunk" as it is frozen
SELECT * FROM test1.hyper1 WHERE time = 30;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;
-- TEST inserts into a frozen chunk fails
INSERT INTO test1.hyper1 VALUES ( 11, 11);
ERROR:  cannot INSERT into frozen chunk "_hyper_1_1_chunk"
-- Test truncating table should fail
TRUNCATE :CHNAME;
ERROR:  cannot TRUNCATE frozen chunk "_hyper_1_1_chunk"
SELECT * from test1.hyper1 ORDER BY 1;
 time | temp 
------+------
   10 |  0.5
   30 |  0.5

\set ON_ERROR_STOP 1
--insert into non-frozen chunk works
INSERT INTO test1.hyper1 VALUES ( 31, 31);
SELECT * from test1.hyper1 ORDER BY 1;
 time | temp 
------+------
   10 |  0.5
   30 |  0.5
   31 |   31

-- TEST unfreeze frozen chunk and then drop
SELECT table_name, status
FROM _timescaledb_catalog.chunk WHERE table_name = :'CHUNK_NAME';
    table_name    | status 
------------------+--------
 _hyper_1_1_chunk |      4

SELECT  _timescaledb_functions.unfreeze_chunk( :'CHNAME');
 unfreeze_chunk 
----------------
 t

SELECT tgname, tgtype FROM pg_trigger WHERE tgrelid = :'CHNAME'::regclass ORDER BY tgname, tgtype;
 tgname | tgtype 
--------+--------

--verify status in catalog
SELECT table_name, status
FROM _timescaledb_catalog.chunk WHERE table_name = :'CHUNK_NAME';
    table_name    | status 
------------------+--------
 _hyper_1_1_chunk |      0

-- Test update works after unfreeze
UPDATE test1.hyper1 SET temp = 40;
-- Test delete works after unfreeze
DELETE FROM test1.hyper1;
--unfreezing again works
SELECT  _timescaledb_functions.unfreeze_chunk( :'CHNAME');
 unfreeze_chunk 
----------------
 t

SELECT  _timescaledb_functions.drop_chunk( :'CHNAME');
 drop_chunk 
------------
 t

-- TEST freeze_chunk api on a chunk that is compressed
CREATE TABLE public.table_to_compress (time date NOT NULL, acq_id bigint, value bigint);
CREATE INDEX idx_table_to_compress_acq_id ON public.table_to_compress(acq_id);
SELECT create_hypertable('public.table_to_compress', 'time', chunk_time_interval => interval '1 day');
       create_hypertable        
--------------------------------
 (2,public,table_to_compress,t)

ALTER TABLE public.table_to_compress SET (timescaledb.compress, timescaledb.compress_segmentby = 'acq_id');
INSERT INTO public.table_to_compress VALUES ('2020-01-01', 1234567, 777888);
INSERT INTO public.table_to_compress VALUES ('2020-02-01', 567567, 890890);
INSERT INTO public.table_to_compress VALUES ('2020-02-10', 1234, 5678);
SELECT show_chunks('public.table_to_compress');
              show_chunks               
----------------------------------------
 _timescaledb_internal._hyper_2_3_chunk
 _timescaledb_internal._hyper_2_4_chunk
 _timescaledb_internal._hyper_2_5_chunk

SELECT chunk_schema || '.' ||  chunk_name as "CHNAME", chunk_name as "CHUNK_NAME"
FROM timescaledb_information.chunks
WHERE hypertable_name = 'table_to_compress' and hypertable_schema = 'public'
ORDER BY chunk_name LIMIT 1
\gset
SELECT  compress_chunk( :'CHNAME');
             compress_chunk             
----------------------------------------
 _timescaledb_internal._hyper_2_3_chunk

SELECT  _timescaledb_functions.freeze_chunk( :'CHNAME');
 freeze_chunk 
--------------
 t

SELECT table_name, status
FROM _timescaledb_catalog.chunk WHERE table_name = :'CHUNK_NAME';
    table_name    | status 
------------------+--------
 _hyper_2_3_chunk |      5

--now chunk is frozen, cannot decompress
\set ON_ERROR_STOP 0
SELECT  decompress_chunk( :'CHNAME');
ERROR:  decompress_chunk not permitted on frozen chunk "_hyper_2_3_chunk" 
--insert into frozen chunk, should fail
INSERT INTO public.table_to_compress VALUES ('2020-01-01 10:00', 12, 77);
ERROR:  cannot INSERT into frozen chunk "_hyper_2_3_chunk"
--touches all chunks
UPDATE public.table_to_compress SET value = 3;
ERROR:  cannot update/delete rows from chunk "_hyper_2_3_chunk" as it is frozen
--touches only frozen chunk
DELETE FROM public.table_to_compress WHERE time < '2020-01-02';
ERROR:  cannot update/delete rows from chunk "_hyper_2_3_chunk" as it is frozen
\set ON_ERROR_STOP 1
--try to refreeze
SELECT  _timescaledb_functions.freeze_chunk( :'CHNAME');
 freeze_chunk 
--------------
 t

--touches non-frozen chunk
SELECT * from public.table_to_compress ORDER BY 1, 3;
    time    | acq_id  | value  
------------+---------+--------
 01-01-2020 | 1234567 | 777888
 02-01-2020 |  567567 | 890890
 02-10-2020 |    1234 |   5678

DELETE FROM public.table_to_compress WHERE time > '2020-01-02';
SELECT * from public.table_to_compress ORDER BY 1, 3;
    time    | acq_id  | value  
------------+---------+--------
 01-01-2020 | 1234567 | 777888

--TEST cannot drop frozen chunk, no error is reported.
-- simply skips
SELECT drop_chunks('table_to_compress', older_than=> '1 day'::interval);
              drop_chunks               
----------------------------------------
 _timescaledb_internal._hyper_2_4_chunk
 _timescaledb_internal._hyper_2_5_chunk

--unfreeze and drop it
SELECT  _timescaledb_functions.unfreeze_chunk( :'CHNAME');
 unfreeze_chunk 
----------------
 t

SELECT  _timescaledb_functions.drop_chunk( :'CHNAME');
 drop_chunk 
------------
 t

--add a new chunk
INSERT INTO public.table_to_compress VALUES ('2019-01-01', 1234567, 777888);
--TEST  compress a frozen chunk fails
SELECT chunk_schema || '.' ||  chunk_name as "CHNAME", chunk_name as "CHUNK_NAME"
FROM timescaledb_information.chunks
WHERE hypertable_name = 'table_to_compress' and hypertable_schema = 'public'
ORDER BY chunk_name DESC LIMIT 1
\gset
SELECT  _timescaledb_functions.freeze_chunk( :'CHNAME');
 freeze_chunk 
--------------
 t

\set ON_ERROR_STOP 0
SELECT  compress_chunk( :'CHNAME');
ERROR:  chunk "_timescaledb_internal._hyper_2_7_chunk" is frozen, skipping compression
\set ON_ERROR_STOP 1
--TEST dropping a frozen chunk
--DO NOT CHANGE this behavior ---
-- frozen chunks cannot be dropped.
\set ON_ERROR_STOP 0
SELECT _timescaledb_functions.drop_chunk(:'CHNAME');
ERROR:  drop_chunk not permitted on frozen chunk "_hyper_2_7_chunk" 
\set ON_ERROR_STOP 1
-- Prepare table for CAGG tests
TRUNCATE test1.hyper1;
INSERT INTO test1.hyper1(time, temp) values(30, 0.5), (31, 31);
--TEST drop_chunk in the presence of caggs. Does not affect cagg data
CREATE OR REPLACE FUNCTION hyper_dummy_now() RETURNS BIGINT
LANGUAGE SQL IMMUTABLE AS  'SELECT 100::BIGINT';
SELECT set_integer_now_func('test1.hyper1', 'hyper_dummy_now');
 set_integer_now_func 
----------------------
 

CREATE MATERIALIZED VIEW hyper1_cagg WITH (timescaledb.continuous, timescaledb.materialized_only=false)
AS SELECT time_bucket( 5, "time") as bucket, count(*)
FROM test1.hyper1 GROUP BY 1;
NOTICE:  refreshing continuous aggregate "hyper1_cagg"
SELECT * FROM hyper1_cagg ORDER BY 1;
 bucket | count 
--------+-------
     30 |     2

--now freeze chunk and try to  drop it
SELECT chunk_schema || '.' ||  chunk_name as "CHNAME1", chunk_name as "CHUNK_NAME"
FROM timescaledb_information.chunks
WHERE hypertable_name = 'hyper1' and hypertable_schema = 'test1'
ORDER BY chunk_name LIMIT 1
\gset
SELECT  _timescaledb_functions.freeze_chunk( :'CHNAME1');
 freeze_chunk 
--------------
 t

--cannot drop frozen chunk
\set ON_ERROR_STOP 0
SELECT  _timescaledb_functions.drop_chunk( :'CHNAME1');
ERROR:  drop_chunk not permitted on frozen chunk "_hyper_1_8_chunk" 
\set ON_ERROR_STOP 1
-- unfreeze the chunk, then drop the single chunk
SELECT  _timescaledb_functions.unfreeze_chunk( :'CHNAME1');
 unfreeze_chunk 
----------------
 t

--drop the single chunk and verify that cagg is unaffected.
SELECT * FROM test1.hyper1 ORDER BY 1;
 time | temp 
------+------
   30 |  0.5
   31 |   31

SELECT  _timescaledb_functions.drop_chunk( :'CHNAME1');
 drop_chunk 
------------
 t

SELECT * from test1.hyper1 ORDER BY 1;
 time | temp 
------+------

SELECT * FROM hyper1_cagg ORDER BY 1;
 bucket | count 
--------+-------
     30 |     2

-- check that dropping cagg triggers OSM callback
SELECT ts_setup_osm_hook();
 ts_setup_osm_hook 
-------------------
 

BEGIN;
DROP MATERIALIZED VIEW hyper1_cagg CASCADE;
NOTICE:  drop cascades to table _timescaledb_internal._hyper_4_9_chunk
NOTICE:  hypertable_drop_hook
DROP TABLE test1.hyper1;
NOTICE:  hypertable_drop_hook
ROLLBACK;
BEGIN;
DROP TABLE test1.hyper1 CASCADE;
NOTICE:  drop cascades to 3 other objects
NOTICE:  drop cascades to table _timescaledb_internal._hyper_4_9_chunk
NOTICE:  hypertable_drop_hook
NOTICE:  hypertable_drop_hook
ROLLBACK;
SELECT ts_undo_osm_hook();
 ts_undo_osm_hook 
------------------
 

--TEST error case (un)freeze a non-chunk
CREATE TABLE nochunk_tab( a timestamp, b integer);
\set ON_ERROR_STOP 0
SELECT _timescaledb_functions.freeze_chunk('nochunk_tab');
ERROR:  chunk not found
SELECT _timescaledb_functions.unfreeze_chunk('nochunk_tab');
ERROR:  chunk not found
\set ON_ERROR_STOP 1
----- TESTS for attach_osm_table_chunk ------------
--TEST for attaching a foreign table as a chunk
--need superuser access to create foreign data server
\c :TEST_DBNAME :ROLE_SUPERUSER
CREATE DATABASE postgres_fdw_db;
GRANT ALL PRIVILEGES ON DATABASE postgres_fdw_db TO :ROLE_4;
\c postgres_fdw_db :ROLE_4
CREATE TABLE fdw_table( timec timestamptz NOT NULL , acq_id bigint, value bigint);
INSERT INTO fdw_table VALUES( '2020-01-01 01:00', 100, 1000);
--create foreign server and user mappings as superuser
\c :TEST_DBNAME :ROLE_SUPERUSER
SELECT current_setting('port') as "PORTNO" \gset
CREATE EXTENSION postgres_fdw;
CREATE SERVER s3_server FOREIGN DATA WRAPPER postgres_fdw
OPTIONS ( host 'localhost', dbname 'postgres_fdw_db', port :'PORTNO');
GRANT USAGE ON FOREIGN SERVER s3_server TO :ROLE_4;
CREATE USER MAPPING FOR :ROLE_4 SERVER s3_server
OPTIONS (  user :'ROLE_4' , password :'ROLE_4_PASS');
ALTER USER MAPPING FOR :ROLE_4 SERVER s3_server
OPTIONS (ADD password_required 'false');
\c :TEST_DBNAME :ROLE_4;
-- this is a stand-in for the OSM table
CREATE FOREIGN TABLE child_fdw_table
(timec timestamptz NOT NULL, acq_id bigint, value bigint)
 SERVER s3_server OPTIONS ( schema_name 'public', table_name 'fdw_table');
--now attach foreign table as a chunk of the hypertable.
CREATE TABLE ht_try(timec timestamptz NOT NULL, acq_id bigint, value bigint);
SELECT create_hypertable('ht_try', 'timec', chunk_time_interval => interval '1 day');
  create_hypertable  
---------------------
 (5,public,ht_try,t)

INSERT INTO ht_try VALUES ('2022-05-05 01:00', 222, 222);
SELECT * FROM child_fdw_table;
            timec             | acq_id | value 
------------------------------+--------+-------
 Wed Jan 01 01:00:00 2020 PST |    100 |  1000

-- test size functions on foreign table
SELECT * FROM _timescaledb_functions.relation_approximate_size('child_fdw_table');
 total_size | heap_size | index_size | toast_size 
------------+-----------+------------+------------
          0 |         0 |          0 |          0

SELECT * FROM _timescaledb_functions.relation_size('child_fdw_table');
 total_size | heap_size | index_size | toast_size 
------------+-----------+------------+------------
          0 |         0 |          0 |          0

-- error should be thrown as the hypertable does not yet have an associated tiered chunk
\set ON_ERROR_STOP 0
SELECT _timescaledb_functions.hypertable_osm_range_update('ht_try','2020-01-01 01:00'::timestamptz, '2020-01-01 03:00');
ERROR:  no OSM chunk found for hypertable public.ht_try
\set ON_ERROR_STOP 1
SELECT _timescaledb_functions.attach_osm_table_chunk('ht_try', 'child_fdw_table');
 attach_osm_table_chunk 
------------------------
 t

-- check hypertable status
SELECT status FROM _timescaledb_catalog.hypertable WHERE table_name = 'ht_try';
 status 
--------
      3

-- must also update the range since the created chunk contains data
BEGIN;
SELECT _timescaledb_functions.lock_osm_chunk_dimension_slice('ht_try');
 lock_osm_chunk_dimension_slice 
--------------------------------
 

SELECT _timescaledb_functions.hypertable_osm_range_update('ht_try', '2020-01-01'::timestamptz, '2020-01-02');
 hypertable_osm_range_update 
-----------------------------
 f

COMMIT;
-- OSM chunk is not visible in chunks view
SELECT chunk_name, range_start, range_end
FROM timescaledb_information.chunks
WHERE hypertable_name = 'ht_try' ORDER BY 1;
    chunk_name     |         range_start          |          range_end           
-------------------+------------------------------+------------------------------
 _hyper_5_10_chunk | Wed May 04 17:00:00 2022 PDT | Thu May 05 17:00:00 2022 PDT

SELECT chunk_name, range_start, range_end
FROM chunk_view
WHERE hypertable_name = 'ht_try'
ORDER BY chunk_name;
    chunk_name     |         range_start          |          range_end           
-------------------+------------------------------+------------------------------
 _hyper_5_10_chunk | Wed May 04 17:00:00 2022 PDT | Thu May 05 17:00:00 2022 PDT
 child_fdw_table   | Wed Jan 01 00:00:00 2020 PST | Thu Jan 02 00:00:00 2020 PST

SELECT * FROM ht_try ORDER BY 1;
            timec             | acq_id | value 
------------------------------+--------+-------
 Wed Jan 01 01:00:00 2020 PST |    100 |  1000
 Thu May 05 01:00:00 2022 PDT |    222 |   222

-- Check that the direct select from OSM chunk doesn't lead to bad effects.
SELECT * FROM child_fdw_table;
            timec             | acq_id | value 
------------------------------+--------+-------
 Wed Jan 01 01:00:00 2020 PST |    100 |  1000

SELECT relname, relowner::regrole FROM pg_class
WHERE relname in ( select chunk_name FROM chunk_view
                   WHERE hypertable_name = 'ht_try' )
ORDER BY relname;
      relname      |  relowner   
-------------------+-------------
 _hyper_5_10_chunk | test_role_4
 child_fdw_table   | test_role_4

SELECT inhrelid::regclass
FROM pg_inherits WHERE inhparent = 'ht_try'::regclass ORDER BY 1;
                inhrelid                 
-----------------------------------------
 child_fdw_table
 _timescaledb_internal._hyper_5_10_chunk

--TEST chunk exclusion code does not filter out OSM chunk
SELECT * from ht_try ORDER BY 1;
            timec             | acq_id | value 
------------------------------+--------+-------
 Wed Jan 01 01:00:00 2020 PST |    100 |  1000
 Thu May 05 01:00:00 2022 PDT |    222 |   222

SELECT * from ht_try WHERE timec < '2022-01-01 01:00' ORDER BY 1;
            timec             | acq_id | value 
------------------------------+--------+-------
 Wed Jan 01 01:00:00 2020 PST |    100 |  1000

SELECT * from ht_try WHERE timec = '2020-01-01 01:00' ORDER BY 1;
            timec             | acq_id | value 
------------------------------+--------+-------
 Wed Jan 01 01:00:00 2020 PST |    100 |  1000

SELECT * from ht_try WHERE  timec > '2000-01-01 01:00' and timec < '2022-01-01 01:00' ORDER BY 1;
            timec             | acq_id | value 
------------------------------+--------+-------
 Wed Jan 01 01:00:00 2020 PST |    100 |  1000

SELECT * from ht_try WHERE timec > '2020-01-01 01:00' ORDER BY 1;
            timec             | acq_id | value 
------------------------------+--------+-------
 Thu May 05 01:00:00 2022 PDT |    222 |   222

-- test ordered append
BEGIN;
-- before updating the ranges
:EXPLAIN SELECT * FROM ht_try ORDER BY 1;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on ht_try
   Order: ht_try.timec
   ->  Foreign Scan on child_fdw_table
   ->  Index Scan Backward using _hyper_5_10_chunk_ht_try_timec_idx on _hyper_5_10_chunk

-- range before update
SELECT ds.chunk_id, c.table_name, c.status, c.osm_chunk, ds.id AS dimension_slice_id, ds.range_start, ds.range_end
FROM _timescaledb_catalog.chunk c, _timescaledb_catalog.dimension_slice ds
WHERE c.table_name = 'child_fdw_table' AND ds.chunk_id = c.id;
 chunk_id |   table_name    | status | osm_chunk | dimension_slice_id |   range_start    |    range_end     
----------+-----------------+--------+-----------+--------------------+------------------+------------------
       11 | child_fdw_table |      0 | t         |                 10 | 1577865600000000 | 1577952000000000

SELECT _timescaledb_functions.hypertable_osm_range_update('ht_try', '2020-01-01 01:00'::timestamptz, '2020-01-02');
 hypertable_osm_range_update 
-----------------------------
 f

SELECT id, schema_name, table_name, status FROM _timescaledb_catalog.hypertable WHERE table_name = 'ht_try';
 id | schema_name | table_name | status 
----+-------------+------------+--------
  5 | public      | ht_try     |      1

-- verify range was updated
SELECT ds.chunk_id, c.table_name, c.status, c.osm_chunk, ds.id AS dimension_slice_id, ds.range_start, ds.range_end
FROM _timescaledb_catalog.chunk c, _timescaledb_catalog.dimension_slice ds
WHERE c.table_name = 'child_fdw_table' AND ds.chunk_id = c.id;
 chunk_id |   table_name    | status | osm_chunk | dimension_slice_id |   range_start    |    range_end     
----------+-----------------+--------+-----------+--------------------+------------------+------------------
       11 | child_fdw_table |      0 | t         |                 10 | 1577869200000000 | 1577952000000000

-- should be ordered append now
:EXPLAIN SELECT * FROM ht_try ORDER BY 1;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on ht_try
   Order: ht_try.timec
   ->  Foreign Scan on child_fdw_table
   ->  Index Scan Backward using _hyper_5_10_chunk_ht_try_timec_idx on _hyper_5_10_chunk

SELECT * FROM ht_try ORDER BY 1;
            timec             | acq_id | value 
------------------------------+--------+-------
 Wed Jan 01 01:00:00 2020 PST |    100 |  1000
 Thu May 05 01:00:00 2022 PDT |    222 |   222

-- test invalid range - should not be ordered append
SELECT _timescaledb_functions.hypertable_osm_range_update('ht_try');
 hypertable_osm_range_update 
-----------------------------
 f

:EXPLAIN SELECT * from ht_try ORDER BY 1;
--- QUERY PLAN ---
 Merge Append
   Sort Key: ht_try.timec
   ->  Index Scan Backward using _hyper_5_10_chunk_ht_try_timec_idx on _hyper_5_10_chunk
   ->  Foreign Scan on child_fdw_table

SELECT * from ht_try ORDER BY 1;
            timec             | acq_id | value 
------------------------------+--------+-------
 Wed Jan 01 01:00:00 2020 PST |    100 |  1000
 Thu May 05 01:00:00 2022 PDT |    222 |   222

ROLLBACK;
\set ON_ERROR_STOP 0
-- test that error is produced when range_start < range_end
SELECT _timescaledb_functions.hypertable_osm_range_update('ht_try', '2020-01-02 01:00'::timestamptz, '2020-01-02 00:00');
ERROR:  dimension slice range_end cannot be less than range_start
-- error when range overlaps
SELECT _timescaledb_functions.hypertable_osm_range_update('ht_try', '2022-05-05 01:00'::timestamptz, '2022-05-06');
ERROR:  attempting to set overlapping range for tiered chunk of public.ht_try
\set ON_ERROR_STOP 1
-- test that approximate size function works when a osm chunk is present
SELECT * FROM hypertable_approximate_size('ht_try');
 hypertable_approximate_size 
-----------------------------
                       32768

\set ON_ERROR_STOP 0
-- Error for a hypertable that has no OSM chunk
SELECT _timescaledb_functions.lock_osm_chunk_dimension_slice('test1.hyper1');
ERROR:  no OSM chunk found for hypertable test1.hyper1
\set ON_ERROR_STOP 1
--TEST GUC variable to enable/disable OSM chunk
SET timescaledb.enable_tiered_reads=false;
:EXPLAIN SELECT * from ht_try;
--- QUERY PLAN ---
 Seq Scan on _hyper_5_10_chunk

:EXPLAIN SELECT * from ht_try WHERE timec > '2022-01-01 01:00';
--- QUERY PLAN ---
 Seq Scan on _hyper_5_10_chunk

:EXPLAIN SELECT * from ht_try WHERE timec < '2023-01-01 01:00';
--- QUERY PLAN ---
 Seq Scan on _hyper_5_10_chunk

SET timescaledb.enable_tiered_reads=true;
:EXPLAIN SELECT * from ht_try;
--- QUERY PLAN ---
 Append
   ->  Foreign Scan on child_fdw_table
   ->  Seq Scan on _hyper_5_10_chunk

-- foreign chunk contains data from Jan 2020, so it is skipped during planning
:EXPLAIN SELECT * from ht_try WHERE timec > '2022-01-01 01:00';
--- QUERY PLAN ---
 Seq Scan on _hyper_5_10_chunk

:EXPLAIN SELECT * from ht_try WHERE timec < '2023-01-01 01:00';
--- QUERY PLAN ---
 Append
   ->  Foreign Scan on child_fdw_table
   ->  Seq Scan on _hyper_5_10_chunk

-- Test forceful refreshment. Here we simulate the situation that we've seen
-- with tiered data when `timescaledb.enable_tiered_reads` were disabled on the
-- server level. In that case we would not see materialized tiered data and
-- we wouldn't be able to re-materialize the data using a normal refresh call
-- because it would skip previously materialized ranges, but it should be
-- possible with `force=>true` parameter.
CREATE MATERIALIZED VIEW ht_try_weekly
WITH (timescaledb.continuous) AS
SELECT time_bucket(interval '1 week', timec) AS ts_bucket, avg(value)
FROM ht_try
GROUP BY 1
WITH NO DATA;
SELECT * FROM ht_try_weekly;
 ts_bucket | avg 
-----------+-----

SET timescaledb.enable_tiered_reads=false;
CALL refresh_continuous_aggregate('ht_try_weekly', '2019-12-29', '2020-01-10', force=>false);
SELECT * FROM ht_try_weekly;
 ts_bucket | avg 
-----------+-----

SET timescaledb.enable_tiered_reads=true;
CALL refresh_continuous_aggregate('ht_try_weekly', '2019-12-29', '2020-01-10', force=>true);
SELECT * FROM ht_try_weekly;
          ts_bucket           |          avg          
------------------------------+-----------------------
 Sun Dec 29 16:00:00 2019 PST | 1000.0000000000000000

DROP MATERIALIZED VIEW ht_try_weekly;
NOTICE:  drop cascades to table _timescaledb_internal._hyper_6_12_chunk
-- Test refresh policy with different settings of `include_tiered_data` parameter
CREATE FUNCTION create_test_cagg(include_tiered_data BOOL)
RETURNS INTEGER AS
$$
DECLARE
	cfg jsonb;
	job_id INTEGER;
BEGIN
	CREATE MATERIALIZED VIEW ht_try_weekly
	WITH (timescaledb.continuous) AS
	SELECT time_bucket(interval '1 week', timec) AS ts_bucket, avg(value)
	FROM ht_try
	GROUP BY 1
	WITH NO DATA;

	job_id := add_continuous_aggregate_policy(
		'ht_try_weekly',
		start_offset => NULL,
		end_offset => INTERVAL '1 hour',
		schedule_interval => INTERVAL '1 hour',
		include_tiered_data => include_tiered_data
	);

	cfg := config FROM _timescaledb_catalog.bgw_job WHERE id = job_id;
	RAISE NOTICE 'config: %', jsonb_pretty(cfg);

	RETURN job_id;
END
$$ LANGUAGE plpgsql;
-- include tiered data
SELECT create_test_cagg(true) AS job_id \gset
NOTICE:  config: {
    "end_offset": "@ 1 hour",
    "start_offset": null,
    "mat_hypertable_id": 7,
    "include_tiered_data": true
}
CALL run_job(:job_id);
SELECT * FROM ht_try_weekly ORDER BY 1;
          ts_bucket           |          avg          
------------------------------+-----------------------
 Sun Dec 29 16:00:00 2019 PST | 1000.0000000000000000
 Sun May 01 17:00:00 2022 PDT |  222.0000000000000000

DROP MATERIALIZED VIEW ht_try_weekly;
NOTICE:  drop cascades to 2 other objects
-- exclude tiered data
SELECT create_test_cagg(false) AS job_id \gset
NOTICE:  config: {
    "end_offset": "@ 1 hour",
    "start_offset": null,
    "mat_hypertable_id": 8,
    "include_tiered_data": false
}
CALL run_job(:job_id);
SELECT * FROM ht_try_weekly ORDER BY 1;
          ts_bucket           |         avg          
------------------------------+----------------------
 Sun May 01 17:00:00 2022 PDT | 222.0000000000000000

DROP MATERIALIZED VIEW ht_try_weekly;
NOTICE:  drop cascades to table _timescaledb_internal._hyper_8_15_chunk
-- default behavior: use instance-wide GUC value
SELECT create_test_cagg(null) AS job_id \gset
NOTICE:  config: {
    "end_offset": "@ 1 hour",
    "start_offset": null,
    "mat_hypertable_id": 9
}
CALL run_job(:job_id);
SELECT * FROM ht_try_weekly ORDER BY 1;
          ts_bucket           |          avg          
------------------------------+-----------------------
 Sun Dec 29 16:00:00 2019 PST | 1000.0000000000000000
 Sun May 01 17:00:00 2022 PDT |  222.0000000000000000

DROP MATERIALIZED VIEW ht_try_weekly;
NOTICE:  drop cascades to 2 other objects
-- This test verifies that a bugfix regarding the way `ROWID_VAR`s are adjusted
-- in the chunks' targetlists on DELETE/UPDATE works (including partially
-- compressed chunks)
ALTER table ht_try SET (timescaledb.compress);
INSERT INTO ht_try VALUES ('2021-06-05 01:00', 10, 222);
SELECT compress_chunk(show_chunks('ht_try', newer_than => '2021-01-01'::timestamptz));
             compress_chunk              
-----------------------------------------
 _timescaledb_internal._hyper_5_10_chunk
 _timescaledb_internal._hyper_5_18_chunk

INSERT INTO ht_try VALUES ('2021-06-05 01:00', 10, 222);
DO $$
DECLARE
    r RECORD;
BEGIN
	EXPLAIN (BUFFERS OFF, COSTS OFF) UPDATE ht_try SET value = 2
	WHERE acq_id = 10 AND timec > now() - '15 years'::interval INTO r;
END
$$ LANGUAGE plpgsql;
-- Check that the direct select from OSM chunk doesn't lead to bad effects in
-- presence of compression.
SELECT * FROM child_fdw_table;
            timec             | acq_id | value 
------------------------------+--------+-------
 Wed Jan 01 01:00:00 2020 PST |    100 |  1000

--TEST insert into a OSM chunk fails. actually any insert will fail. But we just need
-- to mock the hook and make sure the timescaledb code works correctly.
SELECT ts_setup_osm_hook();
 ts_setup_osm_hook 
-------------------
 

\set ON_ERROR_STOP 0
--the mock hook returns true always. so cannot create a new chunk on the hypertable
INSERT INTO ht_try VALUES ('2022-06-05 01:00', 222, 222);
NOTICE:  chunk_insert_check_hook
ERROR:  Cannot insert into tiered chunk range of public.ht_try - attempt to create new chunk with range  [Sat Jun 04 17:00:00 2022 PDT Sun Jun 05 17:00:00 2022 PDT] failed
\set ON_ERROR_STOP 1
SELECT ts_undo_osm_hook();
 ts_undo_osm_hook 
------------------
 

-- TEST error have to be hypertable owner to attach a chunk to it
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER
\set ON_ERROR_STOP 0
SELECT _timescaledb_functions.attach_osm_table_chunk('ht_try', 'child_fdw_table');
ERROR:  must be owner of hypertable "ht_try"
-- TEST error try to attach to non hypertable
CREATE TABLE non_ht (time bigint, temp float);
SELECT _timescaledb_functions.attach_osm_table_chunk('non_ht', 'child_fdw_table');
ERROR:  "non_ht" is not a hypertable
-- TEST drop OSM chunk
\c :TEST_DBNAME :ROLE_4
-- We need the OSM chunk for other tests so we run the test in a single
-- transaction so that we could roll it back in the end
BEGIN;
-- get OSM chunk id
SELECT c.id as osm_chunk_id, c.table_name as osm_chunk_name, ds.id as osm_dimension_slice
FROM _timescaledb_catalog.chunk c
JOIN _timescaledb_catalog.hypertable ht ON ht.id = c.hypertable_id
JOIN _timescaledb_catalog.dimension_slice ds ON ds.chunk_id = c.id
WHERE ht.table_name = 'ht_try' AND osm_chunk = true \gset
\echo :osm_chunk_id, :osm_chunk_name, :osm_dimension_slice
11, child_fdw_table, 10
-- drop OSM chunk
SELECT _timescaledb_functions.drop_osm_chunk('ht_try');
 drop_osm_chunk 
----------------
 t

-- status should be 0 meaning hypertable doesn't have an OSM chunk
SELECT status FROM _timescaledb_catalog.hypertable WHERE table_name = 'ht_try';
 status 
--------
      0

-- chunk and dimension slice should have been cleaned up
SELECT FROM _timescaledb_catalog.chunk WHERE id = :osm_chunk_id;
--

SELECT FROM _timescaledb_catalog.dimension_slice WHERE id = :osm_dimension_slice;
--

-- foreign chunk no longer appears in the inheritance hierarchy
SELECT * FROM test.show_subtables('ht_try');
                  Child                  | Tablespace 
-----------------------------------------+------------
 _timescaledb_internal._hyper_5_10_chunk | 
 _timescaledb_internal._hyper_5_18_chunk | 

-- verify that still can read from the table after catalog manipulations
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT * FROM ht_try;
--- QUERY PLAN ---
 Append (actual rows=3.00 loops=1)
   ->  Custom Scan (ColumnarScan) on _hyper_5_10_chunk (actual rows=1.00 loops=1)
         ->  Seq Scan on compress_hyper_10_19_chunk (actual rows=1.00 loops=1)
   ->  Custom Scan (ColumnarScan) on _hyper_5_18_chunk (actual rows=1.00 loops=1)
         ->  Seq Scan on compress_hyper_10_20_chunk (actual rows=1.00 loops=1)
   ->  Seq Scan on _hyper_5_18_chunk (actual rows=1.00 loops=1)

ROLLBACK;
-- TEST error out when trying to drop an OSM chunk from a hypertable that
-- doesn't have it
SELECT _timescaledb_functions.drop_osm_chunk('test1.hyper1');
ERROR:  chunk not found
-- TEST error out when trying to drop an OSM chunk from a regular table
SELECT _timescaledb_functions.drop_osm_chunk('non_ht');
ERROR:  "non_ht" is not a hypertable or a continuous aggregate
\set ON_ERROR_STOP 1
-- TEST drop the hypertable and make sure foreign chunks are dropped as well --
\c :TEST_DBNAME :ROLE_4;
DROP TABLE ht_try;
SELECT relname FROM pg_class WHERE relname = 'child_fdw_table';
 relname 
---------

SELECT table_name, status, osm_chunk
FROM _timescaledb_catalog.chunk
WHERE hypertable_id IN (SELECT id from _timescaledb_catalog.hypertable
                        WHERE table_name = 'ht_try')
ORDER BY table_name;
 table_name | status | osm_chunk 
------------+--------+-----------

-- TEST can create OSM chunk if there are constraints on the hypertable
\c :TEST_DBNAME :ROLE_4
CREATE TABLE measure( id integer PRIMARY KEY, mname varchar(10));
INSERT INTO measure VALUES( 1, 'temp');
INSERT INTO measure VALUES( 2, 'osmtemp');
CREATE TABLE devices( id integer PRIMARY KEY, devname varchar(10) );
INSERT INTO devices VALUES( 111, 'dev1');
INSERT INTO devices VALUES( 222, 'osmdev');
CREATE TABLE devicesref( id integer PRIMARY KEY, devname varchar(10) );
INSERT INTO devicesref VALUES( 44, 'devref');
INSERT INTO devicesref VALUES( 55, 'osmdevref');
CREATE TABLE hyper_constr  ( id integer, time bigint, temp float, mid integer
                             ,dev integer
                             ,devref integer
                             ,PRIMARY KEY (id, time)
                             ,FOREIGN KEY ( mid) REFERENCES measure(id)
                             ,FOREIGN KEY ( dev ) REFERENCES devices(id) ON DELETE CASCADE
                             ,FOREIGN KEY ( devref ) REFERENCES devicesref(id) ON DELETE
RESTRICT
                             ,CHECK ( temp > 10)
                           );
SELECT create_hypertable('hyper_constr', 'time', chunk_time_interval => 10);
     create_hypertable      
----------------------------
 (11,public,hyper_constr,t)

INSERT INTO hyper_constr VALUES( 10, 200, 22, 1, 111, 44);
\c postgres_fdw_db :ROLE_4
CREATE TABLE fdw_hyper_constr(id integer, time bigint, temp float, mid integer, dev integer, devref integer);
INSERT INTO fdw_hyper_constr VALUES( 10, 100, 33, 2, 222, 55);
INSERT INTO fdw_hyper_constr VALUES( 10, 300, 44, 2, 333, 30);
INSERT INTO fdw_hyper_constr VALUES( 10, 400, 55, 2, 444, 40);
\c :TEST_DBNAME :ROLE_4
-- this is a stand-in for the OSM table
CREATE FOREIGN TABLE child_hyper_constr
( id integer NOT NULL, time bigint NOT NULL, temp float, mid integer, dev integer, devref integer)
 SERVER s3_server OPTIONS ( schema_name 'public', table_name 'fdw_hyper_constr');
--check constraints are automatically added for the foreign table
SELECT _timescaledb_functions.attach_osm_table_chunk('hyper_constr', 'child_hyper_constr');
 attach_osm_table_chunk 
------------------------
 t

SELECT chunk_name, range_start_int, range_end_int
FROM chunk_view
WHERE hypertable_name = 'hyper_constr'
ORDER BY chunk_name;
     chunk_name     |   range_start_int   |    range_end_int    
--------------------+---------------------+---------------------
 _hyper_11_21_chunk |                 200 |                 210
 child_hyper_constr | 9223372036854775806 | 9223372036854775807

SELECT table_name, status, osm_chunk
FROM _timescaledb_catalog.chunk
WHERE hypertable_id IN (SELECT id from _timescaledb_catalog.hypertable
                        WHERE table_name = 'hyper_constr')
ORDER BY table_name;
     table_name     | status | osm_chunk 
--------------------+--------+-----------
 _hyper_11_21_chunk |      0 | f
 child_hyper_constr |      0 | t

SELECT * FROM hyper_constr order by time;
 id | time | temp | mid | dev | devref 
----+------+------+-----+-----+--------
 10 |  100 |   33 |   2 | 222 |     55
 10 |  200 |   22 |   1 | 111 |     44
 10 |  300 |   44 |   2 | 333 |     30
 10 |  400 |   55 |   2 | 444 |     40

-- TEST verify data from fdw is selected correctly
SELECT * FROM hyper_constr WHERE time > 200 order by time;
 id | time | temp | mid | dev | devref 
----+------+------+-----+-----+--------
 10 |  300 |   44 |   2 | 333 |     30
 10 |  400 |   55 |   2 | 444 |     40

SELECT * FROM hyper_constr WHERE time > 200 and time < 400 order by time;
 id | time | temp | mid | dev | devref 
----+------+------+-----+-----+--------
 10 |  300 |   44 |   2 | 333 |     30

--TEST verify the check constraint exists on the OSM chunk
SELECT * FROM test.show_constraints('child_hyper_constr');
       Constraint        | Type | Columns | Index |              Expr               | Deferrable | Deferred | Validated 
-------------------------+------+---------+-------+---------------------------------+------------+----------+-----------
 hyper_constr_temp_check | c    | {temp}  | -     | (temp > (10)::double precision) | f          | f        | t

-- dimension slices owned by the OSM chunk
SELECT ds.id AS dimension_slice_id, ds.dimension_id, ds.range_start, ds.range_end
FROM _timescaledb_catalog.chunk c
JOIN _timescaledb_catalog.dimension_slice ds ON ds.chunk_id = c.id
WHERE c.table_name = 'child_hyper_constr'
ORDER BY ds.id;
 dimension_slice_id | dimension_id |     range_start     |      range_end      
--------------------+--------------+---------------------+---------------------
                 19 |            9 | 9223372036854775806 | 9223372036854775807

-- TEST foreign key trigger: deleting data from foreign table measure
-- does not error out due to data in osm chunk
\set ON_ERROR_STOP 0
BEGIN;
-- only show sqlstate here to hide constraint name difference
\set VERBOSITY sqlstate
DELETE FROM measure where id = 1;
ERROR:  23503
\set VERBOSITY terse
ROLLBACK;
\set ON_ERROR_STOP 1
--this touches osm chunk. should succeed silently without deleting any data
BEGIN;
DELETE FROM measure where id = 2;
SELECT * FROM measure order by id;
 id | mname 
----+-------
  1 | temp

ROLLBACK;
BEGIN;
DELETE FROM devices where id = 222;
SELECT * FROM devices order by id;
 id  | devname 
-----+---------
 111 | dev1

ROLLBACK;
BEGIN;
DELETE FROM devicesref where id = 55;
SELECT * FROM devicesref order by id;
 id | devname 
----+---------
 44 | devref

ROLLBACK;
--TEST retention policy is applied on OSM chunk by calling registered callback
CREATE OR REPLACE FUNCTION dummy_now_smallint() RETURNS BIGINT LANGUAGE SQL IMMUTABLE as  'SELECT 500::bigint' ;
SELECT set_integer_now_func('hyper_constr', 'dummy_now_smallint');
 set_integer_now_func 
----------------------
 

SELECT add_retention_policy('hyper_constr', 100::int) AS deljob_id \gset
--add hooks for osm callbacks that are triggered when drop_chunks is invoked---
SELECT ts_setup_osm_hook();
 ts_setup_osm_hook 
-------------------
 

BEGIN;
SELECT drop_chunks('hyper_constr', 10::int);
NOTICE:  hypertable_drop_chunks_hook 
         drop_chunks          
------------------------------
 _timescaledb_internal.dummy0
 _timescaledb_internal.dummy1

SELECT id, table_name FROM _timescaledb_catalog.chunk
where hypertable_id = (Select id from _timescaledb_catalog.hypertable where table_name = 'hyper_constr')
ORDER BY id;
 id |     table_name     
----+--------------------
 21 | _hyper_11_21_chunk
 22 | child_hyper_constr

-- show_chunks will not show the OSM chunk which is visible via the above query
SELECT show_chunks('hyper_constr');
               show_chunks                
------------------------------------------
 _timescaledb_internal._hyper_11_21_chunk

ROLLBACK;
CALL run_job(:deljob_id);
NOTICE:  hypertable_drop_chunks_hook 
CALL run_job(:deljob_id);
NOTICE:  hypertable_drop_chunks_hook 
SELECT chunk_name
FROM chunk_view
WHERE hypertable_name = 'hyper_constr'
ORDER BY chunk_name;
     chunk_name     
--------------------
 child_hyper_constr

SELECT ts_undo_osm_hook();
 ts_undo_osm_hook 
------------------
 

----- TESTS for copy into frozen chunk ------------
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER
CREATE TABLE test1.copy_test (
    "time" timestamptz NOT NULL,
    "value" double precision NOT NULL
);
SELECT create_hypertable('test1.copy_test', 'time', chunk_time_interval => interval '1 day');
   create_hypertable    
------------------------
 (12,test1,copy_test,t)

COPY test1.copy_test FROM STDIN DELIMITER ',';
-- Freeze one of the chunks
SELECT chunk_schema || '.' ||  chunk_name as "COPY_CHNAME", chunk_name as "COPY_CHUNK_NAME"
FROM timescaledb_information.chunks
WHERE hypertable_name = 'copy_test' and hypertable_schema = 'test1'
ORDER BY chunk_name LIMIT 1
\gset
SELECT _timescaledb_functions.freeze_chunk( :'COPY_CHNAME');
 freeze_chunk 
--------------
 t

-- Check state
SELECT table_name, status
FROM _timescaledb_catalog.chunk WHERE table_name = :'COPY_CHUNK_NAME';
     table_name     | status 
--------------------+--------
 _hyper_12_23_chunk |      4

\set ON_ERROR_STOP 0
-- Copy should fail because one of che chunks is frozen
COPY test1.copy_test FROM STDIN DELIMITER ',';
ERROR:  cannot INSERT into frozen chunk "_hyper_12_23_chunk"
\set ON_ERROR_STOP 1
-- Count existing rows
SELECT COUNT(*) FROM test1.copy_test;
 count 
-------
     2

-- Check state
SELECT table_name, status
FROM _timescaledb_catalog.chunk WHERE table_name = :'COPY_CHUNK_NAME';
     table_name     | status 
--------------------+--------
 _hyper_12_23_chunk |      4

\set ON_ERROR_STOP 0
-- Copy should fail because one of che chunks is frozen
COPY test1.copy_test FROM STDIN DELIMITER ',';
ERROR:  cannot INSERT into frozen chunk "_hyper_12_23_chunk"
\set ON_ERROR_STOP 1
-- Count existing rows
SELECT COUNT(*) FROM test1.copy_test;
 count 
-------
     2

-- Check unfreeze restored chunk
SELECT _timescaledb_functions.unfreeze_chunk( :'COPY_CHNAME');
 unfreeze_chunk 
----------------
 t

-- Check state
SELECT table_name, status
FROM _timescaledb_catalog.chunk WHERE table_name = :'COPY_CHUNK_NAME';
     table_name     | status 
--------------------+--------
 _hyper_12_23_chunk |      0

-- Copy should work now
COPY test1.copy_test FROM STDIN DELIMITER ',';
--Utility functions -check index creation on hypertable with OSM chunk
-- Indexes are not created on OSM chunks, they are skipped as these are foreign tables
\c :TEST_DBNAME :ROLE_4
CREATE INDEX hyper_constr_mid_idx ON hyper_constr( mid, time);
NOTICE:  skipping index creation for tiered data
SELECT indexname, tablename FROM pg_indexes WHERE indexname = 'hyper_constr_mid_idx';
      indexname       |  tablename   
----------------------+--------------
 hyper_constr_mid_idx | hyper_constr

DROP INDEX hyper_constr_mid_idx;
CREATE INDEX hyper_constr_mid_idx ON hyper_constr(mid, time) WITH (timescaledb.transaction_per_chunk);
NOTICE:  skipping index creation for tiered data
SELECT indexname, tablename FROM pg_indexes WHERE indexname = 'hyper_constr_mid_idx';
      indexname       |  tablename   
----------------------+--------------
 hyper_constr_mid_idx | hyper_constr

DROP INDEX hyper_constr_mid_idx;
\i include/chunk_utils_internal_orderedappend.sql
-- 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.
-- TESTS for hypertable_osm_range_update_function
--           orderedappend (needs hypertable_osm_range_update to update catalog range)
--                         (so that ordered append will work with OSM chunks)
-- TEST for hypertable_osm_range_update function
-- test range of dimension slice for osm chunk for different datatypes
CREATE TABLE osm_int2(time int2 NOT NULL);
CREATE TABLE osm_int4(time int4 NOT NULL);
CREATE TABLE osm_int8(time int8 NOT NULL);
CREATE TABLE osm_date(time date NOT NULL);
CREATE TABLE osm_ts(time timestamp NOT NULL);
CREATE TABLE osm_tstz(time timestamptz NOT NULL);
SELECT table_name FROM create_hypertable('osm_int2','time',chunk_time_interval:=1000);
 table_name 
------------
 osm_int2

SELECT table_name FROM create_hypertable('osm_int4','time',chunk_time_interval:=1000);
 table_name 
------------
 osm_int4

SELECT table_name FROM create_hypertable('osm_int8','time',chunk_time_interval:=1000);
 table_name 
------------
 osm_int8

SELECT table_name FROM create_hypertable('osm_date','time');
 table_name 
------------
 osm_date

SELECT table_name FROM create_hypertable('osm_ts','time');
psql:include/chunk_utils_internal_orderedappend.sql:21: WARNING:  column type "timestamp without time zone" used for "time" does not follow best practices
 table_name 
------------
 osm_ts

SELECT table_name FROM create_hypertable('osm_tstz','time');
 table_name 
------------
 osm_tstz

CREATE FOREIGN TABLE osm_int2_fdw_child(time int2 NOT NULL) SERVER s3_server;
CREATE FOREIGN TABLE osm_int4_fdw_child(time int4 NOT NULL) SERVER s3_server;
CREATE FOREIGN TABLE osm_int8_fdw_child(time int8 NOT NULL) SERVER s3_server;
CREATE FOREIGN TABLE osm_date_fdw_child(time date NOT NULL) SERVER s3_server;
CREATE FOREIGN TABLE osm_ts_fdw_child(time timestamp NOT NULL) SERVER s3_server;
CREATE FOREIGN TABLE osm_tstz_fdw_child(time timestamptz NOT NULL) SERVER s3_server;
SELECT dt, _timescaledb_functions.attach_osm_table_chunk('osm_' || dt, 'osm_' || dt || '_fdw_child') FROM unnest('{int2,int4,int8,date,ts,tstz}'::text[]) u(dt);
  dt  | attach_osm_table_chunk 
------+------------------------
 int2 | t
 int4 | t
 int8 | t
 date | t
 ts   | t
 tstz | t

SELECT ht.table_name, ds.*
FROM _timescaledb_catalog.dimension_slice ds
INNER JOIN _timescaledb_catalog.dimension d ON d.id=ds.dimension_id
INNER JOIN _timescaledb_catalog.hypertable ht on ht.id=d.hypertable_id
WHERE ht.table_name LIKE 'osm%'
ORDER BY 2,3;
 table_name | id | chunk_id | dimension_id |     range_start     |      range_end      
------------+----+----------+--------------+---------------------+---------------------
 osm_int2   | 22 |       25 |           11 | 9223372036854775806 | 9223372036854775807
 osm_int4   | 23 |       26 |           12 | 9223372036854775806 | 9223372036854775807
 osm_int8   | 24 |       27 |           13 | 9223372036854775806 | 9223372036854775807
 osm_date   | 25 |       28 |           14 | 9223372036854775806 | 9223372036854775807
 osm_ts     | 26 |       29 |           15 | 9223372036854775806 | 9223372036854775807
 osm_tstz   | 27 |       30 |           16 | 9223372036854775806 | 9223372036854775807

-- test that correct slice is found and updated for table with multiple chunk constraints
CREATE TABLE test_multicon(time timestamptz not null unique, a int);
SELECT hypertable_id as htid FROM create_hypertable('test_multicon', 'time', chunk_time_interval => interval '1 day') \gset
insert into test_multicon values ('2020-01-02 01:00'::timestamptz, 1);
SELECT c.id, c.hypertable_id, c.schema_name, c.table_name, c.status, c.osm_chunk,
ds.chunk_id, ds.id AS dimension_slice_id, format('constraint_%s', ds.id)::name AS constraint_name FROM
_timescaledb_catalog.chunk c, _timescaledb_catalog.dimension_slice ds WHERE c.hypertable_id = :htid
AND ds.chunk_id = c.id;
 id | hypertable_id |      schema_name      |     table_name     | status | osm_chunk | chunk_id | dimension_slice_id | constraint_name 
----+---------------+-----------------------+--------------------+--------+-----------+----------+--------------------+-----------------
 31 |            19 | _timescaledb_internal | _hyper_19_31_chunk |      0 | f         |       31 |                 28 | constraint_28

\c :TEST_DBNAME :ROLE_SUPERUSER ;
UPDATE _timescaledb_catalog.chunk SET osm_chunk = true WHERE hypertable_id = :htid;
\c :TEST_DBNAME :ROLE_4;
SELECT _timescaledb_functions.hypertable_osm_range_update('test_multicon', '2020-01-02 01:00'::timestamptz, '2020-01-04 01:00');
 hypertable_osm_range_update 
-----------------------------
 f

-- view udpated range
SELECT ds.chunk_id, c.table_name, c.status, c.osm_chunk, ds.id AS dimension_slice_id, ds.range_start, ds.range_end
FROM _timescaledb_catalog.chunk c, _timescaledb_catalog.dimension_slice ds
WHERE c.hypertable_id = :htid AND ds.chunk_id = c.id;
 chunk_id |     table_name     | status | osm_chunk | dimension_slice_id |   range_start    |    range_end     
----------+--------------------+--------+-----------+--------------------+------------------+------------------
       31 | _hyper_19_31_chunk |      0 | t         |                 28 | 1577955600000000 | 1578128400000000

-- check that range was reset to default - infinity
\set ON_ERROR_STOP 0
-- both range_start and range_end must be NULL, or non-NULL
SELECT _timescaledb_functions.hypertable_osm_range_update('test_multicon', NULL, '2020-01-04 01:00'::timestamptz);
psql:include/chunk_utils_internal_orderedappend.sql:58: ERROR:  range_start and range_end parameters must be both NULL or both non-NULL
SELECT _timescaledb_functions.hypertable_osm_range_update('test_multicon', NULL, NULL);
psql:include/chunk_utils_internal_orderedappend.sql:59: ERROR:  could not determine polymorphic type because input has type unknown
SELECT _timescaledb_functions.hypertable_osm_range_update('test_multicon');
 hypertable_osm_range_update 
-----------------------------
 f

\set ON_ERROR_STOP 1
SELECT _timescaledb_functions.hypertable_osm_range_update('test_multicon', NULL::timestamptz, NULL);
 hypertable_osm_range_update 
-----------------------------
 f

SELECT ds.chunk_id, c.table_name, c.status, c.osm_chunk, ds.id AS dimension_slice_id, ds.range_start, ds.range_end
FROM _timescaledb_catalog.chunk c, _timescaledb_catalog.dimension_slice ds
WHERE c.hypertable_id = :htid AND ds.chunk_id = c.id ORDER BY ds.chunk_id;
 chunk_id |     table_name     | status | osm_chunk | dimension_slice_id |     range_start     |      range_end      
----------+--------------------+--------+-----------+--------------------+---------------------+---------------------
       31 | _hyper_19_31_chunk |      0 | t         |                 28 | 9223372036854775806 | 9223372036854775807

-- TEST for orderedappend that depends on hypertable_osm_range_update functionality
-- test further with ordered append
\c postgres_fdw_db :ROLE_4;
CREATE TABLE test_chunkapp_fdw (time timestamptz NOT NULL, a int);
INSERT INTO test_chunkapp_fdw (time, a) VALUES ('2020-01-03 02:00'::timestamptz, 3);
\c :TEST_DBNAME :ROLE_4
CREATE TABLE test_chunkapp(time timestamptz NOT NULL, a int);
SELECT hypertable_id as htid FROM create_hypertable('test_chunkapp', 'time', chunk_time_interval => interval '1day') \gset
INSERT INTO test_chunkapp (time, a) VALUES ('2020-01-01 01:00'::timestamptz, 1), ('2020-01-02 01:00'::timestamptz, 2);
CREATE FOREIGN TABLE test_chunkapp_fdw_child(time timestamptz NOT NULL, a int) SERVER s3_server OPTIONS (schema_name 'public', table_name 'test_chunkapp_fdw');;
SELECT _timescaledb_functions.attach_osm_table_chunk('test_chunkapp','test_chunkapp_fdw_child');
 attach_osm_table_chunk 
------------------------
 t

-- view range before update
SELECT ds.chunk_id, c.table_name, c.status, c.osm_chunk, ds.id AS dimension_slice_id, ds.range_start, ds.range_end
FROM _timescaledb_catalog.chunk c, _timescaledb_catalog.dimension_slice ds
WHERE c.hypertable_id = :htid AND ds.chunk_id = c.id ORDER BY ds.chunk_id;
 chunk_id |       table_name        | status | osm_chunk | dimension_slice_id |     range_start     |      range_end      
----------+-------------------------+--------+-----------+--------------------+---------------------+---------------------
       32 | _hyper_20_32_chunk      |      0 | f         |                 29 |    1577836800000000 |    1577923200000000
       33 | _hyper_20_33_chunk      |      0 | f         |                 30 |    1577923200000000 |    1578009600000000
       34 | test_chunkapp_fdw_child |      0 | t         |                 31 | 9223372036854775806 | 9223372036854775807

-- attempt to update overlapping range, should fail
\set ON_ERROR_STOP 0
SELECT _timescaledb_functions.hypertable_osm_range_update('test_chunkapp', '2020-01-02 01:00'::timestamptz, '2020-01-04 01:00');
psql:include/chunk_utils_internal_orderedappend.sql:86: ERROR:  attempting to set overlapping range for tiered chunk of public.test_chunkapp
\set ON_ERROR_STOP 1
-- update actual range of OSM chunk, should work
SELECT _timescaledb_functions.hypertable_osm_range_update('test_chunkapp', '2020-01-03 00:00'::timestamptz, '2020-01-04 00:00');
 hypertable_osm_range_update 
-----------------------------
 f

-- view udpated range
SELECT ds.chunk_id, c.table_name, c.status, c.osm_chunk, ds.id AS dimension_slice_id, ds.range_start, ds.range_end
FROM _timescaledb_catalog.chunk c, _timescaledb_catalog.dimension_slice ds
WHERE c.hypertable_id = :htid AND ds.chunk_id = c.id ORDER BY ds.chunk_id;
 chunk_id |       table_name        | status | osm_chunk | dimension_slice_id |   range_start    |    range_end     
----------+-------------------------+--------+-----------+--------------------+------------------+------------------
       32 | _hyper_20_32_chunk      |      0 | f         |                 29 | 1577836800000000 | 1577923200000000
       33 | _hyper_20_33_chunk      |      0 | f         |                 30 | 1577923200000000 | 1578009600000000
       34 | test_chunkapp_fdw_child |      0 | t         |                 31 | 1578038400000000 | 1578124800000000

-- ordered append should be possible as ranges do not overlap
:EXPLAIN SELECT * FROM test_chunkapp ORDER BY 1;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on test_chunkapp
   Order: test_chunkapp."time"
   ->  Index Scan Backward using _hyper_20_32_chunk_test_chunkapp_time_idx on _hyper_20_32_chunk
   ->  Index Scan Backward using _hyper_20_33_chunk_test_chunkapp_time_idx on _hyper_20_33_chunk
   ->  Foreign Scan on test_chunkapp_fdw_child

SELECT * FROM test_chunkapp ORDER BY 1;
             time             | a 
------------------------------+---
 Wed Jan 01 01:00:00 2020 PST | 1
 Thu Jan 02 01:00:00 2020 PST | 2
 Fri Jan 03 02:00:00 2020 PST | 3

-- but, insert should not be possible
SELECT ts_setup_osm_hook();
 ts_setup_osm_hook 
-------------------
 

\set ON_ERROR_STOP 0
INSERT INTO test_chunkapp VALUES ('2020-01-03 02:00'::timestamptz, 3);
psql:include/chunk_utils_internal_orderedappend.sql:100: ERROR:  Cannot insert into tiered chunk range of public.test_chunkapp - attempt to create new chunk with range  [Fri Jan 03 00:00:00 2020 PST Sat Jan 04 00:00:00 2020 PST] failed
\set ON_ERROR_STOP 1
SELECT ts_undo_osm_hook();
 ts_undo_osm_hook 
------------------
 

-- reset range to infinity
SELECT _timescaledb_functions.hypertable_osm_range_update('test_chunkapp',empty:=false);
 hypertable_osm_range_update 
-----------------------------
 f

-- ordered append not possible because range is invalid and empty was not specified
:EXPLAIN SELECT * FROM test_chunkapp ORDER BY 1;
--- QUERY PLAN ---
 Merge Append
   Sort Key: test_chunkapp."time"
   ->  Index Scan Backward using _hyper_20_32_chunk_test_chunkapp_time_idx on _hyper_20_32_chunk
   ->  Index Scan Backward using _hyper_20_33_chunk_test_chunkapp_time_idx on _hyper_20_33_chunk
   ->  Foreign Scan on test_chunkapp_fdw_child

SELECT * FROM test_chunkapp ORDER BY 1;
             time             | a 
------------------------------+---
 Wed Jan 01 01:00:00 2020 PST | 1
 Thu Jan 02 01:00:00 2020 PST | 2
 Fri Jan 03 02:00:00 2020 PST | 3

SELECT ds.chunk_id, c.table_name, c.status, c.osm_chunk, ds.id AS dimension_slice_id, ds.range_start, ds.range_end
FROM _timescaledb_catalog.chunk c, _timescaledb_catalog.dimension_slice ds
WHERE c.hypertable_id = :htid AND ds.chunk_id = c.id ORDER BY ds.chunk_id;
 chunk_id |       table_name        | status | osm_chunk | dimension_slice_id |     range_start     |      range_end      
----------+-------------------------+--------+-----------+--------------------+---------------------+---------------------
       32 | _hyper_20_32_chunk      |      0 | f         |                 29 |    1577836800000000 |    1577923200000000
       33 | _hyper_20_33_chunk      |      0 | f         |                 30 |    1577923200000000 |    1578009600000000
       34 | test_chunkapp_fdw_child |      0 | t         |                 31 | 9223372036854775806 | 9223372036854775807

-- but also, OSM chunk should be included in the scan, since range is invalid and chunk is not empty
:EXPLAIN SELECT * FROM test_chunkapp WHERE time < '2023-01-01' ORDER BY 1;
--- QUERY PLAN ---
 Merge Append
   Sort Key: test_chunkapp."time"
   ->  Index Scan Backward using _hyper_20_32_chunk_test_chunkapp_time_idx on _hyper_20_32_chunk
   ->  Index Scan Backward using _hyper_20_33_chunk_test_chunkapp_time_idx on _hyper_20_33_chunk
   ->  Foreign Scan on test_chunkapp_fdw_child

SELECT * FROM test_chunkapp WHERE time < '2023-01-01' ORDER BY 1;
             time             | a 
------------------------------+---
 Wed Jan 01 01:00:00 2020 PST | 1
 Thu Jan 02 01:00:00 2020 PST | 2
 Fri Jan 03 02:00:00 2020 PST | 3

-- now set empty to true, should ordered append
\c postgres_fdw_db :ROLE_4;
DELETE FROM test_chunkapp_fdw;
\c :TEST_DBNAME :ROLE_4;
SELECT _timescaledb_functions.hypertable_osm_range_update('test_chunkapp', NULL::timestamptz, NULL, empty => true);
 hypertable_osm_range_update 
-----------------------------
 f

:EXPLAIN SELECT * FROM test_chunkapp ORDER BY 1;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on test_chunkapp
   Order: test_chunkapp."time"
   ->  Index Scan Backward using _hyper_20_32_chunk_test_chunkapp_time_idx on _hyper_20_32_chunk
   ->  Index Scan Backward using _hyper_20_33_chunk_test_chunkapp_time_idx on _hyper_20_33_chunk
   ->  Foreign Scan on test_chunkapp_fdw_child

SELECT * FROM test_chunkapp ORDER BY 1;
             time             | a 
------------------------------+---
 Wed Jan 01 01:00:00 2020 PST | 1
 Thu Jan 02 01:00:00 2020 PST | 2

-- should exclude the OSM chunk this time since it is empty
:EXPLAIN SELECT * FROM test_chunkapp WHERE time < '2023-01-01' ORDER BY 1;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on test_chunkapp
   Order: test_chunkapp."time"
   ->  Index Scan Backward using _hyper_20_32_chunk_test_chunkapp_time_idx on _hyper_20_32_chunk
   ->  Index Scan Backward using _hyper_20_33_chunk_test_chunkapp_time_idx on _hyper_20_33_chunk

SELECT * FROM test_chunkapp WHERE time < '2023-01-01' ORDER BY 1;
             time             | a 
------------------------------+---
 Wed Jan 01 01:00:00 2020 PST | 1
 Thu Jan 02 01:00:00 2020 PST | 2

\set ON_ERROR_STOP 0
-- test adding constraint directly on OSM chunk is blocked
ALTER TABLE test_chunkapp_fdw_child ADD CHECK (a > 0); -- non-dimensional
psql:include/chunk_utils_internal_orderedappend.sql:127: ERROR:  operation not supported on OSM chunk tables
ALTER TABLE test_chunkapp_fdw_child ADD CHECK (time > '1600-01-01'::timestamptz); -- dimensional
psql:include/chunk_utils_internal_orderedappend.sql:128: ERROR:  operation not supported on OSM chunk tables
-- but on hypertable, it is allowed
ALTER TABLE test_chunkapp ADD CHECK (a > 0);
SELECT * FROM test.show_constraints('test_chunkapp_fdw_child');
      Constraint       | Type | Columns | Index |  Expr   | Deferrable | Deferred | Validated 
-----------------------+------+---------+-------+---------+------------+----------+-----------
 test_chunkapp_a_check | c    | {a}     | -     | (a > 0) | f          | f        | t

\set ON_ERROR_STOP 1
-- test error is triggered when time dimension not found
CREATE TABLE test2(time timestamptz not null, a int);
SELECT create_hypertable('test2', 'time');
  create_hypertable  
---------------------
 (21,public,test2,t)

INSERT INTO test2 VALUES ('2020-01-01'::timestamptz, 1);
ALTER TABLE test2 SET (timescaledb.compress);
SELECT compress_chunk(show_chunks('test2'));
              compress_chunk              
------------------------------------------
 _timescaledb_internal._hyper_21_35_chunk

-- find internal compression table, call API function on it
SELECT format('%I.%I', cht.schema_name, cht.table_name) AS "COMPRESSION_TBLNM"
FROM _timescaledb_catalog.hypertable ht, _timescaledb_catalog.hypertable cht
WHERE ht.table_name = 'test2' and cht.id = ht.compressed_hypertable_id \gset
\set ON_ERROR_STOP 0
SELECT _timescaledb_functions.hypertable_osm_range_update(:'COMPRESSION_TBLNM'::regclass, '2020-01-01'::timestamptz);
psql:include/chunk_utils_internal_orderedappend.sql:145: ERROR:  could not find time dimension for hypertable _timescaledb_internal._compressed_hypertable_22
\set ON_ERROR_STOP 1
-- test wrong/incompatible data types with hypertable time dimension
-- update range of int2 with int4
\set ON_ERROR_STOP 0
SELECT _timescaledb_functions.hypertable_osm_range_update('osm_int2', range_start => 65540::int4, range_end => 100000::int4);
psql:include/chunk_utils_internal_orderedappend.sql:151: ERROR:  invalid time argument type "integer"
-- update range of int8 with int4
SELECT _timescaledb_functions.hypertable_osm_range_update('osm_int8', 120, 150);
 hypertable_osm_range_update 
-----------------------------
 f

-- update range of timestamptz with date
SELECT _timescaledb_functions.hypertable_osm_range_update('osm_tstz', '2020-01-01'::date, '2020-01-03'::date);
 hypertable_osm_range_update 
-----------------------------
 f

-- udpate range of timestamp with bigint
SELECT _timescaledb_functions.hypertable_osm_range_update('osm_tstz', 9223372036854771806, 9223372036854775406);
psql:include/chunk_utils_internal_orderedappend.sql:157: ERROR:  invalid time argument type "bigint"
\set ON_ERROR_STOP 1
-- test dimension slice tuple visibility
\c :TEST_DBNAME :ROLE_SUPERUSER
CREATE TABLE osm_slice_update(time int not null);
SELECT hypertable_id AS ht_id FROM create_hypertable('osm_slice_update', 'time', chunk_time_interval => 10) \gset
INSERT INTO osm_slice_update VALUES (1);
UPDATE _timescaledb_catalog.hypertable SET status = 3 WHERE id = :ht_id;
UPDATE _timescaledb_catalog.chunk SET osm_chunk = true WHERE hypertable_id = :ht_id;
\c
BEGIN;
 	SELECT _timescaledb_functions.hypertable_osm_range_update('osm_slice_update',40,50);
 hypertable_osm_range_update 
-----------------------------
 f

ROLLBACK;
\c
-- new session should not be affected by previous rolled back transaction
-- should show 0 10 as range
\set ON_ERROR_STOP 0
INSERT INTO osm_slice_update VALUES (1);
psql:include/chunk_utils_internal_orderedappend.sql:178: ERROR:  Cannot insert into tiered chunk range of public.osm_slice_update - attempt to create new chunk with range  [0 10] failed
INSERT INTO osm_slice_update VALUES (1);
psql:include/chunk_utils_internal_orderedappend.sql:179: ERROR:  Cannot insert into tiered chunk range of public.osm_slice_update - attempt to create new chunk with range  [0 10] failed
\set ON_ERROR_STOP 1
--TEST hypertable with foreign key into it
\c :TEST_DBNAME :ROLE_4
CREATE TABLE hyper_fk(ts timestamptz primary key, device text, value float);
SELECT table_name FROM create_hypertable('hyper_fk', 'ts');
 table_name 
------------
 hyper_fk

INSERT INTO hyper_fk(ts, device, value) VALUES ('2020-01-01 00:00:00+00', 'd1', 1.0);
\c postgres_fdw_db :ROLE_4
CREATE TABLE fdw_hyper_fk(ts timestamptz NOT NULL, device text, value float);
INSERT INTO fdw_hyper_fk VALUES( '2021-05-05 00:00:00+00', 'd2', 2.0);
\c :TEST_DBNAME :ROLE_4
-- this is a stand-in for the OSM table
CREATE FOREIGN TABLE child_hyper_fk
(ts timestamptz NOT NULL, device text, value float)
 SERVER s3_server OPTIONS ( schema_name 'public', table_name 'fdw_hyper_fk');
SELECT _timescaledb_functions.attach_osm_table_chunk('hyper_fk', 'child_hyper_fk');
 attach_osm_table_chunk 
------------------------
 t

--create table with fk into hypertable
CREATE TABLE event(ts timestamptz REFERENCES hyper_fk(ts) , info text);
-- NOTE: current behavior is to allow inserts/deletes from PG tables when data
-- references OSM table.
--insert referencing OSM chunk
INSERT INTO event VALUES( '2021-05-05 00:00:00+00' , 'osm_chunk_ts');
--insert referencing non-existent value
\set ON_ERROR_STOP 0
INSERT INTO event VALUES( '2020-01-02 00:00:00+00' , 'does_not_exist_ts');
ERROR:  insert or update on table "event" violates foreign key constraint "event_ts_fkey"
\set ON_ERROR_STOP 1
INSERT INTO event VALUES( '2020-01-01 00:00:00+00' , 'chunk_ts');
SELECT * FROM event ORDER BY ts;
              ts              |     info     
------------------------------+--------------
 Tue Dec 31 16:00:00 2019 PST | chunk_ts
 Tue May 04 17:00:00 2021 PDT | osm_chunk_ts

DELETE FROM event WHERE  info = 'osm_chunk_ts';
DELETE FROM event WHERE  info = 'chunk_ts';
SELECT * FROM event ORDER BY ts;
 ts | info 
----+------

-- event triggers on chunk creation
CREATE TABLE ht_try(timec timestamptz NOT NULL, acq_id bigint, value bigint);
SELECT create_hypertable('ht_try', 'timec', chunk_time_interval => interval '1 day');
  create_hypertable   
----------------------
 (25,public,ht_try,t)

-- creating event triggers requires superuser permissions
\c :TEST_DBNAME :ROLE_SUPERUSER
-- event trigger on ddl_start
CREATE OR REPLACE FUNCTION ddl_start_trigger_func() RETURNS EVENT_TRIGGER AS
$$
BEGIN
    RAISE NOTICE 'ddl_start_trigger_func() is invoked';
END;
$$ LANGUAGE plpgsql;
CREATE EVENT TRIGGER ddl_start_trigger
ON ddl_command_start WHEN TAG IN ('CREATE TABLE') EXECUTE FUNCTION ddl_start_trigger_func();
-- event trigger on ddl_end
CREATE OR REPLACE FUNCTION ddl_end_trigger_func() RETURNS EVENT_TRIGGER AS
$$
DECLARE
    cmd RECORD;
BEGIN
    RAISE NOTICE 'ddl_end_trigger_func() is invoked';
    FOR cmd IN SELECT * FROM pg_event_trigger_ddl_commands()
    LOOP
        RAISE NOTICE 'tag: %, object: %', cmd.command_tag, cmd.object_identity::regclass;
    END LOOP;
END;
$$ LANGUAGE plpgsql;
CREATE EVENT TRIGGER ddl_end_trigger
ON ddl_command_end WHEN TAG IN ('CREATE TABLE') EXECUTE FUNCTION ddl_end_trigger_func();
-- by default event triggers on chunk creation are disabled
INSERT INTO ht_try VALUES ('2025-05-01 00:00', 1, 10);
SET timescaledb.enable_event_triggers = on;
INSERT INTO ht_try VALUES ('2025-05-02 00:00', 1, 10);
NOTICE:  ddl_start_trigger_func() is invoked
NOTICE:  ddl_end_trigger_func() is invoked
NOTICE:  tag: CREATE TABLE, object: _timescaledb_internal._hyper_25_41_chunk
RESET timescaledb.enable_event_triggers;
DROP EVENT TRIGGER ddl_start_trigger;
DROP EVENT TRIGGER ddl_end_trigger;
DROP TABLE ht_try;
--TEST ALTER TABLE propagation with foreign chunks
\c :TEST_DBNAME :ROLE_4
CREATE TABLE ht_alter(ts timestamptz) WITH (tsdb.hypertable,tsdb.compress=false);
NOTICE:  using column "ts" as partitioning column
INSERT INTO ht_alter SELECT '2025-01-01';
\c postgres_fdw_db :ROLE_4
CREATE TABLE fdw_ht_alter(ts timestamptz NOT NULL, device text, value float);
INSERT INTO fdw_ht_alter SELECT '2021-05-05';
\c :TEST_DBNAME :ROLE_4
-- this is a stand-in for the OSM table
CREATE FOREIGN TABLE child_ht_alter(ts timestamptz NOT NULL)
 SERVER s3_server OPTIONS ( schema_name 'public', table_name 'fdw_ht_alter');
SELECT _timescaledb_functions.attach_osm_table_chunk('ht_alter', 'child_ht_alter');
 attach_osm_table_chunk 
------------------------
 t

ALTER TABLE ht_alter SET (autovacuum_enabled = false);
ALTER TABLE ht_alter RESET (autovacuum_enabled);
-- test DML blocker on frozen chunks
CREATE TABLE dml_blocks (time timestamptz, device text, value float) WITH (tsdb.hypertable, tsdb.segmentby='device');
NOTICE:  using column "time" as partitioning column
-- DML on hypertable before freezing should work
INSERT INTO dml_blocks VALUES ('2025-01-01','dev1',1.0);
BEGIN;
UPDATE dml_blocks SET value = 2.0 WHERE device = 'dev1';
DELETE FROM dml_blocks WHERE device = 'dev2';
COPY dml_blocks FROM STDIN;
ROLLBACK;
SELECT show_chunks('dml_blocks') AS "CHUNK" \gset
-- DML on chunk before freezing should work
BEGIN;
INSERT INTO :CHUNK VALUES ('2025-01-01','dev1',1.0);
UPDATE :CHUNK SET value = 2.0 WHERE device = 'dev1';
DELETE FROM :CHUNK WHERE device = 'dev2';
COPY :CHUNK FROM STDIN;
ROLLBACK;
SELECT _timescaledb_functions.freeze_chunk(:'CHUNK');
 freeze_chunk 
--------------
 t

-- DML on hypertable after freezing should be blocked
\set ON_ERROR_STOP 0
INSERT INTO dml_blocks VALUES ('2025-01-01','dev1',1.0);
ERROR:  cannot INSERT into frozen chunk "_hyper_27_44_chunk"
UPDATE dml_blocks SET value = 2.0 WHERE device = 'dev1';
ERROR:  cannot update/delete rows from chunk "_hyper_27_44_chunk" as it is frozen
DELETE FROM dml_blocks WHERE device = 'dev2';
ERROR:  cannot update/delete rows from chunk "_hyper_27_44_chunk" as it is frozen
COPY dml_blocks FROM STDIN;
ERROR:  cannot INSERT into frozen chunk "_hyper_27_44_chunk"
\set ON_ERROR_STOP 1
-- DML on chunk after freezing should be blocked
\set ON_ERROR_STOP 0
INSERT INTO :CHUNK VALUES ('2025-01-01','dev1',1.0);
ERROR:  cannot INSERT into frozen chunk "_hyper_27_44_chunk"
UPDATE :CHUNK SET value = 2.0 WHERE device = 'dev1';
ERROR:  cannot update/delete rows from chunk "_hyper_27_44_chunk" as it is frozen
DELETE FROM :CHUNK WHERE device = 'dev2';
ERROR:  cannot update/delete rows from chunk "_hyper_27_44_chunk" as it is frozen
COPY :CHUNK FROM STDIN;
ERROR:  cannot INSERT into frozen chunk "_hyper_27_44_chunk"
\set ON_ERROR_STOP 1
-- repeat tests with compressed chunk
SELECT _timescaledb_functions.unfreeze_chunk(:'CHUNK');
 unfreeze_chunk 
----------------
 t

SELECT compress_chunk(:'CHUNK');
              compress_chunk              
------------------------------------------
 _timescaledb_internal._hyper_27_44_chunk

SELECT _timescaledb_functions.freeze_chunk(:'CHUNK');
 freeze_chunk 
--------------
 t

SELECT format('%I.%I', schema_name, table_name) AS "COMPRESSED_CHUNK" FROM _timescaledb_catalog.chunk ORDER BY id DESC LIMIT 1 \gset
-- DML on hypertable after freezing should be blocked
\set ON_ERROR_STOP 0
INSERT INTO dml_blocks VALUES ('2025-01-01','dev1',1.0);
ERROR:  cannot INSERT into frozen chunk "_hyper_27_44_chunk"
UPDATE dml_blocks SET value = 2.0 WHERE device = 'dev1';
ERROR:  cannot update/delete rows from chunk "_hyper_27_44_chunk" as it is frozen
DELETE FROM dml_blocks WHERE device = 'dev2';
ERROR:  cannot update/delete rows from chunk "_hyper_27_44_chunk" as it is frozen
COPY dml_blocks FROM STDIN;
ERROR:  cannot INSERT into frozen chunk "_hyper_27_44_chunk"
\set ON_ERROR_STOP 1
-- DML on chunk after freezing should be blocked
\set ON_ERROR_STOP 0
INSERT INTO :CHUNK VALUES ('2025-01-01','dev1',1.0);
ERROR:  cannot INSERT into frozen chunk "_hyper_27_44_chunk"
UPDATE :CHUNK SET value = 2.0 WHERE device = 'dev1';
ERROR:  cannot update/delete rows from chunk "_hyper_27_44_chunk" as it is frozen
DELETE FROM :CHUNK WHERE device = 'dev2';
ERROR:  cannot update/delete rows from chunk "_hyper_27_44_chunk" as it is frozen
COPY :CHUNK FROM STDIN;
ERROR:  cannot INSERT into frozen chunk "_hyper_27_44_chunk"
\set ON_ERROR_STOP 1
-- DML on chunk after freezing should be blocked
\set ON_ERROR_STOP 0
INSERT INTO :COMPRESSED_CHUNK SELECT;
ERROR:  cannot modify compressed chunk belonging to a frozen chunk
UPDATE :COMPRESSED_CHUNK SET device = 'dev3' WHERE device = 'dev1';
ERROR:  cannot modify compressed chunk belonging to a frozen chunk
DELETE FROM :COMPRESSED_CHUNK WHERE device = 'dev1';
ERROR:  cannot modify compressed chunk belonging to a frozen chunk
COPY :COMPRESSED_CHUNK FROM STDIN;
ERROR:  cannot COPY into chunk belonging to a frozen chunk
\set ON_ERROR_STOP 1
-- TEST: OSM chunks should NOT be added to publications
-- Create a new hypertable for publication testing
\c :TEST_DBNAME :ROLE_4
CREATE TABLE ht_pub_test(timec timestamptz NOT NULL, device_id int, value float);
SELECT create_hypertable('ht_pub_test', 'timec', chunk_time_interval => interval '1 day');
     create_hypertable     
---------------------------
 (29,public,ht_pub_test,t)

-- Insert data to create first normal chunk
INSERT INTO ht_pub_test VALUES ('2023-01-01 01:00', 1, 10.5);
-- Create publication
\c :TEST_DBNAME :ROLE_SUPERUSER
SET timescaledb.enable_chunk_auto_publication = true;
CREATE PUBLICATION test_pub_osm FOR TABLE ht_pub_test;
-- Verify: 1 normal chunk in publication
SELECT schemaname, tablename
FROM pg_publication_tables
WHERE pubname = 'test_pub_osm'
ORDER BY schemaname, tablename;
      schemaname       |     tablename      
-----------------------+--------------------
 _timescaledb_internal | _hyper_29_46_chunk
 public                | ht_pub_test

-- Create a new foreign table for OSM testing
\c :TEST_DBNAME :ROLE_4
CREATE FOREIGN TABLE osm_chunk_pub_test
(timec timestamptz NOT NULL, device_id int, value float)
SERVER s3_server OPTIONS (schema_name 'public', table_name 'fdw_table');
-- Attach OSM chunk
SELECT _timescaledb_functions.attach_osm_table_chunk('ht_pub_test', 'osm_chunk_pub_test');
 attach_osm_table_chunk 
------------------------
 t

SELECT _timescaledb_functions.hypertable_osm_range_update('ht_pub_test', '2020-01-01'::timestamptz, '2020-01-02');
 hypertable_osm_range_update 
-----------------------------
 f

-- Check chunks also have OSM chunk
SELECT schema_name, table_name, status, osm_chunk
FROM _timescaledb_catalog.chunk
WHERE hypertable_id IN (SELECT id from _timescaledb_catalog.hypertable
                        WHERE table_name = 'ht_pub_test')
ORDER BY table_name;
      schema_name      |     table_name     | status | osm_chunk 
-----------------------+--------------------+--------+-----------
 _timescaledb_internal | _hyper_29_46_chunk |      0 | f
 public                | osm_chunk_pub_test |      0 | t

-- Verify: still only 1 normal chunk in publication (OSM chunk NOT added)
\c :TEST_DBNAME :ROLE_SUPERUSER
SELECT schemaname, tablename
FROM pg_publication_tables
WHERE pubname = 'test_pub_osm'
ORDER BY schemaname, tablename;
      schemaname       |     tablename      
-----------------------+--------------------
 _timescaledb_internal | _hyper_29_46_chunk
 public                | ht_pub_test

-- Insert data to create second normal chunk
\c :TEST_DBNAME :ROLE_4
SET timescaledb.enable_chunk_auto_publication = true;
INSERT INTO ht_pub_test VALUES ('2023-01-02 01:00', 2, 20.5);
-- Verify: 2 normal chunks in publication (second chunk added, OSM still not)
\c :TEST_DBNAME :ROLE_SUPERUSER
SELECT schemaname, tablename
FROM pg_publication_tables
WHERE pubname = 'test_pub_osm'
ORDER BY schemaname, tablename;
      schemaname       |     tablename      
-----------------------+--------------------
 _timescaledb_internal | _hyper_29_46_chunk
 _timescaledb_internal | _hyper_29_48_chunk
 public                | ht_pub_test

-- Cleanup
DROP PUBLICATION test_pub_osm CASCADE;
\c :TEST_DBNAME :ROLE_4
DROP TABLE ht_pub_test CASCADE;
\c :TEST_DBNAME :ROLE_SUPERUSER
-- clean up databases created
DROP DATABASE postgres_fdw_db WITH (FORCE);
-- Test recompression for partial chunks blocked for frozen chunks
\set ON_ERROR_STOP 0
BEGIN;
SET timescaledb.enable_direct_compress_insert = true;
CREATE TABLE metrics (time TIMESTAMPTZ NOT NULL, device TEXT, value float) WITH (tsdb.hypertable, tsdb.orderby='time');
NOTICE:  using column "time" as partitioning column
INSERT INTO metrics SELECT '2025-01-01'::timestamptz, 'd1', 0;
WARNING:  disabling direct compress because of too small batch size
INSERT INTO metrics SELECT '2025-01-01'::timestamptz - (i || ' minute')::interval, 'd1', i::float FROM generate_series(0,3000) i;
SELECT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics') chunk;
       chunk_status_text        
--------------------------------
 {COMPRESSED,UNORDERED,PARTIAL}

SELECT _timescaledb_functions.freeze_chunk(chunk) FROM show_chunks('metrics') chunk;
 freeze_chunk 
--------------
 t

SELECT compress_chunk(ch) FROM show_chunks('metrics') ch;
ERROR:  chunk "_timescaledb_internal._hyper_30_49_chunk" is frozen, skipping compression
ROLLBACK;
\set ON_ERROR_STOP 1