How to create index for querying _deleted

Couchbase Server
1

I use Sync Gateway as a backend for my mobile device apps. Every now and then I need to purge the deleted objects, otherwise the initial pull replication takes too long (see thread Cleanup Sync Gateway deleted documents )

For a manual purge query I need to query for _deleted = true and _sync.time_saved < “2019-05-05”
I can’t figure out how to create an index to support that query.
my index:

CREATE INDEX `i_nro-dev_sync_time_saved` ON `nro-dev`(`_sync.time_saved`) WITH { “num_replica”:1 }

my query:

select * from nro-dev where _deleted and _sync.time_saved < “2019-05-05”;

The error message:

{
“code”: 4000,
“msg”: “No index available on keyspace nro-dev that matches your query. Use CREATE INDEX or CREATE PRIMARY INDEX to create an index, or check that your expected index is online.”,
“query_from_user”: “select * from `nro-dev` where _deleted and _sync.time_saved < "2019-05-05";”
}

2

There are couple of options for purging tombstones depending on whether you have shared bucket access enabled or not. You can check out the options [here](https://docs.couchbase.com/sync-gateway/2.5/managing-tombstones.html.

3

Thanks, I’ll look at better tombstone management later.
This question is just about the index and the query. It is a straight forward query ‘where _deleted and _sync.time_saved < “2019”’, but I can’t figure out how to create an index for it.

4

I’ve moved this into the N1QL category since this is a question purely on the server side.

5

I can’t reproduce your specific problem. But the statement you are using to try to create the index isn’t quite correct. Because the name of the bucket and the name of the index both contain a minus sign, which isn’t a standard character in names, you need backticks around them. The clause about replicas is also redundant. You also need backticks around the name of the bucket in the select statement. Perhaps that was causing some sort of problem.

select * from `nro-dev` where _deleted and _sync.time_saved < "2019-05-05"

CREATE INDEX `i_nro-dev_sync_time_saved` ON `nro-dev`(_sync.time_saved)

If things still aren’t working, please tell us what version of couchbase you are using.

6

Thank you. It was indeed an issue with the backticks.
wrong is: `_sync.time_saved`
correct is: _sync.time_saved
also correct: `_sync`.`time_saved`