Live view that emits no rows keeps its base table's WAL from being purged #7852

Summary

A live view writes a head checkpoint only on a refresh cycle that emitted rows. A view whose filter rejects every row of a stretch of base ingest keeps consuming the commits, but its head checkpoint stays where the last emitted row left it, and WalPurgeJob holds the base table's WAL at that checkpoint.

The base table's WAL segments and sequencer parts then accumulate for as long as no matching row arrives. cairo.live.view.checkpoint.max.duration.micros does not bound it, and live_views() shows nothing unusual: the view is active with zero lag.

To reproduce

CREATE TABLE base (ts TIMESTAMP, sym SYMBOL, x DOUBLE) TIMESTAMP(ts) PARTITION BY DAY WAL;

CREATE LIVE VIEW lv FLUSH EVERY 100ms START FROM BEGINNING AS
SELECT ts, sym, x, sum(x) OVER (PARTITION BY sym ORDER BY ts ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) s
FROM base
WHERE x > 0;

1. Commit a few rows the filter accepts (x = 1.5), in timestamp order.
2. Keep committing rows it rejects (x <= 0), in timestamp order.
3. Count the wal*/<segment> directories of base, and compare with a table that has an unfiltered view or no view.

These settings make it visible within seconds. They only change the measurement granularity; the default-settings run below shows the same retention.

cairo.wal.segment.rollover.row.count=1
cairo.wal.purge.interval=300
cairo.live.view.checkpoint.max.duration.micros=1000000

Observed

On a full ServerMain at 6c63556431, on the wall clock, with the three settings above: 6 accepted commits, then rejected commits every 250 ms for 15 s (15 duration cadences), then 4 s without commits.

• live_views() for lv: view_status = active, lag_seqtxn = 0, lv_consumed_seqtxn = 62, checkpoint_timeline_normalized_base_seqtxn = 1.
• A server restart changes nothing: base still keeps 61 segments.
• One accepted row moves the head checkpoint to the latest commit, but the purge floor stays at 1 and base keeps 62 segments. The floor is the minimum over both checkpoint timeline slots, so the WAL is released only after a second seal.
• DROP LIVE VIEW releases everything, and so does an invalidation of the view.

With the default segment rollover (200,000 rows) and 300 commits of 5,000 rejected rows each, base keeps 8 segments (42.0 MB) where the control table keeps 2 (8.4 MB), measured after a restart. That is all base ingest since the pinned commit.

The duration setting was also tried at 1 s with cairo.live.view.checkpoint.rows=1 on a hand-driven job: the clock advanced 10 s per commit and then 3 idle hours, and the head checkpoint did not move.

Cause

A cycle that emits nothing advances the consumed watermark and returns without sealing. Its comment states an intent that the missing seal defeats: "Advance the watermarks anyway so base WAL retention releases the consumed segments."

The seal call sits behind appendedRows > 0 at both drain sites:

The duration trigger is evaluated only inside that call, so it never runs for a view that emits nothing, and no timer drives such a view:

WalPurgeJob floors the purge at the minimum of the consumed watermark, the head checkpoint's base seqTxn and the timeline floor, so the consumed watermark alone releases nothing:

server.conf describes cairo.live.view.checkpoint.max.duration.micros as a "Wall-clock ceiling between consecutive head-checkpoint writes" that "Caps the worst-case O3 and restart replay window for low-rate views".

Proposed fix

Evaluate the duration trigger on a cycle that consumed commits and emitted nothing, and seal a head checkpoint at the consumed seqTxn. The filter rejected those rows before they reached the window, so the sealed state is the previous root's at a later base coordinate.

Version

master at 6c63556431 (10.0.2-SNAPSHOT). The files involved are unchanged at 77c6cf2d6b.

Found while reviewing #6939. That PR does not change this behaviour for in-order ingest.