I have a “smart” put function that takes care of saving (creating or updating) a resource and all its sub resources automatically. It works by recursively calling itself for each sub resource (as deeply as needed).

The database operations are wrapped in a Repo.transaction/1 function, which means that when the put function calls itself, the transactions get nested.

Whenever something fails when saving a sub resource, I want the outer transaction to fail and return some information about what went wrong, typically a changeset with errors.

This works in the general case, but not when a check_constraint/3 results in a changeset with errors. In the example below, the StayPolicyPeriod changeset function has the following validation:

cast(entity, [...])
|> check_constraint(:opens_at, name: "stay_policy_periods_opens_before_start")

This works as it should (setting an error on the changeset instead of causing an exception), but it also seems to trigger a generic rollback which doesn’t return any information.

I’ve created a small script to illustrate the issue:

defmodule TestNestedTransactions do
  require Logger
  import Ecto.Query, warn: false

  alias ApiServer.Repo
  alias ApiServer.V1.Geo.Country
  alias ApiServer.V1.Stays.StayPolicy
  alias ApiServer.V1.Stays.StayPolicyPeriod

  def test_country do
    outer_transaction_result =
      Repo.transaction(fn ->
        inner_transaction_result =
          Repo.transaction(fn ->
            Country.changeset(%Country{}, %{
              name: "Test Country",
              code: "TEST",
              enabled: true
            })
            |> Repo.insert()
          end)

        Logger.info("COUNTRY inner transaction result: #{inspect(inner_transaction_result)}\n")
        inner_transaction_result
      end)

    Logger.info("COUNTRY outer transaction result: #{inspect(outer_transaction_result)}\n")
    outer_transaction_result
  end

  def test_stay_policy_period do
    outer_transaction_result =
      Repo.transaction(fn ->
        inner_transaction_result =
          Repo.transaction(fn ->
            some_stay_policy = from(sp in StayPolicy, limit: 1) |> Repo.one()

            StayPolicyPeriod.changeset(%StayPolicyPeriod{}, %{
              stay_policy_id: some_stay_policy.id,
              period: %Postgrex.Range{
                lower: ~D[2022-01-10],
                upper: ~D[2022-02-01]
              },
              opens_at: ~U[2022-01-20 12:00:00Z]
            })
            |> Repo.insert()
          end)

        Logger.info(
          "STAY POLICY PERIOD inner transaction result: #{inspect(inner_transaction_result)}\n"
        )

        inner_transaction_result
      end)

    Logger.info(
      "STAY POLICY PERIOD outer transaction result: #{inspect(outer_transaction_result)}\n"
    )

    outer_transaction_result
  end
end

TestNestedTransactions.test_country()
TestNestedTransactions.test_stay_policy_period()

which results in the following output:

[info] COUNTRY inner transaction result: {:ok, {:error, #Ecto.Changeset<action: :insert, changes: %{code: "TEST", enabled: true, name: "Test Country"}, errors: [phone_code: {"can't be blank", [validation: :required]}], data: #ApiServer.V1.Geo.Country<>, valid?: false, ...>}}
[info] COUNTRY outer transaction result: {:ok, {:ok, {:error, #Ecto.Changeset<action: :insert, changes: %{code: "TEST", enabled: true, name: "Test Country"}, errors: [phone_code: {"can't be blank", [validation: :required]}], data: #ApiServer.V1.Geo.Country<>, valid?: false, ...>}}}

[info] STAY POLICY PERIOD inner transaction result: {:ok, {:error, #Ecto.Changeset<action: :insert, changes: %{opens_at: ~U[2022-01-20 12:00:00Z], period: %Postgrex.Range{lower: ~D[2022-01-10], upper: ~D[2022-02-01], lower_inclusive: true, upper_inclusive: true}, stay_policy_id: "2d4bb686-713f-4715-812f-6611dc2e2ba7"}, errors: [opens_at: {"is invalid", [constraint: :check, constraint_name: "stay_policy_periods_opens_before_start"]}], data: #ApiServer.V1.Stays.StayPolicyPeriod<>, valid?: false, ...>}}
[info] STAY POLICY PERIOD outer transaction result: {:error, :rollback}

For the Country test, which results in a non-database related validation error, I get what I need from the inner transaction (a changeset that I can process and return as an error to the client, with helpful info about which fields need to be corrected).

But for the StayPolicyPeriod test, I just get {:error, :rollback}.

My understanding is that this happens automatically because the constraint error happens on the database level, and even though it is “handled” by the check_constraint/3 function, it nevertheless causes a generic rollback to happen, because something went wrong at the database level.

Is this simply not solvable? Is there no way to get the changeset from the inner transaction instead of the generic rollback error without any information?