Unique limitation in Mnesia

Question

I am developing an Erlang application that requires a lot of DB writing. In my scheme, in addition to the primary key, there is another attribute that enforces a unique constraint.

Say I have an identifier, a unique_field_field and some other fields. I need to update a row in the database corresponding to a unique identifier, given that no other row should already have the value of the unique_constraint_field value that I am going to update.

Due to the large volume of updates (each update will only affect 1 line), I need to execute (also requiring low latency). I rely on the primary key and the unique restriction on this attribute to catch duplication, not the update statement using a subquery. This allows me to update in one request (which happens in 95% of cases), and in the remaining 5% I can catch an exception to take the necessary actions regarding the primary key or violation of a unique attribute.

I am currently using the mysql ODBC driver. However, the driver returns a very general error message for ANY error. Although my prototype is working fine now when I assume that some kind of mistake is a key violation, this model is obviously pretty much wrong. I can not find another decent driver / way to connect to mysql from erlang.

I'm thinking of switching to Mnesia (a memory-only mode for my speed requirements), since Erlang and Mnesia combine so easily. However, I see that Mnesia does not have any unique key constraints that I can use to perform my database update in a single query.

I need suggestions on how best to implement this requirement from within Erlang. Is there a way to do conditional updates in Mnesia? Or is there any other high-speed DB alternative that I should look at? Any help / understanding is greatly appreciated.

Answer 1

I don’t know what is the best solution, but I will try to make two tables for records and one for index with unique_constraint_field and process each CUD from CRUD operations in a transaction that checks and updates the index. The reason is that in mnesia you cannot set the index type and always duplicate the bag. I think that since your index will be unique anyway, it should not introduce any additional penalties. If you use the mnesia index function, you still have to write your own CUD operations, and the result seems almost the same as using two tables. Fortunately, mnesia processes nested transactions with minimal developer effort, and this thing is relatively simple compared to classic RDBMSs.

Answer 2

mnesia can crash with a lot of records if you configure it . But you can use complex primary keys that look like {ID, UniqueConstraint} , which will simplify your updates. There is also a new erlang library for osmos for tables on the ordered_set disk, created specifically for processing large-volume records.

Answer 3

Ulf Viger has released a library that allows you to use mnesia as a relational database. It was called "rdbms", it is several years old and it has not been updated for a long time, but you can probably use it as is, or at least rely on your work to deal with it. Take the source if you want.

His description:

I am filtering out my standard answer, the “rdbms” contribution has proposed a solution for this, supporting composite attributes and user-defined indexing, including specifying that the index value must be unique.

Rdbms / was / used commercially, but I do not find it ready for commercial use in general. I have not done anything on it for a while, since I do not see any user pressure, but anyone who wants a change, of course, can contact me and argue their case.

http://ulf.wiger.net/rdbms/doc/rdbms.html (Documents leave much to be desired, I know - see above.)

A document that mentions a “unique” constraint can be found here . There is an opportunity to achieve productivity; mnesia is for storing keys. I can’t remember exactly, but perhaps the definition of “unique” indexes could include checking the full table when checking them.

In general, as before, you probably won't be able to run it. See the trapezoidal flow . Using it to learn how this was done might be a better idea.