thiagomajesk
Hi everyone, it seems I can’t get out of this forum this week 
(you guys are awesome btw).
So, I got surprised by this behavior while using Ecto and I’m still banging my head against the wall with some conflicting information.
A coworker opened this issue: Updating a record in a Repo transaction failure · Issue #3944 · elixir-ecto/ecto · GitHub yesterday where he describes something unexpected we found with how transactions work.
The context of the whole thing is in the issue, but to summarize… The docs about transaction/2 say:
If an unhandled error occurs the transaction will be rolled back and the error will bubble up from the transaction function. If no error occurred the transaction will be committed when the function returns. A transaction can be explicitly rolled back by calling
rollback/1, this will immediately leave the function and return the value given torollbackas{:error, value}
This is what we are trying to do… We always have a “tag” persisted after trying to save a “post”. If we are unable to persist the “tag”, we also don’t care about saving the “post”:
Repo.transaction(fn repo ->
case repo.insert(changeset) do
{:ok, post} ->
# Great! Please also save the tag confirming it
# If we can't just explode and rollback...
tag
|> Ecto.Changeset.change(%{name: "success"})
|> repo.update!()
{:error, changeset} ->
# Oh noes! Please save some information about it
# If we can't save just explode and rollback...
tag
|> Ecto.Changeset.change(%{name: "failure"})
|> repo.update!()
end
end)
What surprised us that is that this operation fails, even though we are handling the errors in the first insert. This is the exception raised when the code gets to the repo.update! (this is from the adapter, so it also happens with the safe alternative repo.update):
** (Postgrex.Error) ERROR 25P02 (in_failed_sql_transaction) current transaction is aborted, commands ignored until end of transaction block
Ok, apparently there’s something preventing the transaction from succeeding. After conducting some tests it seems that repo.insert is putting the transaction in a bad state and repo.update! can’t succeed because of it. We had that confirmed by jose and as it seems, this error is happening because an operation already failed inside the transaction. He also seems to agree that the docs are talking about exceptions and not just “errors”. So, what exactly are we missing here?
If the docs are right about the transaction rolling back on exceptions, why can’t this code succeed? If not, and we agree that there’s something to be improved in the docs, what should the correct behavior be?
I’m certain that there’s some important information I’m missing, but I’m feeling a bit misguided by the docs and we want to improve it for posterity.
Where Next?
Trending in Questions
Other Trending Topics
Latest on Elixir Forum
Sponsor Spotlight
Supporting innovation across the BEAM ecosystem.
Categories:
Sub Categories:
Forums
Popular Tags
- #ecto
- #liveview
- #troubleshooting
- #learning-elixir
- #deployment
- #library
- #erlang
- #testing
- #genserver
- #mix
- #absinthe
- #remote-other
- #otp
- #plug
- #how-to-question
- #macros
- #postgres
- #elixirconf
- #channels
- #exunit
- #discussion
- #code-sync
- #podcasts
- #javascript
- #onsite
- #dialyzer
- #docker
- #authentication
- #umbrella
- #full-time-contract
- #podcasts-by-brainlid
- #ecto-query
- #blog-post
- #elixirconf-us
- #elixir-ls
- #ai
- #phoenix_html
- #iex
- #graphql
- #genstage
- #websockets
- #supervisor
- #advent-of-code
- #distillery
- #processes
- #api
- #forms
- #hex
- #security
- #metaprogramming
Our Sponsors
Build Elixir applications with speed and confidence.
Supporting innovation across the BEAM ecosystem.
We build reliable cloud platforms for business-critical systems.
Catch errors, track performance, monitor hosts and more.
Error tracking, logs, uptime, and Just Enough APM. Built for developers.
Practical resources that improve the lives of professional developers.
Master modern tools, write cleaner code, and tackle real-world challenges.
The deployment platform built for Elixir: PaaS ease, VPS control.
The team behind Membrane, Popcorn, LiveDebugger. Available for hire.
Courses that'll move you from confusion to "Aha, now I get it!"
Showing Posts 1 to 10- Show Best Posts
- Show All (oldest first)
- Show All (newest first)
joey_the_snake
I think this is normal database behaviour. Whatever database you are using shouldn’t let you continue with the same transaction if a previous command caused an error. This is an error from the database not from Ecto:
** (Postgrex.Error) ERROR 25P02 (in_failed_sql_transaction)thiagomajesk
Yep, we found that out ourselves afterward, but still unexpected considering the docs description. I guess it’s perhaps one of those “Ecto is not your ORM” moments
.
Still, there are some improvements to be made to the docs and we were already feeling kinda stressed out by apparently contradictory information. I’m honestly kinda drained at the point of having things spelled out to me.
BTW, just to vent a little bit: It’s not immediately obvious to more experienced people that sometimes questions like this one are a symptom of some sort of cognitive dissonance or lack of broader understanding about a subject. So answering those questions too objectively, without taking into account the reason for such misalignment of information might worsen the experience even more and in the end, make people more stressed overall. I know this is a technical community, but even so, we are still only people
.
trisolaran
What I guess it’s going on is that the first
insertfailed because it was violating some DB constraints, but this DB error was caught by your changeset (perhaps using unique_constraint/3 or similar) and converted into a changeset error. So the exception has been handled in the code, but the DB transaction is still tainted by the error and unable to continue (this is what the DB error is telling you).I guess the documentation may be technically correct in that it says nothing about what happens when an error is handled
That said, it would make sense to describe this possible situation.
All of this of course assuming that my above assumption is correct.
thiagomajesk
Hey, @trisolaran this was intuitively what we thought at first, but look at this repro over here: ecto-transaction-flow-sample/script.exs at master · rafaeliga/ecto-transaction-flow-sample · GitHub. There is a constraint check in the changeset but it doesn’t seem to be enough to prevent this from happening.
Technically correct can be the best kind of correct
, but unfortunately not enough to help our thick skulls I’m afraid!? Jokes aside, We will try to help with that if we can so no one else goes through the same experience ever again.
trisolaran
Yeah that’s what I meant.
unique_constraintdoesn’t prevent the DB error, it works by waiting for the DB error to happen on insert, catching it and putting it into the changeset. What you’re seeing is expected. When you’re callinginserthere you’re triggering a DB error.unique_constraintis simply catching the error for you and turning it into a nice human friendly error for your changeset, but the DB transaction is still in an error state.thiagomajesk
Although a little unintuitive at first; considering that Ecto doesn’t do much for those cases it makes sense that it behaves like that. Thanks for the clarifications @trisolaran
.
So as it seems, it simply isn’t possible to wrap two operations in the same transaction if one of them relies on checking any kind of constraints.
That question that remains is if there’s another way to solve this without having to manually check for the existence of the record in the then to prevent tainting the transaction. Any particular suggestions!?
joey_the_snake
You could do an insert with
on_conflict: :nothing. This will not cause an error.thiagomajesk
Wow!!!
I have never thought about using the
. Excellent, excellent observation @joey_the_snake!!! 
:on_conflictoption with intent other than for upserts but this makes complete senseal2o3cr
For whatever it’s worth, this issue has been confounding people for a long time (note the date!):
trisolaran
I’m afraid that won’t help.
on_conflict: :nothingwill prevent Ecto from raising, but the DB doesn’t care about it: the conflict violation will still have occurred, your post will not be inserted and the transaction will be in an error state. Plus you will not get any feedback on whether you were able to successfully insert your post or not, and you want to know that, right?My suggestion: rollback your transaction at the first error and handle error reporting and logging outside of it.