sql: cannot add a new sequence-populated column (or pg-compatible SERIAL) to an existing table, + bug #42508
Description
Two problems in this issue
- a known limitation: cannot add such columns
- a bug: the error is only detected at run-time, should be detected at compile-time.
Because of the bug part, the severity is higher than if this was just a missing feature.
The limitation
Today the column backfiller is unable to execute sequence operations such as evaluating the nextval() function.
This makes it impossible to add a column that uses a sequence-based DEFAULT to an existing table.
For example:
> create table t(x int); insert into t(x) values (1), (2), (3);
> create sequence s;
> alter table t add column y int default nextval('s');
pq: nextval(): cannot backfill such sequence operationOr:
> set experimental_serial_normalization=sql_sequence; -- for pg compat
> alter table t add column y serial;
pq: nextval(): cannot backfill such sequence operationThe bug
A large problem with this limitation is that the error is only detected at runtime, not at compile-time. This means that an attempt to add such a column inside an explicit txn will violate atomicity with the dreaded XXA00 error (see #42061)
> begin;
insert into t(x) values (4);
alter table t add column y int default nextval('d');
commit;
pq: transaction committed but schema change aborted with error: (42P15): nextval(): cannot backfill such sequence operation
HINT: Some of the non-DDL statements may have committed successfully, but some of the DDL statement(s) failed.
Manual inspection may be required to determine the actual state of the database.
--
See: https://github.com/cockroachdb/cockroach/issues/42061
> select * from t;
x
+---+
1
2
3
4
(4 rows)(the INSERT was committed, but the ALTER failed: atomicity anomaly)
Jira issue: CRDB-5351
Epic CRDB-60873