sql: cannot do schema operations on a database different from the session's database

[jordanlewis]

Unlike other commands in the database, you can't make or reference a schema inside of a database that isn't the one that your session is attached to. Repro:

root@127.0.0.1:63131/defaultdb> create schema foo;
CREATE SCHEMA

Server Execution Time: 2.667ms
Network Latency: 28.512ms

root@127.0.0.1:63131/defaultdb> create schema otherdb.foo;
invalid syntax: statement ignored: at or near ".": syntax error
SQLSTATE: 42601
DETAIL: source SQL:
create schema otherdb.foo

I'd expect that the latter statement work okay. This is also a problem for every other command that references a schema, such as ALTER TABLE SET SCHEMA, ALTER SCHEMA, DROP SCHEMA, etc.

[ajwerner]

I feel like this is one of those ones that I and we were very aware of when writing things like logic tests and somehow it didn’t occur to raise this a major issue but, as you noted, it very much is. Thanks for documenting. Should be easy-ish to fix. Let’s look at the fix and decide whether it’ll make the cut?

[thoszhang]

The current decision is that we do want to do this, but not for 20.2.

FWIW, I think we originally decided this wasn't very important way back in #50613 and never revisited it, which seems like a mistake. I think Rohan's point about unintended interactions still stands but we can figure those out.

[ajwerner]

Tentatively assigning @jayshrivastava .