sql: deprecate cross-database references between catalog elements

[thoszhang]

As part of moving toward a more Postgres-compatible world where databases are isolated from each other, we're going to stop supporting references between catalog elements (i.e., tables, types, and schemas) in different databases. The following kinds of references will only be permitted for tables/types in the same database:

• foreign key constraints
• sequence ownership
• views
• user-defined types (cross-database references were never allowed, aside from a blip in the 20.2 prereleases: #55779)

In 20.2 we introduced cluster settings to disallow creating new cross-database references for foreign keys, sequence owners, and views by default (#54126).

The proposal is that in 21.1 we'll always disallow the creation of new cross-database references, and have a mechanism to prevent upgrading to 21.2 until users have removed all their existing cross-database references by either moving tables or removing the cross-database schema elements. Specifically, we'd use the long-running migration framework, and have a potentially very long running migration that continually checks whether users have finished removing their cross-database references, and only completes successfully upon confirming that they have.

Other concerns:

• 55709 needs to be fixed in general.

• 51445 (removing the ability to rename tables into a different database) is somewhat related, in that we'll want to preserve a way for users to move their tables across databases to avoid cross-database references, so we probably shouldn't remove this functionality until 21.2. (Note that moving tables across databases isn't a "cross-database reference" in the sense meant by this issue.)

Epic: CRDB-1519

Jira issue: CRDB-3631

[thoszhang]

The current thinking is that we'll start the migration for #55793 automatically after the user has set some flag (via cluster setting) and implement all this as one long-running migration.

[ajwerner]

One important consideration for this task is the interaction with RESTORE of a backup which includes such references. What is the user story? Do we reject them?

cc @mwang1026

[mwang1026]

Thanks for the ping. Let me bring this to the team to discuss. My initial thought is to invalidate them with some sort of warning after the fact, but want to talk through other options too (e.g. invalidation is an option, otherwise error if we find them).

[postamar]

@ajwerner should we add a cluster upgrade precondition for 23.1 which checks that the cross-database cluster setting is disabled, to consider this done?

[ajwerner]

Steps:

1) Add a migration and pre-condition to make sure these things don't exist

• Migration makes cluster setting meaningless 2) Block restores of such references 3) Add gated validation that ensures that such references do not exist