cockroachdb / cockroachdb/cockroach

ttl: better handling of inbound FK with ON DELETE RESTRICT

Open
#101,372 0 comments 0 reactions 0 assignees View on GitHub
A-row-level-ttl C-enhancement docs-known-limitation T-sql-queries
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

After #101376, the TTL job will fail if the TTL table has an inbound FK with the `ON DELETE RESTRICT` option and a row references the TTL table.

Some options to fix this:
1) Add an `ON FOREIGN KEY REFERENCE DO NOTHING` syntax to the `DELETE` statement. See https://www.cockroachlabs.com/docs/v22.2/insert#do-not-update-values-on-conflict
2) Filter these rows out in the TTL job
```
DELETE FROM ttl_table ttl WHERE ttl.fk NOT IN (SELECT other.fk FROM other_table other WHERE other.fk IS NOT NULL)
```

Jira issue: CRDB-26916

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.