Phoenix / Elixir testing when setting isolation level of transaction

Question

I have a chunk of code that looks something like this:

Repo.transaction(fn ->
  Repo.query!("set transaction isolation level serializable;")

  # do some queries

end)

In my test suite, I continually run into the error:

(Postgrex.Error) ERROR 25001 (active_sql_transaction): SET TRANSACTION ISOLATION LEVEL must be called before any query

I'm wondering if I'm doing something fundamentally wrong, or if there's something about the test environment that I'm missing.

Thanks!

Answer 1

Not sure if you are still looking for the answer to this but I found a nice solution for this. For the case I have setup block like so:

setup tags do
  :ok =
    if tags[:isolation] do
      Sandbox.checkout(Repo, isolation: tags[:isolation])
    else
      Sandbox.checkout(Repo)
    end

    unless tags[:async] do
      Sandbox.mode(Repo, {:shared, self()})
    end

    :ok
  end

then on the test that is in the path of the serializable transaction you have to tag it with "serializable" like so:

@tag isolation: "serializable"
test "my test" do
  ...
end

this will let you run your tests that come across serializable in the path and still use sandbox.

Answer 2

The problem is for testing purposes all of the tests are wrapped in a transaction so they can be rolled back so you don't pollute your database with tons of old test data. Which could result in failures that should have passed and passes that should have failed depending on how you've written your tests.

You can work around it but it will, again, pollute your test database and you'll have to clean it up yourself:

setup do 
  [Other set up stuff]
  Ecto.Adapters.SQL.Sandbox.checkin(MyApp.Repo) #This closes any open transaction, effectively.
  Ecto.Adapters.SQL.Sandbox.checkout(MyApp.Repo, [sandbox: false]) # This opens a new transaction without sandboxing. 
end

This setup task goes in the test file with your failing tests if you don't have a setup. If you don't do the checkin call you'll (most likely) get an error about other queries running before the one setting the transaction level because you are inserting something before the test.

See here for someone essentially calling out the same issue.