cockroachdb / cockroachdb/cockroach

Table-level restore of table with DEFAULT expression including UDF is not possible

Open
#118,195 1 comment 0 reactions 0 assignees View on GitHub
A-sql-udf C-bug P-3 T-sql-foundations
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.