sql: creating a table and adding an index within a single transaction produces problems

[BramGruneir]

The following has a number of issues and affects 19.1.x as well as master.

BEGIN;
CREATE TABLE test (
    id                 UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    segment_key        UUID
);
CREATE INDEX idx ON test (segment_key);
COMMIT;

There are a number of issues here:

1. if you delay a bit between issuing the create table and create index, the transaction will fail and require a retry.

root@:26257/defaultdb  OPEN> commit;
pq: restart transaction: TransactionRetryWithProtoRefreshError: TransactionRetryError: retry txn (RETRY_SERIALIZABLE): id=3754c52d key=/Table/SystemConfigSpan/Start rw=true pri=0.00095111 stat=PENDING epo=0 ts=1565796278.946017000,1 orig=1565796271.134457000,0 min=1565796271.134457000,0 max=1565796271.134457000,0 wto=false seq=17

2. If the full transaction is committed, it will leave a job in a pending state and never complete it.

3. Subsequent schema changes to the table, may not take effect, even if they show up as passing.
   This is the problem that flyway migrations keep experiencing. Columns not being added is the most common.

[BramGruneir]

Assigning @dt for triage.
cc @awoods187

[awoods187]

This impacted our support for Flyway. We were able to enable Flyway to work for a customer if they separated schema changes into separate files as a temporary workaround.

[BramGruneir]

That being said, flyway itself initializes itself and does exactly this:

So it will always be in some sort of error state.

[RoachietheSupportRoach]

Zendesk ticket #3516 has been linked to this issue.

[adityamaru27]

Following are my findings after debugging (and discussing with @dt ):
• The "pending" jobs showing up for schema changes in the same txn as the one in which the table was created, is a problem which I'll send a fix out for tomorrow. In terms of subsequent schema changes failing it was a red herring.

• The problem lies in the fact that the flyway scripts attempt to update a column in the same txn as it was added. This is a fundamental restriction in the way schema changes are setup. I traced down the error column * does not exist to the optimizer, when it is attempting to plan the UPDATE statement. The reason for this error is that the new column has not been added to the table desc (it is still queued up in the descs mutation list), as the schema changer only runs after the txn has committed. So the planning of UPDATE fails. Similar behavior can be seen in the logic test - https://github.com/cockroachdb/cockroach/blob/master/pkg/sql/logictest/testdata/logic_test/update#L462
and so while not ideal, this is the correct/expected behavior.

• I haven't debugged the txn retrying issue mentioned above yet.

[dt]

I think the bug here was fixed in #39819. What is left is a known-limitation of the SQL txn/schema-change interaction so I think this particular ticket can be closed now.