If you didn't, you could get anything, because unless I'm mistaken this is the typical example used to illustrate what goes wrong when you don't have full serialisable isolation.

[–] tatterdemalion@programming.dev 1 points 1 week ago (1 children)

I believe both SERIALIZABLE and REPEATABLE READ isolation levels would raise an error for this example.

And I looked this up: Postgres won't retry the transaction for you either. Though ultimately I think this a good default behavior.

[–] FishFace@piefed.social 2 points 1 week ago (1 children)

Why would repeatable read raise an error? I'm a little rusty but I thought it was only serialisable that could do, and that repeatable read simply ensured that the committed writes of transaction 2 would not be read in transaction 1, so it updating over the top of 2 is fine.

[–] tatterdemalion@programming.dev 2 points 1 week ago

I think you're right. An UPDATE would fail, but not an INSERT.