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
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_internal.to_timestamp(dimsl.range_start)
     AS range_start,
    _timescaledb_internal.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.chunk_constraint chcons ON srcch.id = chcons.chunk_id
    INNER JOIN _timescaledb_catalog.dimension dim ON srcch.hypertable_id = dim.hypertable_id
    INNER JOIN _timescaledb_catalog.dimension_slice dimsl ON dim.id = dimsl.dimension_id
      AND chcons.dimension_slice_id = dimsl.id;
GRANT SELECT on chunk_view TO PUBLIC;
\c :TEST_DBNAME :ROLE_SUPERUSER
-- fake presence of timescaledb_osm
INSERT INTO pg_extension(oid,extname,extowner,extnamespace,extrelocatable,extversion) SELECT 1,'timescaledb_osm',10,11,false,'1.0';
CREATE SCHEMA test1;
GRANT CREATE ON SCHEMA test1 TO :ROLE_DEFAULT_PERM_USER;
GRANT USAGE ON SCHEMA test1 TO :ROLE_DEFAULT_PERM_USER;
-- 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);
NOTICE:  adding not-null constraint to column "time"
 create_hypertable  
--------------------
 (1,test1,hyper1,t)
(1 row)

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
(2 rows)

----- 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_internal.freeze_chunk( :'CHNAME');
 freeze_chunk 
--------------
 t
(1 row)

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

-- 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
(2 rows)

-- 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
(2 rows)

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
(2 rows)

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

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
(1 row)

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
(2 rows)

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

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

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
(2 rows)

\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
(3 rows)

-- 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
(1 row)

SELECT  _timescaledb_internal.unfreeze_chunk( :'CHNAME');
 unfreeze_chunk 
----------------
 t
(1 row)

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

--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
(1 row)

-- 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_internal.unfreeze_chunk( :'CHNAME');
 unfreeze_chunk 
----------------
 t
(1 row)

SELECT  _timescaledb_internal.drop_chunk( :'CHNAME');
 drop_chunk 
------------
 t
(1 row)

-- 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)
(1 row)

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
(3 rows)

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
(1 row)

SELECT  _timescaledb_internal.freeze_chunk( :'CHNAME');
 freeze_chunk 
--------------
 t
(1 row)

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

--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_internal.freeze_chunk( :'CHNAME');
 freeze_chunk 
--------------
 t
(1 row)

--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
(3 rows)

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
(1 row)

--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
(2 rows)

--unfreeze and drop it
SELECT  _timescaledb_internal.unfreeze_chunk( :'CHNAME');
 unfreeze_chunk 
----------------
 t
(1 row)

SELECT  _timescaledb_internal.drop_chunk( :'CHNAME');
 drop_chunk 
------------
 t
(1 row)

--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_internal.freeze_chunk( :'CHNAME');
 freeze_chunk 
--------------
 t
(1 row)

\set ON_ERROR_STOP 0
SELECT  compress_chunk( :'CHNAME');
ERROR:  compress_chunk not permitted on frozen chunk "_hyper_2_7_chunk" 
\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_internal.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 
----------------------
 
(1 row)

CREATE MATERIALIZED VIEW hyper1_cagg WITH (timescaledb.continuous)
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
(1 row)

--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_internal.freeze_chunk( :'CHNAME1');
 freeze_chunk 
--------------
 t
(1 row)

--cannot drop frozen chunk
\set ON_ERROR_STOP 0
SELECT  _timescaledb_internal.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_internal.unfreeze_chunk( :'CHNAME1');
 unfreeze_chunk 
----------------
 t
(1 row)

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

SELECT  _timescaledb_internal.drop_chunk( :'CHNAME1');
 drop_chunk 
------------
 t
(1 row)

SELECT * from test1.hyper1 ORDER BY 1;
 time | temp 
------+------
(0 rows)

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

--TEST error case (un)freeze a non-chunk
CREATE TABLE nochunk_tab( a timestamp, b integer);
\set ON_ERROR_STOP 0
SELECT _timescaledb_internal.freeze_chunk('nochunk_tab');
ERROR:  chunk not found
SELECT _timescaledb_internal.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)
(1 row)

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
(1 row)

SELECT _timescaledb_internal.attach_osm_table_chunk('ht_try', 'child_fdw_table');
 attach_osm_table_chunk 
------------------------
 t
(1 row)

-- 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
(1 row)

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   | Thu Dec 31 16:00:00 294246 PST | infinity
(2 rows)

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
(2 rows)

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
(2 rows)

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

--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
(2 rows)

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
(1 row)

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
(1 row)

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
(1 row)

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
(1 row)

--TEST GUC variable to enable/disable OSM chunk
SET timescaledb.enable_tiered_reads=false;
EXPLAIN (COSTS OFF) SELECT * from ht_try;
          QUERY PLAN          
------------------------------
 Seq Scan on _hyper_5_9_chunk
(1 row)

EXPLAIN (COSTS OFF) SELECT * from ht_try WHERE timec > '2022-01-01 01:00';
                                    QUERY PLAN                                    
----------------------------------------------------------------------------------
 Index Scan using _hyper_5_9_chunk_ht_try_timec_idx on _hyper_5_9_chunk
   Index Cond: (timec > 'Sat Jan 01 01:00:00 2022 PST'::timestamp with time zone)
(2 rows)

SET timescaledb.enable_tiered_reads=true;
EXPLAIN (COSTS OFF) SELECT * from ht_try;
              QUERY PLAN               
---------------------------------------
 Append
   ->  Foreign Scan on child_fdw_table
   ->  Seq Scan on _hyper_5_9_chunk
(3 rows)

EXPLAIN (COSTS OFF) SELECT * from ht_try WHERE timec > '2022-01-01 01:00';
                                       QUERY PLAN                                       
----------------------------------------------------------------------------------------
 Append
   ->  Foreign Scan on child_fdw_table
   ->  Index Scan using _hyper_5_9_chunk_ht_try_timec_idx on _hyper_5_9_chunk
         Index Cond: (timec > 'Sat Jan 01 01:00:00 2022 PST'::timestamp with time zone)
(4 rows)

--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 
-------------------
 
(1 row)

\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);
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 
------------------
 
(1 row)

-- 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_internal.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_internal.attach_osm_table_chunk('non_ht', 'child_fdw_table');
ERROR:  "non_ht" is not a hypertable
\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 
---------
(0 rows)

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 
------------+--------+-----------
(0 rows)

-- TEST error try freeze/unfreeze on dist hypertable
-- Add distributed hypertables
\set DATA_NODE_1 :TEST_DBNAME _1
\set DATA_NODE_2 :TEST_DBNAME _2
\c :TEST_DBNAME :ROLE_SUPERUSER
SELECT node_name, database, node_created, database_created, extension_created
FROM (
  SELECT (add_data_node(name, host => 'localhost', DATABASE => name)).*
  FROM (VALUES (:'DATA_NODE_1'), (:'DATA_NODE_2')) v(name)
) a;
         node_name         |         database          | node_created | database_created | extension_created 
---------------------------+---------------------------+--------------+------------------+-------------------
 db_chunk_utils_internal_1 | db_chunk_utils_internal_1 | t            | t                | t
 db_chunk_utils_internal_2 | db_chunk_utils_internal_2 | t            | t                | t
(2 rows)

CREATE TABLE disthyper (timec timestamp, device integer);
SELECT create_distributed_hypertable('disthyper', 'timec', 'device');
WARNING:  column type "timestamp without time zone" used for "timec" does not follow best practices
NOTICE:  adding not-null constraint to column "timec"
 create_distributed_hypertable 
-------------------------------
 (6,public,disthyper,t)
(1 row)

INSERT into disthyper VALUES ('2020-01-01', 10);
--freeze one of the chunks
SELECT chunk_schema || '.' ||  chunk_name as "CHNAME3"
FROM timescaledb_information.chunks
WHERE hypertable_name = 'disthyper'
ORDER BY chunk_name LIMIT 1
\gset
\set ON_ERROR_STOP 0
SELECT  _timescaledb_internal.freeze_chunk( :'CHNAME3');
ERROR:  operation not supported on distributed chunk or foreign table "_dist_hyper_6_12_chunk"
SELECT  _timescaledb_internal.unfreeze_chunk( :'CHNAME3');
ERROR:  operation not supported on distributed chunk or foreign table "_dist_hyper_6_12_chunk"
\set ON_ERROR_STOP 1
-- 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');
CREATE TABLE hyper_constr  ( id integer, time bigint, temp float, mid integer
                             ,PRIMARY KEY (id, time)
                             ,FOREIGN KEY ( mid) REFERENCES measure(id)
                             ,CHECK ( temp > 10)
                           );
SELECT create_hypertable('hyper_constr', 'time', chunk_time_interval => 10);
     create_hypertable     
---------------------------
 (7,public,hyper_constr,t)
(1 row)

INSERT INTO hyper_constr VALUES( 10, 200, 22, 1);
\c postgres_fdw_db :ROLE_4
CREATE TABLE fdw_hyper_constr(id integer, time bigint, temp float, mid integer);
INSERT INTO fdw_hyper_constr VALUES( 10, 100, 33, 1);
\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)
 SERVER s3_server OPTIONS ( schema_name 'public', table_name 'fdw_hyper_constr');
--check constraints are automatically added for the foreign table
SELECT _timescaledb_internal.attach_osm_table_chunk('hyper_constr', 'child_hyper_constr');
 attach_osm_table_chunk 
------------------------
 t
(1 row)

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_7_13_chunk  |      0 | f
 child_hyper_constr |      0 | t
(2 rows)

SELECT * FROM hyper_constr order by time;
 id | time | temp | mid 
----+------+------+-----
 10 |  100 |   33 |   1
 10 |  200 |   22 |   1
(2 rows)

--verify the check constraint exists on the OSM chunk
SELECT conname FROM pg_constraint
where conrelid = 'child_hyper_constr'::regclass ORDER BY 1;
         conname         
-------------------------
 hyper_constr_temp_check
(1 row)

--TEST policy is not applied on OSM chunk
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 
----------------------
 
(1 row)

SELECT add_retention_policy('hyper_constr', 100::int) AS deljob_id \gset
CALL run_job(:deljob_id);
CALL run_job(:deljob_id);
SELECT chunk_name, range_start, range_end
FROM chunk_view
WHERE hypertable_name = 'hyper_constr'
ORDER BY chunk_name;
     chunk_name     |             range_start             | range_end 
--------------------+-------------------------------------+-----------
 child_hyper_constr | Sat Jan 09 20:00:54.7758 294247 PST | infinity
(1 row)

----- 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   
-----------------------
 (8,test1,copy_test,t)
(1 row)

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_internal.freeze_chunk( :'COPY_CHNAME');
 freeze_chunk 
--------------
 t
(1 row)

-- Check state
SELECT table_name, status
FROM _timescaledb_catalog.chunk WHERE table_name = :'COPY_CHUNK_NAME';
    table_name     | status 
-------------------+--------
 _hyper_8_15_chunk |      4
(1 row)

\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_8_15_chunk"
\set ON_ERROR_STOP 1
-- Count existing rows
SELECT COUNT(*) FROM test1.copy_test;
 count 
-------
     2
(1 row)

-- Check state
SELECT table_name, status
FROM _timescaledb_catalog.chunk WHERE table_name = :'COPY_CHUNK_NAME';
    table_name     | status 
-------------------+--------
 _hyper_8_15_chunk |      4
(1 row)

\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_8_15_chunk"
\set ON_ERROR_STOP 1
-- Count existing rows
SELECT COUNT(*) FROM test1.copy_test;
 count 
-------
     2
(1 row)

-- Check unfreeze restored chunk
SELECT _timescaledb_internal.unfreeze_chunk( :'COPY_CHNAME');
 unfreeze_chunk 
----------------
 t
(1 row)

-- Check state
SELECT table_name, status
FROM _timescaledb_catalog.chunk WHERE table_name = :'COPY_CHUNK_NAME';
    table_name     | status 
-------------------+--------
 _hyper_8_15_chunk |      0
(1 row)

-- Copy should work now
COPY test1.copy_test FROM STDIN DELIMITER ',';
-- clean up databases created
\c :TEST_DBNAME :ROLE_SUPERUSER
DROP DATABASE postgres_fdw_db;
DROP DATABASE :DATA_NODE_1;
DROP DATABASE :DATA_NODE_2;