compact_chunk.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.
-- Tests for the compact_chunk function.
-- compact_chunk merges overlapping compressed batches within a chunk
-- without decompressing/recompressing batches that are already ordered.
CREATE TABLE metrics (time TIMESTAMPTZ NOT NULL, device TEXT, value float) WITH (tsdb.hypertable, tsdb.orderby='time');
NOTICE:  using column "time" as partitioning column
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;
-- compact_chunk with no overlapping batches (no-op case)
-- Insert 3000 ordered rows: Jan 2 00:00 to Jan 4 02:00 PST.
-- Direct compress insert creates 3 batches: 3x1000 rows.
INSERT INTO metrics
SELECT '2025-01-02'::timestamptz + (i || ' minute')::interval, 'd1', i::float
FROM generate_series(1,3000) i;
-- Status should be COMPRESSED,UNORDERED after direct compress insert
SELECT DISTINCT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics') chunk;
   chunk_status_text    
------------------------
 {COMPRESSED,UNORDERED}

-- Get the first compressed chunk for inspection
SELECT cs.compress_relid::regclass::text AS "CHUNK_FULL_NAME"
FROM _timescaledb_catalog.chunk ch
    JOIN _timescaledb_catalog.compression_settings cs
        ON cs.relid = ch.relid
    JOIN _timescaledb_catalog.hypertable ht ON ch.hypertable_id = ht.id
WHERE ht.table_name = 'metrics'
ORDER BY ch.id LIMIT 1 \gset
-- Show batch metadata: 3 non-overlapping batches (ordered by min time)
SELECT ctid, _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_v2_first_time, _ts_meta_v2_last_time
FROM :CHUNK_FULL_NAME
ORDER BY _ts_meta_min_1;
 ctid  | _ts_meta_count |        _ts_meta_min_1        |        _ts_meta_max_1        |    _ts_meta_v2_first_time    |    _ts_meta_v2_last_time     
-------+----------------+------------------------------+------------------------------+------------------------------+------------------------------
 (0,1) |           1000 | Thu Jan 02 00:01:00 2025 PST | Thu Jan 02 16:40:00 2025 PST | Thu Jan 02 00:01:00 2025 PST | Thu Jan 02 16:40:00 2025 PST
 (0,2) |           1000 | Thu Jan 02 16:41:00 2025 PST | Fri Jan 03 09:20:00 2025 PST | Thu Jan 02 16:41:00 2025 PST | Fri Jan 03 09:20:00 2025 PST
 (0,3) |           1000 | Fri Jan 03 09:21:00 2025 PST | Sat Jan 04 02:00:00 2025 PST | Fri Jan 03 09:21:00 2025 PST | Sat Jan 04 02:00:00 2025 PST

-- compact_chunk on non-overlapping batches is a no-op; returns the chunk regclass
SELECT _timescaledb_functions.compact_chunk(chunk) FROM show_chunks('metrics') chunk;
             compact_chunk              
----------------------------------------
 _timescaledb_internal._hyper_1_1_chunk

-- Show batch metadata: 3 non-overlapping batches with same ctids as before
SELECT ctid, _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_v2_first_time, _ts_meta_v2_last_time
FROM :CHUNK_FULL_NAME
ORDER BY _ts_meta_min_1;
 ctid  | _ts_meta_count |        _ts_meta_min_1        |        _ts_meta_max_1        |    _ts_meta_v2_first_time    |    _ts_meta_v2_last_time     
-------+----------------+------------------------------+------------------------------+------------------------------+------------------------------
 (0,1) |           1000 | Thu Jan 02 00:01:00 2025 PST | Thu Jan 02 16:40:00 2025 PST | Thu Jan 02 00:01:00 2025 PST | Thu Jan 02 16:40:00 2025 PST
 (0,2) |           1000 | Thu Jan 02 16:41:00 2025 PST | Fri Jan 03 09:20:00 2025 PST | Thu Jan 02 16:41:00 2025 PST | Fri Jan 03 09:20:00 2025 PST
 (0,3) |           1000 | Fri Jan 03 09:21:00 2025 PST | Sat Jan 04 02:00:00 2025 PST | Fri Jan 03 09:21:00 2025 PST | Sat Jan 04 02:00:00 2025 PST

-- Status UNORDERED removed: compact_chunk clears the UNORDERED flag
SELECT DISTINCT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics') chunk;
 chunk_status_text 
-------------------
 {COMPRESSED}

-- compact_chunk with overlapping batches
-- Insert data that overlaps with existing batches.
-- Jan 1 to Jan 3 data overlaps with the existing Jan 2 to Jan 4 batches
-- in the compressed chunk.
INSERT INTO metrics
SELECT '2025-01-01'::timestamptz + (i || ' minute')::interval, 'd1', i::float
FROM generate_series(1,3000) i;
-- Show compressed chunk metadata: overlapping batches are now visible.
-- The new batches from the Jan 1 insert interleave with the old Jan 2 batches.
SELECT ctid, _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_v2_first_time, _ts_meta_v2_last_time
FROM :CHUNK_FULL_NAME
ORDER BY _ts_meta_min_1;
 ctid  | _ts_meta_count |        _ts_meta_min_1        |        _ts_meta_max_1        |    _ts_meta_v2_first_time    |    _ts_meta_v2_last_time     
-------+----------------+------------------------------+------------------------------+------------------------------+------------------------------
 (0,4) |           1000 | Wed Jan 01 16:00:00 2025 PST | Thu Jan 02 08:39:00 2025 PST | Wed Jan 01 16:00:00 2025 PST | Thu Jan 02 08:39:00 2025 PST
 (0,1) |           1000 | Thu Jan 02 00:01:00 2025 PST | Thu Jan 02 16:40:00 2025 PST | Thu Jan 02 00:01:00 2025 PST | Thu Jan 02 16:40:00 2025 PST
 (0,5) |           1000 | Thu Jan 02 08:40:00 2025 PST | Fri Jan 03 01:19:00 2025 PST | Thu Jan 02 08:40:00 2025 PST | Fri Jan 03 01:19:00 2025 PST
 (0,2) |           1000 | Thu Jan 02 16:41:00 2025 PST | Fri Jan 03 09:20:00 2025 PST | Thu Jan 02 16:41:00 2025 PST | Fri Jan 03 09:20:00 2025 PST
 (0,6) |             41 | Fri Jan 03 01:20:00 2025 PST | Fri Jan 03 02:00:00 2025 PST | Fri Jan 03 01:20:00 2025 PST | Fri Jan 03 02:00:00 2025 PST
 (0,3) |           1000 | Fri Jan 03 09:21:00 2025 PST | Sat Jan 04 02:00:00 2025 PST | Fri Jan 03 09:21:00 2025 PST | Sat Jan 04 02:00:00 2025 PST

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

-- compact_chunk should identify and merge the overlapping batches.
SELECT _timescaledb_functions.compact_chunk(chunk) FROM show_chunks('metrics') chunk;
             compact_chunk              
----------------------------------------
 _timescaledb_internal._hyper_1_1_chunk
 _timescaledb_internal._hyper_1_2_chunk

-- Show compressed chunk metadata: no overlapping batches anymore.
-- We should see new ctids of the newly merged batches.
SELECT ctid, _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_v2_first_time, _ts_meta_v2_last_time
FROM :CHUNK_FULL_NAME
ORDER BY _ts_meta_min_1;
  ctid  | _ts_meta_count |        _ts_meta_min_1        |        _ts_meta_max_1        |    _ts_meta_v2_first_time    |    _ts_meta_v2_last_time     
--------+----------------+------------------------------+------------------------------+------------------------------+------------------------------
 (0,7)  |           1000 | Wed Jan 01 16:00:00 2025 PST | Thu Jan 02 04:20:00 2025 PST | Wed Jan 01 16:00:00 2025 PST | Thu Jan 02 04:20:00 2025 PST
 (0,8)  |           1000 | Thu Jan 02 04:20:00 2025 PST | Thu Jan 02 12:40:00 2025 PST | Thu Jan 02 04:20:00 2025 PST | Thu Jan 02 12:40:00 2025 PST
 (0,9)  |           1000 | Thu Jan 02 12:40:00 2025 PST | Thu Jan 02 21:00:00 2025 PST | Thu Jan 02 12:40:00 2025 PST | Thu Jan 02 21:00:00 2025 PST
 (0,10) |           1000 | Thu Jan 02 21:00:00 2025 PST | Fri Jan 03 08:39:00 2025 PST | Thu Jan 02 21:00:00 2025 PST | Fri Jan 03 08:39:00 2025 PST
 (1,1)  |             41 | Fri Jan 03 08:40:00 2025 PST | Fri Jan 03 09:20:00 2025 PST | Fri Jan 03 08:40:00 2025 PST | Fri Jan 03 09:20:00 2025 PST
 (0,3)  |           1000 | Fri Jan 03 09:21:00 2025 PST | Sat Jan 04 02:00:00 2025 PST | Fri Jan 03 09:21:00 2025 PST | Sat Jan 04 02:00:00 2025 PST

-- Status should not contain UNORDERED flag
SELECT DISTINCT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics') chunk;
 chunk_status_text 
-------------------
 {COMPRESSED}

-- compact_chunk with negative max_batches must fail
\set ON_ERROR_STOP 0
SELECT _timescaledb_functions.compact_chunk(chunk, -1) FROM show_chunks('metrics') chunk;
ERROR:  max_batches must be greater than or equal to 0
\set ON_ERROR_STOP 1
-- compact an uncompressed chunk
-- Create a new uncompressed chunk for a different time range by
-- inserting with direct compress insert disabled.
SET timescaledb.enable_direct_compress_insert = false;
INSERT INTO metrics VALUES ('2025-12-01', 'd1', -1.0);
SET timescaledb.enable_direct_compress_insert = true;
-- The new chunk for Dec 2025 should be uncompressed (empty status array)
SELECT _timescaledb_functions.chunk_status_text(chunk) AS chunk_status,
       chunk::text AS chunk_name
FROM show_chunks('metrics') chunk
WHERE NOT (_timescaledb_functions.chunk_status_text(chunk) && ARRAY['COMPRESSED'])
ORDER BY chunk;
 chunk_status |               chunk_name               
--------------+----------------------------------------
 {}           | _timescaledb_internal._hyper_1_3_chunk

-- compact_chunk on an uncompressed chunk must fail
SELECT chunk AS "UNCOMPRESSED_CHUNK"
FROM show_chunks('metrics') chunk
WHERE NOT (_timescaledb_functions.chunk_status_text(chunk) && ARRAY['COMPRESSED'])
LIMIT 1 \gset
\set ON_ERROR_STOP 0
SELECT _timescaledb_functions.compact_chunk(:'UNCOMPRESSED_CHUNK');
ERROR:  trying to compact an uncompressed chunk _timescaledb_internal._hyper_1_3_chunk
\set ON_ERROR_STOP 1
-- compact a partially compressed chunk
-- Insert an uncompressed row into the already-compressed Jan 2 chunk.
-- This makes chunk 1 PARTIAL (it has both compressed and uncompressed rows).
SET timescaledb.enable_direct_compress_insert = false;
INSERT INTO metrics VALUES ('2025-01-02 12:00', 'd1', -1.0);
SET timescaledb.enable_direct_compress_insert = true;
-- Chunk 1 should now show PARTIAL status
SELECT _timescaledb_functions.chunk_status_text(chunk) AS chunk_status,
       chunk::text AS chunk_name
FROM show_chunks('metrics') chunk
WHERE _timescaledb_functions.chunk_status_text(chunk) && ARRAY['PARTIAL']
ORDER BY chunk;
     chunk_status     |               chunk_name               
----------------------+----------------------------------------
 {COMPRESSED,PARTIAL} | _timescaledb_internal._hyper_1_1_chunk

-- compact_chunk on a partially compressed chunk must fail
SELECT chunk AS "PARTIAL_CHUNK"
FROM show_chunks('metrics') chunk
WHERE _timescaledb_functions.chunk_status_text(chunk) && ARRAY['PARTIAL']
LIMIT 1 \gset
\set ON_ERROR_STOP 0
SELECT _timescaledb_functions.compact_chunk(:'PARTIAL_CHUNK');
ERROR:  trying to compact a partially compressed chunk _timescaledb_internal._hyper_1_1_chunk
\set ON_ERROR_STOP 1
-- compact_chunk is STRICT, a NULL argument returns NULL
SELECT _timescaledb_functions.compact_chunk(NULL::regclass);
 compact_chunk 
---------------
 

-- compact_chunk in read-only mode must fail
SET default_transaction_read_only TO on;
\set ON_ERROR_STOP 0
SELECT _timescaledb_functions.compact_chunk(chunk) FROM show_chunks('metrics') chunk;
ERROR:  cannot execute compact_chunk() in a read-only transaction
\set ON_ERROR_STOP 1
SET default_transaction_read_only TO off;
-- compact_chunk in REPEATABLE READ must fail
\set ON_ERROR_STOP 0
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT _timescaledb_functions.compact_chunk(chunk) FROM show_chunks('metrics') chunk;
ERROR:  compact_chunk is not supported in REPEATABLE READ or SERIALIZABLE isolation level
COMMIT;
\set ON_ERROR_STOP 1
-- Create a hypertable with a segmentby column.
-- Each segment ('d1', 'd2') will have its own set of batches, and
-- compact_chunk should handle overlaps per segment independently.
CREATE TABLE metrics_seg (time TIMESTAMPTZ NOT NULL, device TEXT, value float)
WITH (tsdb.hypertable, tsdb.orderby='time', tsdb.segmentby='device');
NOTICE:  using column "time" as partitioning column
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;
-- Insert 2000 rows for each of two devices: non-overlapping within each segment
INSERT INTO metrics_seg
SELECT '2025-01-03'::timestamptz + (i || ' minute')::interval, 'd1', i::float
FROM generate_series(0,1999) i;
INSERT INTO metrics_seg
SELECT '2025-01-03'::timestamptz + (i || ' minute')::interval, 'd2', i::float
FROM generate_series(0,1999) i;
SELECT DISTINCT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics_seg') chunk;
   chunk_status_text    
------------------------
 {COMPRESSED,UNORDERED}

-- Get the compressed chunk for metrics_seg
SELECT cs.compress_relid::regclass::text AS "SEG_CHUNK_FULL_NAME"
FROM _timescaledb_catalog.chunk ch
    JOIN _timescaledb_catalog.compression_settings cs
        ON cs.relid = ch.relid
    JOIN _timescaledb_catalog.hypertable ht ON ch.hypertable_id = ht.id
WHERE ht.table_name = 'metrics_seg'
ORDER BY ch.id LIMIT 1 \gset
-- 4 batches: 2 per segment, no overlaps
SELECT ctid, device, _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_v2_first_time, _ts_meta_v2_last_time
FROM :SEG_CHUNK_FULL_NAME
ORDER BY device, _ts_meta_min_1;
 ctid  | device | _ts_meta_count |        _ts_meta_min_1        |        _ts_meta_max_1        |    _ts_meta_v2_first_time    |    _ts_meta_v2_last_time     
-------+--------+----------------+------------------------------+------------------------------+------------------------------+------------------------------
 (0,1) | d1     |           1000 | Fri Jan 03 00:00:00 2025 PST | Fri Jan 03 16:39:00 2025 PST | Fri Jan 03 00:00:00 2025 PST | Fri Jan 03 16:39:00 2025 PST
 (0,2) | d1     |           1000 | Fri Jan 03 16:40:00 2025 PST | Sat Jan 04 09:19:00 2025 PST | Fri Jan 03 16:40:00 2025 PST | Sat Jan 04 09:19:00 2025 PST
 (0,3) | d2     |           1000 | Fri Jan 03 00:00:00 2025 PST | Fri Jan 03 16:39:00 2025 PST | Fri Jan 03 00:00:00 2025 PST | Fri Jan 03 16:39:00 2025 PST
 (0,4) | d2     |           1000 | Fri Jan 03 16:40:00 2025 PST | Sat Jan 04 09:19:00 2025 PST | Fri Jan 03 16:40:00 2025 PST | Sat Jan 04 09:19:00 2025 PST

-- compact_chunk with no overlaps: no-op for both segments
SELECT _timescaledb_functions.compact_chunk(chunk) FROM show_chunks('metrics_seg') chunk;
             compact_chunk              
----------------------------------------
 _timescaledb_internal._hyper_2_4_chunk

-- Same ctids: nothing was rewritten
SELECT ctid, device, _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_v2_first_time, _ts_meta_v2_last_time
FROM :SEG_CHUNK_FULL_NAME
ORDER BY device, _ts_meta_min_1;
 ctid  | device | _ts_meta_count |        _ts_meta_min_1        |        _ts_meta_max_1        |    _ts_meta_v2_first_time    |    _ts_meta_v2_last_time     
-------+--------+----------------+------------------------------+------------------------------+------------------------------+------------------------------
 (0,1) | d1     |           1000 | Fri Jan 03 00:00:00 2025 PST | Fri Jan 03 16:39:00 2025 PST | Fri Jan 03 00:00:00 2025 PST | Fri Jan 03 16:39:00 2025 PST
 (0,2) | d1     |           1000 | Fri Jan 03 16:40:00 2025 PST | Sat Jan 04 09:19:00 2025 PST | Fri Jan 03 16:40:00 2025 PST | Sat Jan 04 09:19:00 2025 PST
 (0,3) | d2     |           1000 | Fri Jan 03 00:00:00 2025 PST | Fri Jan 03 16:39:00 2025 PST | Fri Jan 03 00:00:00 2025 PST | Fri Jan 03 16:39:00 2025 PST
 (0,4) | d2     |           1000 | Fri Jan 03 16:40:00 2025 PST | Sat Jan 04 09:19:00 2025 PST | Fri Jan 03 16:40:00 2025 PST | Sat Jan 04 09:19:00 2025 PST

-- status should be updated to COMPRESSED only
SELECT DISTINCT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics_seg') chunk;
 chunk_status_text 
-------------------
 {COMPRESSED}

-- Insert overlapping data
INSERT INTO metrics_seg
SELECT '2025-01-03'::timestamptz + (i || ' minute')::interval, 'd1', i::float
FROM generate_series(0,1999) i;
INSERT INTO metrics_seg
SELECT '2025-01-03'::timestamptz + (i || ' minute')::interval, 'd2', i::float
FROM generate_series(0,1999) i;
-- 8 batches: 4 per segment, overlapping
SELECT ctid, device, _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_v2_first_time, _ts_meta_v2_last_time
FROM :SEG_CHUNK_FULL_NAME
ORDER BY device, _ts_meta_min_1;
 ctid  | device | _ts_meta_count |        _ts_meta_min_1        |        _ts_meta_max_1        |    _ts_meta_v2_first_time    |    _ts_meta_v2_last_time     
-------+--------+----------------+------------------------------+------------------------------+------------------------------+------------------------------
 (0,1) | d1     |           1000 | Fri Jan 03 00:00:00 2025 PST | Fri Jan 03 16:39:00 2025 PST | Fri Jan 03 00:00:00 2025 PST | Fri Jan 03 16:39:00 2025 PST
 (0,5) | d1     |           1000 | Fri Jan 03 00:00:00 2025 PST | Fri Jan 03 16:39:00 2025 PST | Fri Jan 03 00:00:00 2025 PST | Fri Jan 03 16:39:00 2025 PST
 (0,2) | d1     |           1000 | Fri Jan 03 16:40:00 2025 PST | Sat Jan 04 09:19:00 2025 PST | Fri Jan 03 16:40:00 2025 PST | Sat Jan 04 09:19:00 2025 PST
 (0,6) | d1     |           1000 | Fri Jan 03 16:40:00 2025 PST | Sat Jan 04 09:19:00 2025 PST | Fri Jan 03 16:40:00 2025 PST | Sat Jan 04 09:19:00 2025 PST
 (0,3) | d2     |           1000 | Fri Jan 03 00:00:00 2025 PST | Fri Jan 03 16:39:00 2025 PST | Fri Jan 03 00:00:00 2025 PST | Fri Jan 03 16:39:00 2025 PST
 (1,1) | d2     |           1000 | Fri Jan 03 00:00:00 2025 PST | Fri Jan 03 16:39:00 2025 PST | Fri Jan 03 00:00:00 2025 PST | Fri Jan 03 16:39:00 2025 PST
 (0,4) | d2     |           1000 | Fri Jan 03 16:40:00 2025 PST | Sat Jan 04 09:19:00 2025 PST | Fri Jan 03 16:40:00 2025 PST | Sat Jan 04 09:19:00 2025 PST
 (1,2) | d2     |           1000 | Fri Jan 03 16:40:00 2025 PST | Sat Jan 04 09:19:00 2025 PST | Fri Jan 03 16:40:00 2025 PST | Sat Jan 04 09:19:00 2025 PST

-- status should be updated to COMPRESSED, UNORDERED
SELECT DISTINCT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics_seg') chunk;
   chunk_status_text    
------------------------
 {COMPRESSED,UNORDERED}

-- compact_chunk with overlaps: combines the batches
SELECT _timescaledb_functions.compact_chunk(chunk) FROM show_chunks('metrics_seg') chunk;
             compact_chunk              
----------------------------------------
 _timescaledb_internal._hyper_2_4_chunk

-- New ctids: overlapping batches merged and re-sorted
SELECT ctid, device, _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_v2_first_time, _ts_meta_v2_last_time
FROM :SEG_CHUNK_FULL_NAME
ORDER BY device, _ts_meta_min_1;
  ctid  | device | _ts_meta_count |        _ts_meta_min_1        |        _ts_meta_max_1        |    _ts_meta_v2_first_time    |    _ts_meta_v2_last_time     
--------+--------+----------------+------------------------------+------------------------------+------------------------------+------------------------------
 (1,3)  | d1     |           1000 | Fri Jan 03 00:00:00 2025 PST | Fri Jan 03 08:19:00 2025 PST | Fri Jan 03 00:00:00 2025 PST | Fri Jan 03 08:19:00 2025 PST
 (1,4)  | d1     |           1000 | Fri Jan 03 08:20:00 2025 PST | Fri Jan 03 16:39:00 2025 PST | Fri Jan 03 08:20:00 2025 PST | Fri Jan 03 16:39:00 2025 PST
 (1,5)  | d1     |           1000 | Fri Jan 03 16:40:00 2025 PST | Sat Jan 04 00:59:00 2025 PST | Fri Jan 03 16:40:00 2025 PST | Sat Jan 04 00:59:00 2025 PST
 (1,6)  | d1     |           1000 | Sat Jan 04 01:00:00 2025 PST | Sat Jan 04 09:19:00 2025 PST | Sat Jan 04 01:00:00 2025 PST | Sat Jan 04 09:19:00 2025 PST
 (1,7)  | d2     |           1000 | Fri Jan 03 00:00:00 2025 PST | Fri Jan 03 08:19:00 2025 PST | Fri Jan 03 00:00:00 2025 PST | Fri Jan 03 08:19:00 2025 PST
 (1,8)  | d2     |           1000 | Fri Jan 03 08:20:00 2025 PST | Fri Jan 03 16:39:00 2025 PST | Fri Jan 03 08:20:00 2025 PST | Fri Jan 03 16:39:00 2025 PST
 (1,9)  | d2     |           1000 | Fri Jan 03 16:40:00 2025 PST | Sat Jan 04 00:59:00 2025 PST | Fri Jan 03 16:40:00 2025 PST | Sat Jan 04 00:59:00 2025 PST
 (1,10) | d2     |           1000 | Sat Jan 04 01:00:00 2025 PST | Sat Jan 04 09:19:00 2025 PST | Sat Jan 04 01:00:00 2025 PST | Sat Jan 04 09:19:00 2025 PST

-- status should be COMPRESSED only (UNORDERED cleared after compaction)
SELECT DISTINCT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics_seg') chunk;
 chunk_status_text 
-------------------
 {COMPRESSED}

-- compact_chunk with nullable secondary orderby column (NULLS FIRST)
-- The second batch starts at a boundary tie on col1 (time) with a NULL
-- in col2 (value). With NULLS FIRST, (time=T, value=NULL) should sort
-- BEFORE (time=T, value=non-null), but the second batch is positioned
-- after the first in the index — an ordering violation.
CREATE TABLE metrics_nullable (time TIMESTAMPTZ, device TEXT, value float)
WITH (tsdb.hypertable, tsdb.orderby='time,value NULLS FIRST');
NOTICE:  using column "time" as partitioning column
SET timescaledb.enable_direct_compress_insert = true;
-- Insert batch 1 (1000 rows): time [00:01..16:40], all non-null values.
-- Last row: (16:40, 1000).
INSERT INTO metrics_nullable
SELECT '2025-01-02'::timestamptz + (i || ' minute')::interval, 'd1', i::float
FROM generate_series(1,1000) i;
-- compact_chunk on nullable orderby with no nulls in data should succeed (no-op)
SELECT _timescaledb_functions.compact_chunk(chunk) FROM show_chunks('metrics_nullable') chunk;
             compact_chunk              
----------------------------------------
 _timescaledb_internal._hyper_3_5_chunk

-- Get the compressed chunk for metrics_nullable
SELECT cs.compress_relid::regclass::text AS "NULLABLE_CHUNK_FULL_NAME"
FROM _timescaledb_catalog.chunk ch
    JOIN _timescaledb_catalog.compression_settings cs
        ON cs.relid = ch.relid
    JOIN _timescaledb_catalog.hypertable ht ON ch.hypertable_id = ht.id
WHERE ht.table_name = 'metrics_nullable'
ORDER BY ch.id LIMIT 1 \gset
-- 1 batch, no nulls
SELECT ctid, _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_v2_first_time, _ts_meta_v2_last_time, _ts_meta_min_2, _ts_meta_max_2, _ts_meta_v2_first_value, _ts_meta_v2_last_value
FROM :NULLABLE_CHUNK_FULL_NAME
ORDER BY _ts_meta_min_1;
 ctid  | _ts_meta_count |        _ts_meta_min_1        |        _ts_meta_max_1        |    _ts_meta_v2_first_time    |    _ts_meta_v2_last_time     | _ts_meta_min_2 | _ts_meta_max_2 | _ts_meta_v2_first_value | _ts_meta_v2_last_value 
-------+----------------+------------------------------+------------------------------+------------------------------+------------------------------+----------------+----------------+-------------------------+------------------------
 (0,1) |           1000 | Thu Jan 02 00:01:00 2025 PST | Thu Jan 02 16:40:00 2025 PST | Thu Jan 02 00:01:00 2025 PST | Thu Jan 02 16:40:00 2025 PST |              1 |           1000 |                       1 |                   1000

-- Insert batch 2 (1000 rows): starts at batch 1's max time (i=1000, time=16:40).
-- First row has NULL value: (16:40, NULL).
-- With NULLS FIRST, (16:40, NULL) should sort BEFORE (16:40, 1000) from
-- batch 1's last row. But batch 2 is after batch 1 in the index → unordered.
INSERT INTO metrics_nullable
SELECT '2025-01-02'::timestamptz + ((999 + i) || ' minute')::interval,
       'd1',
       CASE WHEN i = 1 THEN NULL ELSE (1000 + i)::float END
FROM generate_series(1,1000) i;
-- 2 batches: boundary tie on col1, NULL in col2 at the boundary
SELECT ctid, _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_v2_first_time, _ts_meta_v2_last_time, _ts_meta_min_2, _ts_meta_max_2, _ts_meta_v2_first_value, _ts_meta_v2_last_value
FROM :NULLABLE_CHUNK_FULL_NAME
ORDER BY _ts_meta_min_1;
 ctid  | _ts_meta_count |        _ts_meta_min_1        |        _ts_meta_max_1        |    _ts_meta_v2_first_time    |    _ts_meta_v2_last_time     | _ts_meta_min_2 | _ts_meta_max_2 | _ts_meta_v2_first_value | _ts_meta_v2_last_value 
-------+----------------+------------------------------+------------------------------+------------------------------+------------------------------+----------------+----------------+-------------------------+------------------------
 (0,1) |           1000 | Thu Jan 02 00:01:00 2025 PST | Thu Jan 02 16:40:00 2025 PST | Thu Jan 02 00:01:00 2025 PST | Thu Jan 02 16:40:00 2025 PST |              1 |           1000 |                       1 |                   1000
 (0,2) |           1000 | Thu Jan 02 16:40:00 2025 PST | Fri Jan 03 09:19:00 2025 PST | Thu Jan 02 16:40:00 2025 PST | Fri Jan 03 09:19:00 2025 PST |           1002 |           2000 |                         |                   2000

-- compact_chunk should detect the boundary-tie overlap via NULL in col2
SELECT _timescaledb_functions.compact_chunk(chunk) FROM show_chunks('metrics_nullable') chunk;
             compact_chunk              
----------------------------------------
 _timescaledb_internal._hyper_3_5_chunk

-- After compaction: batches merged and re-sorted
SELECT ctid, _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_v2_first_time, _ts_meta_v2_last_time, _ts_meta_min_2, _ts_meta_max_2, _ts_meta_v2_first_value, _ts_meta_v2_last_value
FROM :NULLABLE_CHUNK_FULL_NAME
ORDER BY _ts_meta_min_1;
 ctid  | _ts_meta_count |        _ts_meta_min_1        |        _ts_meta_max_1        |    _ts_meta_v2_first_time    |    _ts_meta_v2_last_time     | _ts_meta_min_2 | _ts_meta_max_2 | _ts_meta_v2_first_value | _ts_meta_v2_last_value 
-------+----------------+------------------------------+------------------------------+------------------------------+------------------------------+----------------+----------------+-------------------------+------------------------
 (0,3) |           1000 | Thu Jan 02 00:01:00 2025 PST | Thu Jan 02 16:40:00 2025 PST | Thu Jan 02 00:01:00 2025 PST | Thu Jan 02 16:40:00 2025 PST |              1 |            999 |                       1 |                       
 (0,4) |           1000 | Thu Jan 02 16:40:00 2025 PST | Fri Jan 03 09:19:00 2025 PST | Thu Jan 02 16:40:00 2025 PST | Fri Jan 03 09:19:00 2025 PST |           1000 |           2000 |                    1000 |                   2000

-- Verify total row count is preserved
SELECT count(*) FROM metrics_nullable;
 count 
-------
  2000

-- Verify NULL rows are accessible
SELECT count(*) AS null_value_count FROM metrics_nullable WHERE value IS NULL;
 null_value_count 
------------------
                1

-- Verify ordering at boundary: NULL must come before non-null with NULLS FIRST
SELECT time, value FROM metrics_nullable
WHERE time = '2025-01-02'::timestamptz + '1000 minutes'::interval
ORDER BY time, value NULLS FIRST;
             time             | value 
------------------------------+-------
 Thu Jan 02 16:40:00 2025 PST |      
 Thu Jan 02 16:40:00 2025 PST |  1000

DROP TABLE metrics_nullable;
-- compact_chunk with DESC orderby column
CREATE TABLE metrics_desc (time TIMESTAMPTZ NOT NULL, device TEXT, value float)
WITH (tsdb.hypertable, tsdb.orderby='time DESC', tsdb.segmentby='device');
NOTICE:  using column "time" as partitioning column
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;
-- Insert non-overlapping data for two devices
INSERT INTO metrics_desc
SELECT '2025-01-03'::timestamptz + (i || ' minute')::interval, 'd1', i::float
FROM generate_series(0,1999) i;
INSERT INTO metrics_desc
SELECT '2025-01-03'::timestamptz + (i || ' minute')::interval, 'd2', i::float
FROM generate_series(0,1999) i;
SELECT DISTINCT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics_desc') chunk;
   chunk_status_text    
------------------------
 {COMPRESSED,UNORDERED}

-- Get the compressed chunk for metrics_desc
SELECT cs.compress_relid::regclass::text AS "DESC_CHUNK_FULL_NAME"
FROM _timescaledb_catalog.chunk ch
    JOIN _timescaledb_catalog.compression_settings cs
        ON cs.relid = ch.relid
    JOIN _timescaledb_catalog.hypertable ht ON ch.hypertable_id = ht.id
WHERE ht.table_name = 'metrics_desc'
ORDER BY ch.id LIMIT 1 \gset
-- 4 batches: 2 per segment, ordered by max time descending
SELECT ctid, device, _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_v2_first_time, _ts_meta_v2_last_time
FROM :DESC_CHUNK_FULL_NAME
ORDER BY device, _ts_meta_max_1 DESC;
 ctid  | device | _ts_meta_count |        _ts_meta_min_1        |        _ts_meta_max_1        |    _ts_meta_v2_first_time    |    _ts_meta_v2_last_time     
-------+--------+----------------+------------------------------+------------------------------+------------------------------+------------------------------
 (0,1) | d1     |           1000 | Fri Jan 03 16:40:00 2025 PST | Sat Jan 04 09:19:00 2025 PST | Sat Jan 04 09:19:00 2025 PST | Fri Jan 03 16:40:00 2025 PST
 (0,2) | d1     |           1000 | Fri Jan 03 00:00:00 2025 PST | Fri Jan 03 16:39:00 2025 PST | Fri Jan 03 16:39:00 2025 PST | Fri Jan 03 00:00:00 2025 PST
 (0,3) | d2     |           1000 | Fri Jan 03 16:40:00 2025 PST | Sat Jan 04 09:19:00 2025 PST | Sat Jan 04 09:19:00 2025 PST | Fri Jan 03 16:40:00 2025 PST
 (0,4) | d2     |           1000 | Fri Jan 03 00:00:00 2025 PST | Fri Jan 03 16:39:00 2025 PST | Fri Jan 03 16:39:00 2025 PST | Fri Jan 03 00:00:00 2025 PST

-- No overlaps: compact_chunk is a no-op
SELECT _timescaledb_functions.compact_chunk(chunk) FROM show_chunks('metrics_desc') chunk;
             compact_chunk              
----------------------------------------
 _timescaledb_internal._hyper_4_6_chunk

-- Same ctids: nothing was rewritten
SELECT ctid, device, _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_v2_first_time, _ts_meta_v2_last_time
FROM :DESC_CHUNK_FULL_NAME
ORDER BY device, _ts_meta_max_1 DESC;
 ctid  | device | _ts_meta_count |        _ts_meta_min_1        |        _ts_meta_max_1        |    _ts_meta_v2_first_time    |    _ts_meta_v2_last_time     
-------+--------+----------------+------------------------------+------------------------------+------------------------------+------------------------------
 (0,1) | d1     |           1000 | Fri Jan 03 16:40:00 2025 PST | Sat Jan 04 09:19:00 2025 PST | Sat Jan 04 09:19:00 2025 PST | Fri Jan 03 16:40:00 2025 PST
 (0,2) | d1     |           1000 | Fri Jan 03 00:00:00 2025 PST | Fri Jan 03 16:39:00 2025 PST | Fri Jan 03 16:39:00 2025 PST | Fri Jan 03 00:00:00 2025 PST
 (0,3) | d2     |           1000 | Fri Jan 03 16:40:00 2025 PST | Sat Jan 04 09:19:00 2025 PST | Sat Jan 04 09:19:00 2025 PST | Fri Jan 03 16:40:00 2025 PST
 (0,4) | d2     |           1000 | Fri Jan 03 00:00:00 2025 PST | Fri Jan 03 16:39:00 2025 PST | Fri Jan 03 16:39:00 2025 PST | Fri Jan 03 00:00:00 2025 PST

-- Status should be COMPRESSED only after compaction
SELECT DISTINCT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics_desc') chunk;
 chunk_status_text 
-------------------
 {COMPRESSED}

-- Insert overlapping data
INSERT INTO metrics_desc
SELECT '2025-01-03'::timestamptz + (i || ' minute')::interval, 'd1', i::float
FROM generate_series(0,1999) i;
INSERT INTO metrics_desc
SELECT '2025-01-03'::timestamptz + (i || ' minute')::interval, 'd2', i::float
FROM generate_series(0,1999) i;
-- 8 batches: 4 per segment, overlapping
SELECT ctid, device, _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_v2_first_time, _ts_meta_v2_last_time
FROM :DESC_CHUNK_FULL_NAME
ORDER BY device, _ts_meta_max_1 DESC;
 ctid  | device | _ts_meta_count |        _ts_meta_min_1        |        _ts_meta_max_1        |    _ts_meta_v2_first_time    |    _ts_meta_v2_last_time     
-------+--------+----------------+------------------------------+------------------------------+------------------------------+------------------------------
 (0,1) | d1     |           1000 | Fri Jan 03 16:40:00 2025 PST | Sat Jan 04 09:19:00 2025 PST | Sat Jan 04 09:19:00 2025 PST | Fri Jan 03 16:40:00 2025 PST
 (0,5) | d1     |           1000 | Fri Jan 03 16:40:00 2025 PST | Sat Jan 04 09:19:00 2025 PST | Sat Jan 04 09:19:00 2025 PST | Fri Jan 03 16:40:00 2025 PST
 (0,2) | d1     |           1000 | Fri Jan 03 00:00:00 2025 PST | Fri Jan 03 16:39:00 2025 PST | Fri Jan 03 16:39:00 2025 PST | Fri Jan 03 00:00:00 2025 PST
 (1,1) | d1     |           1000 | Fri Jan 03 00:00:00 2025 PST | Fri Jan 03 16:39:00 2025 PST | Fri Jan 03 16:39:00 2025 PST | Fri Jan 03 00:00:00 2025 PST
 (0,3) | d2     |           1000 | Fri Jan 03 16:40:00 2025 PST | Sat Jan 04 09:19:00 2025 PST | Sat Jan 04 09:19:00 2025 PST | Fri Jan 03 16:40:00 2025 PST
 (1,2) | d2     |           1000 | Fri Jan 03 16:40:00 2025 PST | Sat Jan 04 09:19:00 2025 PST | Sat Jan 04 09:19:00 2025 PST | Fri Jan 03 16:40:00 2025 PST
 (0,4) | d2     |           1000 | Fri Jan 03 00:00:00 2025 PST | Fri Jan 03 16:39:00 2025 PST | Fri Jan 03 16:39:00 2025 PST | Fri Jan 03 00:00:00 2025 PST
 (1,3) | d2     |           1000 | Fri Jan 03 00:00:00 2025 PST | Fri Jan 03 16:39:00 2025 PST | Fri Jan 03 16:39:00 2025 PST | Fri Jan 03 00:00:00 2025 PST

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

-- compact_chunk should merge overlapping batches with DESC ordering
SELECT _timescaledb_functions.compact_chunk(chunk) FROM show_chunks('metrics_desc') chunk;
             compact_chunk              
----------------------------------------
 _timescaledb_internal._hyper_4_6_chunk

-- New ctids: overlapping batches merged
SELECT ctid, device, _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_v2_first_time, _ts_meta_v2_last_time
FROM :DESC_CHUNK_FULL_NAME
ORDER BY device, _ts_meta_max_1 DESC;
  ctid  | device | _ts_meta_count |        _ts_meta_min_1        |        _ts_meta_max_1        |    _ts_meta_v2_first_time    |    _ts_meta_v2_last_time     
--------+--------+----------------+------------------------------+------------------------------+------------------------------+------------------------------
 (1,4)  | d1     |           1000 | Sat Jan 04 01:00:00 2025 PST | Sat Jan 04 09:19:00 2025 PST | Sat Jan 04 09:19:00 2025 PST | Sat Jan 04 01:00:00 2025 PST
 (1,5)  | d1     |           1000 | Fri Jan 03 16:40:00 2025 PST | Sat Jan 04 00:59:00 2025 PST | Sat Jan 04 00:59:00 2025 PST | Fri Jan 03 16:40:00 2025 PST
 (1,6)  | d1     |           1000 | Fri Jan 03 08:20:00 2025 PST | Fri Jan 03 16:39:00 2025 PST | Fri Jan 03 16:39:00 2025 PST | Fri Jan 03 08:20:00 2025 PST
 (1,7)  | d1     |           1000 | Fri Jan 03 00:00:00 2025 PST | Fri Jan 03 08:19:00 2025 PST | Fri Jan 03 08:19:00 2025 PST | Fri Jan 03 00:00:00 2025 PST
 (1,8)  | d2     |           1000 | Sat Jan 04 01:00:00 2025 PST | Sat Jan 04 09:19:00 2025 PST | Sat Jan 04 09:19:00 2025 PST | Sat Jan 04 01:00:00 2025 PST
 (1,9)  | d2     |           1000 | Fri Jan 03 16:40:00 2025 PST | Sat Jan 04 00:59:00 2025 PST | Sat Jan 04 00:59:00 2025 PST | Fri Jan 03 16:40:00 2025 PST
 (1,10) | d2     |           1000 | Fri Jan 03 08:20:00 2025 PST | Fri Jan 03 16:39:00 2025 PST | Fri Jan 03 16:39:00 2025 PST | Fri Jan 03 08:20:00 2025 PST
 (1,11) | d2     |           1000 | Fri Jan 03 00:00:00 2025 PST | Fri Jan 03 08:19:00 2025 PST | Fri Jan 03 08:19:00 2025 PST | Fri Jan 03 00:00:00 2025 PST

-- Status should be COMPRESSED only
SELECT DISTINCT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics_desc') chunk;
 chunk_status_text 
-------------------
 {COMPRESSED}

-- Verify data is correctly ordered (DESC) within each segment
SELECT device, time FROM metrics_desc WHERE device = 'd1' ORDER BY device, time DESC LIMIT 5;
 device |             time             
--------+------------------------------
 d1     | Sat Jan 04 09:19:00 2025 PST
 d1     | Sat Jan 04 09:19:00 2025 PST
 d1     | Sat Jan 04 09:18:00 2025 PST
 d1     | Sat Jan 04 09:18:00 2025 PST
 d1     | Sat Jan 04 09:17:00 2025 PST

DROP TABLE metrics_desc;
-- compact_chunk with multi-column orderby
-- Tests that overlap detection works correctly when using ORDER BY device, time.
-- The bug: secondary column min/max metadata is a global aggregate across
-- all rows, not scoped to the primary column's value. This causes false
-- negatives where interleaving batches go undetected.
CREATE TABLE metrics_multi (time TIMESTAMPTZ NOT NULL, device TEXT NOT NULL, value float)
WITH (tsdb.hypertable, tsdb.orderby='device,time');
NOTICE:  using column "time" as partitioning column
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;
-- Insert 3 batches with different devices creating boundary ties on col1:
-- Batch 1 (500 rows): device d1..d2, time [00:01..08:20]
-- Batch 2 (500 rows): device d2..d3, time [08:21..16:40]
-- The d2 rows in both batches create a boundary tie on col1 (device).
-- Col2 (time) ranges are non-overlapping within the d2 group → no actual overlap.
INSERT INTO metrics_multi
SELECT '2025-01-03'::timestamptz + (i || ' minute')::interval,
       CASE WHEN i <= 250 THEN 'd1' ELSE 'd2' END,
       i::float
FROM generate_series(1,500) i;
INSERT INTO metrics_multi
SELECT '2025-01-03'::timestamptz + ((500 + i) || ' minute')::interval,
       CASE WHEN i <= 250 THEN 'd2' ELSE 'd3' END,
       (500 + i)::float
FROM generate_series(1,500) i;
-- Get the compressed chunk for metrics_multi
SELECT cs.compress_relid::regclass::text AS "MULTI_CHUNK_FULL_NAME"
FROM _timescaledb_catalog.chunk ch
    JOIN _timescaledb_catalog.compression_settings cs
        ON cs.relid = ch.relid
    JOIN _timescaledb_catalog.hypertable ht ON ch.hypertable_id = ht.id
WHERE ht.table_name = 'metrics_multi'
ORDER BY ch.id LIMIT 1 \gset
-- 2 batches: boundary tie on col1 (device=d2), non-overlapping time ranges
SELECT ctid, _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_v2_first_device, _ts_meta_v2_last_device, _ts_meta_min_2, _ts_meta_max_2, _ts_meta_v2_first_time, _ts_meta_v2_last_time
FROM :MULTI_CHUNK_FULL_NAME
ORDER BY _ts_meta_min_1, _ts_meta_min_2;
 ctid  | _ts_meta_count | _ts_meta_min_1 | _ts_meta_max_1 | _ts_meta_v2_first_device | _ts_meta_v2_last_device |        _ts_meta_min_2        |        _ts_meta_max_2        |    _ts_meta_v2_first_time    |    _ts_meta_v2_last_time     
-------+----------------+----------------+----------------+--------------------------+-------------------------+------------------------------+------------------------------+------------------------------+------------------------------
 (0,1) |            500 | d1             | d2             | d1                       | d2                      | Fri Jan 03 00:01:00 2025 PST | Fri Jan 03 08:20:00 2025 PST | Fri Jan 03 00:01:00 2025 PST | Fri Jan 03 08:20:00 2025 PST
 (0,2) |            500 | d2             | d3             | d2                       | d3                      | Fri Jan 03 08:21:00 2025 PST | Fri Jan 03 16:40:00 2025 PST | Fri Jan 03 08:21:00 2025 PST | Fri Jan 03 16:40:00 2025 PST

-- compact_chunk: boundary tie on col1, but no overlap on col2 (time) — should be a no-op
SELECT _timescaledb_functions.compact_chunk(chunk) FROM show_chunks('metrics_multi') chunk;
             compact_chunk              
----------------------------------------
 _timescaledb_internal._hyper_5_7_chunk

-- Same ctids: nothing was rewritten
SELECT ctid, _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_v2_first_device, _ts_meta_v2_last_device, _ts_meta_min_2, _ts_meta_max_2, _ts_meta_v2_first_time, _ts_meta_v2_last_time
FROM :MULTI_CHUNK_FULL_NAME
ORDER BY _ts_meta_min_1, _ts_meta_min_2;
 ctid  | _ts_meta_count | _ts_meta_min_1 | _ts_meta_max_1 | _ts_meta_v2_first_device | _ts_meta_v2_last_device |        _ts_meta_min_2        |        _ts_meta_max_2        |    _ts_meta_v2_first_time    |    _ts_meta_v2_last_time     
-------+----------------+----------------+----------------+--------------------------+-------------------------+------------------------------+------------------------------+------------------------------+------------------------------
 (0,1) |            500 | d1             | d2             | d1                       | d2                      | Fri Jan 03 00:01:00 2025 PST | Fri Jan 03 08:20:00 2025 PST | Fri Jan 03 00:01:00 2025 PST | Fri Jan 03 08:20:00 2025 PST
 (0,2) |            500 | d2             | d3             | d2                       | d3                      | Fri Jan 03 08:21:00 2025 PST | Fri Jan 03 16:40:00 2025 PST | Fri Jan 03 08:21:00 2025 PST | Fri Jan 03 16:40:00 2025 PST

-- Now insert truly overlapping data within the d2 device group:
-- Batch 3 (500 rows): device d2, time range overlaps with both existing d2 ranges
INSERT INTO metrics_multi
SELECT '2025-01-03'::timestamptz + ((250 + i) || ' minute')::interval, 'd2', (250 + i)::float
FROM generate_series(1,500) i;
-- 3 batches: the new d2 batch overlaps with the d2 portions of existing batches
SELECT ctid, _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_v2_first_device, _ts_meta_v2_last_device, _ts_meta_min_2, _ts_meta_max_2, _ts_meta_v2_first_time, _ts_meta_v2_last_time
FROM :MULTI_CHUNK_FULL_NAME
ORDER BY _ts_meta_min_1, _ts_meta_min_2;
 ctid  | _ts_meta_count | _ts_meta_min_1 | _ts_meta_max_1 | _ts_meta_v2_first_device | _ts_meta_v2_last_device |        _ts_meta_min_2        |        _ts_meta_max_2        |    _ts_meta_v2_first_time    |    _ts_meta_v2_last_time     
-------+----------------+----------------+----------------+--------------------------+-------------------------+------------------------------+------------------------------+------------------------------+------------------------------
 (0,1) |            500 | d1             | d2             | d1                       | d2                      | Fri Jan 03 00:01:00 2025 PST | Fri Jan 03 08:20:00 2025 PST | Fri Jan 03 00:01:00 2025 PST | Fri Jan 03 08:20:00 2025 PST
 (0,3) |            500 | d2             | d2             | d2                       | d2                      | Fri Jan 03 04:11:00 2025 PST | Fri Jan 03 12:30:00 2025 PST | Fri Jan 03 04:11:00 2025 PST | Fri Jan 03 12:30:00 2025 PST
 (0,2) |            500 | d2             | d3             | d2                       | d3                      | Fri Jan 03 08:21:00 2025 PST | Fri Jan 03 16:40:00 2025 PST | Fri Jan 03 08:21:00 2025 PST | Fri Jan 03 16:40:00 2025 PST

-- compact_chunk: boundary tie on col1 with actual overlap on col2 — must detect and merge
SELECT _timescaledb_functions.compact_chunk(chunk) FROM show_chunks('metrics_multi') chunk;
             compact_chunk              
----------------------------------------
 _timescaledb_internal._hyper_5_7_chunk

-- New ctids: overlapping batches merged
SELECT ctid, _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_v2_first_device, _ts_meta_v2_last_device, _ts_meta_min_2, _ts_meta_max_2, _ts_meta_v2_first_time, _ts_meta_v2_last_time
FROM :MULTI_CHUNK_FULL_NAME
ORDER BY _ts_meta_min_1, _ts_meta_min_2;
 ctid  | _ts_meta_count | _ts_meta_min_1 | _ts_meta_max_1 | _ts_meta_v2_first_device | _ts_meta_v2_last_device |        _ts_meta_min_2        |        _ts_meta_max_2        |    _ts_meta_v2_first_time    |    _ts_meta_v2_last_time     
-------+----------------+----------------+----------------+--------------------------+-------------------------+------------------------------+------------------------------+------------------------------+------------------------------
 (0,4) |           1000 | d1             | d2             | d1                       | d2                      | Fri Jan 03 00:01:00 2025 PST | Fri Jan 03 10:25:00 2025 PST | Fri Jan 03 00:01:00 2025 PST | Fri Jan 03 10:25:00 2025 PST
 (0,5) |            500 | d2             | d3             | d2                       | d3                      | Fri Jan 03 10:26:00 2025 PST | Fri Jan 03 16:40:00 2025 PST | Fri Jan 03 10:26:00 2025 PST | Fri Jan 03 16:40:00 2025 PST

-- Total row count preserved
SELECT count(*) FROM metrics_multi;
 count 
-------
  1500

DROP TABLE metrics_multi;
-- compact_chunk with multi-column orderby, second column DESC
-- Same boundary-tie logic but the secondary column uses descending order.
-- The sort operator for col2 is now ">" instead of "<", so the boundary
-- decompression must use the correct comparator.
CREATE TABLE metrics_multi_desc (time TIMESTAMPTZ NOT NULL, device TEXT NOT NULL, value float)
WITH (tsdb.hypertable, tsdb.orderby='device,time DESC');
NOTICE:  using column "time" as partitioning column
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;
-- Insert 2 batches with multiple devices, DESC time ordering:
-- Batch 1 (500 rows): device d2..d3, time [08:21..16:40] DESC
-- Batch 2 (500 rows): device d1..d2, time [00:01..08:20] DESC
-- The d2 rows create a boundary tie on col1.
-- With DESC, batch 1's d2 trailing edge (min time=08:21) and batch 2's
-- d2 leading edge (max time=08:20) are non-overlapping → no actual overlap.
INSERT INTO metrics_multi_desc
SELECT '2025-01-03'::timestamptz + ((500 + i) || ' minute')::interval,
       CASE WHEN i <= 250 THEN 'd2' ELSE 'd3' END,
       (500 + i)::float
FROM generate_series(1,500) i;
INSERT INTO metrics_multi_desc
SELECT '2025-01-03'::timestamptz + (i || ' minute')::interval,
       CASE WHEN i <= 250 THEN 'd1' ELSE 'd2' END,
       i::float
FROM generate_series(1,500) i;
-- Get the compressed chunk
SELECT cs.compress_relid::regclass::text AS "MULTI_DESC_CHUNK"
FROM _timescaledb_catalog.chunk ch
    JOIN _timescaledb_catalog.compression_settings cs
        ON cs.relid = ch.relid
    JOIN _timescaledb_catalog.hypertable ht ON ch.hypertable_id = ht.id
WHERE ht.table_name = 'metrics_multi_desc'
ORDER BY ch.id LIMIT 1 \gset
-- 2 batches: boundary tie on col1 (device=d2), non-overlapping DESC time ranges
SELECT ctid, _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_v2_first_device, _ts_meta_v2_last_device, _ts_meta_min_2, _ts_meta_max_2, _ts_meta_v2_first_time, _ts_meta_v2_last_time
FROM :MULTI_DESC_CHUNK
ORDER BY _ts_meta_min_1, _ts_meta_max_2 DESC;
 ctid  | _ts_meta_count | _ts_meta_min_1 | _ts_meta_max_1 | _ts_meta_v2_first_device | _ts_meta_v2_last_device |        _ts_meta_min_2        |        _ts_meta_max_2        |    _ts_meta_v2_first_time    |    _ts_meta_v2_last_time     
-------+----------------+----------------+----------------+--------------------------+-------------------------+------------------------------+------------------------------+------------------------------+------------------------------
 (0,2) |            500 | d1             | d2             | d1                       | d2                      | Fri Jan 03 00:01:00 2025 PST | Fri Jan 03 08:20:00 2025 PST | Fri Jan 03 04:10:00 2025 PST | Fri Jan 03 04:11:00 2025 PST
 (0,1) |            500 | d2             | d3             | d2                       | d3                      | Fri Jan 03 08:21:00 2025 PST | Fri Jan 03 16:40:00 2025 PST | Fri Jan 03 12:30:00 2025 PST | Fri Jan 03 12:31:00 2025 PST

-- compact_chunk: boundary tie on col1, no overlap on col2 (DESC) — should be a no-op
SELECT _timescaledb_functions.compact_chunk(chunk) FROM show_chunks('metrics_multi_desc') chunk;
             compact_chunk              
----------------------------------------
 _timescaledb_internal._hyper_6_8_chunk

-- Same ctids: nothing was rewritten
SELECT ctid, _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_v2_first_device, _ts_meta_v2_last_device, _ts_meta_min_2, _ts_meta_max_2, _ts_meta_v2_first_time, _ts_meta_v2_last_time
FROM :MULTI_DESC_CHUNK
ORDER BY _ts_meta_min_1, _ts_meta_max_2 DESC;
 ctid  | _ts_meta_count | _ts_meta_min_1 | _ts_meta_max_1 | _ts_meta_v2_first_device | _ts_meta_v2_last_device |        _ts_meta_min_2        |        _ts_meta_max_2        |    _ts_meta_v2_first_time    |    _ts_meta_v2_last_time     
-------+----------------+----------------+----------------+--------------------------+-------------------------+------------------------------+------------------------------+------------------------------+------------------------------
 (0,3) |           1000 | d1             | d3             | d1                       | d3                      | Fri Jan 03 00:01:00 2025 PST | Fri Jan 03 16:40:00 2025 PST | Fri Jan 03 04:10:00 2025 PST | Fri Jan 03 12:31:00 2025 PST

-- Now insert overlapping data within the d2 device group
INSERT INTO metrics_multi_desc
SELECT '2025-01-03'::timestamptz + ((250 + i) || ' minute')::interval, 'd2', (250 + i)::float
FROM generate_series(1,500) i;
-- 3 batches: the new d2 batch overlaps with existing d2 portions
SELECT ctid, _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_v2_first_device, _ts_meta_v2_last_device, _ts_meta_min_2, _ts_meta_max_2, _ts_meta_v2_first_time, _ts_meta_v2_last_time
FROM :MULTI_DESC_CHUNK
ORDER BY _ts_meta_min_1, _ts_meta_max_2 DESC;
 ctid  | _ts_meta_count | _ts_meta_min_1 | _ts_meta_max_1 | _ts_meta_v2_first_device | _ts_meta_v2_last_device |        _ts_meta_min_2        |        _ts_meta_max_2        |    _ts_meta_v2_first_time    |    _ts_meta_v2_last_time     
-------+----------------+----------------+----------------+--------------------------+-------------------------+------------------------------+------------------------------+------------------------------+------------------------------
 (0,3) |           1000 | d1             | d3             | d1                       | d3                      | Fri Jan 03 00:01:00 2025 PST | Fri Jan 03 16:40:00 2025 PST | Fri Jan 03 04:10:00 2025 PST | Fri Jan 03 12:31:00 2025 PST
 (0,4) |            500 | d2             | d2             | d2                       | d2                      | Fri Jan 03 04:11:00 2025 PST | Fri Jan 03 12:30:00 2025 PST | Fri Jan 03 12:30:00 2025 PST | Fri Jan 03 04:11:00 2025 PST

-- compact_chunk: boundary tie on col1, actual overlap on col2 (DESC) — must merge
SELECT _timescaledb_functions.compact_chunk(chunk) FROM show_chunks('metrics_multi_desc') chunk;
             compact_chunk              
----------------------------------------
 _timescaledb_internal._hyper_6_8_chunk

-- New ctids: overlapping batches merged
SELECT ctid, _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_v2_first_device, _ts_meta_v2_last_device, _ts_meta_min_2, _ts_meta_max_2, _ts_meta_v2_first_time, _ts_meta_v2_last_time
FROM :MULTI_DESC_CHUNK
ORDER BY _ts_meta_min_1, _ts_meta_max_2 DESC;
 ctid  | _ts_meta_count | _ts_meta_min_1 | _ts_meta_max_1 | _ts_meta_v2_first_device | _ts_meta_v2_last_device |        _ts_meta_min_2        |        _ts_meta_max_2        |    _ts_meta_v2_first_time    |    _ts_meta_v2_last_time     
-------+----------------+----------------+----------------+--------------------------+-------------------------+------------------------------+------------------------------+------------------------------+------------------------------
 (0,5) |           1000 | d1             | d2             | d1                       | d2                      | Fri Jan 03 00:01:00 2025 PST | Fri Jan 03 12:30:00 2025 PST | Fri Jan 03 04:10:00 2025 PST | Fri Jan 03 06:16:00 2025 PST
 (0,6) |            500 | d2             | d3             | d2                       | d3                      | Fri Jan 03 04:11:00 2025 PST | Fri Jan 03 16:40:00 2025 PST | Fri Jan 03 06:15:00 2025 PST | Fri Jan 03 12:31:00 2025 PST

-- Total row count preserved
SELECT count(*) FROM metrics_multi_desc;
 count 
-------
  1500

DROP TABLE metrics_multi_desc;
-- compact_chunk with segmentby + multi-column orderby + nullable orderby column
-- Combines all three features:
--   segmentby='device'         multiple segments processed independently
--   orderby='time,value'       multi-column overlap detection with boundary ties
--   'value' is nullable        boundary tie NULL values must respect NULL ordering
CREATE TABLE metrics_combined (time TIMESTAMPTZ NOT NULL, device TEXT, value float)
WITH (tsdb.hypertable, tsdb.orderby='time,value', tsdb.segmentby='device');
NOTICE:  using column "time" as partitioning column
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;
-- Insert non-null data for two segments: 1000 rows each, non-overlapping
INSERT INTO metrics_combined
SELECT '2025-01-03'::timestamptz + (i || ' minute')::interval, 'd1', i::float
FROM generate_series(1,1000) i;
INSERT INTO metrics_combined
SELECT '2025-01-03'::timestamptz + (i || ' minute')::interval, 'd2', (i + 1000)::float
FROM generate_series(1,1000) i;
-- Get the compressed chunk
SELECT cs.compress_relid::regclass::text AS "COMBINED_CHUNK"
FROM _timescaledb_catalog.chunk ch
    JOIN _timescaledb_catalog.compression_settings cs
        ON cs.relid = ch.relid
    JOIN _timescaledb_catalog.hypertable ht ON ch.hypertable_id = ht.id
WHERE ht.table_name = 'metrics_combined'
ORDER BY ch.id LIMIT 1 \gset
-- 2 batches: 1 per segment, no overlaps, no nulls
SELECT ctid, device, _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_v2_first_time, _ts_meta_v2_last_time, _ts_meta_min_2, _ts_meta_max_2, _ts_meta_v2_first_value, _ts_meta_v2_last_value
FROM :COMBINED_CHUNK
ORDER BY device, _ts_meta_min_1;
 ctid  | device | _ts_meta_count |        _ts_meta_min_1        |        _ts_meta_max_1        |    _ts_meta_v2_first_time    |    _ts_meta_v2_last_time     | _ts_meta_min_2 | _ts_meta_max_2 | _ts_meta_v2_first_value | _ts_meta_v2_last_value 
-------+--------+----------------+------------------------------+------------------------------+------------------------------+------------------------------+----------------+----------------+-------------------------+------------------------
 (0,1) | d1     |           1000 | Fri Jan 03 00:01:00 2025 PST | Fri Jan 03 16:40:00 2025 PST | Fri Jan 03 00:01:00 2025 PST | Fri Jan 03 16:40:00 2025 PST |              1 |           1000 |                       1 |                   1000
 (0,2) | d2     |           1000 | Fri Jan 03 00:01:00 2025 PST | Fri Jan 03 16:40:00 2025 PST | Fri Jan 03 00:01:00 2025 PST | Fri Jan 03 16:40:00 2025 PST |           1001 |           2000 |                    1001 |                   2000

-- No overlaps, no nulls: compact_chunk is a no-op
SELECT _timescaledb_functions.compact_chunk(chunk) FROM show_chunks('metrics_combined') chunk;
             compact_chunk              
----------------------------------------
 _timescaledb_internal._hyper_7_9_chunk

-- Same ctids as before: nothing was rewritten
SELECT ctid, device, _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_v2_first_time, _ts_meta_v2_last_time, _ts_meta_min_2, _ts_meta_max_2, _ts_meta_v2_first_value, _ts_meta_v2_last_value
FROM :COMBINED_CHUNK
ORDER BY device, _ts_meta_min_1;
 ctid  | device | _ts_meta_count |        _ts_meta_min_1        |        _ts_meta_max_1        |    _ts_meta_v2_first_time    |    _ts_meta_v2_last_time     | _ts_meta_min_2 | _ts_meta_max_2 | _ts_meta_v2_first_value | _ts_meta_v2_last_value 
-------+--------+----------------+------------------------------+------------------------------+------------------------------+------------------------------+----------------+----------------+-------------------------+------------------------
 (0,1) | d1     |           1000 | Fri Jan 03 00:01:00 2025 PST | Fri Jan 03 16:40:00 2025 PST | Fri Jan 03 00:01:00 2025 PST | Fri Jan 03 16:40:00 2025 PST |              1 |           1000 |                       1 |                   1000
 (0,2) | d2     |           1000 | Fri Jan 03 00:01:00 2025 PST | Fri Jan 03 16:40:00 2025 PST | Fri Jan 03 00:01:00 2025 PST | Fri Jan 03 16:40:00 2025 PST |           1001 |           2000 |                    1001 |                   2000

-- Status should be COMPRESSED only
SELECT DISTINCT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics_combined') chunk;
 chunk_status_text 
-------------------
 {COMPRESSED}

-- Insert overlapping data with NULLs in the nullable orderby column (value)
-- for both segments. Every 5th row has NULL value.
INSERT INTO metrics_combined
SELECT '2025-01-03'::timestamptz + (i || ' minute')::interval,
       'd1',
       CASE WHEN i % 5 = 0 THEN NULL ELSE (i + 2000)::float END
FROM generate_series(1,1000) i;
INSERT INTO metrics_combined
SELECT '2025-01-03'::timestamptz + (i || ' minute')::interval,
       'd2',
       CASE WHEN i % 5 = 0 THEN NULL ELSE (i + 3000)::float END
FROM generate_series(1,1000) i;
-- 4 batches: original + overlapping with nulls. Both segments overlap on time col1.
SELECT ctid, device, _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_v2_first_time, _ts_meta_v2_last_time, _ts_meta_min_2, _ts_meta_max_2, _ts_meta_v2_first_value, _ts_meta_v2_last_value
FROM :COMBINED_CHUNK
ORDER BY device, _ts_meta_min_1;
 ctid  | device | _ts_meta_count |        _ts_meta_min_1        |        _ts_meta_max_1        |    _ts_meta_v2_first_time    |    _ts_meta_v2_last_time     | _ts_meta_min_2 | _ts_meta_max_2 | _ts_meta_v2_first_value | _ts_meta_v2_last_value 
-------+--------+----------------+------------------------------+------------------------------+------------------------------+------------------------------+----------------+----------------+-------------------------+------------------------
 (0,1) | d1     |           1000 | Fri Jan 03 00:01:00 2025 PST | Fri Jan 03 16:40:00 2025 PST | Fri Jan 03 00:01:00 2025 PST | Fri Jan 03 16:40:00 2025 PST |              1 |           1000 |                       1 |                   1000
 (0,3) | d1     |           1000 | Fri Jan 03 00:01:00 2025 PST | Fri Jan 03 16:40:00 2025 PST | Fri Jan 03 00:01:00 2025 PST | Fri Jan 03 16:40:00 2025 PST |           2001 |           2999 |                    2001 |                       
 (0,2) | d2     |           1000 | Fri Jan 03 00:01:00 2025 PST | Fri Jan 03 16:40:00 2025 PST | Fri Jan 03 00:01:00 2025 PST | Fri Jan 03 16:40:00 2025 PST |           1001 |           2000 |                    1001 |                   2000
 (0,4) | d2     |           1000 | Fri Jan 03 00:01:00 2025 PST | Fri Jan 03 16:40:00 2025 PST | Fri Jan 03 00:01:00 2025 PST | Fri Jan 03 16:40:00 2025 PST |           3001 |           3999 |                    3001 |                       

-- Status should be UNORDERED
SELECT DISTINCT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics_combined') chunk;
   chunk_status_text    
------------------------
 {COMPRESSED,UNORDERED}

-- compact_chunk must:
-- 1. Detect overlaps per segment via multi-column boundary-tie logic
-- 2. Recompress overlapping rows into ordered batches
SELECT _timescaledb_functions.compact_chunk(chunk) FROM show_chunks('metrics_combined') chunk;
             compact_chunk              
----------------------------------------
 _timescaledb_internal._hyper_7_9_chunk

-- After compaction: new ctids, overlapping batches merged and re-sorted
SELECT ctid, device, _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_v2_first_time, _ts_meta_v2_last_time, _ts_meta_min_2, _ts_meta_max_2, _ts_meta_v2_first_value, _ts_meta_v2_last_value
FROM :COMBINED_CHUNK
ORDER BY device, _ts_meta_min_1;
 ctid  | device | _ts_meta_count |        _ts_meta_min_1        |        _ts_meta_max_1        |    _ts_meta_v2_first_time    |    _ts_meta_v2_last_time     | _ts_meta_min_2 | _ts_meta_max_2 | _ts_meta_v2_first_value | _ts_meta_v2_last_value 
-------+--------+----------------+------------------------------+------------------------------+------------------------------+------------------------------+----------------+----------------+-------------------------+------------------------
 (0,5) | d1     |           1000 | Fri Jan 03 00:01:00 2025 PST | Fri Jan 03 08:20:00 2025 PST | Fri Jan 03 00:01:00 2025 PST | Fri Jan 03 08:20:00 2025 PST |              1 |           2499 |                       1 |                       
 (0,6) | d1     |           1000 | Fri Jan 03 08:21:00 2025 PST | Fri Jan 03 16:40:00 2025 PST | Fri Jan 03 08:21:00 2025 PST | Fri Jan 03 16:40:00 2025 PST |            501 |           2999 |                     501 |                       
 (0,7) | d2     |           1000 | Fri Jan 03 00:01:00 2025 PST | Fri Jan 03 08:20:00 2025 PST | Fri Jan 03 00:01:00 2025 PST | Fri Jan 03 08:20:00 2025 PST |           1001 |           3499 |                    1001 |                       
 (0,8) | d2     |           1000 | Fri Jan 03 08:21:00 2025 PST | Fri Jan 03 16:40:00 2025 PST | Fri Jan 03 08:21:00 2025 PST | Fri Jan 03 16:40:00 2025 PST |           1501 |           3999 |                    1501 |                       

-- Status should be COMPRESSED only (UNORDERED cleared).
-- NULLs in the secondary orderby column (value) just sort within the batch per
-- NULLS FIRST/LAST; only the first orderby column (time, NOT NULL) affects
-- batch placement.
SELECT DISTINCT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics_combined') chunk;
 chunk_status_text 
-------------------
 {COMPRESSED}

-- Total row count preserved
SELECT count(*) FROM metrics_combined;
 count 
-------
  4000

-- NULL rows preserved per segment
SELECT device, count(*) AS null_value_count FROM metrics_combined WHERE value IS NULL GROUP BY device ORDER BY device;
 device | null_value_count 
--------+------------------
 d1     |              200
 d2     |              200

-- Data integrity per segment
SELECT device, count(*) FROM metrics_combined GROUP BY device ORDER BY device;
 device | count 
--------+-------
 d1     |  2000
 d2     |  2000

-- Verify ordering is correct within segment d1
SELECT device, time, value FROM metrics_combined
WHERE device = 'd1' ORDER BY time, value LIMIT 5;
 device |             time             | value 
--------+------------------------------+-------
 d1     | Fri Jan 03 00:01:00 2025 PST |     1
 d1     | Fri Jan 03 00:01:00 2025 PST |  2001
 d1     | Fri Jan 03 00:02:00 2025 PST |     2
 d1     | Fri Jan 03 00:02:00 2025 PST |  2002
 d1     | Fri Jan 03 00:03:00 2025 PST |     3

SELECT device, time, value FROM metrics_combined
WHERE device = 'd1' AND value IS NULL ORDER BY time LIMIT 5;
 device |             time             | value 
--------+------------------------------+-------
 d1     | Fri Jan 03 00:05:00 2025 PST |      
 d1     | Fri Jan 03 00:10:00 2025 PST |      
 d1     | Fri Jan 03 00:15:00 2025 PST |      
 d1     | Fri Jan 03 00:20:00 2025 PST |      
 d1     | Fri Jan 03 00:25:00 2025 PST |      

DROP TABLE metrics_combined;
-- compact_chunk with nullable first orderby column (NULLS LAST, default)
-- A NULL boundary value makes a mixed-null batch overlap its neighbor, so the
-- overlap merge re-sorts both batches; the NULLs land at the end (NULLS LAST).
-- A mixed-null batch that overlaps nothing is already ordered and left untouched.
CREATE TABLE metrics_nulls_last (time TIMESTAMPTZ NOT NULL, device TEXT, value float)
WITH (tsdb.hypertable, tsdb.orderby='value,time', tsdb.segmentby='device');
NOTICE:  using column "time" as partitioning column
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;
-- Insert non-null data for two segments
INSERT INTO metrics_nulls_last
SELECT '2025-01-03'::timestamptz + (i || ' minute')::interval, 'd1', i::float
FROM generate_series(1,1000) i;
INSERT INTO metrics_nulls_last
SELECT '2025-01-03'::timestamptz + (i || ' minute')::interval, 'd2', (i + 1000)::float
FROM generate_series(1,1000) i;
SELECT cs.compress_relid::regclass::text AS "NULLS_LAST_CHUNK"
FROM _timescaledb_catalog.chunk ch
    JOIN _timescaledb_catalog.compression_settings cs
        ON cs.relid = ch.relid
    JOIN _timescaledb_catalog.hypertable ht ON ch.hypertable_id = ht.id
WHERE ht.table_name = 'metrics_nulls_last'
ORDER BY ch.id LIMIT 1 \gset
-- 2 non-overlapping batches, no nulls
SELECT ctid, device, _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_v2_first_value, _ts_meta_v2_last_value
FROM :NULLS_LAST_CHUNK
ORDER BY device, _ts_meta_min_1;
 ctid  | device | _ts_meta_count | _ts_meta_min_1 | _ts_meta_max_1 | _ts_meta_v2_first_value | _ts_meta_v2_last_value 
-------+--------+----------------+----------------+----------------+-------------------------+------------------------
 (0,1) | d1     |           1000 |              1 |           1000 |                       1 |                   1000
 (0,2) | d2     |           1000 |           1001 |           2000 |                    1001 |                   2000

-- Insert overlapping data with NULLs in value (first orderby column)
INSERT INTO metrics_nulls_last
SELECT '2025-01-03'::timestamptz + (i || ' minute')::interval,
       'd1',
       CASE WHEN i % 5 = 0 THEN NULL ELSE (i + 2000)::float END
FROM generate_series(1,1000) i;
INSERT INTO metrics_nulls_last
SELECT '2025-01-03'::timestamptz + (i || ' minute')::interval,
       'd2',
       CASE WHEN i % 5 = 0 THEN NULL ELSE (i + 3000)::float END
FROM generate_series(1,1000) i;
-- 4 batches with overlaps and nulls in first orderby column
SELECT ctid, device, _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_v2_first_value, _ts_meta_v2_last_value
FROM :NULLS_LAST_CHUNK
ORDER BY device, _ts_meta_min_1;
 ctid  | device | _ts_meta_count | _ts_meta_min_1 | _ts_meta_max_1 | _ts_meta_v2_first_value | _ts_meta_v2_last_value 
-------+--------+----------------+----------------+----------------+-------------------------+------------------------
 (0,1) | d1     |           1000 |              1 |           1000 |                       1 |                   1000
 (0,3) | d1     |           1000 |           2001 |           2999 |                    2001 |                       
 (0,2) | d2     |           1000 |           1001 |           2000 |                    1001 |                   2000
 (0,4) | d2     |           1000 |           3001 |           3999 |                    3001 |                       

SELECT _timescaledb_functions.compact_chunk(chunk) FROM show_chunks('metrics_nulls_last') chunk;
              compact_chunk              
-----------------------------------------
 _timescaledb_internal._hyper_8_10_chunk

-- After compaction: the mixed-null batch does not overlap its neighbor, so it
-- stays as-is (nulls already sort to the end of the batch with NULLS LAST)
SELECT ctid, device, _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_v2_first_value, _ts_meta_v2_last_value
FROM :NULLS_LAST_CHUNK
ORDER BY device, _ts_meta_min_1;
 ctid  | device | _ts_meta_count | _ts_meta_min_1 | _ts_meta_max_1 | _ts_meta_v2_first_value | _ts_meta_v2_last_value 
-------+--------+----------------+----------------+----------------+-------------------------+------------------------
 (0,1) | d1     |           1000 |              1 |           1000 |                       1 |                   1000
 (0,3) | d1     |           1000 |           2001 |           2999 |                    2001 |                       
 (0,2) | d2     |           1000 |           1001 |           2000 |                    1001 |                   2000
 (0,4) | d2     |           1000 |           3001 |           3999 |                    3001 |                       

-- UNORDERED cleared: NULLs fold into the end of the segment (NULLS LAST)
SELECT DISTINCT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics_nulls_last') chunk;
 chunk_status_text 
-------------------
 {COMPRESSED}

-- Data integrity
SELECT count(*) FROM metrics_nulls_last;
 count 
-------
  4000

SELECT device, count(*) FROM metrics_nulls_last GROUP BY device ORDER BY device;
 device | count 
--------+-------
 d1     |  2000
 d2     |  2000

SELECT device, count(*) AS null_count FROM metrics_nulls_last WHERE value IS NULL GROUP BY device ORDER BY device;
 device | null_count 
--------+------------
 d1     |        200
 d2     |        200

-- Now test the overlap path with NULLs: insert data that truly overlaps
-- on the value range AND contains NULLs, with a nullable first orderby column.
-- The NULL rows must survive the overlap merge and end up ordered correctly.
INSERT INTO metrics_nulls_last
SELECT '2025-01-03'::timestamptz + ((2000 + i) || ' minute')::interval,
       'd1',
       CASE WHEN i % 5 = 0 THEN NULL ELSE (i + 500)::float END
FROM generate_series(1,500) i;
-- Batches before overlap compaction
SELECT ctid, device, _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_v2_first_value, _ts_meta_v2_last_value
FROM :NULLS_LAST_CHUNK
WHERE device = 'd1'
ORDER BY _ts_meta_min_1 NULLS LAST;
 ctid  | device | _ts_meta_count | _ts_meta_min_1 | _ts_meta_max_1 | _ts_meta_v2_first_value | _ts_meta_v2_last_value 
-------+--------+----------------+----------------+----------------+-------------------------+------------------------
 (0,1) | d1     |           1000 |              1 |           1000 |                       1 |                   1000
 (0,5) | d1     |            500 |            501 |            999 |                     501 |                       
 (0,3) | d1     |           1000 |           2001 |           2999 |                    2001 |                       

-- This overlaps with existing d1 data (value 501..900 overlaps with 1..1000 and 2001..2999)
SELECT _timescaledb_functions.compact_chunk(chunk) FROM show_chunks('metrics_nulls_last') chunk;
              compact_chunk              
-----------------------------------------
 _timescaledb_internal._hyper_8_10_chunk

-- After overlap merge: NULLs from overlapping batches preserved
SELECT ctid, device, _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_v2_first_value, _ts_meta_v2_last_value
FROM :NULLS_LAST_CHUNK
WHERE device = 'd1'
ORDER BY _ts_meta_min_1 NULLS LAST;
 ctid  | device | _ts_meta_count | _ts_meta_min_1 | _ts_meta_max_1 | _ts_meta_v2_first_value | _ts_meta_v2_last_value 
-------+--------+----------------+----------------+----------------+-------------------------+------------------------
 (0,6) | d1     |           1000 |              1 |            778 |                       1 |                    778
 (0,7) | d1     |           1000 |            778 |           2749 |                     778 |                   2749
 (0,8) | d1     |            500 |           2751 |           2999 |                    2751 |                       

-- Data integrity: all rows including NULLs from the overlap merge must survive
SELECT count(*) FROM metrics_nulls_last WHERE device = 'd1';
 count 
-------
  2500

SELECT count(*) AS null_count FROM metrics_nulls_last WHERE device = 'd1' AND value IS NULL;
 null_count 
------------
        300

DROP TABLE metrics_nulls_last;
-- compact_chunk with nullable first orderby column (NULLS FIRST)
-- With NULLS FIRST, the re-sort places NULLs at the start of each segment, so a
-- null-containing batch is already correctly positioned.
CREATE TABLE metrics_nulls_first (time TIMESTAMPTZ NOT NULL, device TEXT, value float)
WITH (tsdb.hypertable, tsdb.orderby='value NULLS FIRST,time', tsdb.segmentby='device');
NOTICE:  using column "time" as partitioning column
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;
-- Insert non-null data for two segments
INSERT INTO metrics_nulls_first
SELECT '2025-01-03'::timestamptz + (i || ' minute')::interval, 'd1', i::float
FROM generate_series(1,1000) i;
INSERT INTO metrics_nulls_first
SELECT '2025-01-03'::timestamptz + (i || ' minute')::interval, 'd2', (i + 1000)::float
FROM generate_series(1,1000) i;
SELECT cs.compress_relid::regclass::text AS "NULLS_FIRST_CHUNK"
FROM _timescaledb_catalog.chunk ch
    JOIN _timescaledb_catalog.compression_settings cs
        ON cs.relid = ch.relid
    JOIN _timescaledb_catalog.hypertable ht ON ch.hypertable_id = ht.id
WHERE ht.table_name = 'metrics_nulls_first'
ORDER BY ch.id LIMIT 1 \gset
-- 2 non-overlapping batches, no nulls
SELECT ctid, device, _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_v2_first_value, _ts_meta_v2_last_value
FROM :NULLS_FIRST_CHUNK
ORDER BY device, _ts_meta_min_1;
 ctid  | device | _ts_meta_count | _ts_meta_min_1 | _ts_meta_max_1 | _ts_meta_v2_first_value | _ts_meta_v2_last_value 
-------+--------+----------------+----------------+----------------+-------------------------+------------------------
 (0,1) | d1     |           1000 |              1 |           1000 |                       1 |                   1000
 (0,2) | d2     |           1000 |           1001 |           2000 |                    1001 |                   2000

-- Insert overlapping data with NULLs in value (first orderby column)
INSERT INTO metrics_nulls_first
SELECT '2025-01-03'::timestamptz + (i || ' minute')::interval,
       'd1',
       CASE WHEN i % 5 = 0 THEN NULL ELSE (i + 2000)::float END
FROM generate_series(1,1000) i;
INSERT INTO metrics_nulls_first
SELECT '2025-01-03'::timestamptz + (i || ' minute')::interval,
       'd2',
       CASE WHEN i % 5 = 0 THEN NULL ELSE (i + 3000)::float END
FROM generate_series(1,1000) i;
-- 4 batches with overlaps and nulls in first orderby column
SELECT ctid, device, _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_v2_first_value, _ts_meta_v2_last_value
FROM :NULLS_FIRST_CHUNK
ORDER BY device, _ts_meta_min_1;
 ctid  | device | _ts_meta_count | _ts_meta_min_1 | _ts_meta_max_1 | _ts_meta_v2_first_value | _ts_meta_v2_last_value 
-------+--------+----------------+----------------+----------------+-------------------------+------------------------
 (0,1) | d1     |           1000 |              1 |           1000 |                       1 |                   1000
 (0,3) | d1     |           1000 |           2001 |           2999 |                         |                   2999
 (0,2) | d2     |           1000 |           1001 |           2000 |                    1001 |                   2000
 (0,4) | d2     |           1000 |           3001 |           3999 |                         |                   3999

SELECT _timescaledb_functions.compact_chunk(chunk) FROM show_chunks('metrics_nulls_first') chunk;
              compact_chunk              
-----------------------------------------
 _timescaledb_internal._hyper_9_11_chunk

-- After compaction: with NULLS FIRST, null batches at segment start are fine
SELECT ctid, device, _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_v2_first_value, _ts_meta_v2_last_value
FROM :NULLS_FIRST_CHUNK
ORDER BY device, _ts_meta_min_1;
 ctid  | device | _ts_meta_count | _ts_meta_min_1 | _ts_meta_max_1 | _ts_meta_v2_first_value | _ts_meta_v2_last_value 
-------+--------+----------------+----------------+----------------+-------------------------+------------------------
 (0,5) | d1     |           1000 |              1 |            800 |                         |                    800
 (0,6) | d1     |           1000 |            801 |           2999 |                     801 |                   2999
 (0,7) | d2     |           1000 |           1001 |           1800 |                         |                   1800
 (0,8) | d2     |           1000 |           1801 |           3999 |                    1801 |                   3999

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

-- Data integrity
SELECT count(*) FROM metrics_nulls_first;
 count 
-------
  4000

SELECT device, count(*) FROM metrics_nulls_first GROUP BY device ORDER BY device;
 device | count 
--------+-------
 d1     |  2000
 d2     |  2000

SELECT device, count(*) AS null_count FROM metrics_nulls_first WHERE value IS NULL GROUP BY device ORDER BY device;
 device | null_count 
--------+------------
 d1     |        200
 d2     |        200

-- Same overlap+NULLs test for NULLS FIRST
INSERT INTO metrics_nulls_first
SELECT '2025-01-03'::timestamptz + ((2000 + i) || ' minute')::interval,
       'd1',
       CASE WHEN i % 5 = 0 THEN NULL ELSE (i + 500)::float END
FROM generate_series(1,500) i;
-- Batches before overlap compaction
SELECT ctid, device, _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_v2_first_value, _ts_meta_v2_last_value
FROM :NULLS_FIRST_CHUNK
WHERE device = 'd1'
ORDER BY _ts_meta_min_1 NULLS FIRST;
 ctid  | device | _ts_meta_count | _ts_meta_min_1 | _ts_meta_max_1 | _ts_meta_v2_first_value | _ts_meta_v2_last_value 
-------+--------+----------------+----------------+----------------+-------------------------+------------------------
 (0,5) | d1     |           1000 |              1 |            800 |                         |                    800
 (0,9) | d1     |            500 |            501 |            999 |                         |                    999
 (0,6) | d1     |           1000 |            801 |           2999 |                     801 |                   2999

SELECT _timescaledb_functions.compact_chunk(chunk) FROM show_chunks('metrics_nulls_first') chunk;
              compact_chunk              
-----------------------------------------
 _timescaledb_internal._hyper_9_11_chunk

-- After overlap merge: NULLs from overlapping batches preserved
SELECT ctid, device, _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_v2_first_value, _ts_meta_v2_last_value
FROM :NULLS_FIRST_CHUNK
WHERE device = 'd1'
ORDER BY _ts_meta_min_1 NULLS FIRST;
  ctid  | device | _ts_meta_count | _ts_meta_min_1 | _ts_meta_max_1 | _ts_meta_v2_first_value | _ts_meta_v2_last_value 
--------+--------+----------------+----------------+----------------+-------------------------+------------------------
 (0,10) | d1     |           1000 |              1 |            611 |                         |                    611
 (0,11) | d1     |           1000 |            612 |           2374 |                     612 |                   2374
 (0,12) | d1     |            500 |           2376 |           2999 |                    2376 |                   2999

-- Data integrity: NULLs from overlap merge must survive
SELECT count(*) FROM metrics_nulls_first WHERE device = 'd1';
 count 
-------
  2500

SELECT count(*) AS null_count FROM metrics_nulls_first WHERE device = 'd1' AND value IS NULL;
 null_count 
------------
        300

DROP TABLE metrics_nulls_first;
-- compact_chunk with a mixed-null batch whose NULLs overlap a later batch
-- A batch with both NULL and non-NULL values in the first orderby column has a
-- NULL last row (NULLS LAST) that sorts after a following non-null batch, so the
-- two batches overlap and the merge re-sorts them with the NULLs back at the end.
-- Without firstlast metadata the NULL boundary is invisible and the overlap is
-- missed, leaving NULLs in the wrong index position.
CREATE TABLE metrics_mixed_nulls (time TIMESTAMPTZ NOT NULL, device TEXT, value float)
WITH (tsdb.hypertable, tsdb.orderby='value,time', tsdb.segmentby='device');
NOTICE:  using column "time" as partitioning column
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;
-- Insert 1800 non-null rows + 200 NULL rows for device 'd1'.
-- DCI sorts by (value NULLS LAST, time), producing:
--   Batch 1 (1000 rows): value [1..1000]       — all non-null
--   Batch 2 (1000 rows): value [1001..1800] + 200 NULLs — mixed!
-- Batch 2's firstlast metadata: first=1001, last=NULL (NULLS LAST).
INSERT INTO metrics_mixed_nulls
SELECT '2025-01-03'::timestamptz + (i || ' minute')::interval,
       'd1',
       CASE WHEN i > 1800 THEN NULL ELSE i::float END
FROM generate_series(1,2000) i;
-- Insert 1000 more non-null rows with values [1801..2800], forming Batch 3
-- (first=1801, last=2800). Batch 2's non-null range [1001..1800] sits below
-- Batch 3, but Batch 2's NULL last row sorts after Batch 3 (NULLS LAST), so the
-- two batches overlap and must be merged.
INSERT INTO metrics_mixed_nulls
SELECT '2025-01-03'::timestamptz + ((2000 + i) || ' minute')::interval,
       'd1',
       (1800 + i)::float
FROM generate_series(1,1000) i;
SELECT cs.compress_relid::regclass::text AS "MIXED_NULLS_CHUNK"
FROM _timescaledb_catalog.chunk ch
    JOIN _timescaledb_catalog.compression_settings cs
        ON cs.relid = ch.relid
    JOIN _timescaledb_catalog.hypertable ht ON ch.hypertable_id = ht.id
WHERE ht.table_name = 'metrics_mixed_nulls'
ORDER BY ch.id LIMIT 1 \gset
-- Show batch metadata before compaction: 3 batches, batch 2 has mixed nulls
SELECT ctid, device, _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_v2_first_value, _ts_meta_v2_last_value
FROM :MIXED_NULLS_CHUNK
ORDER BY device, _ts_meta_min_1;
 ctid  | device | _ts_meta_count | _ts_meta_min_1 | _ts_meta_max_1 | _ts_meta_v2_first_value | _ts_meta_v2_last_value 
-------+--------+----------------+----------------+----------------+-------------------------+------------------------
 (0,1) | d1     |           1000 |              1 |           1000 |                       1 |                   1000
 (0,2) | d1     |           1000 |           1001 |           1800 |                    1001 |                       
 (0,3) | d1     |           1000 |           1801 |           2800 |                    1801 |                   2800

-- compact_chunk must detect the overlap and re-sort the NULLs back to the end
SELECT _timescaledb_functions.compact_chunk(chunk) FROM show_chunks('metrics_mixed_nulls') chunk;
              compact_chunk               
------------------------------------------
 _timescaledb_internal._hyper_10_12_chunk

-- After compaction: Batch 2 and Batch 3 merged, NULLs folded into the trailing
-- batch at the end (NULLS LAST).
SELECT ctid, device, _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_v2_first_value, _ts_meta_v2_last_value
FROM :MIXED_NULLS_CHUNK
ORDER BY device, _ts_meta_min_1 NULLS LAST;
 ctid  | device | _ts_meta_count | _ts_meta_min_1 | _ts_meta_max_1 | _ts_meta_v2_first_value | _ts_meta_v2_last_value 
-------+--------+----------------+----------------+----------------+-------------------------+------------------------
 (0,1) | d1     |           1000 |              1 |           1000 |                       1 |                   1000
 (0,4) | d1     |           1000 |           1001 |           2000 |                    1001 |                   2000
 (0,5) | d1     |           1000 |           2001 |           2800 |                    2001 |                       

-- Status should be COMPRESSED only (UNORDERED cleared correctly this time)
SELECT DISTINCT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics_mixed_nulls') chunk;
 chunk_status_text 
-------------------
 {COMPRESSED}

-- Data integrity
SELECT count(*) FROM metrics_mixed_nulls;
 count 
-------
  3000

SELECT count(*) AS null_count FROM metrics_mixed_nulls WHERE value IS NULL;
 null_count 
------------
        200

-- Verify ordering is correct: NULLs must come after all non-null values
-- This query will return wrong results if the bug is present (NULLs between 1800 and 1801)
SELECT value FROM metrics_mixed_nulls
WHERE device = 'd1'
ORDER BY value NULLS LAST, time
LIMIT 5 OFFSET 1795;
 value 
-------
  1796
  1797
  1798
  1799
  1800

DROP TABLE metrics_mixed_nulls;
-- compact_chunk with nullable SECONDARY orderby column at boundary tie
-- Regression test: when orderby='time,value' and value is nullable,
-- two batches can tie on col1 (time) at the boundary. If the prev batch's
-- last row has NULL in col2 (value), the overlap check must compare using
-- NULLS FIRST/LAST semantics so the tie is correctly seen as an overlap.
CREATE TABLE metrics_secondary_null (time TIMESTAMPTZ NOT NULL, device TEXT, value float)
WITH (tsdb.hypertable, tsdb.orderby='time,value', tsdb.segmentby='device');
NOTICE:  using column "time" as partitioning column
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;
-- Insert batch 1 (500 rows): time [00:01..08:20], with NULL value at the
-- last timestamp (i=500, time=08:20). With orderby='time,value NULLS LAST',
-- the last row in sorted order is (08:20, NULL).
INSERT INTO metrics_secondary_null
SELECT '2025-01-03'::timestamptz + (i || ' minute')::interval,
       'd1',
       CASE WHEN i = 500 THEN NULL ELSE i::float END
FROM generate_series(1,500) i;
-- Insert batch 2 (500 rows): starts exactly at batch 1's max time (08:20).
-- This creates a boundary tie on col1 (time). All values are non-null.
-- The first row (08:20, 1001) ties with batch 1's last row (08:20, NULL).
-- With NULLS LAST, (08:20, NULL) should sort AFTER (08:20, 1001), but in
-- index order batch 1 comes before batch 2 — an ordering violation.
INSERT INTO metrics_secondary_null
SELECT '2025-01-03'::timestamptz + ((499 + i) || ' minute')::interval,
       'd1',
       (1000 + i)::float
FROM generate_series(1,500) i;
SELECT cs.compress_relid::regclass::text AS "SEC_NULL_CHUNK"
FROM _timescaledb_catalog.chunk ch
    JOIN _timescaledb_catalog.compression_settings cs
        ON cs.relid = ch.relid
    JOIN _timescaledb_catalog.hypertable ht ON ch.hypertable_id = ht.id
WHERE ht.table_name = 'metrics_secondary_null'
ORDER BY ch.id LIMIT 1 \gset
-- Show batch metadata: col1 (time) ranges overlap at the boundary
SELECT ctid, device, _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_v2_first_time, _ts_meta_v2_last_time, _ts_meta_min_2, _ts_meta_max_2, _ts_meta_v2_first_value, _ts_meta_v2_last_value
FROM :SEC_NULL_CHUNK
ORDER BY device, _ts_meta_min_1;
 ctid  | device | _ts_meta_count |        _ts_meta_min_1        |        _ts_meta_max_1        |    _ts_meta_v2_first_time    |    _ts_meta_v2_last_time     | _ts_meta_min_2 | _ts_meta_max_2 | _ts_meta_v2_first_value | _ts_meta_v2_last_value 
-------+--------+----------------+------------------------------+------------------------------+------------------------------+------------------------------+----------------+----------------+-------------------------+------------------------
 (0,1) | d1     |            500 | Fri Jan 03 00:01:00 2025 PST | Fri Jan 03 08:20:00 2025 PST | Fri Jan 03 00:01:00 2025 PST | Fri Jan 03 08:20:00 2025 PST |              1 |            499 |                       1 |                       
 (0,2) | d1     |            500 | Fri Jan 03 08:20:00 2025 PST | Fri Jan 03 16:39:00 2025 PST | Fri Jan 03 08:20:00 2025 PST | Fri Jan 03 16:39:00 2025 PST |           1001 |           1500 |                    1001 |                   1500

-- compact_chunk must detect the boundary-tie overlap caused by NULL in col2
SELECT _timescaledb_functions.compact_chunk(chunk) FROM show_chunks('metrics_secondary_null') chunk;
              compact_chunk               
------------------------------------------
 _timescaledb_internal._hyper_11_13_chunk

-- After compaction: batches should be merged and re-sorted
SELECT ctid, device, _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_v2_first_time, _ts_meta_v2_last_time
FROM :SEC_NULL_CHUNK
ORDER BY device, _ts_meta_min_1;
 ctid  | device | _ts_meta_count |        _ts_meta_min_1        |        _ts_meta_max_1        |    _ts_meta_v2_first_time    |    _ts_meta_v2_last_time     
-------+--------+----------------+------------------------------+------------------------------+------------------------------+------------------------------
 (0,3) | d1     |           1000 | Fri Jan 03 00:01:00 2025 PST | Fri Jan 03 16:39:00 2025 PST | Fri Jan 03 00:01:00 2025 PST | Fri Jan 03 16:39:00 2025 PST

-- Status should be COMPRESSED only
SELECT DISTINCT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics_secondary_null') chunk;
 chunk_status_text 
-------------------
 {COMPRESSED}

-- Data integrity
SELECT count(*) FROM metrics_secondary_null;
 count 
-------
  1000

SELECT count(*) AS null_count FROM metrics_secondary_null WHERE value IS NULL;
 null_count 
------------
          1

-- Verify ordering: at the boundary time (08:20 = +500 min), non-null value
-- must come before NULL with NULLS LAST ordering.
SELECT time, value FROM metrics_secondary_null
WHERE device = 'd1' AND time = '2025-01-03'::timestamptz + '500 minutes'::interval
ORDER BY time, value NULLS LAST;
             time             | value 
------------------------------+-------
 Fri Jan 03 08:20:00 2025 PST |  1001
 Fri Jan 03 08:20:00 2025 PST |      

DROP TABLE metrics_secondary_null;
-- compact_chunk with nullable SECONDARY orderby column at boundary tie (NULLS FIRST)
-- Mirror of the NULLS LAST test above. With NULLS FIRST, the curr batch's
-- first row has NULL in col2, which should sort BEFORE the prev batch's
-- last non-null col2 value — meaning the curr batch actually starts earlier
-- than its non-null min suggests, causing an ordering violation.
CREATE TABLE metrics_secondary_null_first (time TIMESTAMPTZ NOT NULL, device TEXT, value float)
WITH (tsdb.hypertable, tsdb.orderby='time,value NULLS FIRST', tsdb.segmentby='device');
NOTICE:  using column "time" as partitioning column
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;
-- Insert batch 1 (500 rows): time [00:01..08:20], all non-null values.
INSERT INTO metrics_secondary_null_first
SELECT '2025-01-03'::timestamptz + (i || ' minute')::interval,
       'd1',
       i::float
FROM generate_series(1,500) i;
-- Insert batch 2 (500 rows): starts at batch 1's max time (08:20).
-- The first row at time=08:20 has NULL value. With NULLS FIRST,
-- (08:20, NULL) should sort BEFORE (08:20, 500) from batch 1's last row.
-- But batch 2 comes after batch 1 in index order — ordering violation.
INSERT INTO metrics_secondary_null_first
SELECT '2025-01-03'::timestamptz + ((499 + i) || ' minute')::interval,
       'd1',
       CASE WHEN i = 1 THEN NULL ELSE (1000 + i)::float END
FROM generate_series(1,500) i;
SELECT cs.compress_relid::regclass::text AS "SEC_NF_CHUNK"
FROM _timescaledb_catalog.chunk ch
    JOIN _timescaledb_catalog.compression_settings cs
        ON cs.relid = ch.relid
    JOIN _timescaledb_catalog.hypertable ht ON ch.hypertable_id = ht.id
WHERE ht.table_name = 'metrics_secondary_null_first'
ORDER BY ch.id LIMIT 1 \gset
-- Show batch metadata: boundary tie on col1 (time) at 08:20
SELECT ctid, device, _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_v2_first_time, _ts_meta_v2_last_time, _ts_meta_min_2, _ts_meta_max_2, _ts_meta_v2_first_value, _ts_meta_v2_last_value
FROM :SEC_NF_CHUNK
ORDER BY device, _ts_meta_min_1;
 ctid  | device | _ts_meta_count |        _ts_meta_min_1        |        _ts_meta_max_1        |    _ts_meta_v2_first_time    |    _ts_meta_v2_last_time     | _ts_meta_min_2 | _ts_meta_max_2 | _ts_meta_v2_first_value | _ts_meta_v2_last_value 
-------+--------+----------------+------------------------------+------------------------------+------------------------------+------------------------------+----------------+----------------+-------------------------+------------------------
 (0,1) | d1     |            500 | Fri Jan 03 00:01:00 2025 PST | Fri Jan 03 08:20:00 2025 PST | Fri Jan 03 00:01:00 2025 PST | Fri Jan 03 08:20:00 2025 PST |              1 |            500 |                       1 |                    500
 (0,2) | d1     |            500 | Fri Jan 03 08:20:00 2025 PST | Fri Jan 03 16:39:00 2025 PST | Fri Jan 03 08:20:00 2025 PST | Fri Jan 03 16:39:00 2025 PST |           1002 |           1500 |                         |                   1500

-- compact_chunk must detect the boundary-tie overlap caused by NULL in col2
SELECT _timescaledb_functions.compact_chunk(chunk) FROM show_chunks('metrics_secondary_null_first') chunk;
              compact_chunk               
------------------------------------------
 _timescaledb_internal._hyper_12_14_chunk

-- After compaction: batches should be merged and re-sorted
SELECT ctid, device, _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_v2_first_time, _ts_meta_v2_last_time
FROM :SEC_NF_CHUNK
ORDER BY device, _ts_meta_min_1;
 ctid  | device | _ts_meta_count |        _ts_meta_min_1        |        _ts_meta_max_1        |    _ts_meta_v2_first_time    |    _ts_meta_v2_last_time     
-------+--------+----------------+------------------------------+------------------------------+------------------------------+------------------------------
 (0,3) | d1     |           1000 | Fri Jan 03 00:01:00 2025 PST | Fri Jan 03 16:39:00 2025 PST | Fri Jan 03 00:01:00 2025 PST | Fri Jan 03 16:39:00 2025 PST

-- Status should be COMPRESSED only
SELECT DISTINCT _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics_secondary_null_first') chunk;
 chunk_status_text 
-------------------
 {COMPRESSED}

-- Data integrity
SELECT count(*) FROM metrics_secondary_null_first;
 count 
-------
  1000

SELECT count(*) AS null_count FROM metrics_secondary_null_first WHERE value IS NULL;
 null_count 
------------
          1

-- Verify ordering: at the boundary time (08:20), NULL must come before non-null
-- with NULLS FIRST ordering.
SELECT time, value FROM metrics_secondary_null_first
WHERE device = 'd1' AND time = '2025-01-03'::timestamptz + '500 minutes'::interval
ORDER BY time, value NULLS FIRST;
             time             | value 
------------------------------+-------
 Fri Jan 03 08:20:00 2025 PST |      
 Fri Jan 03 08:20:00 2025 PST |   500

DROP TABLE metrics_secondary_null_first;
-- compact_chunk skips legacy chunks whose orderby column has no firstlast sparse
-- index. Compaction reads exact batch boundaries from firstlast metadata, so
-- without it the chunk is left untouched (UNORDERED stays set). Configuring minmax
-- (and not firstlast) on the orderby column reproduces a legacy chunk: a
-- user-supplied index for a column suppresses the firstlast that orderby columns
-- get by default.
CREATE TABLE metrics_no_firstlast (time TIMESTAMPTZ NOT NULL, device TEXT, value float)
WITH (tsdb.hypertable, tsdb.orderby='time', tsdb.index='minmax(time)');
NOTICE:  using column "time" as partitioning column
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;
-- Two inserts over the same time range (one chunk), leaving the chunk UNORDERED.
INSERT INTO metrics_no_firstlast
SELECT '2025-01-03'::timestamptz + (i || ' minute')::interval, 'd1', i::float
FROM generate_series(1,1000) i;
INSERT INTO metrics_no_firstlast
SELECT '2025-01-03'::timestamptz + (i || ' minute')::interval, 'd1', (1000 + i)::float
FROM generate_series(1,1000) i;
SELECT cs.compress_relid::regclass::text AS "NO_FL_CHUNK"
FROM _timescaledb_catalog.chunk ch
    JOIN _timescaledb_catalog.compression_settings cs
        ON cs.relid = ch.relid
    JOIN _timescaledb_catalog.hypertable ht ON ch.hypertable_id = ht.id
WHERE ht.table_name = 'metrics_no_firstlast'
ORDER BY ch.id LIMIT 1 \gset
-- The orderby column only carries minmax metadata (no _ts_meta_v2_first/last).
SELECT count(*) FILTER (WHERE attname LIKE '\_ts\_meta\_v2\_first%') AS first_cols,
       count(*) FILTER (WHERE attname LIKE '\_ts\_meta\_v2\_last%') AS last_cols
FROM pg_attribute
WHERE attrelid = :'NO_FL_CHUNK'::regclass AND attnum > 0;
 first_cols | last_cols 
------------+-----------
          0 |         0

-- Overlapping batches; chunk is UNORDERED.
SELECT ctid, _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1
FROM :NO_FL_CHUNK ORDER BY _ts_meta_min_1;
 ctid  | _ts_meta_count |        _ts_meta_min_1        |        _ts_meta_max_1        
-------+----------------+------------------------------+------------------------------
 (0,1) |           1000 | Fri Jan 03 00:01:00 2025 PST | Fri Jan 03 16:40:00 2025 PST
 (0,2) |           1000 | Fri Jan 03 00:01:00 2025 PST | Fri Jan 03 16:40:00 2025 PST

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

-- compact_chunk is a no-op and warns with a hint on how to enable it: there is
-- no firstlast metadata to read batch boundaries from.
\set VERBOSITY default
SELECT _timescaledb_functions.compact_chunk(chunk) FROM show_chunks('metrics_no_firstlast') chunk;
WARNING:  skipping compaction on chunk _timescaledb_internal._hyper_13_15_chunk
DETAIL:  Orderby column "time" has no firstlast sparse index.
HINT:  Recompress the chunk to add firstlast sparse index metadata for its orderby columns.
              compact_chunk               
------------------------------------------
 _timescaledb_internal._hyper_13_15_chunk

\set VERBOSITY terse
-- Batches unchanged (same ctids) and UNORDERED is still set.
SELECT ctid, _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1
FROM :NO_FL_CHUNK ORDER BY _ts_meta_min_1;
 ctid  | _ts_meta_count |        _ts_meta_min_1        |        _ts_meta_max_1        
-------+----------------+------------------------------+------------------------------
 (0,1) |           1000 | Fri Jan 03 00:01:00 2025 PST | Fri Jan 03 16:40:00 2025 PST
 (0,2) |           1000 | Fri Jan 03 00:01:00 2025 PST | Fri Jan 03 16:40:00 2025 PST

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

DROP TABLE metrics_no_firstlast;
-- Test max batches limit
SET timescaledb.enable_direct_compress_insert = true;
CREATE TABLE metrics_max_batches (time TIMESTAMPTZ NOT NULL, device TEXT, value float)
  WITH (tsdb.hypertable, tsdb.orderby='time', tsdb.segmentby='device');
NOTICE:  using column "time" as partitioning column
-- inserts are in a pattern to test compaction
-- two overlapping batches (d1 and d3)
INSERT INTO metrics_max_batches
SELECT '2025-01-02'::timestamptz + (i || ' minute')::interval, d, i::float
FROM generate_series(1,100) i, (VALUES ('d1'), ('d3')) AS v(d);
INSERT INTO metrics_max_batches
SELECT '2025-01-02'::timestamptz + (i || ' minute')::interval, d, i::float
FROM generate_series(50,150) i, (VALUES ('d1'), ('d3')) AS v(d);
-- non overlapping batch (d1 and d3)
INSERT INTO metrics_max_batches
SELECT '2025-01-02'::timestamptz + (i || ' minute')::interval, d, i::float
FROM generate_series(250, 350) i, (VALUES ('d1'), ('d3')) AS v(d);
-- three overlapping batches (d1 and d3)
INSERT INTO metrics_max_batches
SELECT '2025-01-02'::timestamptz + (i || ' minute')::interval, d, i::float
FROM generate_series(400, 500) i, (VALUES ('d1'), ('d3')) AS v(d);
INSERT INTO metrics_max_batches
SELECT '2025-01-02'::timestamptz + (i || ' minute')::interval, d, i::float
FROM generate_series(450, 550) i, (VALUES ('d1'), ('d3')) AS v(d);
INSERT INTO metrics_max_batches
SELECT '2025-01-02'::timestamptz + (i || ' minute')::interval, d, i::float
FROM generate_series(500, 600) i, (VALUES ('d1'), ('d3')) AS v(d);
-- two overlapping batches with segment change
INSERT INTO metrics_max_batches
SELECT '2025-01-02'::timestamptz + (i || ' minute')::interval, 'd1', i::float
FROM generate_series(700, 800) i;
INSERT INTO metrics_max_batches
SELECT '2025-01-02'::timestamptz + (i || ' minute')::interval, 'd1', i::float
FROM generate_series(750, 850) i;
INSERT INTO metrics_max_batches
SELECT '2025-01-02'::timestamptz + (i || ' minute')::interval, 'd2', i::float
FROM generate_series(700, 800) i;
INSERT INTO metrics_max_batches
SELECT '2025-01-02'::timestamptz + (i || ' minute')::interval, 'd2', i::float
FROM generate_series(750, 850) i;
SELECT cs.compress_relid::regclass::text AS "MB_CHUNK"
FROM _timescaledb_catalog.chunk ch
    JOIN _timescaledb_catalog.compression_settings cs ON cs.relid = ch.relid
    JOIN _timescaledb_catalog.hypertable ht ON ch.hypertable_id = ht.id
WHERE ht.table_name = 'metrics_max_batches'
ORDER BY ch.id LIMIT 1 \gset
-- only showing device = 'd1' to reduce noise
SELECT _ts_meta_count, device, _ts_meta_min_1, _ts_meta_max_1 FROM :MB_CHUNK WHERE device = 'd1' ORDER BY _ts_meta_min_1;
 _ts_meta_count | device |        _ts_meta_min_1        |        _ts_meta_max_1        
----------------+--------+------------------------------+------------------------------
            100 | d1     | Thu Jan 02 00:01:00 2025 PST | Thu Jan 02 01:40:00 2025 PST
            101 | d1     | Thu Jan 02 00:50:00 2025 PST | Thu Jan 02 02:30:00 2025 PST
            101 | d1     | Thu Jan 02 04:10:00 2025 PST | Thu Jan 02 05:50:00 2025 PST
            101 | d1     | Thu Jan 02 06:40:00 2025 PST | Thu Jan 02 08:20:00 2025 PST
            101 | d1     | Thu Jan 02 07:30:00 2025 PST | Thu Jan 02 09:10:00 2025 PST
            101 | d1     | Thu Jan 02 08:20:00 2025 PST | Thu Jan 02 10:00:00 2025 PST
            101 | d1     | Thu Jan 02 11:40:00 2025 PST | Thu Jan 02 13:20:00 2025 PST
            101 | d1     | Thu Jan 02 12:30:00 2025 PST | Thu Jan 02 14:10:00 2025 PST

SELECT chunk, _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics_max_batches') chunk;
                  chunk                   |   chunk_status_text    
------------------------------------------+------------------------
 _timescaledb_internal._hyper_14_16_chunk | {COMPRESSED,UNORDERED}

-- first compact (compacts two batches)
SELECT _timescaledb_functions.compact_chunk(chunk, 2) FROM show_chunks('metrics_max_batches') chunk;
              compact_chunk               
------------------------------------------
 _timescaledb_internal._hyper_14_16_chunk

SELECT _ts_meta_count, device, _ts_meta_min_1, _ts_meta_max_1 FROM :MB_CHUNK WHERE device = 'd1' ORDER BY _ts_meta_min_1;
 _ts_meta_count | device |        _ts_meta_min_1        |        _ts_meta_max_1        
----------------+--------+------------------------------+------------------------------
            201 | d1     | Thu Jan 02 00:01:00 2025 PST | Thu Jan 02 02:30:00 2025 PST
            101 | d1     | Thu Jan 02 04:10:00 2025 PST | Thu Jan 02 05:50:00 2025 PST
            101 | d1     | Thu Jan 02 06:40:00 2025 PST | Thu Jan 02 08:20:00 2025 PST
            101 | d1     | Thu Jan 02 07:30:00 2025 PST | Thu Jan 02 09:10:00 2025 PST
            101 | d1     | Thu Jan 02 08:20:00 2025 PST | Thu Jan 02 10:00:00 2025 PST
            101 | d1     | Thu Jan 02 11:40:00 2025 PST | Thu Jan 02 13:20:00 2025 PST
            101 | d1     | Thu Jan 02 12:30:00 2025 PST | Thu Jan 02 14:10:00 2025 PST

-- second compact (compacts three batches even though max_batches = 2)
SELECT _timescaledb_functions.compact_chunk(chunk, 2) FROM show_chunks('metrics_max_batches') chunk;
              compact_chunk               
------------------------------------------
 _timescaledb_internal._hyper_14_16_chunk

SELECT _ts_meta_count, device, _ts_meta_min_1, _ts_meta_max_1 FROM :MB_CHUNK WHERE device = 'd1' ORDER BY _ts_meta_min_1;
 _ts_meta_count | device |        _ts_meta_min_1        |        _ts_meta_max_1        
----------------+--------+------------------------------+------------------------------
            201 | d1     | Thu Jan 02 00:01:00 2025 PST | Thu Jan 02 02:30:00 2025 PST
            101 | d1     | Thu Jan 02 04:10:00 2025 PST | Thu Jan 02 05:50:00 2025 PST
            303 | d1     | Thu Jan 02 06:40:00 2025 PST | Thu Jan 02 10:00:00 2025 PST
            101 | d1     | Thu Jan 02 11:40:00 2025 PST | Thu Jan 02 13:20:00 2025 PST
            101 | d1     | Thu Jan 02 12:30:00 2025 PST | Thu Jan 02 14:10:00 2025 PST

-- third compact (compacts with segment change)
SELECT _timescaledb_functions.compact_chunk(chunk, 3) FROM show_chunks('metrics_max_batches') chunk;
              compact_chunk               
------------------------------------------
 _timescaledb_internal._hyper_14_16_chunk

SELECT _ts_meta_count, device, _ts_meta_min_1, _ts_meta_max_1 FROM :MB_CHUNK WHERE device IN ('d1', 'd2') ORDER BY device, _ts_meta_min_1;
 _ts_meta_count | device |        _ts_meta_min_1        |        _ts_meta_max_1        
----------------+--------+------------------------------+------------------------------
            201 | d1     | Thu Jan 02 00:01:00 2025 PST | Thu Jan 02 02:30:00 2025 PST
            101 | d1     | Thu Jan 02 04:10:00 2025 PST | Thu Jan 02 05:50:00 2025 PST
            303 | d1     | Thu Jan 02 06:40:00 2025 PST | Thu Jan 02 10:00:00 2025 PST
            202 | d1     | Thu Jan 02 11:40:00 2025 PST | Thu Jan 02 14:10:00 2025 PST
            202 | d2     | Thu Jan 02 11:40:00 2025 PST | Thu Jan 02 14:10:00 2025 PST

-- should be compressed, unordered
SELECT chunk, _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics_max_batches') chunk;
                  chunk                   |   chunk_status_text    
------------------------------------------+------------------------
 _timescaledb_internal._hyper_14_16_chunk | {COMPRESSED,UNORDERED}

-- compact_chunk with max_batches=0 (unlimited): resolves all remaining overlaps.
SELECT _timescaledb_functions.compact_chunk(chunk, 0) FROM show_chunks('metrics_max_batches') chunk;
              compact_chunk               
------------------------------------------
 _timescaledb_internal._hyper_14_16_chunk

SELECT _ts_meta_count, device, _ts_meta_min_1, _ts_meta_max_1 FROM :MB_CHUNK ORDER BY device, _ts_meta_min_1;
 _ts_meta_count | device |        _ts_meta_min_1        |        _ts_meta_max_1        
----------------+--------+------------------------------+------------------------------
            201 | d1     | Thu Jan 02 00:01:00 2025 PST | Thu Jan 02 02:30:00 2025 PST
            101 | d1     | Thu Jan 02 04:10:00 2025 PST | Thu Jan 02 05:50:00 2025 PST
            303 | d1     | Thu Jan 02 06:40:00 2025 PST | Thu Jan 02 10:00:00 2025 PST
            202 | d1     | Thu Jan 02 11:40:00 2025 PST | Thu Jan 02 14:10:00 2025 PST
            202 | d2     | Thu Jan 02 11:40:00 2025 PST | Thu Jan 02 14:10:00 2025 PST
            201 | d3     | Thu Jan 02 00:01:00 2025 PST | Thu Jan 02 02:30:00 2025 PST
            101 | d3     | Thu Jan 02 04:10:00 2025 PST | Thu Jan 02 05:50:00 2025 PST
            303 | d3     | Thu Jan 02 06:40:00 2025 PST | Thu Jan 02 10:00:00 2025 PST

-- should be compressed
SELECT chunk, _timescaledb_functions.chunk_status_text(chunk) FROM show_chunks('metrics_max_batches') chunk;
                  chunk                   | chunk_status_text 
------------------------------------------+-------------------
 _timescaledb_internal._hyper_14_16_chunk | {COMPRESSED}

DROP TABLE metrics_max_batches;
-- Test Small intermediate batches must not hide overlaps with later batches.
CREATE TABLE metrics_running_max (time TIMESTAMPTZ NOT NULL, value float)
  WITH (tsdb.hypertable, tsdb.orderby='time');
NOTICE:  using column "time" as partitioning column
INSERT INTO metrics_running_max SELECT '2025-07-07'::timestamptz + (i || ' minute')::interval, i FROM generate_series(1,1000) i;
INSERT INTO metrics_running_max SELECT '2025-07-07'::timestamptz + (i || ' minute')::interval, i FROM generate_series(500,1500) i;
INSERT INTO metrics_running_max SELECT '2025-07-07'::timestamptz + (i || ' minute')::interval, i FROM generate_series(1000,2000) i;
INSERT INTO metrics_running_max SELECT '2025-07-07'::timestamptz + (i || ' minute')::interval, i FROM generate_series(1500,2500) i;
-- Should resolve all overlaps in one pass.
SELECT _timescaledb_functions.compact_chunk(chunk) FROM show_chunks('metrics_running_max') chunk;
              compact_chunk               
------------------------------------------
 _timescaledb_internal._hyper_15_17_chunk

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

DROP TABLE metrics_running_max;
-- Test NULL last with NULLS LAST must stay as the running max.
CREATE TABLE metrics_null_boundary (time TIMESTAMPTZ NOT NULL, value float)
  WITH (tsdb.hypertable, tsdb.orderby='value NULLS LAST');
NOTICE:  using column "time" as partitioning column
-- insert in a pattern to trigger NULL value overlap
INSERT INTO metrics_null_boundary
SELECT '2025-01-02'::timestamptz + (i || ' minute')::interval,
       CASE WHEN i = 1000 THEN NULL ELSE i::float END
FROM generate_series(1,1000) i;
INSERT INTO metrics_null_boundary
SELECT '2025-01-02'::timestamptz + ((999 + i) || ' minute')::interval, (499 + i)::float
FROM generate_series(1,11) i;
INSERT INTO metrics_null_boundary
SELECT '2025-01-02'::timestamptz + ((1010 + i) || ' minute')::interval, (799 + i)::float
FROM generate_series(1,101) i;
INSERT INTO metrics_null_boundary
SELECT '2025-01-02'::timestamptz + ((1200 + i) || ' minute')::interval, (950 + i)::float
FROM generate_series(1,50) i;
SELECT cs.compress_relid::regclass::text AS "MIXED_NULLS_CHUNK"
FROM _timescaledb_catalog.chunk ch
    JOIN _timescaledb_catalog.compression_settings cs
        ON cs.relid = ch.relid
    JOIN _timescaledb_catalog.hypertable ht ON ch.hypertable_id = ht.id
WHERE ht.table_name = 'metrics_null_boundary'
ORDER BY ch.id LIMIT 1 \gset
SELECT ctid, _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_v2_first_value, _ts_meta_v2_last_value
FROM :MIXED_NULLS_CHUNK
ORDER BY _ts_meta_min_1;
 ctid  | _ts_meta_count | _ts_meta_min_1 | _ts_meta_max_1 | _ts_meta_v2_first_value | _ts_meta_v2_last_value 
-------+----------------+----------------+----------------+-------------------------+------------------------
 (0,1) |           1000 |              1 |            999 |                       1 |                       
 (0,2) |             11 |            500 |            510 |                     500 |                    510
 (0,3) |            101 |            800 |            900 |                     800 |                    900
 (0,4) |             50 |            951 |           1000 |                     951 |                   1000

-- Should resolve all overlaps in one pass.
SELECT _timescaledb_functions.compact_chunk(chunk) FROM show_chunks('metrics_null_boundary') chunk;
              compact_chunk               
------------------------------------------
 _timescaledb_internal._hyper_16_18_chunk

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

SELECT ctid, _ts_meta_count, _ts_meta_min_1, _ts_meta_max_1, _ts_meta_v2_first_value, _ts_meta_v2_last_value
FROM :MIXED_NULLS_CHUNK
ORDER BY _ts_meta_min_1;
 ctid  | _ts_meta_count | _ts_meta_min_1 | _ts_meta_max_1 | _ts_meta_v2_first_value | _ts_meta_v2_last_value 
-------+----------------+----------------+----------------+-------------------------+------------------------
 (0,5) |           1000 |              1 |            894 |                       1 |                    894
 (0,6) |            162 |            895 |           1000 |                     895 |                       

DROP TABLE metrics_null_boundary;
-- Test with orderby DESC
CREATE TABLE metrics_rm_desc (time TIMESTAMPTZ NOT NULL, value float)
  WITH (tsdb.hypertable, tsdb.orderby='time DESC');
NOTICE:  using column "time" as partitioning column
INSERT INTO metrics_rm_desc SELECT '2025-07-07'::timestamptz + (i || ' minute')::interval, i FROM generate_series(1,1000) i;
INSERT INTO metrics_rm_desc SELECT '2025-07-07'::timestamptz + (i || ' minute')::interval, i FROM generate_series(500,1500) i;
INSERT INTO metrics_rm_desc SELECT '2025-07-07'::timestamptz + (i || ' minute')::interval, i FROM generate_series(1000,2000) i;
INSERT INTO metrics_rm_desc SELECT '2025-07-07'::timestamptz + (i || ' minute')::interval, i FROM generate_series(1500,2500) i;
SELECT _timescaledb_functions.compact_chunk(chunk) FROM show_chunks('metrics_rm_desc') chunk;
              compact_chunk               
------------------------------------------
 _timescaledb_internal._hyper_17_19_chunk

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

SELECT count(*) FROM metrics_rm_desc;
 count 
-------
  4003

DROP TABLE metrics_rm_desc;
-- Running max with multi-column orderby and mixed directions.
CREATE TABLE metrics_rm_multi (time TIMESTAMPTZ NOT NULL, device TEXT, value float)
  WITH (tsdb.hypertable, tsdb.orderby='time ASC, value DESC');
NOTICE:  using column "time" as partitioning column
INSERT INTO metrics_rm_multi SELECT '2025-07-07'::timestamptz + (i || ' minute')::interval, 'd1', i FROM generate_series(1,1000) i;
INSERT INTO metrics_rm_multi SELECT '2025-07-07'::timestamptz + (i || ' minute')::interval, 'd1', i FROM generate_series(500,1500) i;
INSERT INTO metrics_rm_multi SELECT '2025-07-07'::timestamptz + (i || ' minute')::interval, 'd1', i FROM generate_series(1000,2000) i;
INSERT INTO metrics_rm_multi SELECT '2025-07-07'::timestamptz + (i || ' minute')::interval, 'd1', i FROM generate_series(1500,2500) i;
SELECT _timescaledb_functions.compact_chunk(chunk) FROM show_chunks('metrics_rm_multi') chunk;
              compact_chunk               
------------------------------------------
 _timescaledb_internal._hyper_18_20_chunk

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

SELECT count(*) FROM metrics_rm_multi;
 count 
-------
  4003

DROP TABLE metrics_rm_multi;
-- Running max with segmentby: each segment tracked independently.
CREATE TABLE metrics_rm_seg (time TIMESTAMPTZ NOT NULL, device TEXT, value float)
  WITH (tsdb.hypertable, tsdb.orderby='time', tsdb.segmentby='device');
NOTICE:  using column "time" as partitioning column
-- Same overlapping pattern for two devices.
INSERT INTO metrics_rm_seg SELECT '2025-07-07'::timestamptz + (i || ' minute')::interval, d, i FROM generate_series(1,1000) i, (VALUES ('d1'), ('d2')) AS v(d);
INSERT INTO metrics_rm_seg SELECT '2025-07-07'::timestamptz + (i || ' minute')::interval, d, i FROM generate_series(500,1500) i, (VALUES ('d1'), ('d2')) AS v(d);
INSERT INTO metrics_rm_seg SELECT '2025-07-07'::timestamptz + (i || ' minute')::interval, d, i FROM generate_series(1000,2000) i, (VALUES ('d1'), ('d2')) AS v(d);
INSERT INTO metrics_rm_seg SELECT '2025-07-07'::timestamptz + (i || ' minute')::interval, d, i FROM generate_series(1500,2500) i, (VALUES ('d1'), ('d2')) AS v(d);
SELECT _timescaledb_functions.compact_chunk(chunk) FROM show_chunks('metrics_rm_seg') chunk;
              compact_chunk               
------------------------------------------
 _timescaledb_internal._hyper_19_21_chunk

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

SELECT count(*) FROM metrics_rm_seg;
 count 
-------
  8006

DROP TABLE metrics_rm_seg;