race condition between ADD COLUMN and a subsequent UPDATE

[lopezator]

Describe the problem

I v22.2.X if you execute a ADD COLUMN followed by an UPDATE over that column, the UPDATE stmt fails with column doesn't exist.

To Reproduce

22.1.15 works ✔️

Start container:

docker run --rm --name cockroach-v22.1.15 -p 22115:26257 cockroachdb/cockroach:v22.1.15 start-single-node --insecure

Execute:

cockroach sql --insecure --url="postgresql://root@localhost:22115/defaultdb?sslmode=disable" -e "CREATE TABLE foo(id CHAR(26) PRIMARY KEY);"

cockroach sql --insecure --url="postgresql://root@localhost:22115/defaultdb?sslmode=disable" -e "ALTER TABLE foo ADD COLUMN IF NOT EXISTS bar VARCHAR(200) NULL; UPDATE foo SET bar = '1';"

22.2.5 fails ❌

Start container:

docker run --rm --name cockroach-v22.2.5 -p 2225:26257 cockroachdb/cockroach:v22.2.5 start-single-node --insecure

Execute:

cockroach sql --insecure --url="postgresql://root@localhost:2225/defaultdb?sslmode=disable" -e "CREATE TABLE foo(id CHAR(26) PRIMARY KEY);"

cockroach sql --insecure --url="postgresql://root@localhost:2225/defaultdb?sslmode=disable" -e "ALTER TABLE foo ADD COLUMN IF NOT EXISTS bar VARCHAR(200) NULL; UPDATE foo SET bar = '1';"

Got error:

ALTER TABLE
ERROR: column "bar" does not exist
SQLSTATE: 42703
Failed running "sql"

Expected behavior

The stmt to work as it was working in 22.1.X

Environment:

• CockroachDB version [e.g. 22.2.5]
• Server OS: [e.g. Linux/Ubuntu]
• Client app [e.g. cockroach sql]

Additional context

The development is block on our side, integration tests are failing because of this.

Jira issue: CRDB-24837

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I have CC'd a few people who may be able to assist you:

• @cockroachdb/sql-schema (found keywords: ALTER TABLE)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

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

[ajwerner]

The reason for the change in behavior here is #76834. That change is intended to improve compatibility in other directions. Namely, now, when you issue sql statements in a command batch, they are treated as part of the same transaction. This is what postgres does. So, before 22.2, the schema change and the update were in different transactions.

You can get the old behavior by setting the session variable: SET enable_implicit_transaction_for_batch_statements = false;

As for why cockroach doesn't support writing to a column in the same transaction that adds a column, that's a long-standing issue that has always applied to cockroach. The underlying reason is that it is difficult to both change the schema in a transaction and keep all schema changes online. We've been working towards that goal for years now, and are closer than ever, but the end state remains in the future. The design document here explains how we may achieve that goal.

[ajwerner]

What's New in v22.2 -> Backward-incompatible changes

Changed the default value of the enable_implicit_transaction_for_batch_statements to true. This means that a batch of statements sent in one string separated by semicolons is treated as an implicit transaction. #76834

I'm inclined to close this issue as duplicating #26508.

[ajwerner]

#31463 is another, perhaps better issue tracking the underlying limitation.