direct_compress_insert.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.
CREATE TABLE metrics (time TIMESTAMPTZ NOT NULL, device TEXT, value float) WITH (tsdb.hypertable, tsdb.orderby='time');
NOTICE:  using column "time" as partitioning column
-- first try without the GUCs
BEGIN;
INSERT INTO metrics SELECT '2025-01-01'::timestamptz + (i || ' hour')::interval, 'd1', i::float FROM generate_series(0,100) i;
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, SUMMARY OFF, TIMING OFF) SELECT * FROM metrics;
--- QUERY PLAN ---
 Append (actual rows=101.00 loops=1)
   ->  Seq Scan on _hyper_1_1_chunk (actual rows=16.00 loops=1)
   ->  Seq Scan on _hyper_1_2_chunk (actual rows=85.00 loops=1)

-- should have no status aka normal uncompressed chunk
SELECT DISTINCT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics') chunk;
 chunk_status_text 
-------------------
 {}

ROLLBACK;
SET timescaledb.enable_direct_compress_insert = true;
SET timescaledb.enable_direct_compress_insert_sort_batches = true;
SET timescaledb.enable_direct_compress_insert_client_sorted = false;
-- EXPLAIN with too small batch
EXPLAIN (BUFFERS OFF, COSTS OFF, SUMMARY OFF, TIMING OFF) INSERT INTO metrics SELECT '2025-01-01'::timestamptz, 'd1', i::float FROM generate_series(0,5) i;
WARNING:  disabling direct compress because of too small batch size
--- QUERY PLAN ---
 Custom Scan (ModifyHypertable)
   Direct Compress: false
   ->  Insert on metrics
         ->  Function Scan on generate_series i

-- EXPLAIN with large enough batch
EXPLAIN (BUFFERS OFF, COSTS OFF, SUMMARY OFF, TIMING OFF) INSERT INTO metrics SELECT '2025-01-01'::timestamptz, 'd1', i::float FROM generate_series(0,500) i;
--- QUERY PLAN ---
 Custom Scan (ModifyHypertable)
   Direct Compress: true
   ->  Insert on metrics
         ->  Function Scan on generate_series i

-- EXPLAIN with debug GUC disabled
SET timescaledb.enable_optimizations TO OFF;
EXPLAIN (BUFFERS OFF, COSTS OFF, SUMMARY OFF, TIMING OFF) INSERT INTO metrics SELECT '2025-01-01'::timestamptz, 'd1', i::float FROM generate_series(0,500) i;
--- QUERY PLAN ---
 Custom Scan (ModifyHypertable)
   Direct Compress: false
   ->  Insert on metrics
         ->  Function Scan on generate_series i

RESET timescaledb.enable_optimizations;
-- simple test with compressed insert enabled
BEGIN;
INSERT INTO metrics SELECT '2025-01-01'::timestamptz + (i || ' minute')::interval, 'd1', i::float FROM generate_series(0,3000) i;
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, SUMMARY OFF, TIMING OFF) SELECT * FROM metrics;
--- QUERY PLAN ---
 Append (actual rows=3001.00 loops=1)
   ->  Custom Scan (ColumnarScan) on _hyper_1_3_chunk (actual rows=960.00 loops=1)
         ->  Seq Scan on _hyper_1_3_chunk_compressed (actual rows=1.00 loops=1)
   ->  Custom Scan (ColumnarScan) on _hyper_1_4_chunk (actual rows=2041.00 loops=1)
         ->  Seq Scan on _hyper_1_4_chunk_compressed (actual rows=3.00 loops=1)

SELECT first(time,rn), last(time,rn) FROM (SELECT ROW_NUMBER() OVER () as rn, time FROM metrics) sub;
            first             |             last             
------------------------------+------------------------------
 Wed Jan 01 00:00:00 2025 PST | Fri Jan 03 02:00:00 2025 PST

-- since the chunks are new status should be COMPRESSED, UNORDERED
SELECT DISTINCT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics') chunk;
   chunk_status_text    
------------------------
 {COMPRESSED,UNORDERED}

ROLLBACK;
-- simple test with compressed insert enabled and reversed order
BEGIN;
INSERT INTO metrics SELECT '2025-01-01'::timestamptz - (i || ' minute')::interval, 'd1', i::float FROM generate_series(0,3000) i;
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, SUMMARY OFF, TIMING OFF) SELECT * FROM metrics;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _hyper_1_5_chunk (actual rows=3001.00 loops=1)
   ->  Seq Scan on _hyper_1_5_chunk_compressed (actual rows=4.00 loops=1)

SELECT first(time,rn), last(time,rn) FROM (SELECT ROW_NUMBER() OVER () as rn, time FROM metrics) sub;
            first             |             last             
------------------------------+------------------------------
 Sun Dec 29 22:00:00 2024 PST | Wed Jan 01 00:00:00 2025 PST

-- since the chunks are new status should be COMPRESSED, UNORDERED
SELECT DISTINCT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics') chunk;
   chunk_status_text    
------------------------
 {COMPRESSED,UNORDERED}

ROLLBACK;
SET timescaledb.enable_direct_compress_insert_sort_batches = false;
-- simple test with compressed insert enabled and without batch sorting
BEGIN;
INSERT INTO metrics SELECT '2025-01-01'::timestamptz + (i || ' minute')::interval, 'd1', i::float FROM generate_series(0,3000) i;
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, SUMMARY OFF, TIMING OFF) SELECT * FROM metrics;
--- QUERY PLAN ---
 Append (actual rows=3001.00 loops=1)
   ->  Custom Scan (ColumnarScan) on _hyper_1_6_chunk (actual rows=960.00 loops=1)
         ->  Seq Scan on _hyper_1_6_chunk_compressed (actual rows=1.00 loops=1)
   ->  Custom Scan (ColumnarScan) on _hyper_1_7_chunk (actual rows=2041.00 loops=1)
         ->  Seq Scan on _hyper_1_7_chunk_compressed (actual rows=3.00 loops=1)

SELECT first(time,rn), last(time,rn) FROM (SELECT ROW_NUMBER() OVER () as rn, time FROM metrics) sub;
            first             |             last             
------------------------------+------------------------------
 Wed Jan 01 00:00:00 2025 PST | Fri Jan 03 02:00:00 2025 PST

-- since the chunks are new status should be COMPRESSED, UNORDERED
SELECT DISTINCT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics') chunk;
   chunk_status_text    
------------------------
 {COMPRESSED,UNORDERED}

ROLLBACK;
-- simple test with compressed insert enabled and reversed order and no batch sorting
BEGIN;
INSERT INTO metrics SELECT '2025-01-01'::timestamptz - (i || ' minute')::interval, 'd1', i::float FROM generate_series(0,3000) i;
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, SUMMARY OFF, TIMING OFF) SELECT * FROM metrics;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _hyper_1_8_chunk (actual rows=3001.00 loops=1)
   ->  Seq Scan on _hyper_1_8_chunk_compressed (actual rows=4.00 loops=1)

SELECT first(time,rn), last(time,rn) FROM (SELECT ROW_NUMBER() OVER () as rn, time FROM metrics) sub;
            first             |             last             
------------------------------+------------------------------
 Wed Jan 01 00:00:00 2025 PST | Sun Dec 29 22:00:00 2024 PST

-- since the chunks are new status should be COMPRESSED, UNORDERED
SELECT DISTINCT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics') chunk;
   chunk_status_text    
------------------------
 {COMPRESSED,UNORDERED}

ROLLBACK;
-- test compressing into uncompressed chunk
RESET timescaledb.enable_direct_compress_insert;
RESET timescaledb.enable_direct_compress_insert_sort_batches;
RESET timescaledb.enable_direct_compress_insert_client_sorted;
BEGIN;
INSERT INTO metrics SELECT '2025-01-01'::timestamptz + (i || ' minute')::interval, 'd1', i::float FROM generate_series(0,3000) i;
SET timescaledb.enable_direct_compress_insert = true;
SET timescaledb.enable_direct_compress_insert_client_sorted = true;
INSERT INTO metrics SELECT '2025-01-01'::timestamptz + (i || ' minute')::interval, 'd1', i::float FROM generate_series(0,3000) i;
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, SUMMARY OFF, TIMING OFF) SELECT * FROM metrics;
--- QUERY PLAN ---
 Append (actual rows=6002.00 loops=1)
   ->  Custom Scan (ColumnarScan) on _hyper_1_9_chunk (actual rows=960.00 loops=1)
         ->  Seq Scan on _hyper_1_9_chunk_compressed (actual rows=1.00 loops=1)
   ->  Seq Scan on _hyper_1_9_chunk (actual rows=960.00 loops=1)
   ->  Custom Scan (ColumnarScan) on _hyper_1_10_chunk (actual rows=2041.00 loops=1)
         ->  Seq Scan on _hyper_1_10_chunk_compressed (actual rows=3.00 loops=1)
   ->  Seq Scan on _hyper_1_10_chunk (actual rows=2041.00 loops=1)

-- since the chunks are new status should be COMPRESSED, PARTIAL
SELECT DISTINCT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics') chunk;
  chunk_status_text   
----------------------
 {COMPRESSED,PARTIAL}

ROLLBACK;
-- simple test with compressed insert enabled and reversed order
BEGIN;
SET timescaledb.enable_direct_compress_insert = true;
SET timescaledb.enable_direct_compress_insert_client_sorted = true;
INSERT INTO metrics SELECT '2025-01-01'::timestamptz - (i || ' minute')::interval, 'd1', i::float FROM generate_series(0,3000) i;
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, SUMMARY OFF, TIMING OFF) SELECT * FROM metrics;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _hyper_1_11_chunk (actual rows=3001.00 loops=1)
   ->  Seq Scan on _hyper_1_11_chunk_compressed (actual rows=4.00 loops=1)

-- since the chunks are new status should be COMPRESSED
SELECT DISTINCT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics') chunk;
 chunk_status_text 
-------------------
 {COMPRESSED}

ROLLBACK;
-- simple test with compressed insert enabled and no presorted
BEGIN;
SET timescaledb.enable_direct_compress_insert = true;
SET timescaledb.enable_direct_compress_insert_client_sorted = false;
INSERT INTO metrics SELECT '2025-01-01'::timestamptz - (i || ' minute')::interval, 'd1', i::float FROM generate_series(0,3000) i;
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, SUMMARY OFF, TIMING OFF) SELECT * FROM metrics;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _hyper_1_12_chunk (actual rows=3001.00 loops=1)
   ->  Seq Scan on _hyper_1_12_chunk_compressed (actual rows=4.00 loops=1)

-- since the chunks are new status should be COMPRESSED, UNORDERED
SELECT DISTINCT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics') chunk;
   chunk_status_text    
------------------------
 {COMPRESSED,UNORDERED}

ROLLBACK;
-- simple test with compressed insert enabled and no presorted and with uncompressed data
BEGIN;
SET timescaledb.enable_direct_compress_insert = true;
SET timescaledb.enable_direct_compress_insert_client_sorted = false;
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;
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, SUMMARY OFF, TIMING OFF) SELECT * FROM metrics;
--- QUERY PLAN ---
 Append (actual rows=3002.00 loops=1)
   ->  Custom Scan (ColumnarScan) on _hyper_1_13_chunk (actual rows=3001.00 loops=1)
         ->  Seq Scan on _hyper_1_13_chunk_compressed (actual rows=4.00 loops=1)
   ->  Seq Scan on _hyper_1_13_chunk (actual rows=1.00 loops=1)

-- since the chunks are new status should be COMPRESSED, UNORDERED, PARTIAL
SELECT DISTINCT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics') chunk;
       chunk_status_text        
--------------------------------
 {COMPRESSED,UNORDERED,PARTIAL}

ROLLBACK;
-- simple test with compressed insert enabled and no presorted with partial and compressed chunks
BEGIN;
SET timescaledb.enable_direct_compress_insert = true;
SET timescaledb.enable_direct_compress_insert_client_sorted = false;
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;
INSERT INTO metrics SELECT '2025-01-02'::timestamptz + (i || ' minute')::interval, 'd1', i::float FROM generate_series(0,3000) i;
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, SUMMARY OFF, TIMING OFF) SELECT * FROM metrics;
--- QUERY PLAN ---
 Append (actual rows=6003.00 loops=1)
   ->  Custom Scan (ColumnarScan) on _hyper_1_14_chunk (actual rows=960.00 loops=1)
         ->  Seq Scan on _hyper_1_14_chunk_compressed (actual rows=1.00 loops=1)
   ->  Seq Scan on _hyper_1_14_chunk (actual rows=1.00 loops=1)
   ->  Custom Scan (ColumnarScan) on _hyper_1_15_chunk (actual rows=5042.00 loops=1)
         ->  Seq Scan on _hyper_1_15_chunk_compressed (actual rows=7.00 loops=1)

SELECT first(time,rn), last(time,rn) FROM (SELECT ROW_NUMBER() OVER () as rn, time FROM metrics) sub;
            first             |             last             
------------------------------+------------------------------
 Wed Jan 01 00:00:00 2025 PST | Sat Jan 04 02:00:00 2025 PST

SELECT compress_relid::text AS "COMPRESSED_CHUNK" FROM _timescaledb_catalog.compression_settings WHERE compress_relid IS NOT NULL order by 1 desc limit 1 \gset
-- should see overlapping batches
select _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1 from :COMPRESSED_CHUNK order by 2;
 _ts_meta_count |        _ts_meta_min_1        |        _ts_meta_max_1        
----------------+------------------------------+------------------------------
           1000 | Wed Jan 01 16:00:00 2025 PST | Thu Jan 02 08:39:00 2025 PST
           1000 | Thu Jan 02 00:00:00 2025 PST | Thu Jan 02 16:39:00 2025 PST
           1000 | Thu Jan 02 08:40:00 2025 PST | Fri Jan 03 01:19:00 2025 PST
           1000 | Thu Jan 02 16:40:00 2025 PST | Fri Jan 03 09:19:00 2025 PST
             41 | Fri Jan 03 01:20:00 2025 PST | Fri Jan 03 02:00:00 2025 PST
           1000 | Fri Jan 03 09:20:00 2025 PST | Sat Jan 04 01:59:00 2025 PST
              1 | Sat Jan 04 02:00:00 2025 PST | Sat Jan 04 02:00:00 2025 PST

-- since the chunks are new status should be COMPRESSED, UNORDERED, PARTIAL and COMPRESSED, UNORDERED
SELECT DISTINCT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics') chunk order by 1;
       chunk_status_text        
--------------------------------
 {COMPRESSED,UNORDERED}
 {COMPRESSED,UNORDERED,PARTIAL}

ROLLBACK;
-- simple test with compressed insert enabled and presorted with partial and compressed chunks
BEGIN;
SET timescaledb.enable_direct_compress_insert = true;
SET timescaledb.enable_direct_compress_insert_client_sorted = true;
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;
INSERT INTO metrics SELECT '2025-01-05'::timestamptz + (i || ' minute')::interval, 'd1', i::float FROM generate_series(0,3000) i;
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, SUMMARY OFF, TIMING OFF) SELECT * FROM metrics;
--- QUERY PLAN ---
 Append (actual rows=6003.00 loops=1)
   ->  Custom Scan (ColumnarScan) on _hyper_1_16_chunk (actual rows=960.00 loops=1)
         ->  Seq Scan on _hyper_1_16_chunk_compressed (actual rows=1.00 loops=1)
   ->  Seq Scan on _hyper_1_16_chunk (actual rows=1.00 loops=1)
   ->  Custom Scan (ColumnarScan) on _hyper_1_17_chunk (actual rows=5042.00 loops=1)
         ->  Seq Scan on _hyper_1_17_chunk_compressed (actual rows=7.00 loops=1)

SELECT first(time,rn), last(time,rn) FROM (SELECT ROW_NUMBER() OVER () as rn, time FROM metrics) sub;
            first             |             last             
------------------------------+------------------------------
 Wed Jan 01 00:00:00 2025 PST | Tue Jan 07 02:00:00 2025 PST

SELECT compress_relid::text AS "COMPRESSED_CHUNK" FROM _timescaledb_catalog.compression_settings WHERE compress_relid IS NOT NULL order by 1 desc limit 1 \gset
-- should not see overlapping batches
select _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1 from :COMPRESSED_CHUNK order by 2;
 _ts_meta_count |        _ts_meta_min_1        |        _ts_meta_max_1        
----------------+------------------------------+------------------------------
           1000 | Wed Jan 01 16:00:00 2025 PST | Thu Jan 02 08:39:00 2025 PST
           1000 | Thu Jan 02 08:40:00 2025 PST | Fri Jan 03 01:19:00 2025 PST
             41 | Fri Jan 03 01:20:00 2025 PST | Fri Jan 03 02:00:00 2025 PST
           1000 | Sun Jan 05 00:00:00 2025 PST | Sun Jan 05 16:39:00 2025 PST
           1000 | Sun Jan 05 16:40:00 2025 PST | Mon Jan 06 09:19:00 2025 PST
           1000 | Mon Jan 06 09:20:00 2025 PST | Tue Jan 07 01:59:00 2025 PST
              1 | Tue Jan 07 02:00:00 2025 PST | Tue Jan 07 02:00:00 2025 PST

-- since the chunks are new status should be COMPRESSED, UNORDERED, PARTIAL and COMPRESSED, UNORDERED
SELECT DISTINCT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics') chunk order by 1;
  chunk_status_text   
----------------------
 {COMPRESSED}
 {COMPRESSED,PARTIAL}

ROLLBACK;
-- test with segmentby
BEGIN;
ALTER TABLE metrics SET (tsdb.segmentby = 'device');
NOTICE:  updated compression settings will only apply to future compressions
SET timescaledb.enable_direct_compress_insert = true;
SET timescaledb.enable_direct_compress_insert_client_sorted = true;
INSERT INTO metrics SELECT '2025-01-01'::timestamptz - (i || ' minute')::interval, floor(i), i::float FROM generate_series(0.0,9.8,0.2) i;
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, SUMMARY OFF, TIMING OFF) SELECT * FROM metrics;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _hyper_1_18_chunk (actual rows=50.00 loops=1)
   ->  Seq Scan on _hyper_1_18_chunk_compressed (actual rows=10.00 loops=1)

SELECT compress_relid::text AS "COMPRESSED_CHUNK" FROM _timescaledb_catalog.compression_settings WHERE compress_relid IS NOT NULL \gset
-- should have 10 batches
SELECT count(*) FROM :COMPRESSED_CHUNK;
 count 
-------
    10

-- since the chunks are new status should be COMPRESSED
SELECT DISTINCT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics') chunk;
 chunk_status_text 
-------------------
 {COMPRESSED}

ROLLBACK;
-- segmentby with overlapping batches
BEGIN;
ALTER TABLE metrics SET (tsdb.segmentby = 'device');
NOTICE:  updated compression settings will only apply to future compressions
SET timescaledb.enable_direct_compress_insert = true;
SET timescaledb.enable_direct_compress_insert_client_sorted = false;
INSERT INTO metrics SELECT '2025-01-01'::timestamptz + (i || ' minute')::interval, 'd'||i%2, i::float FROM generate_series(0,3000) i;
INSERT INTO metrics SELECT '2025-01-02'::timestamptz + (i || ' minute')::interval, 'd'||i%2, i::float FROM generate_series(0,3000) i;
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, SUMMARY OFF, TIMING OFF) SELECT * FROM metrics;
--- QUERY PLAN ---
 Append (actual rows=6002.00 loops=1)
   ->  Custom Scan (ColumnarScan) on _hyper_1_19_chunk (actual rows=960.00 loops=1)
         ->  Seq Scan on _hyper_1_19_chunk_compressed (actual rows=2.00 loops=1)
   ->  Custom Scan (ColumnarScan) on _hyper_1_20_chunk (actual rows=5042.00 loops=1)
         ->  Seq Scan on _hyper_1_20_chunk_compressed (actual rows=8.00 loops=1)

SELECT compress_relid::text AS "COMPRESSED_CHUNK" FROM _timescaledb_catalog.compression_settings WHERE compress_relid IS NOT NULL order by 1 desc limit 1 \gset
-- should see overlapping batches per device
select _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1, device from :COMPRESSED_CHUNK order by 4, 2;
 _ts_meta_count |        _ts_meta_min_1        |        _ts_meta_max_1        | device 
----------------+------------------------------+------------------------------+--------
           1000 | Wed Jan 01 16:00:00 2025 PST | Fri Jan 03 01:18:00 2025 PST | d0
           1000 | Thu Jan 02 00:00:00 2025 PST | Fri Jan 03 09:18:00 2025 PST | d0
             21 | Fri Jan 03 01:20:00 2025 PST | Fri Jan 03 02:00:00 2025 PST | d0
            501 | Fri Jan 03 09:20:00 2025 PST | Sat Jan 04 02:00:00 2025 PST | d0
           1000 | Wed Jan 01 16:01:00 2025 PST | Fri Jan 03 01:19:00 2025 PST | d1
           1000 | Thu Jan 02 00:01:00 2025 PST | Fri Jan 03 09:19:00 2025 PST | d1
             20 | Fri Jan 03 01:21:00 2025 PST | Fri Jan 03 01:59:00 2025 PST | d1
            500 | Fri Jan 03 09:21:00 2025 PST | Sat Jan 04 01:59:00 2025 PST | d1

-- since the chunks are new status should be COMPRESSED, UNORDERED
SELECT DISTINCT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics') chunk;
   chunk_status_text    
------------------------
 {COMPRESSED,UNORDERED}

ROLLBACK;
-- multikey orderby
BEGIN;
ALTER TABLE metrics SET (tsdb.orderby = 'device desc,time');
NOTICE:  updated compression settings will only apply to future compressions
SET timescaledb.enable_direct_compress_insert = true;
SET timescaledb.enable_direct_compress_insert_client_sorted = false;
INSERT INTO metrics SELECT '2025-01-01'::timestamptz + (i || ' minute')::interval, 'd'||i%3, i::float FROM generate_series(0,3000) i;
INSERT INTO metrics SELECT '2025-01-02'::timestamptz - (i || ' minute')::interval, 'd'||i%3, i::float FROM generate_series(0,3000) i;
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, SUMMARY OFF, TIMING OFF) SELECT * FROM metrics;
--- QUERY PLAN ---
 Append (actual rows=6002.00 loops=1)
   ->  Custom Scan (ColumnarScan) on _hyper_1_21_chunk (actual rows=3480.00 loops=1)
         ->  Seq Scan on _hyper_1_21_chunk_compressed (actual rows=4.00 loops=1)
   ->  Custom Scan (ColumnarScan) on _hyper_1_22_chunk (actual rows=2522.00 loops=1)
         ->  Seq Scan on _hyper_1_22_chunk_compressed (actual rows=4.00 loops=1)

SELECT compress_relid::text AS "COMPRESSED_CHUNK" FROM _timescaledb_catalog.compression_settings WHERE compress_relid IS NOT NULL order by 1 limit 1 \gset
-- should see overlapping batches
select _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_min_2, _ts_meta_max_2 from :COMPRESSED_CHUNK order by 2, 4;
 _ts_meta_count | _ts_meta_min_1 | _ts_meta_max_1 |        _ts_meta_min_2        |        _ts_meta_max_2        
----------------+----------------+----------------+------------------------------+------------------------------
           1000 | d0             | d1             | Mon Dec 30 22:00:00 2024 PST | Wed Jan 01 15:59:00 2025 PST
            520 | d0             | d0             | Tue Dec 31 14:00:00 2024 PST | Wed Jan 01 15:57:00 2025 PST
            960 | d0             | d2             | Wed Jan 01 00:00:00 2025 PST | Wed Jan 01 15:59:00 2025 PST
           1000 | d1             | d2             | Mon Dec 30 22:01:00 2024 PST | Wed Jan 01 15:58:00 2025 PST

-- since the chunks are new status should be COMPRESSED, UNORDERED
SELECT DISTINCT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics') chunk;
   chunk_status_text    
------------------------
 {COMPRESSED,UNORDERED}

ROLLBACK;
-- test unique constraints prevent direct compress
BEGIN;
SET timescaledb.enable_direct_compress_insert = true;
ALTER TABLE metrics ADD CONSTRAINT unique_time_device UNIQUE (time, device);
INSERT INTO metrics SELECT '2025-01-01'::timestamptz + (i || ' minute')::interval, 'd1', i::float FROM generate_series(0,100) i;
WARNING:  disabling direct compress because the destination table has unique constraints
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, SUMMARY OFF, TIMING OFF) SELECT * FROM metrics;
--- QUERY PLAN ---
 Seq Scan on _hyper_1_23_chunk (actual rows=101.00 loops=1)

SELECT DISTINCT status FROM _timescaledb_catalog.chunk WHERE relid IN (SELECT relid FROM _timescaledb_catalog.compression_settings WHERE compress_relid IS NOT NULL);
 status 
--------

ROLLBACK;
-- test triggers prevent direct compress
BEGIN;
SET timescaledb.enable_direct_compress_insert = true;
CREATE OR REPLACE FUNCTION test_trigger() RETURNS TRIGGER AS $$ BEGIN RETURN NEW; END; $$ LANGUAGE plpgsql;
CREATE TRIGGER metrics_trigger BEFORE INSERT OR UPDATE ON metrics FOR EACH ROW EXECUTE FUNCTION test_trigger();
INSERT INTO metrics SELECT '2025-01-01'::timestamptz + (i || ' minute')::interval, 'd1', i::float FROM generate_series(0,100) i;
WARNING:  disabling direct compress because the destination table has triggers
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, SUMMARY OFF, TIMING OFF) SELECT * FROM metrics;
--- QUERY PLAN ---
 Seq Scan on _hyper_1_24_chunk (actual rows=101.00 loops=1)

SELECT DISTINCT status FROM _timescaledb_catalog.chunk WHERE relid IN (SELECT relid FROM _timescaledb_catalog.compression_settings WHERE compress_relid IS NOT NULL);
 status 
--------

ROLLBACK;
-- test caggs with direct compress
BEGIN;
SET timescaledb.enable_direct_compress_insert = true;
CREATE MATERIALIZED VIEW metrics_cagg WITH (tsdb.continuous) AS SELECT time_bucket('1 hour', time) AS bucket, device, avg(value) AS avg_value FROM metrics GROUP BY bucket, device WITH NO DATA;
INSERT INTO metrics SELECT '2025-01-01'::timestamptz + (i || ' minute')::interval, 'd1', i::float FROM generate_series(0,100) i;
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, SUMMARY OFF, TIMING OFF) SELECT * FROM metrics;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _hyper_1_25_chunk (actual rows=101.00 loops=1)
   ->  Seq Scan on _hyper_1_25_chunk_compressed (actual rows=1.00 loops=1)

SELECT DISTINCT status FROM _timescaledb_catalog.chunk WHERE relid IN (SELECT relid FROM _timescaledb_catalog.compression_settings WHERE compress_relid IS NOT NULL);
 status 
--------
      3

ROLLBACK;
-- cagg + direct compress where the cagg time column is segmentby.
-- segmentby columns don't get _ts_meta_min/max columns, so no MINMAX builder
-- is created for them; the flush path must still derive an invalidation range
-- from the segmentby value rather than crashing.
BEGIN;
CREATE TABLE metrics_segmentby_time (time TIMESTAMPTZ NOT NULL, device TEXT, value float)
    WITH (tsdb.hypertable);
NOTICE:  using column "time" as partitioning column
CREATE MATERIALIZED VIEW metrics_segmentby_time_cagg WITH (tsdb.continuous) AS
    SELECT time_bucket('1 hour', time) AS bucket, device, avg(value) AS avg_value
    FROM metrics_segmentby_time GROUP BY bucket, device WITH NO DATA;
ALTER TABLE metrics_segmentby_time SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'time'
);
NOTICE:  updated compression settings will only apply to future compressions
INSERT INTO metrics_segmentby_time
SELECT '2025-01-01'::timestamptz + (i * interval '5 minutes'), 'd1', random()
FROM generate_series(1, 50) i;
SET timescaledb.enable_direct_compress_insert = true;
INSERT INTO metrics_segmentby_time
SELECT '2025-02-01'::timestamptz + (i * interval '1 second'), 'd1', random()
FROM generate_series(1, 5000) i;
-- The new chunk should be in COMPRESSED state, confirming the flush path ran.
SELECT _timescaledb_functions.chunk_status_text(chunk)
  FROM show_chunks('metrics_segmentby_time') chunk
 WHERE chunk::text LIKE '%hyper_%'
 ORDER BY chunk::text;
   chunk_status_text    
------------------------
 {}
 {COMPRESSED,UNORDERED}

ROLLBACK;
-- test chunk status handling
CREATE TABLE metrics_status(time timestamptz) WITH (tsdb.hypertable,tsdb.partition_column='time');
INSERT INTO metrics_status SELECT '2025-01-01';
-- normal insert should result in chunk status 0
SELECT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics_status') chunk;
 chunk_status_text 
-------------------
 {}

SET timescaledb.enable_direct_compress_insert = true;
BEGIN;
INSERT INTO metrics_status SELECT '2025-01-01' FROM generate_series(1,9);
WARNING:  disabling direct compress because of too small batch size
-- small insert batches should not result in compressed chunk
SELECT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics_status') chunk;
 chunk_status_text 
-------------------
 {}

ROLLBACK;
BEGIN;
INSERT INTO metrics_status SELECT '2025-01-01' FROM generate_series(1,10);
-- status should be COMPRESSED, UNORDERED, PARTIAL since we have more than 10 rows in the chunk
SELECT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics_status') chunk;
       chunk_status_text        
--------------------------------
 {COMPRESSED,UNORDERED,PARTIAL}

ROLLBACK;
BEGIN;
-- compressed sorted copy into uncompressed chunk should result in chunk status 9 (compressed,partial)
SET timescaledb.enable_direct_compress_insert = true;
SET timescaledb.enable_direct_compress_insert_client_sorted = true;
INSERT INTO metrics_status SELECT '2025-01-01'::timestamptz + (i || ' minute')::interval FROM generate_series(0,100) i;
-- status should be COMPRESSED, PARTIAL
SELECT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics_status') chunk;
  chunk_status_text   
----------------------
 {COMPRESSED,PARTIAL}

ROLLBACK;
TRUNCATE metrics_status;
BEGIN;
-- compressed insert into new chunk should result in chunk status 3 (compressed,unordered)
SET timescaledb.enable_direct_compress_insert = true;
SET timescaledb.enable_direct_compress_insert_client_sorted = false;
INSERT INTO metrics_status SELECT '2025-01-01'::timestamptz + (i || ' minute')::interval FROM generate_series(0,100) i;
-- status should be COMPRESSED, UNORDERED
SELECT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics_status') chunk;
   chunk_status_text    
------------------------
 {COMPRESSED,UNORDERED}

ROLLBACK;
BEGIN;
-- compressed sorted copy into new chunk should result in chunk status 1 (compressed)
SET timescaledb.enable_direct_compress_insert = true;
SET timescaledb.enable_direct_compress_insert_client_sorted = true;
INSERT INTO metrics_status SELECT '2025-01-01'::timestamptz + (i || ' minute')::interval FROM generate_series(0,100) i;
-- status should be COMPRESSED
SELECT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics_status') chunk;
 chunk_status_text 
-------------------
 {COMPRESSED}

ROLLBACK;
SET timescaledb.enable_direct_compress_insert = false;
SET timescaledb.enable_direct_compress_insert_client_sorted = false;
INSERT INTO metrics_status SELECT '2025-01-01';
-- no status aka normal uncompressed chunk
SELECT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics_status') chunk;
 chunk_status_text 
-------------------
 {}

SELECT compress_chunk(show_chunks('metrics_status'));
             compress_chunk              
-----------------------------------------
 _timescaledb_internal._hyper_5_31_chunk

-- status should be COMPRESSED
SELECT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics_status') chunk;
 chunk_status_text 
-------------------
 {COMPRESSED}

BEGIN;
-- compressed insert into fully compressed chunk should result in chunk status 3 (compressed,unordered)
SET timescaledb.enable_direct_compress_insert = true;
SET timescaledb.enable_direct_compress_insert_client_sorted = false;
INSERT INTO metrics_status SELECT '2025-01-01'::timestamptz + (i || ' minute')::interval FROM generate_series(0,100) i;
-- status should be COMPRESSED, UNORDERED
SELECT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics_status') chunk;
   chunk_status_text    
------------------------
 {COMPRESSED,UNORDERED}

ROLLBACK;
BEGIN;
-- compressed insert new chunk should result in chunk status 1 (compressed)
SET timescaledb.enable_direct_compress_insert = true;
SET timescaledb.enable_direct_compress_insert_client_sorted = true;
INSERT INTO metrics_status SELECT '2025-01-01'::timestamptz + (i || ' minute')::interval FROM generate_series(0,100) i;
-- status should be COMPRESSED
SELECT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics_status') chunk;
 chunk_status_text 
-------------------
 {COMPRESSED}

ROLLBACK;
-- test direct compress into chunk directly
CREATE TABLE metrics_chunk(time timestamptz) WITH (tsdb.hypertable,tsdb.partition_column='time');
SET timescaledb.enable_direct_compress_insert = true;
-- create uncompressed chunk
INSERT INTO metrics_chunk SELECT '2025-01-01';
WARNING:  disabling direct compress because of too small batch size
-- status should be normal uncompressed chunk since it was single tuple insert
SELECT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics_chunk') chunk;
 chunk_status_text 
-------------------
 {}

SELECT show_chunks('metrics_chunk') AS "CHUNK" \gset
EXPLAIN (costs off,summary off,timing off) INSERT INTO :CHUNK SELECT '2025-01-01'::timestamptz + (i || ' minute')::interval FROM generate_series(0,100) i;
--- QUERY PLAN ---
 Custom Scan (ModifyHypertable)
   Direct Compress: true
   ->  Insert on _hyper_6_32_chunk
         ->  Function Scan on generate_series i

BEGIN;
INSERT INTO :CHUNK SELECT '2025-01-01'::timestamptz + (i || ' minute')::interval FROM generate_series(0,100) i;
-- status should be COMPRESSED, UNORDERED, PARTIAL
SELECT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics_chunk') chunk;
       chunk_status_text        
--------------------------------
 {COMPRESSED,UNORDERED,PARTIAL}

-- delete should propagate to compressed chunk
SELECT count(*) FROM :CHUNK;
 count 
-------
   102

EXPLAIN (analyze,buffers off,costs off,summary off,timing off) DELETE FROM :CHUNK WHERE time > '2025-01-01'::timestamptz;
--- QUERY PLAN ---
 Custom Scan (ModifyHypertable) (actual rows=0.00 loops=1)
   Batches scanned: 1
   Batches decompressed: 1
   Tuples decompressed: 101
   ->  Delete on _hyper_6_32_chunk (actual rows=0.00 loops=1)
         ->  Index Scan using _hyper_6_32_chunk_metrics_chunk_time_idx on _hyper_6_32_chunk (actual rows=100.00 loops=1)
               Index Cond: ("time" > 'Wed Jan 01 00:00:00 2025 PST'::timestamp with time zone)

SELECT count(*) FROM :CHUNK;
 count 
-------
     2

ROLLBACK;
BEGIN;
SET timescaledb.enable_direct_compress_insert_client_sorted = true;
INSERT INTO :CHUNK SELECT '2025-01-01'::timestamptz + (i || ' minute')::interval FROM generate_series(0,100) i;
-- status should be COMPRESSED, PARTIAL
SELECT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics_chunk') chunk;
  chunk_status_text   
----------------------
 {COMPRESSED,PARTIAL}

ROLLBACK;
-- simple test with compressed insert enabled and sorting limited to 500
-- batches should be limited to that amount so we have more compressed batches
-- dataset is tweaked to fall into single chunk with exactly 3 batches
BEGIN;
SET timescaledb.enable_direct_compress_insert = true;
SET timescaledb.direct_compress_insert_tuple_sort_limit = 1000;
INSERT INTO metrics SELECT '2025-01-02'::timestamptz + (i || ' minute')::interval, 'd1', i::float FROM generate_series(1,3000) i;
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, SUMMARY OFF, TIMING OFF) SELECT * FROM metrics;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _hyper_1_33_chunk (actual rows=3000.00 loops=1)
   ->  Seq Scan on _hyper_1_33_chunk_compressed (actual rows=3.00 loops=1)

SELECT first(time,rn), last(time,rn) FROM (SELECT ROW_NUMBER() OVER () as rn, time FROM metrics) sub;
            first             |             last             
------------------------------+------------------------------
 Thu Jan 02 00:01:00 2025 PST | Sat Jan 04 02:00:00 2025 PST

-- since the chunks are new status should be COMPRESSED, UNORDERED
SELECT DISTINCT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics') chunk;
   chunk_status_text    
------------------------
 {COMPRESSED,UNORDERED}

ROLLBACK;
-- test sort limit with TEXT segmentby triggers multiple flush cycles
BEGIN;
ALTER TABLE metrics SET (tsdb.segmentby = 'device');
NOTICE:  updated compression settings will only apply to future compressions
SET timescaledb.enable_direct_compress_insert = true;
SET timescaledb.enable_direct_compress_insert_sort_batches = true;
SET timescaledb.enable_direct_compress_insert_client_sorted = false;
SET timescaledb.direct_compress_insert_tuple_sort_limit = 50;
INSERT INTO metrics
SELECT t, 'location_' || (i % 3), random()
FROM generate_series('2025-01-02'::timestamptz,
                     '2025-01-02 00:30:00'::timestamptz,
                     '1 minute') t,
     generate_series(1, 3) i;
SELECT count(*) FROM metrics;
 count 
-------
    93

EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, SUMMARY OFF, TIMING OFF) SELECT * FROM metrics;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _hyper_1_34_chunk (actual rows=93.00 loops=1)
   ->  Seq Scan on _hyper_1_34_chunk_compressed (actual rows=4.00 loops=1)

SELECT DISTINCT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics') chunk;
   chunk_status_text    
------------------------
 {COMPRESSED,UNORDERED}

ROLLBACK;
-- test INSERT SELECT from compressed source into compressed target
-- this tests that reading from ColumnarScan correctly copies all rows
SET timescaledb.enable_direct_compress_insert = true;
SET timescaledb.enable_direct_compress_insert_client_sorted = true;
CREATE TABLE compress_src (
    time TIMESTAMPTZ NOT NULL,
    device_id TEXT NOT NULL,
    value FLOAT NOT NULL
) WITH (
    tsdb.hypertable,
    tsdb.partition_column = 'time',
    tsdb.chunk_interval = '4 hours',
    tsdb.orderby = 'time asc',
    tsdb.segment_by = 'device_id'
);
CREATE TABLE compress_tgt (
    time TIMESTAMPTZ NOT NULL,
    device_id TEXT NOT NULL,
    value FLOAT NOT NULL
) WITH (
    tsdb.hypertable,
    tsdb.partition_column = 'time',
    tsdb.chunk_interval = '4 hours',
    tsdb.orderby = 'time asc',
    tsdb.segment_by = 'device_id'
);
-- Insert with device_id, time ordering to create multi-row compressed blocks
INSERT INTO compress_src (time, device_id, value)
SELECT t.time, s.device_id, random()
FROM (SELECT generate_series('2025-01-01'::timestamptz, '2025-01-02'::timestamptz, INTERVAL '1 minute') AS time) t
CROSS JOIN (SELECT generate_series(1, 5)::TEXT AS device_id) s
ORDER BY s.device_id, t.time;
SELECT count(*) FROM compress_src;
 count 
-------
  7205

INSERT INTO compress_tgt SELECT * FROM compress_src ORDER BY device_id, time;
SELECT count(*) FROM compress_tgt;
 count 
-------
  7205

RESET timescaledb.enable_direct_compress_insert;
RESET timescaledb.enable_direct_compress_insert_client_sorted;
DROP TABLE compress_src;
DROP TABLE compress_tgt;
-- simple test to check default segmentby does not get set for direct compress
BEGIN;
RESET timescaledb.enable_direct_compress_insert;
CREATE TABLE test_segmentby_stats (
    time TIMESTAMPTZ NOT NULL,
    device_id INT NOT NULL,
    value FLOAT
) WITH (tsdb.hypertable, tsdb.compress);
NOTICE:  using column "time" as partitioning column
INSERT INTO test_segmentby_stats
SELECT t, (i % 5), random()
FROM generate_series('2024-01-01'::timestamptz, '2024-01-02'::timestamptz, '1 second') t,
     generate_series(1, 5) i;
ANALYZE test_segmentby_stats;
SELECT compress_chunk(c) FROM show_chunks('test_segmentby_stats') c;
             compress_chunk              
-----------------------------------------
 _timescaledb_internal._hyper_9_49_chunk

-- will have devide_id as default segmentby for compressed chunk;
SELECT * FROM _timescaledb_catalog.compression_settings ORDER BY relid::text COLLATE "C";
                  relid                  |                   compress_relid                   |  segmentby  | orderby | orderby_desc | orderby_nullsfirst |                                                           index                                                           
-----------------------------------------+----------------------------------------------------+-------------+---------+--------------+--------------------+---------------------------------------------------------------------------------------------------------------------------
 _timescaledb_internal._hyper_5_31_chunk | _timescaledb_internal._hyper_5_31_chunk_compressed |             | {time}  | {t}          | {t}                | [{"type": "minmax", "column": "time", "source": "orderby"}, {"type": "firstlast", "column": "time", "source": "orderby"}]
 _timescaledb_internal._hyper_9_49_chunk | _timescaledb_internal._hyper_9_49_chunk_compressed | {device_id} | {time}  | {t}          | {t}                | [{"type": "minmax", "column": "time", "source": "orderby"}, {"type": "firstlast", "column": "time", "source": "orderby"}]
 metrics                                 |                                                    |             | {time}  | {f}          | {f}                | [{"type": "minmax", "column": "time", "source": "orderby"}, {"type": "firstlast", "column": "time", "source": "orderby"}]
 metrics_chunk                           |                                                    |             |         |              |                    | 
 metrics_status                          |                                                    |             |         |              |                    | 
 test_segmentby_stats                    |                                                    |             |         |              |                    | 

SET timescaledb.enable_direct_compress_insert = true;
INSERT INTO test_segmentby_stats
SELECT t, (i % 5), random()
FROM generate_series('2024-01-06'::timestamptz, '2024-01-07'::timestamptz, '1 second') t,
     generate_series(1, 5) i;
ANALYZE test_segmentby_stats;
-- will have default segmentby set for a newly created direct compressed chunk (should observe a skipped chunk id);
SELECT * FROM _timescaledb_catalog.compression_settings ORDER BY relid::text COLLATE "C";
                  relid                  |                   compress_relid                   |  segmentby  | orderby | orderby_desc | orderby_nullsfirst |                                                           index                                                           
-----------------------------------------+----------------------------------------------------+-------------+---------+--------------+--------------------+---------------------------------------------------------------------------------------------------------------------------
 _timescaledb_internal._hyper_5_31_chunk | _timescaledb_internal._hyper_5_31_chunk_compressed |             | {time}  | {t}          | {t}                | [{"type": "minmax", "column": "time", "source": "orderby"}, {"type": "firstlast", "column": "time", "source": "orderby"}]
 _timescaledb_internal._hyper_9_49_chunk | _timescaledb_internal._hyper_9_49_chunk_compressed | {device_id} | {time}  | {t}          | {t}                | [{"type": "minmax", "column": "time", "source": "orderby"}, {"type": "firstlast", "column": "time", "source": "orderby"}]
 _timescaledb_internal._hyper_9_50_chunk | _timescaledb_internal._hyper_9_50_chunk_compressed | {device_id} | {time}  | {t}          | {t}                | [{"type": "minmax", "column": "time", "source": "orderby"}, {"type": "firstlast", "column": "time", "source": "orderby"}]
 metrics                                 |                                                    |             | {time}  | {f}          | {f}                | [{"type": "minmax", "column": "time", "source": "orderby"}, {"type": "firstlast", "column": "time", "source": "orderby"}]
 metrics_chunk                           |                                                    |             |         |              |                    | 
 metrics_status                          |                                                    |             |         |              |                    | 
 test_segmentby_stats                    |                                                    |             |         |              |                    | 

ROLLBACK;
BEGIN;
RESET timescaledb.enable_direct_compress_insert;
CREATE TABLE test_segmentby_stats (
    time TIMESTAMPTZ NOT NULL,
    device_id INT NOT NULL,
    value FLOAT
) WITH (tsdb.hypertable, tsdb.compress, tsdb.segmentby='device_id');
NOTICE:  using column "time" as partitioning column
SET timescaledb.enable_direct_compress_insert = true;
INSERT INTO test_segmentby_stats
SELECT t, (i % 5), random()
FROM generate_series('2024-01-06'::timestamptz, '2024-01-07'::timestamptz, '1 second') t,
     generate_series(1, 5) i;
ANALYZE test_segmentby_stats;
-- will have device_id by as configured segmentby for direct compressed chunk (should not observe skipped chunk id);
SELECT * FROM _timescaledb_catalog.compression_settings ORDER BY relid::text COLLATE "C";
                  relid                   |                   compress_relid                    |  segmentby  | orderby | orderby_desc | orderby_nullsfirst |                                                           index                                                           
------------------------------------------+-----------------------------------------------------+-------------+---------+--------------+--------------------+---------------------------------------------------------------------------------------------------------------------------
 _timescaledb_internal._hyper_10_51_chunk | _timescaledb_internal._hyper_10_51_chunk_compressed | {device_id} | {time}  | {t}          | {t}                | [{"type": "minmax", "column": "time", "source": "orderby"}, {"type": "firstlast", "column": "time", "source": "orderby"}]
 _timescaledb_internal._hyper_5_31_chunk  | _timescaledb_internal._hyper_5_31_chunk_compressed  |             | {time}  | {t}          | {t}                | [{"type": "minmax", "column": "time", "source": "orderby"}, {"type": "firstlast", "column": "time", "source": "orderby"}]
 metrics                                  |                                                     |             | {time}  | {f}          | {f}                | [{"type": "minmax", "column": "time", "source": "orderby"}, {"type": "firstlast", "column": "time", "source": "orderby"}]
 metrics_chunk                            |                                                     |             |         |              |                    | 
 metrics_status                           |                                                     |             |         |              |                    | 
 test_segmentby_stats                     |                                                     | {device_id} |         |              |                    | 

ROLLBACK;
-- Direct compress on a hypertable with a dropped column before the time
-- dimension. Chunks created after the drop have a different TupleDesc than
-- the hypertable, so the slot must be converted to chunk format before being
-- passed to the compressor. Cover both INSERT and COPY entry points.
BEGIN;
RESET timescaledb.enable_direct_compress_insert;
CREATE TABLE test_dropcol(col1 int, time timestamptz NOT NULL, device text, value float);
SELECT create_hypertable('test_dropcol', 'time');
     create_hypertable      
----------------------------
 (11,public,test_dropcol,t)

ALTER TABLE test_dropcol DROP COLUMN col1;
INSERT INTO test_dropcol(time, device, value)
SELECT '2025-01-01'::timestamptz + (i * interval '5 minutes'), 'd1', i::float
FROM generate_series(1, 50) i;
ALTER TABLE test_dropcol SET (timescaledb.compress, timescaledb.compress_segmentby = 'device');
SELECT count(compress_chunk(c)) FROM show_chunks('test_dropcol') c;
 count 
-------
     1

SET timescaledb.enable_direct_compress_insert = true;
INSERT INTO test_dropcol(time, device, value)
SELECT '2025-02-01'::timestamptz + (i * interval '1 second'), 'd2', i::float
FROM generate_series(1, 5000) i;
SELECT count(*), min(time), max(time), min(value), max(value) FROM test_dropcol WHERE device = 'd2';
 count |             min              |             max              | min | max  
-------+------------------------------+------------------------------+-----+------
  5000 | Sat Feb 01 00:00:01 2025 PST | Sat Feb 01 01:23:20 2025 PST |   1 | 5000

RESET timescaledb.enable_direct_compress_insert;
SET timescaledb.enable_direct_compress_copy = true;
COPY test_dropcol(time, device, value) FROM STDIN WITH (FORMAT csv);
SELECT count(*), min(time), max(time), min(value), max(value) FROM test_dropcol WHERE device = 'd3';
 count |             min              |             max              | min | max 
-------+------------------------------+------------------------------+-----+-----
     3 | Fri Feb 28 16:00:01 2025 PST | Fri Feb 28 16:00:03 2025 PST |   1 |   3

ROLLBACK;
-- Test orderby columns are not selected by DC segmentby default
BEGIN;
RESET timescaledb.enable_direct_compress_insert;
CREATE TABLE test_orderby_not_segmentby(time timestamptz NOT NULL, device_id int NOT NULL,
    value float) WITH (tsdb.hypertable);
NOTICE:  using column "time" as partitioning column
CREATE INDEX ON test_orderby_not_segmentby(device_id);
INSERT INTO test_orderby_not_segmentby SELECT '2024-01-01'::timestamptz + (i||' min')::interval,
    (i%5)+1, random() FROM generate_series(1,500) i;
ANALYZE test_orderby_not_segmentby;
ALTER TABLE test_orderby_not_segmentby SET (timescaledb.compress);
SET timescaledb.enable_direct_compress_insert = on;
INSERT INTO test_orderby_not_segmentby SELECT '2024-06-01'::timestamptz + (i||' min')::interval,
    (i%5)+1, random() FROM generate_series(1,2000) i;
SELECT * FROM _timescaledb_catalog.compression_settings ORDER BY relid::text COLLATE "C";
                  relid                   |                   compress_relid                    | segmentby |     orderby      | orderby_desc | orderby_nullsfirst |                                                                                                                            index                                                                                                                             
------------------------------------------+-----------------------------------------------------+-----------+------------------+--------------+--------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 _timescaledb_internal._hyper_12_56_chunk | _timescaledb_internal._hyper_12_56_chunk_compressed |           | {time,device_id} | {t,f}        | {t,f}              | [{"type": "minmax", "column": "time", "source": "orderby"}, {"type": "firstlast", "column": "time", "source": "orderby"}, {"type": "minmax", "column": "device_id", "source": "orderby"}, {"type": "firstlast", "column": "device_id", "source": "orderby"}]
 _timescaledb_internal._hyper_5_31_chunk  | _timescaledb_internal._hyper_5_31_chunk_compressed  |           | {time}           | {t}          | {t}                | [{"type": "minmax", "column": "time", "source": "orderby"}, {"type": "firstlast", "column": "time", "source": "orderby"}]
 metrics                                  |                                                     |           | {time}           | {f}          | {f}                | [{"type": "minmax", "column": "time", "source": "orderby"}, {"type": "firstlast", "column": "time", "source": "orderby"}]
 metrics_chunk                            |                                                     |           |                  |              |                    | 
 metrics_status                           |                                                     |           |                  |              |                    | 
 test_orderby_not_segmentby               |                                                     |           |                  |              |                    | 

ROLLBACK;
-- Direct compress must still enforce CHECK and NOT NULL constraints
CREATE TABLE metrics_check(time timestamptz NOT NULL, value int NOT NULL CHECK (value > 0))
    WITH (tsdb.hypertable, tsdb.partition_column='time');
SET timescaledb.enable_direct_compress_insert = true;
\set ON_ERROR_STOP 0
-- CHECK violation should be reported
INSERT INTO metrics_check SELECT '2025-01-01'::timestamptz + (i || ' minute')::interval, CASE WHEN i = 50 THEN -1 ELSE i END FROM generate_series(1,100) i;
ERROR:  new row for relation "_hyper_13_57_chunk" violates check constraint "metrics_check_value_check"
-- NOT NULL violation should be reported
INSERT INTO metrics_check SELECT '2025-01-01'::timestamptz + (i || ' minute')::interval, CASE WHEN i = 50 THEN NULL ELSE i END FROM generate_series(1,100) i;
ERROR:  null value in column "value" of relation "_hyper_13_58_chunk" violates not-null constraint
\set ON_ERROR_STOP 1
-- valid rows insert and compress without error
INSERT INTO metrics_check SELECT '2025-01-01'::timestamptz + (i || ' minute')::interval, i FROM generate_series(1,100) i;
SELECT count(*), min(value), max(value) FROM metrics_check;
 count | min | max 
-------+-----+-----
   100 |   1 | 100

DROP TABLE metrics_check;
RESET timescaledb.enable_direct_compress_insert;
-- Direct compress must still enforce a view's WITH CHECK OPTION
CREATE TABLE metrics_view(time timestamptz NOT NULL, value int)
    WITH (tsdb.hypertable, tsdb.partition_column='time');
CREATE VIEW metrics_view_pos AS SELECT * FROM metrics_view WHERE value > 0 WITH CHECK OPTION;
SET timescaledb.enable_direct_compress_insert = true;
\set ON_ERROR_STOP 0
-- row not satisfying the view qual should be reported
INSERT INTO metrics_view_pos SELECT '2025-01-01'::timestamptz + (i || ' minute')::interval, CASE WHEN i = 50 THEN -1 ELSE i END FROM generate_series(1,100) i;
ERROR:  new row violates check option for view "metrics_view_pos"
\set ON_ERROR_STOP 1
-- valid rows insert without error
INSERT INTO metrics_view_pos SELECT '2025-01-01'::timestamptz + (i || ' minute')::interval, i FROM generate_series(1,100) i;
SELECT count(*), min(value), max(value) FROM metrics_view;
 count | min | max 
-------+-----+-----
   100 |   1 | 100

RESET timescaledb.enable_direct_compress_insert;
DROP VIEW metrics_view_pos;
DROP TABLE metrics_view;
-- Direct compress must be disabled when the table has an exclusion constraint
CREATE TABLE dc_excl(time timestamptz NOT NULL, device text NOT NULL, value float,
    EXCLUDE USING btree (time WITH =, device WITH =))
    WITH (tsdb.hypertable, tsdb.partition_column='time');
ALTER TABLE dc_excl SET (timescaledb.compress, timescaledb.compress_segmentby='device');
NOTICE:  updated compression settings will only apply to future compressions
SET timescaledb.enable_direct_compress_insert = true;
-- insert warns, disables direct compress and falls back to a normal insert
INSERT INTO dc_excl SELECT '2025-01-01'::timestamptz + (i || ' minute')::interval, 'd1', i::float FROM generate_series(1,100) i;
WARNING:  disabling direct compress because the destination table has exclusion constraints
-- chunk stays uncompressed since direct compress was disabled
SELECT DISTINCT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('dc_excl') chunk;
 chunk_status_text 
-------------------
 {}

-- exclusion constraint is still enforced
\set ON_ERROR_STOP 0
INSERT INTO dc_excl VALUES ('2025-01-01'::timestamptz + INTERVAL '1 minute', 'd1', 99);
WARNING:  disabling direct compress because the destination table has exclusion constraints
ERROR:  conflicting key value violates exclusion constraint "62_dc_excl_time_device_excl"
\set ON_ERROR_STOP 1
SELECT count(*) FROM dc_excl;
 count 
-------
   100

RESET timescaledb.enable_direct_compress_insert;
DROP TABLE dc_excl;
-- Direct compress must return rows for a RETURNING clause
CREATE TABLE dc_ret(time timestamptz NOT NULL, device text NOT NULL, val int NOT NULL)
    WITH (tsdb.hypertable, tsdb.partition_column='time');
ALTER TABLE dc_ret SET (timescaledb.compress, timescaledb.compress_segmentby='device');
NOTICE:  updated compression settings will only apply to future compressions
SET timescaledb.enable_direct_compress_insert = true;
-- confirm the direct compress path is used for this insert
EXPLAIN (COSTS OFF, SUMMARY OFF, TIMING OFF)
INSERT INTO dc_ret SELECT '2025-01-01'::timestamptz + (i * INTERVAL '1 minute'), 'd' || (i % 2)::text, i
    FROM generate_series(1, 20) i RETURNING val;
--- QUERY PLAN ---
 Custom Scan (ModifyHypertable)
   Direct Compress: true
   ->  Insert on dc_ret
         ->  Function Scan on generate_series i

-- RETURNING should return one row per inserted tuple
WITH inserted AS (
    INSERT INTO dc_ret SELECT '2025-01-01'::timestamptz + (i * INTERVAL '1 minute'), 'd' || (i % 2)::text, i
        FROM generate_series(1, 20) i RETURNING val, device
)
SELECT count(*), min(val), max(val), sum(val), count(DISTINCT device) FROM inserted;
 count | min | max | sum | count 
-------+-----+-----+-----+-------
    20 |   1 |  20 | 210 |     2

-- RETURNING with an expression referencing multiple columns
WITH inserted AS (
    INSERT INTO dc_ret SELECT '2025-01-01'::timestamptz + (i * INTERVAL '1 hour'), 'd' || (i % 2)::text, i
        FROM generate_series(1, 20) i RETURNING device || ':' || val AS label
)
SELECT count(*), min(label), max(label) FROM inserted;
 count |  min  | max  
-------+-------+------
    20 | d0:10 | d1:9

-- RETURNING a system column falls back to the normal insert path, except
-- tableoid which the returning projection sets explicitly.
EXPLAIN (COSTS OFF, SUMMARY OFF, TIMING OFF)
INSERT INTO dc_ret SELECT '2025-01-01'::timestamptz + (i * INTERVAL '1 minute'), 'd' || (i % 2)::text, i
    FROM generate_series(1, 20) i RETURNING ctid;
WARNING:  disabling direct compress because the RETURNING clause references system columns
--- QUERY PLAN ---
 Custom Scan (ModifyHypertable)
   Direct Compress: false
   ->  Insert on dc_ret
         ->  Function Scan on generate_series i

EXPLAIN (COSTS OFF, SUMMARY OFF, TIMING OFF)
INSERT INTO dc_ret SELECT '2025-01-01'::timestamptz + (i * INTERVAL '1 minute'), 'd' || (i % 2)::text, i
    FROM generate_series(1, 20) i RETURNING xmin;
WARNING:  disabling direct compress because the RETURNING clause references system columns
--- QUERY PLAN ---
 Custom Scan (ModifyHypertable)
   Direct Compress: false
   ->  Insert on dc_ret
         ->  Function Scan on generate_series i

EXPLAIN (COSTS OFF, SUMMARY OFF, TIMING OFF)
INSERT INTO dc_ret SELECT '2025-01-01'::timestamptz + (i * INTERVAL '1 minute'), 'd' || (i % 2)::text, i
    FROM generate_series(1, 20) i RETURNING cmin;
WARNING:  disabling direct compress because the RETURNING clause references system columns
--- QUERY PLAN ---
 Custom Scan (ModifyHypertable)
   Direct Compress: false
   ->  Insert on dc_ret
         ->  Function Scan on generate_series i

EXPLAIN (COSTS OFF, SUMMARY OFF, TIMING OFF)
INSERT INTO dc_ret SELECT '2025-01-01'::timestamptz + (i * INTERVAL '1 minute'), 'd' || (i % 2)::text, i
    FROM generate_series(1, 20) i RETURNING xmax;
WARNING:  disabling direct compress because the RETURNING clause references system columns
--- QUERY PLAN ---
 Custom Scan (ModifyHypertable)
   Direct Compress: false
   ->  Insert on dc_ret
         ->  Function Scan on generate_series i

EXPLAIN (COSTS OFF, SUMMARY OFF, TIMING OFF)
INSERT INTO dc_ret SELECT '2025-01-01'::timestamptz + (i * INTERVAL '1 minute'), 'd' || (i % 2)::text, i
    FROM generate_series(1, 20) i RETURNING cmax;
WARNING:  disabling direct compress because the RETURNING clause references system columns
--- QUERY PLAN ---
 Custom Scan (ModifyHypertable)
   Direct Compress: false
   ->  Insert on dc_ret
         ->  Function Scan on generate_series i

-- tableoid stays on the direct compress path
EXPLAIN (COSTS OFF, SUMMARY OFF, TIMING OFF)
INSERT INTO dc_ret SELECT '2025-01-01'::timestamptz + (i * INTERVAL '1 minute'), 'd' || (i % 2)::text, i
    FROM generate_series(1, 20) i RETURNING tableoid;
--- QUERY PLAN ---
 Custom Scan (ModifyHypertable)
   Direct Compress: true
   ->  Insert on dc_ret
         ->  Function Scan on generate_series i

-- ctid falls back and returns real tuple ids
WITH inserted AS (
    INSERT INTO dc_ret SELECT '2025-01-01'::timestamptz + (i * INTERVAL '1 day'), 'd' || (i % 2)::text, i
        FROM generate_series(1, 20) i RETURNING ctid AS c, val
)
SELECT count(*), bool_and(c IS NOT NULL), min(val), max(val) FROM inserted;
WARNING:  disabling direct compress because the RETURNING clause references system columns
 count | bool_and | min | max 
-------+----------+-----+-----
    20 | t        |   1 |  20

-- tableoid on the direct compress path holds the target chunk (all 20 rows
-- land in one chunk, so every RETURNING row reports the same chunk)
INSERT INTO dc_ret SELECT '2025-06-01'::timestamptz + (i * INTERVAL '1 minute'), 'd' || (i % 2)::text, i
    FROM generate_series(1, 20) i RETURNING tableoid::regclass AS chunk;
                  chunk                   
------------------------------------------
 _timescaledb_internal._hyper_16_67_chunk
 _timescaledb_internal._hyper_16_67_chunk
 _timescaledb_internal._hyper_16_67_chunk
 _timescaledb_internal._hyper_16_67_chunk
 _timescaledb_internal._hyper_16_67_chunk
 _timescaledb_internal._hyper_16_67_chunk
 _timescaledb_internal._hyper_16_67_chunk
 _timescaledb_internal._hyper_16_67_chunk
 _timescaledb_internal._hyper_16_67_chunk
 _timescaledb_internal._hyper_16_67_chunk
 _timescaledb_internal._hyper_16_67_chunk
 _timescaledb_internal._hyper_16_67_chunk
 _timescaledb_internal._hyper_16_67_chunk
 _timescaledb_internal._hyper_16_67_chunk
 _timescaledb_internal._hyper_16_67_chunk
 _timescaledb_internal._hyper_16_67_chunk
 _timescaledb_internal._hyper_16_67_chunk
 _timescaledb_internal._hyper_16_67_chunk
 _timescaledb_internal._hyper_16_67_chunk
 _timescaledb_internal._hyper_16_67_chunk

-- rows were actually inserted
SELECT count(*) FROM dc_ret;
 count 
-------
    80

RESET timescaledb.enable_direct_compress_insert;
DROP TABLE dc_ret;