3 ms·
What transaction mode would you need to make this safe? Can you explain further?
by BeefySwain 2y ago
What transaction mode would you need to make this safe? Can you explain further?
- VWWHFSfQ 2y agoI believe PG will abort the transaction on an exception (PK violation) and the subsequent update will not run in the same context that had original violation. So it could result in a data race. I don't know what isolation level would fix that, if any. My understanding is that in general, if you hit an exception in postgres at all then you can't trust the isolation of the current transaction anymore. That's what MERGE and speculative insertion (on conflict do update) addresses.
- zokier 2y agowell, yes, you are right that exception does abort the transaction. but even if you use something like 'on conflict do nothing' to avoid the exception, you still can get problems with concurrent writes (see my sibling comment https://news.ycombinator.com/item?id=41169638 https://news.ycombinator.com/item?id=41169638) REPEATABLE READ or SERIALIZABLE isolation levels would help with that, as the name suggest repeatable read ensures that the read made by inserts constraint check is repeatable in successive select (or update) statements. https://www.postgresql.org/docs/current/transaction-iso.html https://www.postgresql.org/docs/current/transaction-iso.html is pretty comprehensive.
- da_chicken 2y agoYeah this is correct. I didn't realize it. Most RDBMSs don't behave this way. They allow you to correct exceptions that occur for anything less than a deadlock without a total rollback, but not Postgres.