[erlang-questions] using mnesia to mirror SQL tables

[Rick Pettit]

Is anyone using mnesia to mirror SQL tables (or views)?

If so, what is your procedure for updating the mirror?

In erlang, I would probably update such a mirror by doing:

  1) pull new copy of SQL table/view into ETS (for sanity checking)
  2) as a transaction:
       a) mnesia:clear_table/1 on "old" mirror
       b) populate new table with copy from ETS
  3) ets:delete/1 on "temporary" table

In C I would probably update such a mirror by doing:

  1) pull new copy of SQL table/view into memory (for sanity checking)
  2) under mutex/lock:
       a) switch the mirror pointer from old copy to new
  3) delete the old copy

The erlang update procedure consists of a table clear followed by repopulating
the table.

The C mirror update procedure consists of a simple pointer swap (since the old
and new copies can co-exist).

It seems to me the C approach blocks clients for a very short period of time,
whereas _my_ erlang approach (which may be flawed) could block considerably
longer. The data cannot co-exist in the erlang solution as that would require
two mnesia tables.

I suppose I could stick a process in between mnesia and the clients, one which
would maintain 2 mnesia tables (e.g. mirror_a and mirror_b) and point clients
at the one which was "most up to date". This would allow for concurrent
updates of the "other" table without blocking clients, and for a very rapid
switch between mirror_a and mirror_b by simply routing clients to one or the
other.

The biggest problem with this approach is I was hoping to make the entire
mirror process transparent to clients (who would simply access the mirror
by performing direct mnesia read operations on the one and only mirror table).

Having to know which of a couple mnesia tables holds the most recent version
of the mirror makes direct use of the mnesia API impossible. This also makes
it harder for clients to request a local ram copy of the mirror (something
easily done when the mnesia API can be used directly without going through
any traffic cop process).

Any comments/suggestions would be greatly appreciated.

-Rick