cockroachdb / cockroachdb/cockroach
Table-level restore of table with DEFAULT expression including UDF is not possible
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
**Describe the problem**
With a schema like:
```
CREATE OR REPLACE FUNCTION a() RETURNS INT AS 'SELECT 1' LANGUAGE SQL;
CREATE TABLE foo (pk INT PRIMARY KEY, b INT DEFAULT a());
```
We can take a database level backup that includes everything needed to restore the table:
```
demo@127.0.0.1:26257/demoapp/defaultdb> backup database defaultdb into 'userfile:///backup';
job_id | status | fraction_completed | rows | index_entries | bytes
---------------------+-----------+--------------------+------+---------------+--------
936955438708523009 | succeeded | 1 | 0 | 0 | 0
(1 row)
Time: 206ms total (execution 206ms / network 0ms)
demo@127.0.0.1:26257/demoapp/defaultdb> SHOW BACKUP LATEST IN 'userfile:///backup';
database_name | parent_schema_name | object_name | object_type | backup_type | start_time | end_time | size_bytes | rows | is_full_cluster | regions
----------------+--------------------+-------------+-------------+-------------+------------+-------------------------------+------------+------+-----------------+----------
NULL | NULL | defaultdb | database | full | NULL | 2024-01-23 10:41:47.991615+00 | NULL | NULL | f | NULL
defaultdb | NULL | public | schema | full | NULL | 2024-01-23 10:41:47.991615+00 | NULL | NULL | f | NULL
defaultdb | public | a | function | full | NULL | 2024-01-23 10:41:47.991615+00 | NULL | NULL | f | NULL
defaultdb | public | foo | table | full | NULL | 2024-01-23 10:41:47.991615+00 | 0 | 0 | f | NULL
(4 rows)
```
But restoring the table alone does not work since we don't currently restore UDFs like we do for types:
```
demo@127.0.0.1:26257/demoapp/defaultdb> restore table foo from latest in 'userfile:///backup';
ERROR: cannot restore table "foo" without referenced function 104 (or "skip_missing_udfs" option)
```
And since functions cannot be specified as restore targets in the restore command, the only option is to skip restoring the function or to do a database level restore.
Jira issue: CRDB-35601
Epic CRDB-61002
Contributor guide
Research direction
Start by reproducing the CREATE FUNCTION, CREATE TABLE, BACKUP DATABASE, and RESTORE TABLE commands shown in the issue, then trace how table-level restore handles referenced UDFs and the skip_missing_udfs option. Done means restoring table foo also restores its referenced function, or provides an explicitly supported restore path without requiring a database-level restore.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 42/100