Stored Procedure not updating some documents in Cosmos DB if a single Partition key have many documents

Question

Stored Procedure not updating some documents in Cosmos DB if a single Partition key have many documents

Santo George Daniel 0

I have a stored procedure to replace some special characters and it is working fine for documents.

I have come across an issue, that If I have a partition key with many documents(200), then this stored procedure is executing only first few documents and then skipping other docs for that partition key.

Plaese advise whether anyone encounter the same issue

GeethaThatipatri-MSFT 29,592 Reputation points Microsoft Employee Moderator

2023-12-08T17:59:40.7766667+00:00

Hi,@SGD Welcome to Microsoft Q&A thanks for posting your question.

It's possible that the stored procedure is encountering an error or exception that is causing it to stop processing documents after a certain point. Can you share more information about the stored procedure and the documents it is processing, it's difficult to say for sure what the issue could be.

Regards

Geetha
Santo George Daniel 0 Reputation points

2023-12-08T18:52:00.0133333+00:00

Thanks for the update.

I have a stored procedure in a container which will remove special characters from items.

I have defined partition key for the container(Eg: processID).

I have many items in the container and same partition key for multiple items.

Like, same processID for 200 items .

If I try to execute the stored procedure for a partition key with more than (may be ) 120 items, the stored proc will remove special character from first 100 items and rest all items data is not removing special characters.

I tried executing multiple times still no change.

Thanks

Santo
GeethaThatipatri-MSFT 29,592 Reputation points Microsoft Employee Moderator

2023-12-11T18:15:29.1066667+00:00

@SGD SP are not supposed to be used for updating all items. SP is executed only on one partition.

you have to log https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/how-to-write-stored-procedures-triggers-udfs?tabs=javascript#logging

Regards

Geetha
Santo George Daniel 0 Reputation points

2023-12-13T04:42:07.5566667+00:00

Thanks for the reply. Currently, my cosmos have 100+ items(documents) related to one partition key .When I execute SP for one partition key, it will have 100+ items fetch from query and need to update some fields for each item. Unfortunately, it is updating only first few documents ( example: 80 items). The SP is returning success message too. In this case, what need to do to make sure all items should be updated.

1 answer

Your answer

GeethaThatipatri-MSFT 29,592 Reputation points Microsoft Employee Moderator

2023-12-08T17:59:40.7766667+00:00

Hi,@SGD Welcome to Microsoft Q&A thanks for posting your question.

It's possible that the stored procedure is encountering an error or exception that is causing it to stop processing documents after a certain point. Can you share more information about the stored procedure and the documents it is processing, it's difficult to say for sure what the issue could be.

Regards

Geetha
Santo George Daniel 0 Reputation points

2023-12-08T18:52:00.0133333+00:00

Thanks for the update.

I have a stored procedure in a container which will remove special characters from items.

I have defined partition key for the container(Eg: processID).

I have many items in the container and same partition key for multiple items.

Like, same processID for 200 items .

If I try to execute the stored procedure for a partition key with more than (may be ) 120 items, the stored proc will remove special character from first 100 items and rest all items data is not removing special characters.

I tried executing multiple times still no change.

Thanks

Santo
GeethaThatipatri-MSFT 29,592 Reputation points Microsoft Employee Moderator

2023-12-11T18:15:29.1066667+00:00

@SGD SP are not supposed to be used for updating all items. SP is executed only on one partition.

you have to log https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/how-to-write-stored-procedures-triggers-udfs?tabs=javascript#logging

Regards

Geetha
Santo George Daniel 0 Reputation points

2023-12-13T04:42:07.5566667+00:00

Thanks for the reply. Currently, my cosmos have 100+ items(documents) related to one partition key .When I execute SP for one partition key, it will have 100+ items fetch from query and need to update some fields for each item. Unfortunately, it is updating only first few documents ( example: 80 items). The SP is returning success message too. In this case, what need to do to make sure all items should be updated.

Answer 1

Sajeetharan 2,261 Microsoft Employee

Looks like you're using the query within stored procedure which migh span across multiple pages, you need to use the continuation token in that case. You can look at this example below,

 function tryQueryAndDelete(continuation) {     var requestOptions = { continuation: continuation };     var isAccepted = container.queryDocuments(       containerLink,       query,       requestOptions,       function(err, retrievedDocs, responseOptions) {         if (err) throw err;         if (retrievedDocs.length > 0) {           tryDelete(retrievedDocs);         } else if (responseOptions.continuation) {           tryQueryAndDelete(responseOptions.continuation);         } else {           responseBody.continuation = false;           response.setBody(responseBody);         }       }     );     if (!isAccepted) {       response.setBody(responseBody);     }   }

Santo George Daniel 0 Reputation points

2023-12-13T04:40:52.92+00:00

Thanks for the reply. Currently, my cosmos have 100+ items(documents) related to one partition key .When I execute SP for one partition key, it will have 100+ items fetch from query and need to update some fields for each item. Unfortunately, it is updating only first few documents ( example: 80 items). The SP is returning success message too. In this case, hoe the continuation can be implemented to make sure all items should be updated.

Share via

Stored Procedure not updating some documents in Cosmos DB if a single Partition key have many documents

1 answer

Your answer