[Bug]: DELETE from compressed chunks does not apply query constraints

[svenklemm]

Description

When operators used in DELETE queries on compressed chunks are using non-btree operators they might not be correctly applied.

How can we reproduce the bug?

CREATE TABLE t(time timestamptz, device_installation_id int, measurement_type_id int);

SELECT public.create_hypertable('t','time',chunk_time_interval => interval '1 weeks',create_default_indexes => false);

ALTER TABLE t SET (timescaledb.compress,timescaledb.compress_segmentby = 'measurement_type_id');

INSERT INTO t
VALUES
  ('2025-01-01 00:00:00+00', 1, 1),  ('2025-01-01 00:15:00+00', 1, 1),  ('2025-01-01 00:15:00+00', 1, 1),
  ('2025-01-01 00:00:00+00', 1, 2),  ('2025-01-01 00:00:00+00', 1, 2),  ('2025-01-01 00:15:00+00', 1, 2),
  ('2025-01-01 00:00:00+00', 2, 1),  ('2025-01-01 00:00:00+00', 2, 1),
  ('2025-01-08 00:00:00+00', 1, 1),  ('2025-01-08 00:00:00+00', 1, 1),  ('2025-01-08 00:15:00+00', 1, 1),
  ('2025-01-08 00:00:00+00', 2, 2),  ('2025-01-08 00:00:00+00', 2, 2),  ('2025-01-08 00:15:00+00', 2, 2)
;
select * from timescaledb_information.chunks;

SELECT compress_chunk('_timescaledb_internal._hyper_1_1_chunk');
select * from _timescaledb_internal._hyper_1_1_chunk; 

BEGIN;
delete FROM t WHERE measurement_type_id <> 1;
select * from _timescaledb_internal._hyper_1_1_chunk;
ROLLBACK;

The = < > <= >= operators with common data types are not affected by this issue.

Workaround

In order to prevent this situation, please set the following GUC:

set timescaledb.enable_compressed_direct_batch_delete to false;

We apologize for the inconvenience and are working on a bug fix, which will be rolled out in the next 2.18.1 release. Updates will be posted on this ticket.

TimescaleDB version affected

• 2.17.0
• 2.17.1
• 2.17.2
• 2.18.0