[SOLVED] Phoenix / Elixir testing when setting isolation level of transaction

Phoenix / Elixir testing when setting isolation level of transaction

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!

Solution

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.