Help implementing a notification system for Sqlite ecto adapter

Questions / Help
,
1

So i’ve landed some features

In the lower level libs that make up the Sqlite.Ecto2 adapter. Now my problem is one of the actual implementation when working with Ecto. I know Ecto itself doesn’t support any notification system, but
the Postgrex adapter does support notifications. From skimming the source, i found that Postgrex just
opens another connection to the database which is something that sqlite can not support just because how it was built.

so one idea i had was to use Elixir.Registry, so every connection in the pool sends notifications out via Registry.

My issue is that sqlite sends a message that looks like
{:insert | :update | :delete, 'tablename', rowid}
(here are the docs for that)

Basically i get a rowid not a primary key or anything that is actually exposed to the Ecto Repo,
so even if an end user subscribed and got this message, they wouldn’t be able to easily lookup the data
that was affected because rowid is kind of an internal thing.

What i was thinking of doing is:

%{columns: c, rows: [row]} = Ecto.Adapters.SQL.query!(repo, "SELECT * FROM '#{table}' where rowid = #{rowid};", [])

And then sending that message to subscribers of the Registry, so a consumer would have to do:

receive do
   {action, 'users', %{columns: c, rows: [row]}} -> MyRepo.load(User, {result.columns, c, row})
end

The only problem i have with this is that every insert will also have a query to dispatch the notification,
and having user call load seems a little strange also.

3 Likes
2

:wave:

Thank you for implementing this.

But could the table name in the message be a binary instead of charlist?

If there isn’t a way to lookup the ecto schema module for a table name at runtime, I’d probably just leave the notification as is (that is, I’d just publish the rowid, without any preloads).

Also, can the nif “overload” the subscriber pid with messages? If the changes are published faster than the pid can handle them.

3

unfortunately the implementation of the message dispatching is handled by a NIF, and the convention for that NIF is to use charlists, because the lib is an Erlang library, not Elixir. Charlists are also faster for NIFs if i remember correctly, since it doesn’t have to allocate, convert to term, deallocate etc.

I don’t think Ecto encourages looking up a schema module at runtime, since multiple schemas could use the same table.

I guess dispatching the rowid isn’t that bad. I could make a wrapper module that one could use by supplying a schema and translate it for the user.

2 Likes
4

That would be perfect, at least for me, I think.

1 Like
5

The nif handles messages in a thread, so it’s possible that messages can come in faster than they can be handled, but they should be received in order making this not that big of a deal i don’t think

1 Like
6

I’ve started implementing this here

But have ran into a couple issues so far.

  1. inserts are slow while notifications are turned on. Not really sure why.
  2. deletes can’t lookup the thing that was deleted since it doesn’t exist anymore
7

Completely uninformed and probably stupid question:

Does sqlite keep a journal of mappings between a row ID and the row itself, even if short-lived? That could help with the deleted row issue.

Failing that, and if you really want notifications, I’m afraid you’ll have to maintain that journal yourself in an ETS or a DETS table.

8

Sqlite keeps it opaquely, and garbage collects it before dispatching the deletion notification as far as i can tell.

I’m not incredibly fond of this idea for a couple reasons.

  1. (specifically dets) My primary usage of sqlite is to not rely on DETS/ETS at all.
  2. there is a possibility of missing an insert/update, meaning the deletion would still not have a missing record.
  3. I’d basically be cloning all Repo data in memory.
9

Fair enough, I figured. At this point you’d basically be making Sqlite++ with half the code being the original Sqlite (C++) and the other half Elixir. Far from ideal.

So it’s kind of a private API? Maybe you can ask the maintainer if you can make use of that journalling metadata before it gets GC-ed?

Sorry I am not very helpful, mostly just thinking out loud.

1 Like
10

I don’t think its a private api, just part of how the Sqlite3 virtual machine works. You can read about rowid here. Basically it exists, and you can use it to index things, but you can’t look at how it works. (as far as my limited knowledge of the sqlite public api goes anyway)

In most cases the rowid is the primary key, (although this isn’t always true), the only problem is that sqlite internally doesn’t actually dispatch the callback (what sends data back to Beam) until after the row has been destroyed.

There actually is commit and rollback hooks (described here) but that would add more complexity to the NIF than i would ideally give it.

2 Likes