cockroachdb / cockroachdb/cockroach
sql: temp sequence cleanup fails on schema-locked permanent tables
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
## Description
When a permanent table is created with a `DEFAULT` expression referencing a temporary sequence, and the table is schema-locked (the default when `create_table_with_schema_locked=true`), temp cleanup fails when the session ends.
The cleanup code (`cleanupTempSequenceDeps`) attempts to `ALTER TABLE` the permanent table to remove the `DEFAULT` expression that references the temp sequence before dropping it. This fails with:
```
this schema change is disallowed because table "perm_table" is locked
and this operation cannot automatically unlock the table
```
Because the `ALTER TABLE` and `DROP SEQUENCE` run in the same transaction, neither succeeds. `retryTempCleanup` retries 5 times but the failure is deterministic. The background cleaner retries every 30 minutes with the same result. The temp sequence and schema persist indefinitely.
## Reproduction
```sql
SET experimental_enable_temp_tables=true;
CREATE TEMP SEQUENCE temp_seq;
CREATE TABLE perm_table (a INT DEFAULT nextval('temp_seq'), b INT);
-- disconnect session
-- temp cleanup fails, temp_seq and temp schema persist
```
## Expected behavior
CockroachDB should either:
1. **Block the cross-reference at creation time**: Reject `CREATE TABLE perm_t (a INT DEFAULT nextval('temp_seq'))` when the table would be schema-locked. This is analogous to the existing check "constraints on permanent tables may reference only permanent tables" for foreign keys.
2. **Or force-unlock the table during cleanup**: Allow the cleanup code to remove the `DEFAULT` expression even on schema-locked tables (though this may undermine schema locking guarantees).
## PostgreSQL behavior
PostgreSQL allows the same `CREATE TABLE` statement and handles cleanup correctly — dropping the temp sequence via `CASCADE` automatically removes the `DEFAULT` expression from the permanent table. PostgreSQL does not have schema locking, so the cleanup always succeeds. After cleanup, the permanent table's column has no default (`column_default` is `NULL`), and `INSERT ... VALUES (DEFAULT, ...)` inserts `NULL`.
Epic CRDB-17128
Jira issue: CRDB-62254
Contributor guide
Assessment
This issue has not been assessed yet.