[Bug] sranges stale hole rows

[axlnur]

Bug描述 Describe the bug

On the QuestDB backend, sranges_q accumulates has_data=false rows that can never be retired, and once one exists every later run reads the covered range as incomplete. The result is a fatal DbReadFail at backtest init:

[DbReadFail] questdb kline coverage not visible before timeout: sid=298 timeframe=15m start=1660704300000 end=1785542400000
  at orm/questdb_visibility.go:172 waitForQuestKlineCoverageVisible
  at orm/kdata.go:429            downOHLCV2DBRange
  at data/feeder.go:1124         (*TfSeriesLoader).DownIfNeed
  at opt/backtest.go:251         (*BackTest).Init

The bars themselves are present — this is metadata that contradicts the data it describes. Two defects combine:

1) Logical deletes are keyed on derived spans instead of stored rows (write side).
QuestDB has no DELETE, so batchInsertSrangesDeleted (orm/srange.go:435, :588) retires a row by inserting one with the same (sid, tbl, timeframe, start_ms) and is_deleted=true. But it is handed spans, the output of loadSRangesSpans — i.e. after normalizeSRangeSpans and overlaySRangeSpans(cache). Any stored row whose start_ms was absorbed by that merge never gets a marker and stays active forever.

Proof from a real failure: the delete marker written at 2026-08-01T12:29:13.840392Z carried stop_ms = 2026-07-29 14:00, while no row with that stop_ms ever existed — the real has_data=true row had stop_ms = 2026-07-29 14:45. The marker was synthesised from normalised output, not from a row key. In that same transaction only 2 of the 4 overlapping rows were retired.

2) normalizeSRangeSpans lets the stale row win (read side).
orm/srange.go:250-258 breaks overlap ties on the larger StartMs:

if s.StartMs <= a && s.StopMs >= b {
    if chosen == nil || s.StartMs > chosen.StartMs {
        chosen = s
    }
}

A leftover hole always starts later than the broad has_data=true span that superseded it, so it wins every sub-interval it covers. max StartMs is a proxy for "more specific", and it inverts exactly when the broader row is the newer one.

Blast radius on one database: 34 (sid, tbl, timeframe) groups / 72 contradictory rows (kline_15m 46, kline_1h 18, kline_1m 8). Each run retires exactly one leading zombie and then dies on the next pair, so four backtests in a row failed on four different symbols.

The TimescaleDB path is unaffected — mergeSpansIntoPg issues a real DELETE + INSERT inside a transaction (orm/pg_srange.go:293).

具体复现流程？Steps To Reproduce

1. Pure-logic reproduction, no database needed. Add to orm/srange_logic_test.go the four rows QuestDB actually held for one symbol at kline_15m (a broad true span plus three stale holes written earlier the same afternoon):

spans := []srangeSpan{
    {StartMs: 1660704300000, StopMs: 1785542400000, HasData: true},  // written 2026-08-01 12:29:13
    {StartMs: 1785334500000, StopMs: 1785369600000, HasData: false}, // written 2026-07-29 14:20:56
    {StartMs: 1785335400000, StopMs: 1785369600000, HasData: false}, // written 2026-07-29 14:44:43
    {StartMs: 1785336300000, StopMs: 1785369600000, HasData: false}, // written 2026-07-29 14:57:39
}
got := normalizeSRangeSpans(filterSRangesSpans(spans, 1660704300000, 1785542400000))
// got == [{1660704300000 1785334500000 true} {1785334500000 1785369600000 false} {1785369600000 1785542400000 true}]
// => subtractMSRanges reports a hole [1785334500000, 1785369600000) that the data does not have

questKlineCoverageVisible therefore never returns true, and waitForQuestKlineCoverageVisible fails after the full klineInsertQuestVisibilityGrace.

2. How those rows appear in normal operation, on QuestDB: run repeated downloads for a timeframe while the requested endMs is still in the future (a sweep that keeps asking for "up to end of day" every few minutes). Each pass writes a [last bar → end of window) hole whose start_ms has advanced, and the previous hole is not retired because normalisation merged them into one span keyed on the earliest start_ms.

3. The next day, download the same range for real. UpdateKRange writes a has_data=true span covering the whole period — but the earlier holes are still active, still start later, and still win. From here every backtest touching that symbol/timeframe dies at Init, and each run only retires one of the leftover holes.

期望行为 Expected behavior

• A has_data=true span written after a hole must supersede it; coverage for a fully downloaded range must read as complete.
• Retiring a row must address the row that actually exists: the logical-delete list has to be built from raw sranges_q rows, not from normalised/merged spans, or rows silently become immortal.
• A hole recorded after a covering span must still carve it out — the rule should be "newest write wins", not "larger start_ms wins".

截图 Screenshots

运行环境 Running Environment

• OS: MacOS
• CPU: arm
• Version: v0.4.2-beta.14 (c3400b66), QuestDB backend

其他信息 Additional context

The fix we are running locally, in case it is useful:

• Carry the row version through: loadSRangesSpansFromDB selects cast(ts as long) into a new srangeSpan.VerUS field, and normalizeSRangeSpans breaks ties on the newest write, falling back to max StartMs only for spans that have no version (in-process cache, computed segments). This alone makes every already-corrupted row inert, with no data migration.
• Split the delete list from the coverage picture: a new loadSRangeRowsFromDB returns stored rows verbatim, and UpdateSRanges / UpdateSRangesWithHoles write their delete markers from the union of those raw rows and the existing span view, de-duplicated by start_ms. Both halves are required: raw rows retire keys the normalisation had absorbed, and the span view carries the in-process cache, so a row this process wrote but whose WAL commit is not visible yet is still retired (dropping that half makes TestSeriesRepoQuestDBDeleteHidesMiddleHole fail). Only the delete list changes — spans must keep feeding overhangSegs and the points/coveredAt computation, since swapping it wholesale would break overhang reinsertion and lose real coverage.

One property worth stating explicitly: "newest write wins" makes the resolution depend on the writers agreeing about wall-clock time, which the max StartMs rule did not. That is a non-issue when every writer runs on one host, but if several hosts write sranges_q for the same (sid, tbl, timeframe), clock skew between them becomes part of the ordering. If that is a supported topology, a monotonic sequence column would be the skew-free version of the same fix.

Verified with the pure-logic test above (red before, green after), the full hermetic engine suite, the live QuestDB TestUpdateSRanges* / TestUpdateSRangesWithHoles* / TestUpdateSRangesRandomInvariant integration tests, and a real backtest that previously died at Init on this data.

微信/QQ/Telegram/Discord/Email

• alx@nur.re