How to make a rethinkdb atomic update if document exists, insert otherwise?

Question

I want to do something like:

var tab = r.db('agflow').table('test');
r.expr([{id: 1, n: 0, x: 11}, {id: 2, n: 0, x: 12}]).forEach(function(row){
  var _id = row('id');
  return r.branch(
    tab.get(_id).eq(null),  // 1
    tab.insert(row),        // 2 
    tab.get(_id).update(function(row2){return {n: row2('n').add(row('n'))}}) // 3
  )})

However this is not fully atomic, because between the time when we check if document exists (1) and inserting it (2) some other thread may insert it.

How to make this query atomic?

score 19 · Answer 1 · answered Jul 15 '15 at 14:31

19

I think the solution is passing

conflict="update"

to the insert method.

Als see RethinkDB documentation on insert

answered Jul 15 '15 at 14:31

wedesoft

2,211
23
22

Update: In this particular case you might want to implement a [conflict resolution function](https://rethinkdb.com/api/python/insert/). – wedesoft Jan 20 '20 at 15:24

score 3 · Accepted Answer · answered Jul 26 '15 at 18:22

3

To make the inner part atomic, you can use replace:

tab.get(_id).replace(function(row2){
  return r.expr({id: _id, n: row2('n').add(row('n'))})
          .default(row)
})

Using replace is the only way at the moment to perform fully atomic upserts with non-trivial update operations.

answered Jul 26 '15 at 18:22

Daniel Mewes

1,826
12
15

How can i update a field or insert a document? Is it possible to actually update a field atomically like a counter, quantity field or last_visit timestamp? I don't want to create a new document with updated data, I want to update a counter field. for user A that has counter x=1 I want counter to be x++ (x=2) I found the inc instruction but no way to do a proper atomic upsert, similar to what zincrby in Redis does – Astronaut Nov 01 '16 at 16:55

score 1 · Answer 3 · answered Jun 19 '14 at 13:04

1

This is a standard database operation called an "upsert".

Does RethinkDB support upserts?

Another question from Sergio Tulentsev. The first public release didn't include support for upserts, but now it can be done by passing an extra flag to insert:
r.table('marvel').insert({ 
      superhero: 'Iron Man', 
      superpower: 'Arc Reactor' 
  }, {upsert: true}).run() 
When set to true, the new document from insert will overwrite the existing one.

_{cite: Answers to common questions about RethinkDB}

answered Jun 19 '14 at 13:04

Brigand

75,952
19
155
166

I know upsert. The case is that I don't want to overwrite. I want to update existing document. – Robert Zaremba Jun 19 '14 at 13:15
Weird, it doesn't implement a traditional [upsert](http://en.wiktionary.org/wiki/upsert). – Brigand Jun 19 '14 at 23:05
3

I got an error using upsert with node.js client 2.3.2 - ReqlServerCompileError: Unrecognized optional argument `upsert` in: r.db("myDB").table("mytable").insert({"userId": something, "lastUpdate": timestamp }, {"upsert": true}) – Nico AD Aug 22 '16 at 15:45
1

`Unrecognized optional argument upsert.` – chovy Mar 23 '17 at 05:48
1

This might have changed since. – Vincent Cantin Feb 02 '18 at 01:04

score 0 · Answer 4 · answered Jun 19 '14 at 15:43

0

OK, I figured out how to do it.

The solution is to firstly try insert, and if it fail we make update:

var tab = r.db('agflow').table('test');
tab.delete();
r.expr([{id: 1, n: 0, x: 11}, {id: 2, n: 0, x: 12}]).forEach(function(row){
  var _id = row('id');
  var res = tab.insert(row);
  return r.branch(
    res('inserted').eq(1),
    res,
    tab.get(_id).update(function(row2){return {n: row2('n').add(1)}})
  )})

answered Jun 19 '14 at 15:43

Robert Zaremba

6,780
6
40
71

6

are you sure this will be performed atomically? – let4be Jul 26 '15 at 07:31
so you can use this for instance to increment/update a field or insert a document in case the secondary index does not exist? Similar to what you can do with http://redis.io/commands/zincrby ? Lets say i have a username and a score for that user, every time the user clicks a button his score increases by +1, is there an easy way to do this? – Astronaut Nov 01 '16 at 14:25
This is not atomic. Instead you need to pass a conflict resolution argument the insert method. – wedesoft Jan 20 '20 at 15:29

How to make a rethinkdb atomic update if document exists, insert otherwise?

4 Answers4

Does RethinkDB support upserts?