attach_chunk-16.out

-- This file and its contents are licensed under the Timescale License.
-- Please see the included NOTICE for copyright information and
-- LICENSE-TIMESCALE for a copy of the license.
\c :TEST_DBNAME :ROLE_SUPERUSER
GRANT CREATE ON DATABASE :"TEST_DBNAME" TO :ROLE_DEFAULT_PERM_USER;
SET ROLE :ROLE_DEFAULT_PERM_USER;
-- Create test tables and hypertables (reusing from detach_chunk test)
CREATE TABLE devices(id int PRIMARY KEY);
INSERT INTO devices VALUES (1), (2), (3);
CREATE TABLE attach_test(id int, time timestamptz not null, device int, temp float);
CREATE INDEX attach_test_device_idx ON attach_test (device);
ALTER TABLE attach_test
    ADD CONSTRAINT attach_test_temp_check CHECK (temp > 0),
    ADD CONSTRAINT attach_test_device_fkey FOREIGN KEY (device) REFERENCES devices(id),
    ADD CONSTRAINT attach_test_id_time_unique UNIQUE (id, time);
SELECT * FROM create_hypertable('attach_test', 'time', 'id', 2);
 hypertable_id | schema_name | table_name  | created 
---------------+-------------+-------------+---------
             1 | public      | attach_test | t

CREATE TABLE attach_test_ref (
    id int PRIMARY KEY,
    ref_id int,
    ref_time timestamptz,
    FOREIGN KEY (ref_id, ref_time) REFERENCES attach_test(id, time)
);
INSERT INTO attach_test VALUES
    (1, '2025-06-01 05:00:00+3', 1, 23.4),
    (2, '2025-06-15 05:00:00+3', 2, 24.5),
    (3, '2025-06-30 05:00:00+3', 3, 25.6);
-- Get chunk information for testing
SELECT
    chunk_id AS "CHUNK_ID",
    hypertable_id AS "HYPERTABLE_ID",
    schema_name AS "CHUNK_SCHEMA",
    table_name AS "CHUNK_TABLE",
    schema_name || '.' || table_name AS "CHUNK_NAME",
    slices AS "CHUNK_SLICES"
FROM _timescaledb_functions.show_chunk((SELECT show_chunks('attach_test') LIMIT 1)); \gset
 CHUNK_ID | HYPERTABLE_ID |     CHUNK_SCHEMA      |   CHUNK_TABLE    |               CHUNK_NAME               |                                       CHUNK_SLICES                                       
----------+---------------+-----------------------+------------------+----------------------------------------+------------------------------------------------------------------------------------------
        1 |             1 | _timescaledb_internal | _hyper_1_1_chunk | _timescaledb_internal._hyper_1_1_chunk | {"id": [-9223372036854775808, 1073741823], "time": [1748476800000000, 1749081600000000]}

-- Successful attachment
CREATE TABLE regular_table_to_attach(id int, time timestamptz not null, device int, temp float);
CREATE INDEX regular_table_device_idx ON regular_table_to_attach (device);
ALTER TABLE regular_table_to_attach
    ADD CONSTRAINT attach_test_temp_check CHECK (temp > 0),
    ADD CONSTRAINT pre_attach_temp_check CHECK (temp < 100),
    ADD CONSTRAINT pre_attach_id_time_unique UNIQUE (id, time);
INSERT INTO regular_table_to_attach VALUES (10, '2025-07-05 13:00:00+3', 2, 27.8);
-- Attach it as a chunk
CALL attach_chunk('attach_test', 'regular_table_to_attach', '{"time": ["2025-07-01 05:00:00+3", "2025-07-07 05:00:00+3"], "id": [-9223372036854775808, 1073741823]}');
-- Verify data is accessible through the hypertable
SELECT count(*) > 0 FROM attach_test WHERE time >= '2025-07-5'::timestamptz;
 ?column? 
----------
 t

-- Check the chunk and related metadata are set up correctly
SELECT id AS "ATTACHED_CHUNK" FROM _timescaledb_catalog.chunk
WHERE hypertable_id = :'HYPERTABLE_ID' AND relid = (SELECT oid FROM pg_class WHERE relname = 'regular_table_to_attach' LIMIT 1); \gset
 ATTACHED_CHUNK 
----------------
              4

-- Verify that each constraint/index on a auto-created chunk is also present on the attached chunk
SELECT * FROM test.show_indexesp('regular_table_to_attach');
          Table          |                    Index                     |  Columns  | Expr | Unique | Primary | Exclusion | Tablespace 
-------------------------+----------------------------------------------+-----------+------+--------+---------+-----------+------------
 regular_table_to_attach | regular_table_device_idx                     | {device}  |      | f      | f       | f         | 
 regular_table_to_attach | pre_attach_id_time_unique                    | {id,time} |      | t      | f       | f         | 
 regular_table_to_attach | regular_table_to_attach_attach_test_time_idx | {time}    |      | f      | f       | f         | 

SELECT * FROM _timescaledb_catalog.dimension_slice WHERE chunk_id = :'ATTACHED_CHUNK';
 id | chunk_id | dimension_id |     range_start      |    range_end     
----+----------+--------------+----------------------+------------------
  7 |        4 |            1 |     1751335200000000 | 1751853600000000
  8 |        4 |            2 | -9223372036854775808 |       1073741823

-- Verify that the chunk is a child of the hypertable
SELECT count(*) > 0 FROM pg_inherits
WHERE inhrelid = 'regular_table_to_attach'::regclass::oid AND inhparent = 'attach_test'::regclass::oid;
 ?column? 
----------
 t

-- Verify foreign key references work
SELECT count(*) > 0 FROM pg_constraint
WHERE contype = 'f' AND confrelid = 'regular_table_to_attach'::regclass::oid;
 ?column? 
----------
 t

SELECT count(*) > 0 FROM pg_constraint
WHERE contype = 'f' AND conrelid = 'regular_table_to_attach'::regclass::oid;
 ?column? 
----------
 t

-- Verify data is routed to the correct chunk
SELECT count(*) FROM attach_test WHERE time > '2025-07-01'::timestamptz;
 count 
-------
     1

INSERT INTO attach_test VALUES (5, '2025-07-4 05:00:00+3', 2, 19.5);
SELECT count(*) FROM regular_table_to_attach;
 count 
-------
     2

-- Detach and re-attach a chunk
CALL detach_chunk(:'CHUNK_NAME');
CALL attach_chunk('attach_test', :'CHUNK_NAME', :'CHUNK_SLICES');
-- Store the new chunk id
SELECT chunk_id AS "CHUNK_ID" FROM _timescaledb_functions.show_chunk(:'CHUNK_NAME'); \gset
 CHUNK_ID 
----------
        5

-- Verify it's re-attached
SELECT count(*) > 0 FROM pg_inherits WHERE inhrelid = :'CHUNK_NAME'::regclass::oid;
 ?column? 
----------
 t

-- Verify constraints and indexes are restored
SELECT * FROM _timescaledb_catalog.dimension_slice WHERE chunk_id = :'CHUNK_ID';
 id | chunk_id | dimension_id |     range_start      |    range_end     
----+----------+--------------+----------------------+------------------
  9 |        5 |            1 |     1748476800000000 | 1749081600000000
 10 |        5 |            2 | -9223372036854775808 |       1073741823

-- Attach a chunk to another hypertable with a different dimension
CALL detach_chunk('regular_table_to_attach');
CREATE TABLE hypertable_with_different_dimension(id int, time timestamptz not null, device int, temp float);
SELECT * FROM create_hypertable('hypertable_with_different_dimension', 'time');
 hypertable_id | schema_name |             table_name              | created 
---------------+-------------+-------------------------------------+---------
             2 | public      | hypertable_with_different_dimension | t

CALL attach_chunk('hypertable_with_different_dimension', 'regular_table_to_attach', '{"time": ["2025-07-01 05:00:00+3", "2025-07-07 05:00:00+3"]}');
CALL detach_chunk('regular_table_to_attach');
CREATE TABLE not_a_hypertable(id int, time timestamptz not null, device int, temp float);
-- Error cases
\set ON_ERROR_STOP 0
CALL attach_chunk('attach_test', 'nonexistent_table', '{"time": ["2025-07-01 05:00:00+3", "2025-07-07 05:00:00+3"], "id": [-9223372036854775808, 1073741823]}');
ERROR:  relation "nonexistent_table" does not exist at character 34
CALL attach_chunk('not_a_hypertable', 'regular_table_to_attach', '{"time": ["2025-07-01 05:00:00+3", "2025-07-07 05:00:00+3"], "id": [-9223372036854775808, 1073741823]}');
ERROR:  table "not_a_hypertable" is not a hypertable
CALL attach_chunk('nonexistent_hypertable', 'regular_table_to_attach', '{"time": ["2025-07-01 05:00:00+3", "2025-07-07 05:00:00+3"], "id": [-9223372036854775808, 1073741823]}');
ERROR:  relation "nonexistent_hypertable" does not exist at character 19
CALL attach_chunk(98765, 'regular_table_to_attach', '{"time": ["2025-07-01 05:00:00+3", "2025-07-07 05:00:00+3"], "id": [-9223372036854775808, 1073741823]}');
ERROR:  relation with OID 98765 does not exist
CALL attach_chunk(0, 'regular_table_to_attach', '{"time": ["2025-07-01 05:00:00+3", "2025-07-07 05:00:00+3"], "id": [-9223372036854775808, 1073741823]}');
ERROR:  invalid hypertable relation OID
-- invalid json format
CALL attach_chunk('attach_test', 'regular_table_to_attach', '"time": ["2025-07-01 05:00:00+3", "2025-07-07 05:00:00+3"], "id": [-9223372036854775808, 1073741823]');
ERROR:  invalid input syntax for type json at character 61
-- incorrect dimension information
CALL attach_chunk('attach_test', 'regular_table_to_attach', '{"time": ["2025123-07-01 05:00:00+3", "2025-07-07 05:00:00+3"], "id": [-9223372036854775808, 1073741823]}');
ERROR:  timestamp out of range: "2025123-07-01 05:00:00+3"
CALL attach_chunk('attach_test', 'regular_table_to_attach', '{"time": ["2025-07-01 05:00:00+3", "2025-07-07 05:00:00+3"], "incorrect_key": [-9223372036854775808, 1073741823]}');
ERROR:  invalid hypercube for hypertable "attach_test"
CALL attach_chunk('attach_test', 'regular_table_to_attach', NULL);
ERROR:  invalid dimension slices argument
CALL attach_chunk('attach_test', 'regular_table_to_attach', :'CHUNK_SLICES');
ERROR:  chunk creation failed due to collision
CALL attach_chunk('attach_test', :'CHUNK_NAME', :'CHUNK_SLICES');
ERROR:  cannot attach chunk that is already a child of another table
-- Attach a chunk of another hypertable
CALL attach_chunk('hypertable_with_different_dimension', :'CHUNK_NAME', :'CHUNK_SLICES');
ERROR:  cannot attach chunk that is already a child of another table
-- Try to attach a table that's already a child of another table
CREATE TABLE parent_table(id int);
CREATE TABLE child_table(time timestamptz not null, device int, temp float) INHERITS (parent_table);
CALL attach_chunk('attach_test', 'child_table', '{"time": ["2025-07-01 05:00:00+3", "2025-07-07 05:00:00+3"], "id": [-9223372036854775808, 1073741823]}');
ERROR:  cannot attach chunk that is already a child of another table
-- Try to attach a table with incompatible types
CREATE TABLE incompatible_table(id int, time timestamptz not null, device text, temp float);
CALL attach_chunk('attach_test', 'incompatible_table', '{"time": ["2025-07-01 05:00:00+3", "2025-07-07 05:00:00+3"], "id": [-9223372036854775808, 1073741823]}');
ERROR:  child table "incompatible_table" has different type for column "device"
-- Try to attach a table with missing columns
CREATE TABLE missing_col_table(id int, time timestamptz not null);
CALL attach_chunk('attach_test', 'missing_col_table', '{"time": ["2025-07-01 05:00:00+3", "2025-07-07 05:00:00+3"], "id": [-9223372036854775808, 1073741823]}');
ERROR:  child table is missing column "device"
-- Try to attach a table with extra columns
CREATE TABLE extra_col_table(id int, time timestamptz not null, device int, temp float, extra_col int);
CALL attach_chunk('attach_test', 'extra_col_table', '{"time": ["2025-07-01 05:00:00+3", "2025-07-07 05:00:00+3"], "id": [-9223372036854775808, 1073741823]}');
ERROR:  table "extra_col_table" contains column "extra_col" not found in parent "attach_test"
-- Attach by non-owner is not allowed
set role :ROLE_1;
CALL attach_chunk('attach_test', 'regular_table_to_attach', '{"time": ["2025-07-01 05:00:00+3", "2025-07-07 05:00:00+3"], "id": [-9223372036854775808, 1073741823]}');
ERROR:  must be owner of table regular_table_to_attach
set role :ROLE_DEFAULT_PERM_USER;
CALL attach_chunk('attach_test', 'regular_table_to_attach', '{"time": ["2025-06-01 05:00:00+3", "2025-06-07 05:00:00+3"], "id": [-9223372036854775808, 1073741823]}');
ERROR:  chunk creation failed due to collision
-- Attach hypertable as a chunk
CALL attach_chunk('attach_test', 'hypertable_with_different_dimension', '{"time": ["2025-07-01 05:00:00+3", "2025-07-07 05:00:00+3"], "id": [-9223372036854775808, 1073741823]}');
ERROR:  cannot attach hypertable as a chunk
\set ON_ERROR_STOP 1
-- Test rollback behavior
SELECT count(*) AS "PRE_ROLLBACK_CHUNKS" FROM _timescaledb_catalog.chunk WHERE hypertable_id = :'HYPERTABLE_ID'; \gset
 PRE_ROLLBACK_CHUNKS 
---------------------
                   3

BEGIN;
CREATE TABLE rollback_test_table(id int, time timestamptz not null, device int, temp float);
ALTER TABLE rollback_test_table ADD CONSTRAINT attach_test_temp_check CHECK (temp > 0);
CALL attach_chunk('attach_test', 'rollback_test_table', '{"time": ["2025-07-01 05:00:00+3", "2025-07-07 05:00:00+3"], "id": [-9223372036854775808, 1073741823]}');
ROLLBACK;
SELECT count(*) = :'PRE_ROLLBACK_CHUNKS' FROM _timescaledb_catalog.chunk WHERE hypertable_id = :'HYPERTABLE_ID';
 ?column? 
----------
 t

CALL attach_chunk('attach_test', 'regular_table_to_attach', '{"time": ["2025-07-01 05:00:00", "2025-07-07 05:00:00"], "id": [-9223372036854775808, 1073741823]}');
CALL detach_chunk('regular_table_to_attach');
CALL attach_chunk('attach_test', 'regular_table_to_attach', '{"time": ["2025-07-01", "2025-07-07"], "id": [-9223372036854775808, 1073741823]}');
CALL detach_chunk('regular_table_to_attach');
CALL attach_chunk('attach_test', 'regular_table_to_attach', '{"time": ["Tue July 1 05:00:00 2025 GMT+3", "Mon July 7 05:00:00 2025 GMT+3"], "id": [-9223372036854775808, 1073741823]}');
CALL detach_chunk('regular_table_to_attach');
CALL attach_chunk('attach_test', 'regular_table_to_attach', '{"time": [1751356800000000, 1751875200000000], "id": [-9223372036854775808, 1073741823]}');
CALL detach_chunk('regular_table_to_attach');
-- Attach a table with rows violating chunk constraints
INSERT INTO regular_table_to_attach VALUES (4, '2024-07-05 13:00:00+3', 2, 27.8);
\set ON_ERROR_STOP 0
CALL attach_chunk('attach_test', 'regular_table_to_attach', '{"time": ["2025-07-01 05:00:00+3", "2025-07-07 05:00:00+3"], "id": [-9223372036854775808, 1073741823]}');
ERROR:  dimension constraint for column "time" violated by some row
\set ON_ERROR_STOP 1
-- Clean up
DROP TABLE regular_table_to_attach;
DROP TABLE attach_test_ref;
DROP TABLE attach_test;
DROP TABLE devices CASCADE;
-- Test dropping columns after detach/attach
CREATE TABLE drop_after_attach(time timestamptz NOT NULL, x int);
SELECT create_hypertable('drop_after_attach', 'time', chunk_time_interval => interval '1 day');
       create_hypertable        
--------------------------------
 (3,public,drop_after_attach,t)

INSERT INTO drop_after_attach VALUES ('2026-01-01', 1), ('2026-01-05', 1);
SELECT schema_name || '.' || table_name AS "ROUNDTRIP_CHUNK", slices AS "ROUNDTRIP_SLICES"
FROM _timescaledb_functions.show_chunk((SELECT show_chunks('drop_after_attach', older_than => '2026-01-02') LIMIT 1)); \gset
             ROUNDTRIP_CHUNK             |                ROUNDTRIP_SLICES                
-----------------------------------------+------------------------------------------------
 _timescaledb_internal._hyper_3_15_chunk | {"time": [1767225600000000, 1767312000000000]}

CALL detach_chunk(:'ROUNDTRIP_CHUNK');
CALL attach_chunk('drop_after_attach', :'ROUNDTRIP_CHUNK', :'ROUNDTRIP_SLICES');
-- The re-attached chunk inherits without any locally-defined columns.
SELECT attname, attislocal, attinhcount
FROM pg_attribute
WHERE attrelid = :'ROUNDTRIP_CHUNK'::regclass AND attnum > 0 AND NOT attisdropped
ORDER BY attnum;
 attname | attislocal | attinhcount 
---------+------------+-------------
 time    | f          |           1
 x       | f          |           1

ALTER TABLE drop_after_attach DROP COLUMN x;
-- The column is gone from the round-tripped chunk, not left orphaned.
SELECT attname, attislocal, attinhcount
FROM pg_attribute
WHERE attrelid = :'ROUNDTRIP_CHUNK'::regclass AND attname = 'x' AND NOT attisdropped;
 attname | attislocal | attinhcount 
---------+------------+-------------

DROP TABLE drop_after_attach;
-- Test dropping a constraint after detach/attach
CREATE TABLE drop_con_after_attach(time timestamptz NOT NULL, x int CHECK (x > 0));
SELECT create_hypertable('drop_con_after_attach', 'time', chunk_time_interval => interval '1 day');
         create_hypertable          
------------------------------------
 (4,public,drop_con_after_attach,t)

INSERT INTO drop_con_after_attach VALUES ('2026-01-01', 1);
SELECT schema_name || '.' || table_name AS "CON_CHUNK", slices AS "CON_SLICES"
FROM _timescaledb_functions.show_chunk((SELECT show_chunks('drop_con_after_attach') LIMIT 1)); \gset
                CON_CHUNK                |                   CON_SLICES                   
-----------------------------------------+------------------------------------------------
 _timescaledb_internal._hyper_4_18_chunk | {"time": [1767225600000000, 1767312000000000]}

CALL detach_chunk(:'CON_CHUNK');
CALL attach_chunk('drop_con_after_attach', :'CON_CHUNK', :'CON_SLICES');
-- The inherited constraint is not marked local; the dimension constraint stays local.
SELECT conname, contype, conislocal, coninhcount
FROM pg_constraint
WHERE conrelid = :'CON_CHUNK'::regclass
ORDER BY conname;
            conname            | contype | conislocal | coninhcount 
-------------------------------+---------+------------+-------------
 constraint_30                 | c       | t          |           0
 drop_con_after_attach_x_check | c       | f          |           1

ALTER TABLE drop_con_after_attach DROP CONSTRAINT drop_con_after_attach_x_check;
-- The dropped constraint is gone from the round-tripped chunk.
SELECT count(*) AS leftover_check
FROM pg_constraint
WHERE conrelid = :'CON_CHUNK'::regclass AND conname = 'drop_con_after_attach_x_check';
 leftover_check 
----------------
              0

DROP TABLE drop_con_after_attach;
-- Test attaching a foreign table as an OSM chunk
-- A dummy server is enough; the foreign table is never queried by attach.
\c :TEST_DBNAME :ROLE_SUPERUSER
CREATE EXTENSION postgres_fdw;
CREATE SERVER attach_chunk_fdw FOREIGN DATA WRAPPER postgres_fdw;
CREATE TABLE osm_ht(time timestamptz NOT NULL, x int CHECK (x > 0));
SELECT create_hypertable('osm_ht', 'time', chunk_time_interval => interval '1 day');
  create_hypertable  
---------------------
 (5,public,osm_ht,t)

CREATE FOREIGN TABLE osm_ft(time timestamptz NOT NULL, x int) SERVER attach_chunk_fdw;
SELECT _timescaledb_functions.attach_osm_table_chunk('osm_ht', 'osm_ft');
 attach_osm_table_chunk 
------------------------
 t

-- The foreign chunk inherits its columns without any left marked local.
SELECT attname, attislocal, attinhcount
FROM pg_attribute
WHERE attrelid = 'osm_ft'::regclass AND attnum > 0 AND NOT attisdropped
ORDER BY attnum;
 attname | attislocal | attinhcount 
---------+------------+-------------
 time    | f          |           1
 x       | f          |           1

-- The CHECK constraint cloned from the hypertable is inherited, not local.
SELECT conname, contype, conislocal, coninhcount
FROM pg_constraint
WHERE conrelid = 'osm_ft'::regclass AND contype = 'c'
ORDER BY conname;
    conname     | contype | conislocal | coninhcount 
----------------+---------+------------+-------------
 osm_ht_x_check | c       | f          |           1

ALTER TABLE osm_ht DROP COLUMN x;
-- Dropping the column on the hypertable propagates to the foreign chunk.
SELECT attname
FROM pg_attribute
WHERE attrelid = 'osm_ft'::regclass AND attname = 'x' AND NOT attisdropped;
 attname 
---------

-- Dropping the hypertable also drops the attached foreign chunk.
DROP TABLE osm_ht;
DROP SERVER attach_chunk_fdw;
DROP EXTENSION postgres_fdw;