sql: can't atomically swap names for catalog entries

[thoszhang]

These operations are allowed in postgres, but not in cockroach, where you'll get a relation ... already exists error:

lucy=# create table foo();
CREATE TABLE
lucy=# begin;
BEGIN
lucy=# alter table foo rename to bar;
ALTER TABLE
lucy=# create table foo();
CREATE TABLE
lucy=# commit;
COMMIT

lucy=# create table foo();
CREATE TABLE
lucy=# create table bar();
CREATE TABLE
lucy=# begin;
BEGIN
lucy=# drop table foo;
DROP TABLE
lucy=# alter table bar rename to foo;
ALTER TABLE
lucy=# commit;
COMMIT

lucy=# create table foo();
CREATE TABLE
lucy=# create table bar();
CREATE TABLE
lucy=# begin;
BEGIN
lucy=# alter table foo rename to temp;
ALTER TABLE
lucy=# alter table bar rename to foo;
ALTER TABLE
lucy=# alter table temp rename to bar;
ALTER TABLE
lucy=# commit;
COMMIT

#12123 is a special case of the same issue. Of course, this also applies to other things in the catalog (databases, schemas, types).

The problem is that we prohibit multiple names from referring to the same descriptor at the same time, and this is enforced through the draining names system. Can we lift this restriction?

---

Epic CRDB-10483

[blathers-crl]

Hi @lucy-zhang, please add a C-ategory label to your issue. Check out the label system docs.

While you're here, please consider adding an A- label to help keep our repository tidy.

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

[ajwerner]

Fixing this represents a trade off. Because of that way that cockroach's online schema change protocol works, if we allowed this, there would be some period of time where the name referred to both tables. Maybe that's acceptable.

@vy-ton this is an interesting case to think about regarding transactional schema changes. I think we will choose to make this trade off because the alternative is not supporting something we know that users do -- and in most cases the semantics are pretty sound. I think this is an example of being too cautious.

[vy-ton]

there would be some period of time where the name referred to both tables

There is some danger if the application code does not correctly handle object renames. Might worth revisiting this discussion about name aliasing.

[ajwerner]

I'm coming around to the idea that we should just let this anomaly happen. The compatibility burden is too high. There are fancy things we could consider one day doing to make it so that during a small window of time, transaction transactionally read the descriptor or something but that seems like an also bad tradeoff. Another big consideration for me is that we're going to need to do something about TRUNCATE. In a transactional world, the transaction is going to want to see an empty table after TRUNCATE. That means that it's going to write to the new indexes. If we want to remain offline, there will be a period of time where transactions which follow that transaction (though not the return of COMMIT) will see the old, pre-truncated data even though they may see writes to other tables by transactions which saw the post-truncated data. That's sort of unavoidable.

I say we boldly opt into this class of anomaly and just call it correct by our definition. I think this all remains pretty cogent. The claim is that operations which are concurrent with one of these schema changes (i.e. before COMMIT returns) then they might see some arbitrary interleaving of the versions. If people are mad about this then later we can add some barrier mode as an add-on that you opt into. I think most people will be very happy with this.