sql: restore fails when a routine references a system table

[DrewKimball]

Here's a backupccl data-driven test that shows how RESTORE fails in response to a routine that references system.descriptor. We may be missing handling for rewriting references to the internal/system tables.

exec-sql
CREATE DATABASE db1;
----

exec-sql
USE db1;
----

query-sql
CREATE PROCEDURE p() LANGUAGE SQL AS $$
  SELECT * FROM system.descriptor;
$$;
----

exec-sql
BACKUP DATABASE db1 INTO 'nodelocal://1/test/'
----

exec-sql
DROP DATABASE db1
----

exec-sql
RESTORE DATABASE db1 FROM LATEST IN 'nodelocal://1/test/' WITH new_db_name = db1_new
----
----
pq: internal error: function descriptor rewrite failed: cannot restore function "p" because referenced table 3 was not found
DETAIL: stack trace:
github.com/cockroachdb/cockroach/pkg/sql/catalog/rewrite/rewrite.go:1161: FunctionDescs()
github.com/cockroachdb/cockroach/pkg/ccl/backupccl/restore_planning.go:2102: doRestorePlan()
github.com/cockroachdb/cockroach/pkg/ccl/backupccl/restore_planning.go:1429: func3()
github.com/cockroachdb/cockroach/pkg/sql/planhook.go:194: func1()
github.com/cockroachdb/cockroach/pkg/util/stop/stopper.go:498: func2()
src/runtime/asm_arm64.s:1222: goexit()

HINT: You have encountered an unexpected error.

Please check the public issue tracker to check whether this problem is
already tracked. If you cannot find it there, please report the error
with details by creating a new issue.

If you would rather not post publicly, please contact us directly
using the support form.

We appreciate your feedback.
----
----

Jira issue: CRDB-43036

[blathers-crl[bot]]

Hi @DrewKimball, please add branch-* labels to identify which branch(es) this C-bug affects.

_{:owl: Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[DrewKimball]

From @dt:

That feels somewhat right/like a legitimate known-limitation to me: restore doesn't restore system tables so references to them potentially should be considered invalid (since they were to the backed up tables).

As long as the SKIP_MISSING_UDFS escape hatch means you can still restore your data, just without the behavior, this seems in line with prior behaviors here.