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?

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.