Does the RBF patch have a realistic chance of being accepted? It seems like so much stability is lost that Bitcoin becomes practically unusable from the consumer perspective.

Bitcoin Core development is based on consensus. Right now there is no consensus for this change, and it's not really a brand new proposal.

But in reality, of course that doesn't really matter. It doesn't matter what code is accepted in some git repository somewhere, what matters is which code is actually run in the wild. Miners and pool operators are the ones who really have the power to accept or reject RBF by running their bitcoin with or without the patch. Right now almost all of them don't use it and the author of this article argues that it will stay that way. We will see.

Worst case things go wrong and bitcoin becomes unusable for a usecase that it was never designed to address.