You’d have similar issues on MySQL as well. PostgreSQL’s style is what has to be done when your sql server supports transactions.
And then you conflict when transactions happen, so one will fail when multiple happen at the same time.
OvermindDL1
slashdotdash
No, the UPDATE … RETURNING statement supports concurrent transactions, but will block until the first update completes. After it commits or aborts, the next transaction can continue with the next value, no gaps. This behaves like a single threaded writer. It’s different from sequences which are assigned outside of a transaction for high performance.
2
acrolink
Thank you very much. I have done something similar with Phoenix updating the sequence but doing this with a PostgreSQL procedure seems more appropriate.
0
OvermindDL1
UPDATE ... RETURNING will, but rarely do I ever update a single table at a time, transactions are almost always necessary for data consistency unless it’s an exceptionally simple database that isn’t using the database features… In addition, the primary key is rarely updated, especially as manually as that would be done. Once transactions are involved this will cause major issues either in terms of handling no concurrency or values getting out of sync.
1