cockroachdb / cockroachdb/cockroach
ttl: better handling of inbound FK with ON DELETE RESTRICT
Open
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
Assessment
This issue has not been assessed yet.