Ankhers

Ecto and CockroachDB issue: (CaseClauseError) no case clause matching

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.

21 comments

#ecto #cockroachdb

0 3789 21

2023-11-08 22:26:14 UTC

First Post!

josevalim

Creator of Elixir

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).

Post #1

Most Liked

fireproofsocks

Ding! I got this to work by leveraging the :hints option as you suggested. I ended up defining a custom Ecto adapter which was a complete copy of the Ecto.Adapters.Postgres.Connection module except for the from/3 function. I modified from/3 to this:

    defp from(%{from: %{hints: hints, source: source}} = query, sources) when hints != [] do
      {from, name} = get_source(query, sources, 0, source)
      from = [" FROM ", from, " AS " | name]
      [from | hints]
    end

    defp from(%{from: %{source: source}} = query, sources) do
      {from, name} = get_source(query, sources, 0, source)
      [" FROM ", from, " AS " | name]
    end

I also made a full copy of Ecto.Adapters.Postgres (no changes).

In my Repo (i.e. the module that use’s Ecto.Repo), I referenced the new adapter, e.g.

  use Ecto.Repo,
    otp_app: :foo,
    adapter: Ecto.Adapters.CockroachDB

This made things work if I was importing Ecto.Query and supplied a :hints option. I took things a bit further and I overrode the prepare_query/3 function in my Ecto Repo:

  def prepare_query(_operation, %{from: %{hints: []}} = query, opts) do
    case Keyword.get(opts, :hints, []) do
      [] -> {query, opts}
      hints -> {put_in(query.from.hints, hints), opts}
    end
  end

  def prepare_query(_operation, query, opts), do: {query, opts}

This let me do more “simple” lookups such as:

Repo.all(MySchema, hints: " AS OF SYSTEM TIME '-5m'")

Note that you have to leave a space before AS.

This all seems to work and it generates queries in the proper syntax, e.g.

[debug] QUERY OK source="my_table" db=6.5ms queue=6.9ms idle=1334.0ms
SELECT c0."id", c0."foo", c0."bar" FROM "foobar" AS c0 AS OF SYSTEM TIME '-5m' []

Probably this could be cleaned up, but would this be worthwhile as a standalone package/adapter?

Post #18

fceruti

I’ve been trying to port from Postgres to Cockroach, and while the process has been mostly uneventfull, that last 10% is giving me headaches.

It’s been a long day, and I may formulate better questions in the future, but for now, have any of you guys been able to perform follower reads, within the confines of a regular ecto query building? By regular confine I mean, without Repo.execute ( << raw sql>> ), or something to that nature.

I’ve tried to use many adapter out there in the hexphere, but all seem to be out of sync. Are there any community members looking at this problem? I’m sold on cockroach and can afford to look deeper into this rabbithole, but I would like to first check if there’s some active development somewhere.

Post #7

Ankhers

I haven’t tried it, but you might be able to do something like

from(u in fragment("? AS OF SYSTEM TIME with_max_staleness('10s')", ^User.__schema__(:source)), 
select: u.email)

Unfortunately you will need to define all of the fields that you want to return from the table in the :select portion of the query.

Either that or you will need to build an ecto_cockroach library that can integrate with the ecto_sql library. This would allow the ecto_sql library do that majority of the work and the ecto_cockroach library would only deal with the differences, such as this. This is already how the ecto_sql library works for the various official implementations just as postgres and mysql. I will admit though, I don’t know if the ecto_sql library is setup for this type of integration.

Post #8