sql: add primary key on empty table very slow

[kocoten1992]

Describe the problem

discover while trying reproduce #47512

To Reproduce

1. start fresh cockroach start-single-node --insecure (empty data)
2. create a file create_users_table.sql with content follow

drop table if exists "users" cascade;
create table "users" ("id" uuid not null, "name" varchar(255) not null, "email" varchar(255) not null, "password" varchar(255) not null, "remember_token" varchar(100) null, "created_at" timestamp(0) without time zone null, "updated_at" timestamp(0) without time zone null, "deleted_at" timestamp(0) without time zone null);
alter table "users" add primary key ("id");
alter table "users" add constraint "users_email_unique" unique ("email");

3. run cockroach sql --insecure < create_users_table.sql

It take a whooping 30s to add primary key on empty table (╯°□°)╯︵ ┻━┻

NOTICE: primary key changes are finalized asynchronously; further schema changes on this table may be restricted until the job completes
ALTER TABLE

Time: 211.001799ms

ALTER TABLE

Time: 30.581253037s

Expected behavior
Should be much faster since the table is empty.

Environment:

• CockroachDB version

Build Tag:    82294d1
Build Time:   2020/04/15 01:47:13
Distribution: CCL
Platform:     linux amd64 (x86_64-pc-linux-gnu)
Go Version:   go1.13.9
C Compiler:   4.2.1 Compatible Clang 3.8.0 (tags/RELEASE_380/final)
Build SHA-1:  82294d100ad1cb26b488f53e7241242d474d58d7
Build Type:   development

• Server OS: ubuntu 20.04 dev
• Client app cockroach sql (same version as server)

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I have CC'd a few people who may be able to assist you:

• @ajwerner (commented on create/drop table (with/without foreign key) take too long #47512)

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[rohany]

As the notice says, there is asynchronous work after adding a primary key that is happening in the background, which is delaying the constraint add from completing. If you put the create + add pk + add constraint into a transaction it will complete immediately. i.e.

begin;
drop table if exists "users" cascade;
create table "users" ("id" uuid not null, "name" varchar(255) not null, "email" varchar(255) not null, "password" varchar(255) not null, "remember_token" varchar(100) null, "created_at" timestamp(0) without time zone null, "updated_at" timestamp(0) without time zone null, "deleted_at" timestamp(0) without time zone null);
alter table "users" add primary key ("id");
alter table "users" add constraint "users_email_unique" unique ("email");
commit;

[awoods187]

we should look into this because i imagine this is common behavior in test suites in which a PK is a logical and not a physical grouping for Postgres and is not created implicitly.

[otan]

i suspect #47624 may help

[knz]

cc @ajwerner for triage. maybe you want to fold under #47790