sql: Can't replace a table with a view in a transaction (SQL docs)

[mbertschler]

I followed the docs at this document and tried running this SQL queries:
https://www.cockroachlabs.com/docs/stable/views.html#simplify-supporting-legacy-code

I expected that the query would run, but it complained that the relation still exists:

root@:26257/> 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.
You can also use \show to display the statements entered so far.
               -> ALTER TABLE bank.user_accounts RENAME TO bank.client_accounts;
               -> CREATE VIEW bank.user_accounts
               ->   AS SELECT type, email
               ->   FROM bank.client_accounts;
               -> COMMIT;
pq: relation "user_accounts" already exists

Outside of a transaction these statements work.

Full SQL to reproduce:

-- setup
CREATE DATABASE bank;
CREATE TABLE bank.user_accounts (email STRING PRIMARY KEY, type STRING);

-- transaction with error
BEGIN;
ALTER TABLE bank.user_accounts RENAME TO bank.client_accounts;
CREATE VIEW bank.user_accounts
  AS SELECT type, email
  FROM bank.client_accounts;
COMMIT;

Version:

[config] binary: CockroachDB CCL v1.1.5 (darwin amd64, built 2018/02/05 18:22:21, go1.9.3)
[config] arguments: [cockroach start --insecure --host=localhost]

Jira issue: CRDB-5837

[vivekmenezes]

similar to #12123

[knz]

@vivekmenezes what do you think about allowing the CREATE to proceed but using some internally defined table name that is guaranteed to be unique. Then perform the rename of the new table to is final name when the txn commits, during the schema change?

[knz]

This would also solve #12123.

[vivekmenezes]

This is discussed in https://github.com/cockroachdb/cockroach/blob/master/pkg/sql/sqlbase/structured.proto
in the comment above NameInfo (see worry 3).

The particular problem with your proposal is that it fails when the CREATE statement is followed by an INSERT statement in the same transaction.

[knz]

Within the same transaction the correct name would be known to the TableCollection (uncommittedTables). The INSERT would see the correct name from there. I think it would work?

[vivekmenezes]

Yeah, it's a lot more complex though because we have to choose what gets done with transaction with timestamp T and what gets done with transaction with timestamp S. There are two transactions at work here. And once that choice is made it requires some documentation work so users know what to expect. Unfortunately this is not looking like it can be done "transactionally" as in it support serializability

[jordanlewis]

This is solved. 🎉