Workaround for affected versions: 2.17.0, 2.17.1, 2.17.2 and 2.18.0

[philkra]

Description

Executing a DELETE statement on Hypertables with compression enabled with a compress_segmentby column is present in the WHERE clause, and the operator <>, leads to deleting all rows within the compressed chunk i.e. even the rows which are not matched by WHERE clause.

Example

ALTER TABLE public.my_hyper_table SET (
  timescaledb.compress,
  timescaledb.compress_segmentby = 'my_segment_by'
);

-- This statement will delete all records from the affected chunks
DELETE FROM my_hyper_table WHERE my_segment_by <> 'Some value';

The = operator is 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

[philkra]

closing as duplicate of #7644