cockroachdb / cockroachdb/cockroach
sql: incorrect caching of database name for query on virtual table
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
Consider the example below:
```
defaultdb> SET sql_safe_updates = false;
SET
defaultdb> CREATE DATABASE db1;
CREATE DATABASE
defaultdb> USE db1;
SET
db1> SELECT * FROM crdb_internal.create_schema_statements;
database_id | database_name | schema_name | descriptor_id | create_statement
--------------+---------------+-------------+---------------+-----------------------
104 | db1 | public | 105 | CREATE SCHEMA public
(1 row)
db1> ALTER DATABASE db1 RENAME TO db2;
ALTER DATABASE
db2> SELECT * FROM crdb_internal.create_schema_statements;
ERROR: database "db1" does not exist
SQLSTATE: 3D000
```
Something is caching the original name of the database, `db1`, from the first invocation of the `SELECT` statement and reusing it in the second invocation. My guess is that this is happening in the query cache or the opt catalog, but I haven't dug in yet.
Jira issue: CRDB-32318
Contributor guide
Research direction
Reproduce the SQL sequence using crdb_internal.create_schema_statements: create and rename a database, then run the virtual-table query again. Inspect the query cache and opt catalog, which the issue identifies as likely areas; done means the second query resolves the renamed database instead of reporting that the old name does not exist.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100