cockroachdb / cockroachdb/cockroach

changefeedccl: add a builtin cdc function to check whether a record's TTL expired

Open
#106,694 3 comments 0 reactions 0 assignees View on GitHub
A-cdc A-cdc-expressions C-enhancement T-cdc
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

Rangefeeds currently don't provide enough information to distinguish a regular delete from a TTL job delete, making it difficult to write a changefeed that only emits transactional deletes (e.g. for auditing) or only emits TTL deletes (e.g. for archiving). However, changefeeds always have a hydrated table descriptor as of every rangefeed event, and the table descriptor gives the TTL configuration for the table, so changefeeds do have enough information to tell whether or not a given row was expired (i.e. _eligible_ for a TTL job delete) at the time of a given event, including a delete event. This might be good enough--we can't disambiguate between somebody manually deleting an expired record before the job gets to it and the job's deletion, but that might be okay.

https://github.com/cockroachdb/cockroach/commit/90ba9414a6fdf8259856d6223bf260a64735661f gives one approach to implementing this: a `cdc_expiration_time` function that under the hood we replace with the table's actual TTL expression, so that column references are captured correctly for the purpose of things like picking column families and tracking type dependencies. You'd use it like

```sql
create changefeed as select * from ttl_test_per_row where cdc_is_delete() AND cdc_expiration_time() < statement_timestamp(); -- Only TTL deletes
create changefeed as select * from ttl_test_per_row where not (cdc_is_delete() AND cdc_expiration_time() < statement_timestamp()); -- Everything but TTL deletes
```

Jira issue: CRDB-29681

Epic CRDB-21713

Contributor guide

Open the contributing guide

Research direction

Start by reading the referenced commit for the proposed cdc_expiration_time approach, then trace the changefeed builtin-function handling and TTL table-descriptor data it relies on. Validate the documented SQL examples for TTL-only deletes and for excluding TTL deletes; done means the function evaluates the table's TTL expression for each event, including deletes.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, sql
Domain
data-engineering, databases
Issue type
Feature
Difficulty
5/5
Estimated time
Over a week
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.