fireproofsocks

fireproofsocks

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.

0
Post #11
fuelen

fuelen

What about subqueries? Will this work?

SELECT f0."id" FROM (SELECT * FROM users AS OF SYSTEM TIME $1) AS f0
0
Post #12
fireproofsocks

fireproofsocks

Unfortunately not:

AS OF SYSTEM TIME must be provided on a top-level statement
0
Post #13
fuelen

fuelen

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

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.
0
Post #14
fireproofsocks

fireproofsocks

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.

0
Post #15
Ankhers

Ankhers OP

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

0
Post #16
fuelen

fuelen

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

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.

0
Post #17
fireproofsocks

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?

2
Post #18
fuelen

fuelen

I was thinking to ask you why, as according to

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 :slight_smile:

0
Post #19
fireproofsocks

fireproofsocks

Haha! Glad to know we were on the right track. It looks like that change is on track to be included with the next tagged release – 3.11 from the looks of it.

The override of the prepare_query/3 might still be useful (but nothing there to commit to the repo, just to add for our personal preferences)

0
Post #20