cockroachdb / cockroachdb/cockroach

sql: an example of our schema changer does not implement transactional ddls correctly

Open
#110,043 2 comments 2 reactions 0 assignees View on GitHub
A-schema-transactional C-bug T-sql-foundations
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

We have a table like this
```
create table t (i int);
insert into t values (0);
set sql_safe_updates = false;
```

If we then perform the following
```
begin;
alter table t add column j int as (i) stored;
alter table t alter column j drop stored;
commit;
```
which supposedly should add a new column `j` with its value copied from column `i`. But selecting the table gives us
```
select * from t;

i | j
--------
0 | NULL
(1 row)

Time: 2ms total (execution 2ms / network 0ms)
```

The reason that we see a NULL value on column `j` is because of how we process DDLs in transactions -- there is work we do in the user transactions and work we do in the job. In this case, in this particular case, `ADD COLUMN j` and `DROP STORED` will produce a mutation of ADD COLUMN with no computed value, and therefore in the job phase when we backfill column `j`, it's filled with NULL.

It might not be easy for us to correct the behavior for this case (this showcases the difficulty of implementing transactional DDLs). Fortunately, as an immediate mitigation, if we were to execute all these ddls separately in their own transactions (i.e. single statement, implicit transaction), it would work as expected.

Jira issue: CRDB-31233

Epic CRDB-24534

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.