Modifying Ecto's SQL generation?

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
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!

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

  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.

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

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

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.

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.

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.

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)