Delete multiple documents with N1QL query problem

Node.js SDK
,
1

I try to delete multiple documents. Current approach is to use the N1QL query.
The query looks more or less like:

DELETE FROM `kit` doc
WHERE `doc`.field = 'somecondition'

Before deleting I do search query with the same WHERE condition in order to compare the amount of deleted documents. If it’s different then I will rollback the transaction.


        const selectResult = await context.query<SearchResult>(`${selectStatement}${whereStatement}`);
        const deleteProjectResult = await context.query(`${deleteStatement}${whereStatement} RETURNING \`doc\`.id`);

        if (selectResult.rows.length !== deleteProjectResult.rows.length) {
          throw new InternalServerErrorException(error)
        }

Until now I received no error so the context.query result returns correct number of the deleted documents. However from time to time when I use the same select query in couchbase web api to check if everything is ok then I notice that not all documents are deleted (for example 6 of 100 documents are not deleted). What should I do to improve this behaviour? Shall I use context.remove(documentToDelete) to each element?

2

For consistent results for queries with SELECT and DELETE which rely on indexes, use the query option “query consistency”. You may need to do a dummy kv replace operation to get a MutationState to use in both the calls. Or you could simply trust the DELETE to find and delete what it will.

It’s important to understand that documents are indexed asynchronously, such that what is found in an index (by a predicate in a where clause) will change from one moment to the next and also from one node to the next - even after the actual documents have changed.

Also - check your application to see if it is critical for the number of documents deleted be the same as the number found by the SELECT. If the delete is to periodically remove documents that have been modified, documents missed on one iteration will be deleted on a subsequent iteration.

1 Like
3

Your select can project document key and use SDK KV operation delete the documents (parallel/asynchronous)

4

The select queries are completly irrelevant (I do it only for debugging purposes). Main goal is to delete all document from the delete-query.
Does KV operation mean

context.delete(object)

? If so then I tried it but it doesn’t help. I iterated over each element and did with no luck:

const obj = await context.get(id_of_document)
await context.delete(obj)

What’s more I hardcoded in application the query to delete problematic documents. Before and right after deletion I also call select query to check if elements are deleted. First select finds problematic elements and the second query can not find any of them however in web panel I’m able to find them. I did it several times.

When I execute the same N1QL delete query in web panel then deleting finishes without any problem. Seems like a bug in Nodejs SDK or some kind of locking which prevents documents from deletion (or both).

5

However from time to time when I use the same select query in couchbase web api to check if everything is ok then I notice that not all documents are deleted (for example 6 of 100 documents are not deleted).

This is because the indexes (which are used by the SELECT) are updated asynchronously from the deletions. If you want the SELECT to wait until the indexes are updated, use the QueryOption QueryScanConsistency=REQUEST_PLUS.

The select queries are completly irrelevant (I do it only for debugging purposes). Main goal is to delete all document from the delete-query.

The delete-query uses the same mechanism as the select-query and has the same issue with indexes being updated asynchronously. So just as the select-query can return results for documents that have just been deleted (and the indexes have not been updated), the delete-query will miss documents that have been inserted but do not yet have indexes generated. To delay the delete-query until indexing is complete, use the DeleteOption QueryScanConsistency=REQUEST_PLUS.

6

collection.remove(key) see Collection | couchbase . The arg is the document key (not the document). Note that the indexes are still updated asynchronously so a subsequent query may still return the deleted document. However collection.get(key) will not find it.

remove() is similar to the upsert() call here Start Using the Node.js SDK | Couchbase Docs

7

Hello mreiche, thanks for answering.

Ok, I think this helped. Problem was that I wanted to ran whole operation within transaction in order to be able to rollback if something goes wrong. This is why I used context to run N1QL delete query and in second scenario to iterate over the documents to delete and call context.remove(document). But it resulted in problem I described above. However when I ran it directly on collection collection.remove(key), it seems to solve problem.

Is it somehow possible to do collection.remove on some documents and later if something wrong happens then rollback removes?

8

Are you using couchbase transactions (Using Couchbase Transactions | Couchbase Docs)? If a delete fails, then the transaction will be rolled back. You don’t have to write code that does that.