19.2: sql: TRUNCATE prevents later ALTER TABLE ... RENAME TO ...

[ajwerner]

Describe the problem

The implementation of TRUNCATE is to create a new table with a new descriptor ID and replace an old table which we'll drop. When we drop the old table, we create a drop job to asynchronously clean up its data. In 19.2 and prior, we'd assign this job ID to the old table prior to copying it for the new table. This means that the new table descriptor will have a populated drop job ID. This populated drop job ID then later prevents renames.

Here's the crazy assignment:

cockroach/pkg/sql/truncate.go

Lines 190 to 191 in ee75989

	tableDesc.DropJobID = dropJobID
	newTableDesc := sqlbase.NewMutableCreatedTableDescriptor(tableDesc.TableDescriptor)

The problem ends up being here:

W200622 19:46:55.858884 359674971 sql/exec_util.go:1140 [n5,client=10.0.32.19:38956,user=root] error executing schema change: job with ID 559393931270488069 does not exist

To Reproduce

Using 19.2.7 or prior: TRUNCATE a table, set the default GC ttl to 1s, wait for the drop of the old table to succeed, then try to rename the table.

root@localhost:26257/defaultdb> ALTER  TABLE bar RENAME TO foo;                          
pq: cannot update progress on succeeded job (id 566502454837379073)
-- it'd be a different error if this happened after the job record had been GC'd
root@localhost:26257/defaultdb> ALTER  TABLE bar RENAME TO foo;
pq: relation "bar" does not exist

Expected behavior

We won't write that DropJobID and we remain robust to schema changes which try to operate on public tables which have it.

Additional context

At this point, we need to do a couple of things:

• Remove the bogus DropJobID in the new table
• Make the rename, and schema changes in general, robust to table which have such drop jobs IDs but are not dropped.

[ajwerner]

I have some lingering concerns about the job upgrade migration that I'm working through. I'm curious to what extent this bug relates to #49876 which I'm still investigating.

[thoszhang]

I have some lingering concerns about the job upgrade migration that I'm working through.

I'm interested in this, say more when you have time?

[dbist]

#50766

[ajwerner]

I have some lingering concerns about the job upgrade migration that I'm working through.

I'm interested in this, say more when you have time?

I just wanted to make sure that these DropJobIDs wouldn't cause problems when upgrading. They don't. Nothing to see here.