...and then you additionally have to verify that the specific reason both the update and the insert failed was due to concurrency, or you can make the mistake I did a couple years ago where I had transactions spinning against my database for weeks on end due to an unrelated constraint failure I had failed to notice with some transactions that I kept retrying in a loop as part of my hacked together upsert implementation (which is why my user id numbers jumped from ~8m to ~460m... it is a meaningless mistake, but one I am reminded of every day due to that "46" staring at me :/).
Sequences in Postgres are updated even if a transaction is rolled back. It's pretty easy to see why that's important, especially when dealing with concurrent transactions.
You can't give out the same number to multiple transactions without causing a bunch of unique constraint violations. And you can't retroactively decrease a sequence number without affecting all the other transactions currently executing. You could in theory go back and plug in the gaps in a sequence, but it's more important to guarantee that a single thread of execution will always see increasing numbers than it is to guarantee that all numbers will eventually be handed out to a successful transaction.
> Important: To avoid blocking concurrent transactions that obtain numbers from the same sequence, a nextval operation is never rolled back; that is, once a value has been fetched it is considered used, even if the transaction that did the nextval later aborts. This means that aborted transactions might leave unused "holes" in the sequence of assigned values.
