Modifying Ecto's SQL generation?

fireproofsocks

I was looking over Ecto and CockroachDB issue: (CaseClauseError) no case clause matching - #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!

7 comments

#ecto #cockroachdb

0 573 7

2023-10-30 16:14:39 UTC

Showing Posts 1 to 7

Show Best Posts
Show All (oldest first)
Show All (newest first)

Ankhers

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.

Post #1

fuelen

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

Post #2

Ankhers

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

Post #3

fireproofsocks

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

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.

Post #4

Ankhers

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.

Post #5

fuelen

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.

Post #6

fireproofsocks

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.

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)

Post #7

— All posts loaded —