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

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

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

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 · GitHub - 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.

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.

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.

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.

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?