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

Ankhers · January 31, 2022, 6:36pm

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.

josevalim · January 31, 2022, 9:17pm

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

Ankhers · January 31, 2022, 9:28pm

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

hauleth · January 31, 2022, 10:20pm

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

Ankhers · January 31, 2022, 10:34pm

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

josevalim · February 2, 2023, 10:14pm

Cross-linking: there is also a discussion on this Ecto SQL issue.

carldr · February 15, 2023, 12:42pm

This repository might be of use to anyone finding this issue and looking for a solution, at least until it’s fixed upstream in either Ecto or CockroachDB : GitHub - btkostner/ecto_replay_sandbox: A custom Ecto Sandbox for CockroachDB to run your tests - look at the update branch for a version which doesn’t require the cockroachdb adapter.

I was having the same issue as the OP, using this fixed it.

fceruti · July 22, 2023, 8:10am

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.

Ankhers · July 24, 2023, 2:25pm

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.

fireproofsocks · October 27, 2023, 7:33pm

I’m interested in this as well… unfortunately, it looks to be a bit trickier than using a simple fragment.

For example, here’s the above query for users:

iex(26)> from(u in fragment("? AS OF SYSTEM TIME '-5m'", ^User.__schema__(:source)), select: [:name, :email])
#Ecto.Query<from f0 in fragment("? AS OF SYSTEM TIME '-5m'", ^"users"),
 select: [:name, :email]>
iex(27)> |> Repo.all()
[debug] QUERY ERROR db=0.0ms queue=5.4ms idle=1805.7ms
SELECT f0."name", f0."email" FROM $1 AS OF SYSTEM TIME '-5m' AS f0 ["users"]
** (Postgrex.Error) ERROR 42601 (syntax_error) at or near "1": syntax error

    query: SELECT f0."name", f0."email" FROM $1 AS OF SYSTEM TIME '-5m' AS f0

    hint: try \h <SOURCE>

source SQL:
SELECT f0."handle", f0."page_title" FROM $1 AS OF SYSTEM TIME '-5m' AS f0
                                         ^
    (ecto_sql 3.10.2) lib/ecto/adapters/sql.ex:1047: Ecto.Adapters.SQL.raise_sql_call_error/1
    (ecto_sql 3.10.2) lib/ecto/adapters/sql.ex:945: Ecto.Adapters.SQL.execute/6
    (ecto 3.10.3) lib/ecto/repo/queryable.ex:229: Ecto.Repo.Queryable.execute/4
    (ecto 3.10.3) lib/ecto/repo/queryable.ex:19: Ecto.Repo.Queryable.all/3
    iex:27: (file)

`
Note that the generated query is not correct:

-- This is invalid
SELECT f0."name", f0."email" FROM $1 AS OF SYSTEM TIME '-5m' AS f0

The SYSTEM TIME needs to place the f0 immediately after the FROM. The correct variant would be:

SELECT f0."name", f0."email" FROM $1 f0 AS OF SYSTEM TIME '-5m'

I’m not sure how to go about modifying this or even if it’s possible.

fuelen · October 30, 2023, 3:41pm

I assume, that this won’t be a correct variant as well, since you pass a string as a table name.
Try the following code

defmodule MyMacro do
  defmacro users_as_of_system_time(time) do
    pattern = "#{User.__schema__(:source)} AS OF SYSTEM TIME ?"
    quote do
      fragment(unquote(pattern), unquote(time))
    end
  end
end
#...
iex> import MyMacro
iex> from u in users_as_of_system_time("5m"), select: u.id
#Ecto.Query<from f0 in fragment("users AS OF SYSTEM TIME ?", "5m"), select: f0.id>

If you execute MyApp.Repo.all/1 for the query above, then the SQL is the following:

SELECT f0."id" FROM users AS OF SYSTEM TIME $1 AS f0

Is this a valid query for CockroachDB?

fireproofsocks · October 30, 2023, 4:40pm

You are correct: this is unfortunately not a valid query in Cockroach because the f0 alias isn’t pinned to the table – it gets pinned to the AS OF SYSTEM TIME expression, and that’s not valid syntax. The proper syntax would look like this:

`SELECT f0."id" FROM users AS f0` AS OF SYSTEM TIME $1

i.e. the AS OF SYSTEM TIME bit comes at the end without any AS alias following.

fuelen · October 30, 2023, 4:52pm

What about subqueries? Will this work?

SELECT f0."id" FROM (SELECT * FROM users AS OF SYSTEM TIME $1) AS f0

fireproofsocks · October 30, 2023, 5:43pm

Unfortunately not:

AS OF SYSTEM TIME must be provided on a top-level statement

fuelen · October 30, 2023, 7:06pm

Another idea is to go from the opposite side. Here is a code that shows the direction :

query = from u in User, where: ...

defmodule MyApp.Repo do
  def all_as_of_system(query) do
    {sql, params} = to_sql(:all, query)
    query!("SELECT * FROM (#{sql}) AS data AS OF SYSTEM TIME '5m'", params)
  end
end

but this approach has a couple of downsides:

you have to figure out how to cast result to data types specified in select of query argument
troubles with 3rd-party libraries which use Repo.all/2, like paginator, dataloader, etc.

fireproofsocks · October 30, 2023, 8:05pm

Ecto.Adapters.Postgres.Connection has a function that looks promising:

    defp from(%{from: %{source: source, hints: hints}} = query, sources) do
      {from, name} = get_source(query, sources, 0, source)
      [" FROM ", from, " AS ", name | Enum.map(hints, &[?\s | &1])]
    end

That appears to be where you might able to tweak the from. I’m gonna kick some tires in a custom adapter and see what I can learn.

Ankhers · October 30, 2023, 8:07pm

I believe the intended use here is FROM my_table AS my_alias.

fuelen · October 30, 2023, 8:48pm

Actually, you found something else. Have a look at hints argument. It is placed right after declaration of an alias.
This keyword is used by Tds adapter Ecto.Adapters.Tds — Ecto SQL v3.10.2

and looks like this is what is needed:

from u in User, hints: ["AS OF SYSTEM TIME '-5m'"]

but if you call Repo.all, then you’ll see the error

** (Ecto.QueryError) table hints are not supported by PostgreSQL

and here patching the adapter may help.

fireproofsocks · October 31, 2023, 4:30pm

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?

fuelen · October 31, 2023, 8:08pm

I was thinking to ask you why, as according to

fireproofsocks:

    defp from(%{from: %{source: source, hints: hints}} = query, sources) do
      {from, name} = get_source(query, sources, 0, source)
      [" FROM ", from, " AS ", name | Enum.map(hints, &[?\s | &1])]
    end

hints are already there and even with space before each hint.
I see that this code is present in master.

Seems like a new adapter is not needed, just use ecto_sql from master