Ecto and CockroachDB

I was wondering if anyone has been able to get ecto working properly with CockroachDB? I am in the process of setting up a project to evaluate cockroach but am having some issues. I set up a new phoenix project and ran mix phx.gen.auth Accounts User users and tried running the tests. A couple tests failed but I was able to figure out what the reason for it was.

Now that I have the tests “fixed”, I have 1-2 tests failing each time I run them. Unfortunately the error I am getting (see below) does not seem to be helpful and seems to be inside the ecto_sql code. So I’m assuming this is a difference between postgres and cockroach. But I think it would also be strange if this would be unable to work in some way.

If anyone has any ideas, I would appreciate it.

Error:

$ mix test
................................................................................13:04:25.165 [error] GenServer #PID<0.954.0> terminating
** (CaseClauseError) no case clause matching: {:transaction, %Postgrex.Protocol{buffer: "", connection_id: 0, connection_key: 0, disconnect_on_error_codes: [], null: nil, parameters: #Reference<0.4070665280.3737387013.242708>, peer: {{127, 0, 0, 1}, 26257}, postgres: :transaction, queries: #Reference<0.4070665280.3737518081.247362>, sock: {:gen_tcp, #Port<0.10>}, timeout: 15000, transactions: :naive, types: {Postgrex.DefaultTypes, #Reference<0.4070665280.3737518081.246564>}}}
    (ecto_sql 3.7.2) lib/ecto/adapters/sql/sandbox.ex:589: Ecto.Adapters.SQL.Sandbox.post_checkout/3
    (db_connection 2.4.1) lib/db_connection/ownership/proxy.ex:101: DBConnection.Ownership.Proxy.handle_info/2
    (stdlib 3.17) gen_server.erl:695: :gen_server.try_dispatch/4
    (stdlib 3.17) gen_server.erl:771: :gen_server.handle_msg/6
    (stdlib 3.17) proc_lib.erl:226: :proc_lib.init_p_do_apply/3
Last message: {:db_connection, {#PID<0.953.0>, #Reference<0.4070665280.3737387009.249387>}, {:checkout, [#PID<0.953.0>], -576460749748, true}}


  1) test apply_user_email/3 validates current password (MyApp.AccountsTest)
     test/my_app/accounts_test.exs:164
     ** (MatchError) no match of right hand side value: {:error, {{{:case_clause, {:transaction, %Postgrex.Protocol{buffer: "", connection_id: 0, connection_key: 0, disconnect_on_error_codes: [], null: nil, parameters: #Reference<0.4070665280.3737387013.242708>, peer: {{127, 0, 0, 1}, 26257}, postgres: :transaction, queries: #Reference<0.4070665280.3737518081.247362>, sock: {:gen_tcp, #Port<0.10>}, timeout: 15000, transactions: :naive, types: {Postgrex.DefaultTypes, #Reference<0.4070665280.3737518081.246564>}}}}, [{Ecto.Adapters.SQL.Sandbox, :post_checkout, 3, [file: 'lib/ecto/adapters/sql/sandbox.ex', line: 589]}, {DBConnection.Ownership.Proxy, :handle_info, 2, [file: 'lib/db_connection/ownership/proxy.ex', line: 101]}, {:gen_server, :try_dispatch, 4, [file: 'gen_server.erl', line: 695]}, {:gen_server, :handle_msg, 6, [file: 'gen_server.erl', line: 771]}, {:proc_lib, :init_p_do_apply, 3, [file: 'proc_lib.erl', line: 226]}]}, {DBConnection.Holder, :checkout, [#PID<0.954.0>, [post_checkout: #Function<0.60388935/2 in Ecto.Adapters.SQL.Sandbox.checkout/2>, pre_checkin: #Function<1.60388935/3 in Ecto.Adapters.SQL.Sandbox.checkout/2>, timeout: 15000, pool: DBConnection.Ownership, pool_size: 10]]}}}
     stacktrace:
       (ecto_sql 3.7.2) lib/ecto/adapters/sql/sandbox.ex:403: Ecto.Adapters.SQL.Sandbox.start_owner!/2
       (my_app 0.1.0) test/support/data_case.ex:31: MyApp.DataCase.__ex_unit_setup_0/1
       (my_app 0.1.0) test/support/data_case.ex:1: MyApp.DataCase.__ex_unit__/2
       test/my_app/accounts_test.exs:1: MyApp.AccountsTest.__ex_unit__/2

........................

Finished in 1.8 seconds (1.1s async, 0.6s sync)
105 tests, 1 failure

I created a similar post on the cockroachdb forms here in case they are able to answer as well.

The tests by default use a sandbox, which means your tests run inside a transaction with nested savepoints. It may be those are not supported by CockroachDB and you will need a different approach (like running your tests synchronously and deleting all data after each test).

As far as I can tell, cockroach supports nested transactions. Maybe they are not identical to the postgres implementation?

Which version of CockroachDB, as it may be problem with nested transactions.

I’m using latest (21.2.4 at the time of writing).