cockroachdb / cockroachdb/cockroach

sql: temp sequence cleanup fails on schema-locked permanent tables

Open
#167,117 1 comment 0 reactions 0 assignees View on GitHub
C-bug T-sql-foundations
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

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.