Yes, you described the problem exactly as it is. The problem is not in arrive order to subscriber, the problem is "selecting next messages with offset > last_offset". And in this case you simply miss late messages.
Oh ok.
Well, I don't believe is permanently solvable, but there can be mitigation techniques where instead the software reads messages way back every now and then, to recover some messages.
Some messages might still be too late and get missed, but most of them should get through, which is what every messaging service is currently doing
It is solvable, but not with SQL. I mean, one have to have side logic to keep all transactions, messages and such and use as simple storage (i.e. do not use database transaction/locking mechanisms for main business logic).
Yeah my point is, it's not solvable by the transport mechanism.
The application logic can indeed solve it, "eventually consistent systems" are a thing. My main goal was figuring out if this was impossible in SQL for some reason, but my understanding is just that the implementations are usually weak and do the "read-back" they need to, to recover late messages.
