unique constraint violated with compressed INSERT

[akuzm]

-- ON CONFLICT DO NOTHING on a compressed hypertable silently inserts a 
-- duplicate when the UNIQUE NULLS NOT DISTINCT arbiter includes a
-- segmentby column and the inserted value is NULL.
          
CREATE TABLE iot_readings ( 
    ts   timestamptz NOT NULL,
    seg1 int,
    seg2 int,
    UNIQUE NULLS NOT DISTINCT (seg2, ts)
);
SELECT create_hypertable('iot_readings', 'ts', create_default_indexes => false);
                                                           
ALTER TABLE iot_readings SET ( 
    timescaledb.compress,
    timescaledb.compress_segmentby = 'seg1, seg2',
    timescaledb.compress_orderby   = 'ts'
);
  
INSERT INTO iot_readings VALUES ('2025-01-01 00:00:00+00', 1, NULL);
SELECT compress_chunk(show_chunks('iot_readings'));
  
INSERT INTO iot_readings VALUES ('2025-01-01 00:00:00+00', 1, NULL)
    ON CONFLICT DO NOTHING;             -- INSERT 0 1, duplicate inserted
SELECT count(*) FROM iot_readings;      -- 2 (expected 1)
                                     
SELECT decompress_chunk(show_chunks('iot_readings'));
-- ERROR:  duplicate key value violates unique constraint

Looks like there's some problem with handling null search for segmentby columns. It doesn't reproduce if you only have one segmentby.

[kpan2034]

I was able to reproduce this with a single segmentby column by disabling enable_dml_decompression_tuple_filtering, and the segmentby isn't the first column on the table.

CREATE TABLE iot_readings (
    ts   timestamptz NOT NULL,
    seg1 int,
    seg2 int,
    UNIQUE NULLS NOT DISTINCT (seg2, ts)
);
SELECT create_hypertable('iot_readings', 'ts');

ALTER TABLE iot_readings SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'seg2', -- using seg1 blocks the insert correctly
    timescaledb.compress_orderby   = 'ts'
);

INSERT INTO iot_readings VALUES ('2025-01-01 00:00:00+00', 1, NULL);
SELECT compress_chunk(show_chunks('iot_readings'));

SET timescaledb.enable_dml_decompression_tuple_filtering = off;
INSERT INTO iot_readings VALUES ('2025-01-01 00:00:00+00', 1, NULL);             -- INSERT 0 1, duplicate inserted
SELECT count(*) FROM iot_readings;      -- 2 (expected 1)

SELECT decompress_chunk(show_chunks('iot_readings'));
-- ERROR:  duplicate key value violates unique constraint

[kpan2034]

Using timescaledb.debug_compression_path_info, I found that this row was being filtered out when doing a table scan:

INFO:  Using table scan with scan keys: index 0, heap 2, memory 0.
INFO:  Number of compressed rows fetched from table scan: 1. Number of compressed rows filtered: 1.

Index scans can be disabled either by disabling tuple filtering, or when a matching index isn't found on the compressed chunk (in the repro script above with 2 segmentby columns).

Looks like we filter out rows with NULL segmentby here, which we shouldn't when NULLs are not distinct. Raised a fix here.