Transactions can fail because they conflict with other transactions happening at the same time.
It's not an application bug. It's real life transactions happening on a production system. It's normal for that to happen all the time. The app can retry, etc., but it should be expected to happen.
Having to deal with distributed transactions is not something easy. Especially when they're part of many different systems.
For example, you'd have to wait until the transaction commits successfully before setting the value in the cache, which makes it hard to read.
Also, life in general happens. Compute a value, cache it, save things to the database, make API calls, and then a network error happens cancelling everything that you've just done. Having code that handles this kind of possibility is relatively hard to write/read.
Right but postgres isn't going to help with this if the application developer isn't doing safe and proper transaction management in the first place. What you described is a bug in the application logic for when and how to update the cache.
It's super hard to get this right. E.g. if you only update the cache after the transaction commits, you might commit without updating the cache, or if 2 writers interleave, the first one to commit might make the final update to the cache with a stale value.
