live_views().lag_seqtxn measures against the base table's applied txn, not the sequencer head

Summary

live_views().lag_seqtxn is named and documented as the count of unprocessed base-table transactions, but it is computed against the base table's applied transaction, not the sequencer head. When a base table's WAL apply is stalled or the table is suspended, lag_seqtxn reads 0 indefinitely while transactions pile up in the sequencer — the column reports "caught up" for a view that is in fact being starved.

Together with lag_micros (below), this leaves live_views() unable to distinguish a healthy idle view from a starved one, which is precisely the discrimination an operator needs when a live view stops updating.

Detail

https://github.com/questdb/questdb/blob/9559b8d490c63bf6edad1054f3af9b2231761ad7/core/src/main/java/io/questdb/griffin/engine/functions/catalogue/LiveViewsFunctionFactory.java#L422-L435

case COLUMN_LAG_SEQTXN -> {
    // base.sequencer.head - last_processed. ...
    ...
    SeqTxnTracker tracker = engine.getTableSequencerAPI().getTxnTracker(baseToken);
    long head = tracker.getWriterTxn();
    long lp = instance.getLastProcessedSeqTxn();
    yield head < 0 || lp < 0 ? Numbers.LONG_NULL : Math.max(0, head - lp);
}

SeqTxnTracker keeps two distinct counters:

• seqTxn — the sequencer head, advanced by notifyOnCommit() when a transaction is committed to the sequencer.
• writerTxn — "txn that is available for reading", advanced by updateWriterTxns() from ApplyWal2TableJob — i.e. the applied transaction.

getWriterTxn() returns the latter, so lag_seqtxn is applied_txn - last_processed, not sequencer_head - last_processed. The comment directly above the code states the opposite of what the code does.

Why it matters

When the base table's own apply is stalled or the table is suspended:

• writerTxn freezes
• lag_seqtxn → 0, indefinitely
• view_status stays active
• writer_stall_micros stays 0 (nothing is stalling the view's writer)

Every health signal in live_views() reads nominal while the view publishes nothing.

lag_micros does not break the tie: lastFlushTimeUs is only stamped on an actual flush, so a view whose base table is genuinely idle grows lag_micros unbounded exactly like a starved one.

Observed

Two live views over a shared base table had published nothing for ~19.5 h. Sampling live_views() three times:

lag_micros tracked wall clock to the millisecond — i.e. zero flushes — while lag_seqtxn was 0 and view_status was active for both views. Diagnosis required cross-checking wal_tables() for sequencerTxn - writerTxn; live_views() on its own could not separate "base table idle" from "base table apply stalled".

Proposed fix

1. Add a column exposing the view's lag against the sequencer head — e.g. base_sequencer_lag_seqtxn = tracker.getSeqTxn() - lastProcessedSeqTxn — so a starved view is visible without joining wal_tables(). The existing applied-relative lag_seqtxn is worth keeping; it is the correct measure of the view's own progress. The two together separate "view is behind" from "view is starved".
2. Fix the incorrect comment at LiveViewsFunctionFactory.java:423.
3. Document lag_seqtxn as applied-relative in the live_views() reference.

Version

10.0.1 (9559b8d); code is unchanged on current master.