tsl/compression: fix stale sparse-index entries after rebuild_sparse_index

[tureba]

What type of bug is this?

Data corruption / wrong query results

What subsystems and features are affected?

Compression (sparse indexes / columnstore), rebuild_sparse_index()

What happened?

After changing a hypertable's compress_index setting and calling rebuild_sparse_index() (or recompressing) on an already-compressed chunk, the btree index that TimescaleDB maintains on the compressed table's sparse index metadata columns (segmentby column plus _ts_meta_v2_first_time/_ts_meta_v2_last_time, and any configured minmax/bloom columns) can end up pointing at superseded, dead tuples instead of the row's new location.

Once that happens, any query plan that uses an index scan or bitmap scan against the compressed table (chosen by the planner, or forced via enable_seqscan = off) silently returns fewer rows than exist, or the wrong rows, for the affected compressed batch — while a sequential scan over the same table returns the correct data. This is a genuine data-correctness bug: two scan strategies over the same committed data return different results.

The bug does not always surface immediately after the metadata rewrite, which is why it can look intermittent: PostgreSQL's HOT-chain "redirect" mechanism transparently follows a stale index entry to the tuple's current location, as long as the whole update chain stays on the same heap page. The wrong results only become visible once that redirect chain is broken — either by ordinary autovacuum/pruning activity, or immediately, whenever the metadata rewrite doesn't fit back onto its original page and the tuple must relocate.

Root cause

populate_sparse_index_columns() in tsl/src/compression/recompress.c rewrites each compressed batch's sparse-index metadata columns in place:

HeapTuple new_tuple = heap_modify_tuple(compressed_tuple, ...);
ExecStoreHeapTuple(new_tuple, update_slot, false);

/*
 * Sparse index metadata columns are not covered by any index.
 * If indexes on metadata columns are added in the future,
 * this will need to handle index updates via update_indexes.
 */
TU_UpdateIndexes update_indexes;
simple_table_tuple_update(compressed_rel, &tid, update_slot,
                          GetActiveSnapshot(), &update_indexes);
ExecClearTuple(update_slot);

The comment's premise is stale: the sparse index metadata columns are covered by a btree index (<chunk>_compressed_<segmentby>__ts_meta_v2_first_time___idx, covering the segmentby column, _ts_meta_v2_first_time, _ts_meta_v2_last_time, plus any minmax/bloom columns configured via compress_index). update_indexes is computed correctly by simple_table_tuple_update()/heap_update() on every call, but the result is discarded — no new index entry is ever inserted for a non-HOT update, leaving the old entry (now pointing at a dead tuple) as the only index entry for that row.

How can we reproduce the bug?

This reproduces deterministically on stock PostgreSQL — no special extension or vendor build required, and confirmed on PostgreSQL 16, 17, and 18. The key is to make each compressed row wide enough that the sparse-index metadata rewrite can't fit back onto its original heap page, forcing a non-HOT update:

CREATE EXTENSION IF NOT EXISTS timescaledb;

CREATE TABLE rsi_bug(
    ts timestamptz NOT NULL,
    device text NOT NULL,
    val int,
    filler text DEFAULT repeat('x', 500)  -- pads rows so pages fill up
);
SELECT create_hypertable('rsi_bug', 'ts');

INSERT INTO rsi_bug(ts, device, val)
SELECT '2024-01-01'::timestamptz + (i * interval '1 second'), 'd' || (i % 20), i
FROM generate_series(1, 4000) i;

SELECT format('%I.%I', chunk_schema, chunk_name)::regclass AS chunk1
FROM timescaledb_information.chunks
WHERE hypertable_name = 'rsi_bug' ORDER BY range_start LIMIT 1 \gset

ALTER TABLE rsi_bug SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'device',
    timescaledb.compress_orderby = 'ts',
    timescaledb.compress_index = 'bloom("val")'
);
SELECT compress_chunk(:'chunk1');

-- Drop and restore the sparse index config, rebuilding it each time --
-- this is a supported, documented workflow, not an edge case.
ALTER TABLE rsi_bug SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'device',
    timescaledb.compress_orderby = 'ts',
    timescaledb.compress_index = ''
);
SELECT _timescaledb_functions.rebuild_sparse_index(:'chunk1'::regclass);

ALTER TABLE rsi_bug SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'device',
    timescaledb.compress_orderby = 'ts',
    timescaledb.compress_index = 'bloom("val")'
);
SELECT _timescaledb_functions.rebuild_sparse_index(:'chunk1'::regclass);

-- Ground truth: sequential scan, one row per device
SET enable_indexscan = off;
SET enable_bitmapscan = off;
SELECT device, count(*) FROM <compressed_table> WHERE device = 'd5';
RESET enable_indexscan;
RESET enable_bitmapscan;
-- -> 1 row

-- Forced index/bitmap scan over the SAME committed data
SET enable_seqscan = off;
EXPLAIN (analyze, costs off)
SELECT device FROM <compressed_table> WHERE device = 'd5';
SELECT device FROM <compressed_table> WHERE device = 'd5';
RESET enable_seqscan;
-- -> 0 rows (WRONG: should match the seq scan above)

(<compressed_table> is the chunk's internal compressed relation, found via _timescaledb_catalog.compression_settings.compress_relid for the chunk.)

Actual output observed on a clean vanilla PostgreSQL 17.9 build:

Bitmap Heap Scan on _hyper_3_5_chunk_compressed (actual time=0.011..0.012 rows=0 loops=1)
  Recheck Cond: (device = 'd5'::text)
  Heap Blocks: exact=1
  ->  Bitmap Index Scan on _hyper_3_5_chunk_compressed_device__ts_meta_v2_first_ts__ts_idx (actual time=0.007..0.008 rows=3 loops=1)
        Index Cond: (device = 'd5'::text)

 device
--------
(0 rows)

vs. the sequential-scan ground truth, which correctly returns d5.

Root-cause confirmation (via pageinspect): the btree index still contains entries pointing at the row's pre-rebuild ctid; the row's actual current location (after the metadata rewrite forced it onto a different heap page) has no corresponding index entry at all.

The fix

populate_sparse_index_columns() now opens the compressed table's indexes once (CatalogOpenIndexes) and inserts a new index entry after each update whenever it wasn't HOT-eligible, using the TU_UpdateIndexes result the code already computed but previously discarded (ts_catalog_index_insert(), TimescaleDB's own vendored copy of PostgreSQL's static CatalogIndexInsert()) — mirroring the same open-index/update/insert-index-entry sequence PostgreSQL's own CatalogTupleUpdate() uses for catalog tuples.

This also required switching from simple_table_tuple_update() to simple_heap_update(): the former funnels the update through a TupleTableSlot and materializes a throwaway copy of the tuple inside heapam_tuple_update(), so the caller's own HeapTuple is never updated with the tuple's real post-update location or HOT status — using it to drive ts_catalog_index_insert() silently inserted nothing. simple_heap_update() operates directly on the passed HeapTuple, exactly as CatalogTupleUpdate() relies on, and is consistent with the rest of this function, which already assumes a plain heap-backed relation (heap_modify_tuple, heap_deform_tuple).

Added a regression check in rebuild_sparse_index.sql that forces an index/bitmap scan after the compress_index drop-and-restore dance in Test 10 and asserts the per-device batch counts match a sequential scan; this fails against unpatched main and passes with the fix.

Verified against clean source builds of vanilla PostgreSQL 16.14, 17.9, and 18.4: compiles warning-free, and the full compress*/recompress*/*sparse* TSL regression suite (51 tests) passes on all three.

[github-actions]

@akuzm, @dbeck: please review this pull request.

Powered by pull-review

[codecov]

Codecov Report

✅ All modified and coverable lines are covered by tests.

📢 Thoughts on this report? Let us know!

[tureba]

At first glance, I don't see how failures in cagg_hierarchical_concurrent_refresh and cagg_concurrent_policy_register would be caused by my proposed changes. Those tests pass for me locally, and that is an independent feature, AFAIK.

EDIT: Actually, I see them fail in other PRs as well, with the same outputs: 10315 10313.
So it looks like they are just unstable tests overall, and their failures are unrelated to this PR.

[Poroma-Banerjee]

Please add a changelog entry.

[Poroma-Banerjee]

Adding the changelog entry myself