sql: error in schema change during begin with reference

[maddyblue]

root@:26257/d> create table t (id int primary key);
CREATE TABLE
root@:26257/d> begin;
Now adding input for a multi-line SQL transaction client-side.
Press Enter two times to send the SQL text collected so far to the server, or Ctrl+C to cancel.
            -> create table x (a int references t);
            -> 
CREATE TABLE
root@:26257  OPEN> commit;
ROLLBACK
root@:26257/d> create table x (a int references t);
CREATE TABLE
root@:26257/d> drop table x;
DROP TABLE
root@:26257/d> begin;
Now adding input for a multi-line SQL transaction client-side.
Press Enter two times to send the SQL text collected so far to the server, or Ctrl+C to cancel.
            -> create table x (a int);
            -> 
CREATE TABLE
root@:26257/d  OPEN> commit;
COMMIT

This demonstrates that adding a table in a begin will fail if it includes a foreign key reference. Is this known and intended? I spent an hour being really confused about what was going on.

[maddyblue]

Also happens when using INTERLEAVE, even when no REFERENCES is specified.

[vivekmenezes]

I can reproduce this only through the cockroach CLI

[vivekmenezes]

This is happening because after executing the schema change

create table x (a int references t);

the server is seeing a SHOW DATABASE command and hitting the
statement cannot follow a schema change in a transaction error

This is specific to the CLI

[knz]

Ok, I'll take that.

[maddyblue]

It's probably safe to bump this to 1.1 also, since it's not a bug, just a surprising behavior.

[knz]

The PR that closed this has been reverted in #15511, so reopening.

[vivekmenezes]

the issue as described is no longer an issue. I just tried to reproduce it.

There is another issue: #13505 related to not being able to use a table created with an fk in the same transaction.