docs: simplify-supporting-legacy-code example doesn't work

[vivekmenezes]

https://www.cockroachlabs.com/docs/stable/views.html#simplify-supporting-legacy-code

I suppose you can get rid of the transaction

[jseldess]

I remember this working when I wrote the document, so it'd be worth testing this in 1.0, 1.1, and 2.0.

[rmloveland]

Related: #182

[rmloveland]

The error that occurs is strange, after doing the ALTER TABLE the DB doesn't "see" that the table name is now client_accounts and it barfs during the view creation:

> BEGIN;
BEGIN
root=> ALTER TABLE bank_example.accounts RENAME TO bank_example.client_accounts;
RENAME TABLE
root=> CREATE VIEW bank_example.accounts AS SELECT type, balance, email FROM bank_example.client_accounts;
ERROR:  relation "accounts" already exists
root=> COMMIT;
ROLLBACK

Update: This error occurs on both of the following versions:

> select version();
+---------------------------------------------------------------------------+
|                                 version()                                 |
+---------------------------------------------------------------------------+
| CockroachDB CCL v1.1.6 (darwin amd64, built 2018/03/12 17:55:09, go1.8.3) |
+---------------------------------------------------------------------------+
(1 row)

> select version();
+--------------------------------------------------------------------------------------+
|                                       version                                        |
+--------------------------------------------------------------------------------------+
| CockroachDB CCL v2.0-beta.20180319 (darwin amd64, built 2018/03/19 18:54:14, go1.10) |
+--------------------------------------------------------------------------------------+
(1 row)

[rmloveland]

Here's the code I'm using to generate the results above:

CREATE DATABASE bank_example;

USE bank_example;

CREATE TABLE accounts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  type STRING check (type='checking' or type='savings') NOT NULL,
  balance INT NOT NULL,
  email STRING NOT NULL
);

INSERT INTO accounts (type, balance, email) VALUES (
  'checking',
  1000,
  'max@roach.com'
);

INSERT INTO accounts (type, balance, email) VALUES (
'savings',
10000,
'max@roach.com'
);

INSERT INTO accounts (type, balance, email) VALUES (
'checking',
15000,
'betsy@roach.com'
);

INSERT INTO accounts (type, balance, email) VALUES (
'checking',
5000,
'lilly@roach.com'
);

INSERT INTO accounts (type, balance, email) VALUES (
'savings',
50000,
'ben@roach.com'
);

BEGIN;
ALTER TABLE bank_example.accounts RENAME TO bank_example.client_accounts;
CREATE VIEW bank_example.accounts AS SELECT type, balance, email FROM bank_example.client_accounts;
COMMIT;

All 3 versions I've tried (1.0, 1.1, 2.0) fail with the above code (see previous comment for exact error message).

[jseldess]

Thanks for testing, @rmloveland. Looks like I just got this wrong from the start!

[rmloveland]

According to known limitations:

A table cannot be dropped and then recreated with the same name

@andreimatei pointed me to a comment in this file which says a bunch of complicated things about table descriptors, finishing with:

BEGIN;
RENAME foo TO bar;
CREATE foo;

will fail

He also was kind enough to answer the following:

Me: re: the example in question, if I were to remove the surrounding transaction, my assumption is that the application would break if it was looking for accounts after the ALTER TABLE but before the CREATE VIEW - is that correct?

Andrei: yes. I don't think you can atomically replace a table with another

@jseldess in light of this information, I think we should remove that section since it seems to describe something we don't provide. What do you think?

[rmloveland]

After discussing the above, we have settled on the following:

• Rewrite the code sample to drop the transaction
• Rewrite the surrounding text to note that there may be a brief window after the rename but before the view creation where your application may need to do some error handling (which in theory it is already doing when accessing the DB)

... or something like that. PR will have the details.