How to not rollback transaction on failed insert because of unique constraint

I have a use case where I import users from an external file.
User has a unique_constraint on email.

I want to make the import as a part of multi, iterate over provided data, save whatever I can and return errors for the rest., :users, fn _repo, _changes_so_far ->
      {successfully_inserted_users, known_errors} =
        |>{}, &1))
        |> {_ok_or_error, member_or_changeset} -> member_or_changeset end)
        |> Enum.split_with(fn
          %ClubMember{} -> true
          %Ecto.Changeset{} -> false
      {:ok, successfully_inserted_users, known_errors}

The idea is this. If I hit the unique_constraint I don’t want to rollback the transaction. But if I hit some other errors that are not recoverable, I’d like to rollback whole transaciton.

The problem is that any failed insert rollsback the transaction. Is there a way around that? Can I somehow tell Repo.insert “it is OK to fail here - don’t fail entire transaction”?


There isn’t. A constraint error fails the transaction already at the db level.

I believe You can pass the on_conflict: :nothing option to Repo.insert, it is supported by both ecto and postgres

1 Like

This is a good idea, and you can tell whether the insert worked or not based on the number of rows returned.

Sounds like a plan! I’ll try to convert structs returned without id to changesets with errors. In case I have other unique fields: there probably is no way to distinguish which one failed, right?

I ended up using unsafe_validate_unique.
This way I can check before trying to insert.

I also needed to add function:

  defp maybe_insert(%Changeset{valid?: true} = ch), do: Repo.insert(ch)
  defp maybe_insert(%Changeset{valid?: false} = ch), do: {:error, ch}

because regular insert on invalid changeset also rolls back the transaction.

Thanks for your help!

I have stumbled upon this thing as well and I am uncertain what to make of it.

The documentation doesn’t mention this behavior either in Repo.insert, Repo.transaction or in the unique_constraint validation documentation, so for the end user it appears the transaction roll backs on it’s own for some reason.

In my case I have larger transaction and I perform conditional action depending of if the insert was successful or no. Ideally I would like to retain the same transaction, and getting the same result with unsave_validate_unique would mean I have to do the locking with somethign like SELECT pg_advisory_xact_lock(tenant_id).

If it’s an expected behavior to roll back transaction completely on unique constraint validation during inset, it’s not a documented one.

And other validations do not roll the transaction back, at least if used Repo.transaction(fn -> ... end) form explicitly, I didn’t try Ecto.Multi.

It kinda feels like it should be either documented or the behavior should be aligned with other validations, i.e. the unique constraint violation if handled in changeset shouldn’t roll back the transaction.

@josevalim or @ericmj any thoughts on the above?

1 Like

The issue is that this behavior is database dependent, it isn’t something that Ecto is choosing to do. When you have a constraint violation in a postgres transaction this poisons the whole transaction, Ecto has no way to prevent this from happening.

However, you do! If you want to do an insert and not rollback, you can Repo.insert(on_conflict: :nothing) and check the return value. If the return value has no id value then no new record was inserted because the record already exists. If it does have an id, then it inserted a new record.

1 Like

Im quite sure you can achieve this with savepoitns. Something among the lines of:

Repo.transaction(fn -> 
  Enum.reduce(to_insert, [], fn t, errors ->
    case Repo.insert(t, mode: :savepoint) do
      {:ok, ...} -> errors
      {:unique_constreaint...}  -> errors
      {:other_error} = error -> [error | errors]
  |> case do
     [] -> :no_errors
     errors -> Repo.rollback(errors)

You need to add proper clauses of course, i’ve just put some random stuff there, but that’s the general idea. Of course you can also use reduce_while to rollback as soon as you get first significant error.

1 Like

OK. Do you think that documenting that on unique_constraint is good idea?

This is an excerpt from the docs of Repo.transaction

If transaction/2 is called inside another transaction, the function is simply executed, without wrapping the new transaction call in any way. If there is an error in the inner transaction and the error is rescued, or the inner transaction is rolled back, the whole outer transaction is marked as tainted, guaranteeing nothing will be committed.