Hacker News Reader

What transaction mode would you need to make this safe? Can you explain further?

I 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.

well, 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)

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 is pretty comprehensive.

Yeah 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.

Reply on news.ycombinator.com