What would I need to do in order to emulate an INSERT or UPDATE in Phoenix, as opposed to an UPSERT?
Suppose I had a TRANSACTIONAL table. To do an INSERT, I then: 1. Start a transaction 2. SELECT the relevant rows, and throw an error if the SELECT is non-empty 3. UPSERT 4. Commit the transaction To do an UPDATE, I do the same, except that in step 2 I throw an error if the SELECT is empty. If all of the possible writes to those rows are enclosed in transactions, will this avoid the race conditions and give me a true INSERT and UPDATE (at a cost of having to make multiple queries, of course)? The case I have in mind is where we might have DELETE and PATCH queries coming into our API. With a back end that supports UPDATE, it's not a problem if a DELETE and a PATCH come in at the same time: either the DELETE succeeds and then the PATCH fails, or the PATCH succeeds and then the DELETE succeeds. Either way, you end up with the row's being deleted. But if we use an UPSERT statement for the PATCH, we have a problem: the DELETE can succeed, and then the PATCH will still succeed, but it'll insert the row back in again. I'm unclear as to how to use transactions to guarantee the right behaviour here. James ________________________________ Elsevier Limited. Registered Office: The Boulevard, Langford Lane, Kidlington, Oxford, OX5 1GB, United Kingdom, Registration No. 1982084, Registered in England and Wales.
