sql: alter column type in transaction not supported #49351
Description
With the current schema changer infrastructure, it's difficult to support alter column type in transactions. This is because the computed column swap doesn't actually occur until the transaction is committed. Thus any references to the column being altered will be done on the original column and not the column after the swap.
Here is an example with adding a constraint to an altered column
root@127.0.0.1:61263/movr> ALTER TABLE t ADD CONSTRAINT x_unique UNIQUE (x);
root@127.0.0.1:61263/movr> BEGIN;
ALTER TABLE t ALTER COLUMN x TYPE STRING;
ALTER TABLE t ADD COLUMN y INT;
ALTER TABLE t ADD COLUMN z INT;
ALTER TABLE t ADD CONSTRAINT x_unique UNIQUE (x);
BEGIN
Time: 101µs
NOTICE: ALTER COLUMN TYPE changes are finalized asynchronously; further schema changes on this table may be restricted until the job completes; some writes to the altered column may be rejected until the schema change is finalized
ALTER TABLE
Time: 5.336ms
ALTER TABLE
Time: 9.576ms
ALTER TABLE
Time: 4.358ms
ALTER TABLE
Time: 3.898ms
root@127.0.0.1:61263/movr OPEN> commit;
ERROR: transaction committed but schema change aborted with error: (XXUUU): verify-idx-count: column-id "1" does not exist
SQLSTATE: XXA00
Allowing alter column type in transactions will likely depend on supporting transactional schema changes in general.
Similarly, ALTER COLUMN TYPE cannot be used in combination with other ALTER TABLE statements since this runs the statements inside a transaction.
Example:
ALTER TABLE t ALTER COLUMN x TYPE STRING, ALTER COLUMN x SET NOT NULL;
Jira issue: CRDB-4235
Epic CRDB-44826