Flaky: SingleFileCompressionTests DatabaseFactory_Compression_GrowingTable_ReopenSelectShouldSurvive (500 vs 499, Linux/macOS runners only)

[MPCoreDeveloper]

Symptom

SingleFileCompressionTests.DatabaseFactory_Compression_GrowingTable_ReopenSelectShouldSurvive fails intermittently on Linux/macOS/container runners:

SharpCoreDB.Tests.Storage.SingleFileCompressionTests.DatabaseFactory_Compression_GrowingTable_ReopenSelectShouldSurvive [FAIL]
Assert.Equal() Failure: Expected: 500, Actual: 499
Stack trace path /_/tests/... (container build)

Test behavior

Single-file (.scdb), Brotli compression. 500 INSERTs with auto-flush that repeatedly rewrites the row-cache block past the compression threshold, then Flush() + ForceSave() + reopen + SELECT * ORDER BY id expecting 500 rows. One row is missing after reopen.

Evidence it is environmental / not a regression

• Windows net10.0 full suite is green many times today (incl. a 5x repeat loop of this test); net11.0 full suite green (1744/0).
• The single-file code path (SingleFileTable.cs, SingleFileStorageProvider.cs, SingleFileOverflowArena.cs, FixedWidthCodec) was not touched by the Sonar cleanup batches (PRs sonar: fix mechanical and new-code issues (batch 1) #396-Flaky: SingleFileCompressionTests DatabaseFactory_Compression_GrowingTable_ReopenSelectShouldSurvive (500 vs 499, Linux/macOS runners only) #400); those only touched directory-mode files plus SqlParser point-lookup (which explicitly excludes SingleFileTable).
• Failure is intermittent and only seen on macOS/container runners.

Suspected root-cause area

WriteBlockAsync enqueues and returns; the physical write happens in the write-behind worker/drain (ProcessWriteQueueAsync / FlushPendingWritesAsync). On slower macOS/container filesystems a reopen can observe the previous block version (499 rows) even though FlushAsync uses flushToDisk: true. There is prior art for this class of bug: the macOS-regression comment in SingleFileStorageProvider.cs (~line 1579: "data stays in OS cache without fsync ... expected 2 rows, got 1") and issue #345 (metadata/data write ordering).

Suggested investigation

1. Reproduce on a failing runner by repeating this test 25-50x.
2. Capture which row id is missing and whether the data block on disk actually holds 499 or 500 records (compare the block-registry entry length vs the parsed record count).
3. Review the ordering guarantees between the write-behind drain, the block-registry flush and the FSM free/allocate (block grow path frees the old block and allocates a new offset on every flush past the page threshold).

[MPCoreDeveloper]

Diagnostics improved (PR #402): the growing-table reopen test now reports the exact missing/extra row ids on failure. Next time the CLI runner hits 500-vs-499, please paste the new assertion message - it distinguishes tail-row loss (write/truncation window) from a random middle row (parse/arena issue).\n\nLocal note: full-suite runs on this Windows box also occasionally trip InsertOptimizationsTests.Optimization1_DelayedTranspose_ImprovesByBatchProcessing ("< 100ms, was 102ms") - a separate timing-threshold flake, unrelated to the compression row loss.