db: ingest during WAL failover can prevent visibility of later writes

(The probability is low, but documenting this for completeness, since we speculated about it https://cockroachlabs.slack.com/archives/CAC6K3SLU/p1757338160400119?thread_ts=1757110210.361039&cid=CAC6K3SLU in a recent escalation, where this did NOT happen https://github.com/cockroachlabs/support/issues/3426)

Say writes to the primary directory are blocked, and WAL failover is configured. Writes will continue to commit successfully. However, if there is an ingest, that does not overlap with the memtable, and therefore does not become a flushable-ingest, it will grab a seqnum and then proceed to attempt to modify the manifest. Since the manifest write will block, normal batch writes that are at a later seqnum cannot become visible.

One solution to this would be convert all ingests while WAL failover is active into flushable ingests. The risk is creation of many small memtables.

One could reduce that risk at the higher layer e.g. CockroachDB can be made aware of WAL failover and can pause admitting operations that require ingest. The difficulty is that such admission happens on the leaseholder of a range, while the WAL failover may be on a follower, which has to ingest once the AddSStable has entered the raft log.

Jira issue: PEBBLE-1184