Accessing expiration of a document using N1QL

SQL++
1

Hi,

I am trying to retrieve the expiration of a document. I have seen on this forum that this information is always 0 when using the bucket.get method and that the only way is to retrieve it either using a view or a N1QL query.

I was successful in doing so by creating a view and reading from it.

But, I am now trying to access it using a N1QL query and I am not successful.

If I do a query like SELECT META() FROM testBucket USE KEYS ‘docWithExpiry’, the only information returned is {“flags”:33554432,“id”:“docWithExpiry”,“cas”:1457964787240075300,“type”:“json”}

There are no expiration field in there so not sure how I should retrieve it.

Any help will be great.
Thanks.

2

Hi @lbertrand, yes we are missing the metadata item in N1QL. One possible workaround until we fix this is to have expiry exist as a attribute in the document as well so N1QL can access and Index the value. Something like this;

cbDoc = new Document
{
Id = _key,
Content = new
{…
exp_datetime = DateTime.UtcNow.AddMilliseconds(30000)
}
};

cbDoc.Expiry = 30000;
var upsert = cbBucket.Upsert(cbDoc);

Would this work?
thanks
-cihan

3

Thanks.
Yes this could work.

I am more tempted to create a view then as it is less intrusive and we do not need to change our documents… And when N1QL support it or when it is available in the metadata returned from the get method of the bucket, there will be less change to revert!

Any idea on timeframe for supporting this metadata in N1QL and SDK get method?

4

Thanks - you could track the work here: https://issues.couchbase.com/browse/MB-15916 - Right now it is on a release named spock which could be late 2016. if you or anyone else need this more urgently, I’d love to know so feedback welcome.
thanks
-cihan

5

Hello, have been using Couchbase Server 5.5 Beta ,
there does exist the expiration field as of now , but still these seems to be set as 0 always irrespective of the expiration set just like from the bucket.get method.
Pls , Any updates on the same.

6

N1QL meta().expiration will work through covering index.

7

Hello vsr1,
I don’t think so it works with covering index as well.

MetaData for one of the docId as seen in the Web-Console , for which i performed a touch operation seems fine:-

{
“doc_size”: 120,
“meta”: {
“id”: “airline_10”,
“rev”: “2-1547096d10c100005b62dd0102000000”,
“expiration”: 1533205761,
“flags”: 33554432,
“type”: “json”
},
“xattrs”: {}
}

But please check the result of the below N1QL query:-

select icao,meta(travel-sample).expiration from travel-sample where icao = “MLA” ;
Result:-
[
{
“expiration”: 0,
“icao”: “MLA”
}
]

8

CB version 5.0 and above. Expiration can’t be set through N1QL. https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/indexing-meta-info.html

CREATE INDEX ix1 ON `travel-sample`(icao,META().expiration);
SELECT t.icao,meta(t).expiration 
FROM `travel-sample` AS t
WHERE t. icao = "MLA" ;
1 Like
9

Hello vsr1,
Thank you for your reply.

In addition to accessing expiration via N1QL, we also intend to access expiration via the Key-Value Service(bucket.get(docId)) as well. At the moment the Documents, contain the expiration as 0, irrespective of any expiration being set. Could you suggest any possible way for the same as well.

10

You should check SDK documentation how to get this. cc @ingenthr

11

(meta-note @prasads697: it might be better to create a new topic rather than pick up a really old one for new questions)

There are a couple options here:

First, if you need to use this programmatically, not just for development time observability, starting with Couchbase Server 5.0, there is a feature named virtual xattrs that allows you to retrieve a number of these metadata fields. This is covered in the documentation on XATTRs. If you have questions after reading the docs I’d encourage you to click the “Feedback” button there.*

Second, if you just need to verify what is occurring with a given doc at development time, the libcouchbase cbc tools make this quite easy to do. There is a cbc keystats utility that can give you statistics about that particular document or you can use the subdocument API from cbc.

You can use the cbc utility included in libcouchbase to fetch the TTL with --keystats. For example:

$ cbc-stats --keystats -u Administrator -P - -U couchbase://localhost/travel-sample airline
_112
Bucket password: 
localhost:11210 key_is_dirty    false
localhost:11210 key_exptime 0
localhost:11210 key_flags   33554432 (cbc: converted via htonl)
localhost:11210 key_cas 1503621971151421440
localhost:11210 key_vb_state    active

Or an example that accesses the virtual xattrs:

$ cbc-subdoc -u Administrator -P - -U couchbase://localhost/travel-sample
Bucket password: 
subdoc> get -x $document airline_112
airline_112          CAS=0x14ddf0375af40000
0. Size=188, RC=0x00 Success (Not an error)
{"CAS":"0x14ddf0375af40000","vbucket_uuid":"0x0000e976b253ad5c","seqno":"0x0000000000000001","exptime":0,"value_bytes":118,"datatype":["json"],"deleted":false,"last_modified":"1503621971"}
1. Size=118, RC=0x00 Success (Not an error)
{"callsign":"FLYSTAR","country":"United Kingdom","iata":"5W","icao":"AEU","id":112,"name":"Astraeus","type":"airline"}
subdoc> get -x $document.exptime airline_112
airline_112          CAS=0x14ddf0375af40000
0. Size=1, RC=0x00 Success (Not an error)
0
1. Size=118, RC=0x00 Success (Not an error)
{"callsign":"FLYSTAR","country":"United Kingdom","iata":"5W","icao":"AEU","id":112,"name":"Astraeus","type":"airline"}
subdoc> get -x $document.exptime -x $document.value_bytes airline_112
airline_112          CAS=0x14ddf0375af40000
0. Size=1, RC=0x00 Success (Not an error)
0
1. Size=3, RC=0x00 Success (Not an error)
118
2. Size=118, RC=0x00 Success (Not an error)
{"callsign":"FLYSTAR","country":"United Kingdom","iata":"5W","icao":"AEU","id":112,"name":"Astraeus","type":"airline"}

Finally, I’d note that over the years we’ve had people occasionally report that the expiry wasn’t being honored when being set by the app, but to my knowledge in all cases that’s been reported we’ve found a bug in the app using our SDK’s lib. There certainly could be an issue and if you can extract a simple test case, we’d be glad to look at it. Expiration handling has been in Couchbase since day one and is tested regularly, so I suspect the issue is probably elsewhere.

Oh, and by the way, note that expiration is always converted from offsets to absolute at the time of receipt. So if you set something like 300 seconds, it’ll actually show in the value as (epoch_time+300s).

* and note there is a documentation request pending of @Richard_Smedley to improve the documentation, which the ticket DOC-3221 says is in progress

1 Like
12

Thanks ingenthr for your reply, will have a look on the virtual XATTR’s and will give appropriate feedback for the same.