I want to set a TTL for rows, but not based on the creation time. I want the TTL to start after I mark a timestamp field as complete. Will that work as expected?
TTL works by timestamping a row when it is created/inserted only, not on updates. So no, it can not be used in the way that you ask. TTL can be a problematic feature in that is breaks proper ACID Isolation when used. In Repeatable Read and Serializable isolation modes, a row expired/deleted through TTL will ‘disappear’ from a running transactions view.
What you ask for I think is not entirely unreasonable as long as your app can handle (or just doesn’t care about) the ACID violations. I believe it sounds like a reasonable feature request. I went ahead and logged a feature request for this against Percona Server ([url][PS-5389] Allow NULL value for rows in column referenced by ts_col to mean "don't filter" - Percona JIRA) and will also send the request upstream to Facebook.
George, thanks for taking action on that, it is much appreciated! I upvoted and watched the Jira issue. I’d also be interested in seeing the upstream feature request discussion if it is somewhere public.
Hello Derek,
I believe I am actually mistaken in my answer. In my previous response I completely forgot about the ttl_col functionality. It looks like everything is in place to do what you want with the exception that MyRocks does not handle or allow a column specified in ‘ttl_col’ to be NULLable. I am not sure of the reason why, but it seems to me that if that were allowed, and the compaction filter just skips rows with either a NULL or a ‘0’ value in that row, that your use case would be satisfied. Currently, if the value of the column specified in ts_col is ‘0’ it is filtered immediately. This seems wrong to me but there may be a subtle reason for this in some use case. I would think though that at least recognizing NULL value as a meaning of “don’t filter” would be a very simple solution and a fit for the case you describe. I will update our feature request and discuss the reasons of and NULL behaviors with upstream and see if we can come up with a solution that works.