How to findAndInsertOrModify in one statement ?

[Albert Kam]

I would like to :

- insert if the id doesnt exist yet

- or inc the counter if the id exists

- and return the newly inserted / modified result

So this is what i came up with :

db.counter.findAndModify({

   query : { '_id' : 'bert' },

   update : { $setOnInsert : { 'counter' : 0 }, $inc : {'counter' : 1} },

   upsert : true,

   new : true

});

and it failed with the error :

Fri Apr 11 12:20:34.519 JavaScript execution failed: findAndModifyFailed failed: {

        "errmsg" : "exception: Field name duplication not allowed with modifiers",

        "code" : 10150,

        "ok" : 0

} at src/mongo/shell/collection.js:L399

Without the inc, the insertion works though :

db.counter.findAndModify({

   query : { '_id' : 'bert' },

   update : { $setOnInsert : { 'counter' : 0 } },

   upsert : true,

   new : true

});

So i'm wondering whether this findAndInsertOrModify is possible to do within a single statement ?

Thanks !

--
Do not pursue the past. Do not lose yourself in the future.
The past no longer is. The future has not yet come.
Looking deeply at life as it is in the very here and now,
the practitioner dwells in stability and freedom.
(Thich Nhat Hanh)

[s.molinari]

Have you tried just  " $inc : {'counter' : 1} " in the update parameter?

http://docs.mongodb.org/manual/reference/method/db.collection.findAndModify/#update-and-insert

Scott

[Albert Kam]

Thanks for the reply. Your suggestion works great for my simple example !

Although my initial intention is to have the value of 0, but i can always adjust my app logic.

But i'm still left wondering what if i need to set 'initial values' instead of updating it.

For example, i want to set multiple initial values using $setOnInsert if the record isnt there yet,

which cannot be solved with the $inc alone. Let's say if the record isnt there, i would like to create :

{

  '_id' : 'bert',

  'age' : 10,

  'counter': 0

}

but if the record is there, then i just want to $inc the counter { $inc : {'counter' : 1}}

So, i would craft out :

db.test.findAndModify({

   query : { '_id' : 'bert' },

   update : { $setOnInsert : { 'age': 10, 'counter': 0 }, $inc : {'counter': 1} },

   upsert : true,

   new : true

});

Based on the docs on http://docs.mongodb.org/manual/reference/operator/update/setOnInsert/

(Because the update() with upsert only performs an update, MongoDB ignores the $setOnInsert operation and only applies the $set operation.)

i believe it's either $setOnInsert or the others, and which is why my assumption is that this error shouldnt have happened just because i have 'counter' on both $setOnInsert and $inc within the same update : 

Fri Apr 11 14:31:47.490 JavaScript execution failed: findAndModifyFailed failed: {

        "errmsg" : "exception: Field name duplication not allowed with modifiers",

        "code" : 10150,

        "ok" : 0

} at src/mongo/shell/collection.js:L399

[Asya Kamsky]

You can do one or more $setOnInsert and you can do $set and $inc in one update/upsert statement, but you cannot have the same field appear in more than one operator clause.   So either you are $setting something or $setting it on insert, or $incrementing it...

Asya

--

You received this message because you are subscribed to the Google Groups "mongodb-user"
group.
 
For other MongoDB technical support options, see: http://www.mongodb.org/about/support/.
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user...@googlegroups.com.
To post to this group, send email to mongod...@googlegroups.com.
Visit this group at http://groups.google.com/group/mongodb-user.

To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/CAMT20g8vUiLfaSTMA6dS%2BBk8rmGt4vat_D0DEXSZ6A4%2BgRtyow%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.

[Albert Kam]

Dear Asya,

Yes, i agree if the update is { $set : {counter:1}, $inc : {counter:2}} should display the error.

But having the same field in both clauses of $setOnInsert and $set/$inc should _not_ be problematic because they're independently executed, am i correct ? I mean it's either $setOnInsert or the $set/$inc.

Can i safely conclude that with the current version, we cannot set an initial value for a field if the document doesnt exist and set/inc that exact same field if the document exists with a findAndModify ?

db.test.findAndModify({

   query : { '_id' : 'bert' },

   update : { // both clauses inside this update are executed independently based on the record's existence

       $setOnInsert : { 'counter' : 0 },  // initialize counter to 0 __only if__ the _id 'bert' doesnt exist

       $inc : {'counter' : 1}  // incr counter by 1 __only if__ the _id 'bert' exists

   },

   upsert : true,

   new : true

});

Regards from Jakarta,

Albert Kam

To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/CAOe6dJAGM0jTght-HQpMdxF-gEKVvdxK155dzTYs1StD3w2EYw%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.

[Glenn Maynard]

Might be another use case for https://jira.mongodb.org/browse/SERVER-6566, to allow running multiple consecutive updates:

db.test.findAndModify({
     upsert: true, new: true,

     query: { _id : 'bert' },
     update: [{

           $setOnInsert: { counter: -1 },

     }, {
           $inc: { counter: 1 } // if this is a new document, -1 now becomes 0
     },
});

To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/CAMT20g-JGfu9EDHz3b-9pNGsrusWwuFy8hxdfX4wzzVX_MH00g%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.

--
Glenn Maynard

[s.molinari]

This might be a newb question, but why would you also need to extra logic in your query for MongoDB to figure out for every increment update, when it isn't really needed for most of those updates? I mean, the insert would most likely be much rarer than the update, isn't it? Covering both situations in one query wouldn't gain you much in network bandwidth, CPU usage or programming simplicity, would it?

Scott

[Albert Kam]

Dear Scott,

My hope in doing find + insert/update within a single findAndModify is to avoid duplication of record creations caused by racing conditions that can happen in multi-user environments.

Regards,

Albert Kam

On Fri, Apr 11, 2014 at 10:41 PM, s.molinari <scottam...@googlemail.com> wrote:

This might be a newb question, but why would you also need to extra logic in your query for MongoDB to figure out for every increment update, when it isn't really needed for most of those updates? I mean, the insert would most likely be much rarer than the update, isn't it? Covering both situations in one query wouldn't gain you much in network bandwidth, CPU usage or programming simplicity, would it?

Scott

--

You received this message because you are subscribed to the Google Groups "mongodb-user"
group.
 
For other MongoDB technical support options, see: http://www.mongodb.org/about/support/.
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user...@googlegroups.com.
To post to this group, send email to mongod...@googlegroups.com.
Visit this group at http://groups.google.com/group/mongodb-user.

To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/11293ece-bcc3-4b58-9036-06324f797eda%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[s.molinari]

Um, correct me if I am wrong, but you are querying for "bert" in your example and since "bert" is in the _id key, the value must be unique. So if "bert" is found, it will increment. If "bert" is missing, it will insert. That seems to be exactly what you need with the code...

db.test.findAndModify({

   query : { '_id' : 'bert' },  //if found

   update : {

       $inc : {'counter' : 1}  

   }, //else insert

   upsert : true,

   new : true

});

isn't it?

This exact same scenario of a race condition is in this explanation and is solved with a unique index (which the index on _id automatically is).

Scott

[Albert Kam]

Yes, your example had helped me get my thing working here since your first reply.

But the issue i raised isnt about on my previous problem anymore, 

but more to why would mongodb throw error when there are the 'same fields' in $setOnInsert and the other update types.

Here is a simple example to illustrate.

This is a working example, inserting '_id' = bert and 'inserted' = true if there's no document found,

and $inc counter by 1 if there's already a record : 

db.test.findAndModify({

   query : { '_id' : 'bert' },  

   update : {

  $setOnInsert : {'inserted' : true},

       $inc : {'counter' : 1},

   },

   upsert : true,

   new : true

});

Now this works without any errors thrown since the fields are not duplicated between $setOnInsert and $inc.

But if i do this :

db.test.findAndModify({

   query : { '_id' : 'bert' },  

   update : {

       // upon insert only

  $setOnInsert : {'inserted' : true},

       // upon updates only

       $inc : {'counter' : 1},

       $set : {'inserted' : false}

   },

   upsert : true,

   new : true

});

This will throw error because $setOnInsert and $set both makes use of the same field 'inserted'.

So my previous question was : why would it throw error when $setOnInsert and both $inc+$set are both executed independently based on the existence of the record ?

But .. finally i realize my misunderstanding :

The non-setOnInsert actually executes, regardless of insert or update !,

which is why we cannot have duplicates of fields in both of them.

Regards from Jakarta,

Albert Kam

--

You received this message because you are subscribed to the Google Groups "mongodb-user"
group.
 
For other MongoDB technical support options, see: http://www.mongodb.org/about/support/.
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user...@googlegroups.com.
To post to this group, send email to mongod...@googlegroups.com.
Visit this group at http://groups.google.com/group/mongodb-user.

To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/11f04963-f8dd-4003-b2c7-37df7a53a47c%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[s.molinari]

All I can suggest then, is to vote for the suggestion in Jira Glenn pointed to.  https://jira.mongodb.org/browse/SERVER-6566

And I have to agree too. Doesn't seem logical to have a setOnInsert parameter and it not work on an insert, no matter what other parameters and fields are there. It should just basically override any duplicated field name so that the one parameter can be set on insert and the other during an update.

Scott

[Glenn Maynard]

On Fri, Apr 11, 2014 at 10:41 AM, s.molinari <scottam...@googlemail.com> wrote:

This might be a newb question, but why would you also need to extra logic in your query for MongoDB to figure out for every increment update, when it isn't really needed for most of those updates? I mean, the insert would most likely be much rarer than the update, isn't it? Covering both situations in one query wouldn't gain you much in network bandwidth, CPU usage or programming simplicity, would it?

Doing it in a single update is simpler, because doing it separately leads to race conditions that you have to deal with.  It's much harder to test and be sure that it does what you expect.

For example:

result = db.coll.update({_id: id}, {x: {$inc: 1}});

if(result.n == 0)
{

    // No records were updated, so we have to insert the record.

    db.coll.insert({_id: id, x: 0});

}

That has a race condition, since if two clients do this simultaneously, you can end up with both attempting the insert (and one failing), instead of one inserting and one incrementing.  You can deal with this as well, but it takes yet more logic.  Having Mongo do it atomically avoids a ton of complexity, since the whole thing happens atomically.

It's also impossible to do this without w:1.

On Fri, Apr 11, 2014 at 11:50 PM, s.molinari <scottam...@googlemail.com> wrote:

All I can suggest then, is to vote for the suggestion in Jira Glenn pointed to.  https://jira.mongodb.org/browse/SERVER-6566

And I have to agree too. Doesn't seem logical to have a setOnInsert parameter and it not work on an insert, no matter what other parameters and fields are there. It should just basically override any duplicated field name so that the one parameter can be set on insert and the other during an update.

Mongo's rule that any field can only appear in a single update helps keep the update syntax simple, because MongoDB never has to define the order that things happen in ("$setOnInsert happens before other operators").  There shouldn't be a weird exception for setOnInsert--solve it generically with the multiple-consecutive-updates feature.

--
Glenn Maynard

[s.molinari]

I understand the reason for wanting a findAndModify command. This is the I in ACID. Isolation, which is a core part of proper concurrency and which is the overall concern in this thread, I believe. 

It still doesn't make sense to me though, if the "find" is querying on a unique field (which is recommended and was used in Albert's example), how could there still be a concurrency issue for inserts? And the use case in this thread isn't about a concurrency issue with updating directly, because there is nothing to hamper a race condition on the update in Albert's use case. That is actually the main concern of the Jira suggestion you referred to, right? Concurrency during updates? Basically pushing conditional logic more into Mongo, so things can be checked and/ or updated in one shot?

Albert's concern is trying to save a trip to the database too, as I see it, which is also always good and usually means a certain race condition can be avoided, but it isn't always possible to do.:)

So, that all being said, I do agree, there will be a possible race condition, if a unique field isn't being used in the "find" query for findAndModify with upsert set to true. For this reason, a unique field to query on is recommended. In other words, it isn't the use of duplicate parameters in Albert's situation, which would help solve any concurrency concerns for the insert. It is what is being found and how it is being found (or not). Because, even if you add the ability to have the same parameters as Albert suggests, without the unique index being the determining factor to insert on, you're back to a possible race condition. I would think.

So theoretically, the Jira suggestion could be extended to basically add that inserts could be carried out according to more conditions within the findAndModify command with upsert set to true, so a unique index isn't always needed? In other words, with upsert set to true, leave it up to the programmer to search for a certain single document. If his query doesn't find a single document, return an error. If it does find a single document, check for a state within that document, if it is a proper state, update. If not, insert a new document. And in either case, the insert or update will happen with possibly different data. Actually, the different data between insert and update should be a whole new Jira. Shouldn't it? Which leads to....

....setOnInsert. You lost me there with "multiple-consecutive-updates". That is exactly my (and I believe Albert's) point. The setOnInsert has absolutely nothing to do with updates. The way I understand how it could work, if Mongo determines it is inserting, it sets fields according to what is behind setOnInsert. If Mongo determines it is updating, then any updating parameters are followed and all the setOnInsert stuff is ignored. In other words, findAndModify should simply logically separate what is done on an insert and what is done on an update.  

I am not arguing for or against any suggestions or concerns for the sake of arguing. I am just trying to better understand them and the situations for their uses with my admittedly limited knowledge.:) So, if I seem annoying or possibly highjacking the thread, I apologize for that. It isn't my intention.

Thanks for the help and explanations too! 

Scott

[Asya Kamsky]

Hold on, this isn't correct.   An update has some fields that get set.  If it's an upsert then *in addition* some other fields will be set on insert.

It's not either or.  This of set on insert functionality something that allows you to initialize certain fields when you create the record that you don't want to touch when you update it.   Whether or not you're querying on _id or unique field is not relevant.

I'm updating a record and I always want to set "lastUpdated" field to current time on update.  In addition, when it's an upsert, I also want to set "createdOn" field to current time, but never touch it after.   This is exactly where you would always do   {$set:{lastUpdated:$currentDate},$setOnInsert:{createdAt:$currentDate}

Asya

--

You received this message because you are subscribed to the Google Groups "mongodb-user"
group.
 
For other MongoDB technical support options, see: http://www.mongodb.org/about/support/.
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user...@googlegroups.com.
To post to this group, send email to mongod...@googlegroups.com.
Visit this group at http://groups.google.com/group/mongodb-user.

To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/91175343-c3d8-4ecd-b809-e75b0c7c2f7c%40googlegroups.com.

[s.molinari]

Hey Asya! 

On Sunday, April 13, 2014 11:16:39 AM UTC+2, Asya Kamsky wrote:

Hold on, this isn't correct.   An update has some fields that get set.  If it's an upsert then *in addition* some other fields will be set on insert.

It's not either or.  

Yes, that is clear.

 

This of set on insert functionality something that allows you to initialize certain fields when you create the record that you don't want to touch when you update it.   Whether or not you're querying on _id or unique field is not relevant.

Right, but it is relevant to the insert, if the field you are querying on isn't unique. The recommendation is to search on a unique indexed field.

I'm updating a record and I always want to set "lastUpdated" field to current time on update.  In addition, when it's an upsert, I also want to set "createdOn" field to current time, but never touch it after.   This is exactly where you would always do   {$set:{lastUpdated:$currentDate},$setOnInsert:{createdAt:$currentDate}

Asya

Which, if that was the only intention for the function (is it?), is fantastic.

However, the suggestion is to make it possible to update and insert different values for the same field, depending on what Mongo decides to do. Basically, searching and then inserting and updating could be more "flexible" within findAndModify, in order to make it simpler to avoid race conditions and increase isolation/ concurrency.

Well, at least that is what I understand from this discussion.:)

Scott

[Glenn Maynard]

Brevity aids discussion.  :)  I won't be trying to respond to every bit, due to time constraints.

On Sun, Apr 13, 2014 at 1:27 AM, s.molinari <scottam...@googlemail.com> wrote:

It still doesn't make sense to me though, if the "find" is querying on a unique field (which is recommended and was used in Albert's example), how could there still be a concurrency issue for inserts?

I explained a concurrency issue.  If you perform the update, and then perform the insert separately if the document didn't exist, then a race condition arises if somebody else performs the insert between your update and subsequence insert.

 

And the use case in this thread isn't about a concurrency issue with updating directly, because there is nothing to hamper a race condition on the update in Albert's use case. That is actually the main concern of the Jira suggestion you referred to, right? Concurrency during updates? Basically pushing conditional logic more into Mongo, so things can be checked and/ or updated in one shot?

Concurrency becomes a concern if you perform Albert's case with a separate update and insert.  I showed a code sample that would have that race condition.

 

So, that all being said, I do agree, there will be a possible race condition, if a unique field isn't being used in the "find" query for findAndModify with upsert set to true. For this reason, a unique field to query on is recommended. In other words, it isn't the use of duplicate parameters in Albert's situation, which would help solve any concurrency concerns for the insert. It is what is being found and how it is being found (or not). Because, even if you add the ability to have the same parameters as Albert suggests, without the unique index being the determining factor to insert on, you're back to a possible race condition. I would think.

So theoretically, the Jira suggestion could be extended to basically add that inserts could be carried out according to more conditions within the findAndModify command with upsert set to true, so a unique index isn't always needed?

(This isn't the race condition I was talking about.  I don't really understand what you're suggesting, but it looks unrelated to SERVER-6566.)

....setOnInsert. You lost me there with "multiple-consecutive-updates". That is exactly my (and I believe Albert's) point. The setOnInsert has absolutely nothing to do with updates. The way I understand how it could work, if Mongo determines it is inserting, it sets fields according to what is behind setOnInsert. If Mongo determines it is updating, then any updating parameters are followed and all the setOnInsert stuff is ignored. In other words, findAndModify should simply logically separate what is done on an insert and what is done on an update.  

The point of SERVER-6566 is to allow performing multiple (atomic, optionally conditional) updates consecutively in a single request.  It allows things like what's Albert's asking for.  (There's nothing specific to setOnInsert about this.)

--
Glenn Maynard

[s.molinari]

I think we are talking about the same things, just not understanding each other. 

I understand the race condition issue, when the insert/update logic is in the application and not being done by a findAndModify. However, I was talking about findAndModify and my "thinking" is all based around that function.

As for the Jira, you suggested Alfred's concern/ suggestion could be a part of that Jira. I was just trying to understand how his suggestion of having fields with different values depending on an insert or update could be added to it.

As I understand Albert, he wants to set a certain field to two different values (counter) depending on if the document is inserted or an update occurs and Mongo doesn't allow duplicate fields. So, I would say what Albert wants surely also has to do with setOnInsert, because that is the differentiator.

Scott

[Asya Kamsky]

I'm curious - is there a reason to set counter:0 when you create the document?

You could treat absence of the field as being 0  - that's what $inc does.

Asya

--

You received this message because you are subscribed to the Google Groups "mongodb-user"
group.
 
For other MongoDB technical support options, see: http://www.mongodb.org/about/support/.
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user...@googlegroups.com.
To post to this group, send email to mongod...@googlegroups.com.
Visit this group at http://groups.google.com/group/mongodb-user.

To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/CAMT20g8z3M1Mrei0N_5z4SMuk_%3D82ZDm-M_w4j3D%2BY75q4XhLQ%40mail.gmail.com.

[Albert Kam]

Using approach suggested by Scott: by avoiding the counter in the $setOnInsert already solves my problem.

I've updated my app logic to treat 1 as 'initialized', as the $inc in the update is executed anyway in the upsert operation.

So to answer Asya's question, no, there's no more reason to initialize the counter to 0 upon insert, at least in my case.

I try to think of cases where i need to initialize the values differently from the update, perhaps like setting an initial state-constant-value on a field, but i can always treat non-existent field as 'initialized' status.

I just need to do the null checking on the field's value on my app logic.

So my current needs are satisfied. 

But imho, being able to insert fields independently from the update would be nice to have.

Thanks for all the helps that lead to my current solution.

To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/CAOe6dJDt7t15Tg3kQZ3w6Rf1%3DJQ53wwWf2aA18YoDzyadCPNdw%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.

[s.molinari]

Great Albert. Glad I could help as a Mongo newb.:-) These kinds of problems and trying to help people help me understand Mongo's innards. 

@Asya - is it possibly a far fetched idea or completely unneeded to be able to set the same field to a different value depending on what Mongo decides to do between an insert or an update? To me, in all my ignorance about Mongo, it would seem quite a handy feature, if there is no other way to do it.

Scott 

[Asya Kamsky]

Since the "update" part of the update is always performed, I just don't see how I can parse your scenario.

There is no way to have something happen *only* on an update and *not* on an upsert.

But let's make it simpler, let's say we're talking about an update of existing document only.  Can you see how it wouldn't make sense to say
update({},{$set:{a:1},$inc:{a:1}})
right?

Asya

--

You received this message because you are subscribed to the Google Groups "mongodb-user"
group.
 
For other MongoDB technical support options, see: http://www.mongodb.org/about/support/.
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user...@googlegroups.com.
To post to this group, send email to mongod...@googlegroups.com.
Visit this group at http://groups.google.com/group/mongodb-user.

To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/d37180c5-870b-4fbc-b744-093a5544154d%40googlegroups.com.

[s.molinari]

Since the "update" part of the update is always performed, I just don't see how I can parse your scenario.

That is just it. Why is the update part always performed (and seems to take prio, a la error when a duplicate field is used), when inserting? Wouldn't it be reasonable to want two completely different things to happen, depending on whether or not a document is inserted or updated, since findAndModify offers the functionality with upsert: true....almost?

Scott

[Asya Kamsky]

Scott,

Maybe it's possible to have some syntax to allow something to happen only on update explicitly - there is no syntax for such operation at the moment.

While I'm not clear on what syntax would be applied to such a capability, I filed SERVER-13578 in Jira to request it - feel free to vote up and/or comment with additional use cases there.

Asya

--

You received this message because you are subscribed to the Google Groups "mongodb-user"
group.
 
For other MongoDB technical support options, see: http://www.mongodb.org/about/support/.
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user...@googlegroups.com.
To post to this group, send email to mongod...@googlegroups.com.
Visit this group at http://groups.google.com/group/mongodb-user.

To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/93229885-bd47-424b-8dde-14920ac94ca3%40googlegroups.com.

[s.molinari]

Thanks Asya.

Being the newb I am, I won't be able to come up with use cases directly. Maybe as our usage of Mongo grows, we will and I can come up with one or two. 

I noticed you used update() and not findAndModify(), which this discussion was about. Is that ok like that? I mean, is the suggested usage then really the same? (Shows how much a newb I am.)

And a final word.....you guys and gals at Mongo certainly know what service means. I like that and appreciate it a lot and makes me even more certain that our decision to go with Mongo is a good one.:-)

Thanks again!

Scott