It's easy to see that moving data from one row to another is doable in a clustered database, and could be interpreted as a message being delivered.
The point is that you can get exactly once delivery, if your whole system is either idempotent, or you can treat the distributed system as one single unit that can be rolled back together (i.e. side effect free wrt. some other system outside the domain).
Both are cases of some form of logical monotonicity, idempotence is easier to see, but transactionality is also based on monotonicity through the used WALs and algorithms like Raft.
The article should really mention CALM (Consistency as logical monotnicity), it's much easier to understand and a more fundamental result than CAP. https://arxiv.org/pdf/1901.01930
If you have exactly-once delivery, there's no difference between being idempotent or not. The only effect of idempotence is to ignore extra deliveries.
If idempotence is a requirement, you don't have exactly-once delivery.
Exactly-once delivery semantics doesn't equal exactly-once physical messages.
tbf, Distributed Systems aren't exactly easy nor common, and each of us either learn from others, or learn it the hard way.
For example, and I don't mean to put anyone on the spot, Cloudflare blogged they'd hit an impossibly novel Byzantine failure, when it turned out to be something that was "common knowledge": https://blog.cloudflare.com/a-byzantine-failure-in-the-real-... / https://archive.md/LK8FI
- An Overview of End-to-End Exactly-Once Processing in Apache Flink (with Apache Kafka, too!) — https://flink.apache.org/2018/02/28/an-overview-of-end-to-en...
- Flink's Fault Tolerance Guarantees — https://nightlies.apache.org/flink/flink-docs-release-1.20/d...
I agree that the first port of call is to make your operation legitimately idempotent without passing IDs around, and the second is to ask if it is really important enough to care about delivery, but if you're not operating at ridiculous scale and you're okay with having a single point of failure then you get to avoid the "distributed systems are hard" rule by not actually having a completely distributed system.
- you send a message
- you receive nothing back
- what now
There is no algorithm that lets you implement exactly-once delivery in the face of delivery instability. Either you don't resend and you implemented at-most-once, or you resend and you implemented at-least-once.
You might say, "but hey, the receiver of course sends acks or checkpoints; I'm not a total buffoon". Sure. Let's game that out:
- you send message 8
- you get an ack for message 7
- you receive no more acks
- what now
Every system you'll use that says it implements exactly-once implements send lots and has some mechanism to coalesce (i.e. make idempotent) duplicate messages.