Modifying Ecto's SQL generation?

Questions / Help
,
1

I was looking over Ecto and CockroachDB - #9 by Ankhers
The basic need we have is that we need many select queries such as this:

SELECT x, y, z
FROM foobar f
WHERE f.x = 'some-condition';

to be modified to include AS OF SYSTEM TIME, like this:

SELECT x, y, z
FROM foobar f
AS OF SYSTEM TIME '-5m'
WHERE f.x = 'some-condition';

These are called follower reads by Cockroach – it’s important to specify this to avoid data sync errors and to take advantage of reading from replicas. It basically allows the query to say “it’s ok if the data is x minutes old”.

The problem is that when Ecto generates the query or renders a fragment, the clauses appear in ways that are considered invalid (from Cockroach’s point of view). I’ve tried a few different things here including the fragment example in the other post as well as including literal clauses, but so far, I haven’t found a winning combination. It would be possible to come up with my own alternative to the from macro, but I’m hoping there’s an easier way.

Thanks in advance for any pointers!

2

This is going to be a non-answer to your question but I think the proper way of handling this would be to create a CockroachDB adapter for ecto that can handle these scenarios. The good news is that ~99% of it should already be done.

If you take a look at the official ecto adapters, you will notice that there is a common SQL module that all of the SQL adapters will call. There is no reason we could not do the same with a cockroach adapter calling postgres. We would only need to implement the differences. In this case we could probably do something like

from(
  f in Foo,
  as_of_system_time: ^"-5m",
  where: ...
)

Then the adapter can all the postgres adapter for everything except the :as_of_system_time key and it can do its own thing.

There may be additional work that needs to be done, but this I think would be the way to get started.

3

This won’t even compile as the key is unknown.

4

Of course it does not compile right now. No one created the adapter that my comment is suggesting to make.

5

Hmm… there is this repo CockroachDB Ecto Adaptor — CockroachDB Ecto Adaptor v1.0.0
It’s been archived because it’s no longer needed (see GitHub - jumpn/ecto_cockroachdb: CockroachDB adaptor for Ecto 3.x)

I guess I should figure out the functionality first before worrying about a package name…

Thank you for your suggestions @Ankhers – I’ll see if I can wrap my head around how the Ecto macros are structured.

6

Let me know if you would like help here. I do not have a project that uses cockroach right now, but I would like an excuse to use it. I might be able to throw something together that can be used as a base.

7

I mean, this is not only about adapter.
Building an Ecto.Query doesn’t depend on used adapter in compile time.
In order to add a new keyword, Ecto.Query struct has to be extended.

8

Right – I’ve just tracked this down in the code and I’ve reached the same conclusion. It seems like maybe what’s needed is more of an augmentation of Ecto.Query and not necessarily a completely separate database adapter. :thinking:

I started inspecting the %Ecto.Query{} struct that gets generated by an Ecto query (e.g. one including the from macro), but I didn’t see any way to manipulate that (granted, this may not be the simplest code to follow)