[using CB 4.5 Enterprise]
We are trying to implement a solution to reduce load from our queries on our main application bucket(s).
What we have decided to do was create an “archive” buckets and move documents from main buckets to it when they are not needed (say, older than X days)
We want to implement that in N1QL.
Ideally, it can be done in a single N1QL statement, so it’s “atomic”.
However, we could not find a way to do it.
We thought to use “DELETE … RETURNING” on the main bucket, and wrap that in an INSERT.
INSERT INTO `archive` (KEY _k, VALUE _v) SELECT _k, _v FROM ( DELETE from `main` a WHERE a.`document_time` <= DATE_ADD_MILLIS(NOW_MILLIS(), -15, 'day') RETURNING meta(a).id _k, a _v )
But that fails with “syntax error - at DELETE”
Or, as a second option, “UPDATE … RETURNING” and again wrap that in the INSERT to archive
(and now at least the docs that were copied are “marked” for deletion):
INSERT INTO `archive` (KEY _k, VALUE _v) SELECT _k, _v FROM ( UPDATE `main` a SET `archive_candidate` = 1 WHERE a.`document_time` <= DATE_ADD_MILLIS(NOW_MILLIS(), -15, 'day') RETURNING meta(a).id _k, a _v )
that too fails the same with “syntax error - at UPDATE”.
For some reason, the “DELETE/UPDATE” statements with “RETURNING”, even tho they return a very real looking result sets, can’t be used in an inner SELECT statement (with or without the wrapping INSERT).
Which leaves us with the least favorable option - to do it in 3 steps:
- PASS 1: Update candidates for deletion
- PASS 2: “copy” candidates (INSERT with SELECT)
- PASS 3: Delete those documents
Any way possible to make it work in 1 (or 2) steps?
Any reason for the inner UPDATE/DELETE not to be allowed?