Upsert by Uniqueness

Feedback
1

Describe the problem you’re experiencing?
this is like most problem from people moving from standard RDBMS (postgre, mysql) to memsql,
for example in normal use case I created a table with

CREATE TABLE users (
   id UINT AUTO_INCREMENT PRIMARY KEY
   email VARCHAR(64) -- UNIQUE
);

since memsql does not allow unique that not a shard key, setting email as primary key is not a good approach, because to update the email, I must delete the old record, then reuse the id (or even worse update all referenced foreign keys to new inserted id).
setting id as uuid is also not an option, since that id is referenced by lots of other table, so smaller key is better.
generating integer id from another atomic source, and using email as unique key is another option, but probably same as the first option, updating the email (which is unfortunately a shard key) is not allowed.
the other problem with the table schema above is we can’t enforce the uniqueness of email, which is a problem if double insert happened.

What is your ideal solution? What are you looking for?
the ideal solution would be allowing non shard key uniqueness, or… add a syntax something like this:

INSERT INTO users(email) VALUES(somedupe) 
ON DUPLICATE (email) IGNORE
RETURNING id;

so if the insert failed because email is already exists, the old id returned and no data inserted.

What version(s) of MemSQL or related tools is this affecting?
6.x

2

That’s an interesting idea. We would likely want to enforce an index on email in your example (or the performance penatly would be pretty harsh).

fyi, we don’t allow non-shard key unique indexes because they would be expensive to enforce (they would require cross-node lookups to check for duplicates).