cockroachdb / cockroachdb/cockroach

TTL processing on big table fails with lexical error

Open
#129,858 4 comments 0 reactions 0 assignees View on GitHub
A-docs branch-release-23.2 branch-release-24.1 C-bug O-community P-3 T-sql-queries
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

**Describe the problem**

Having a big table with billions rows, makes ttl jobs fail with lexical error: placeholder index must be between 1 and 65536"

Having a big table with billion of rows, where thousands of records are being added every second, TTL fails to process the table.

**To Reproduce**

```
CREATE TABLE public.accesslog (
login STRING(255) NOT NULL,
ip INET NOT NULL,
"timestamp" TIMESTAMPTZ(6) NOT NULL,
CONSTRAINT "primary" PRIMARY KEY (ip ASC, "timestamp" DESC),
INDEX authpolicy_accesslog_login_idx (login ASC, "timestamp" DESC),
INDEX authpolicy_accesslog_time_idx ("timestamp" ASC)
) WITH (ttl = 'on', ttl_expiration_expression = e'(("timestamp" AT TIME ZONE \'UTC\') + INTERVAL \'30 days\') AT TIME ZONE \'UTC\'', ttl_job_cron = '13 */4 * * *', ttl_select_batch_size = 5000000, ttl_delete_batch_size = 50000)
```

Fill the table with >1b rows, and add ~2k rows every second, and watch the ttl cleanup log.

**Expected behavior**
Expects the TTL job to run, where TTL select loads the oldest 5.000.000 rows at each interval, deleting all selected records at batches of 50.000

**Environment:**
- CockroachDB version: 24.1.0
- Server OS: Ubuntu 22.04.4 LTS
- Client app: n/a internal TTL job

**Additional context**
Lowering the intervals/batch sizes make the select statement take unproportional resources from the cluster, raising the interval/batch sizes causes this error - hence using TTL to trim a big table seems broken.

**Update:** See internal Slack thread: https://cockroachlabs.slack.com/archives/C0168LW5THS/p1724253038124859

We can close this after the Jira documentation issue is completed https://cockroachlabs.atlassian.net/browse/DOC-10962

Jira issue: CRDB-41739

Epic CRDB-18322

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.