ALTER TABLE ADD COLUMN

Why this stage exists

Tables change. A product gains a discount column; a user table gains last_login. The obvious way to add a column is to read every row, add the new value, and write it back. On a table of a hundred million rows that takes hours, holds a transaction open the whole time, and doubles the log. Real databases avoid it, and so will you: ALTER TABLE ADD COLUMN changes the catalog and nothing else.

The stage closes Querying because it needs NULL from this part, as the value a new column reads as by default, and because every reader this part built, from the WHERE to GROUP BY, must see the new column.

That works because of a decision made back in Flexible Storage. Every row has stored its own column_count since varlen-rows, even though, until now, it always equalled the table's. Now it can be smaller.

Old rows, new schema

After alter table t add column active int default 1, the catalog says t has three columns, while every row on disk still says it has two. The reader reconciles them: it decodes the columns the row has, and gives every column after them its default.

The bytes stay as they are until something writes the row. An UPDATE decodes it, defaults included, and writes it back with every column the table has, so the row grows to full width in place. Rows that are never updated stay short forever, and that is fine.

DEFAULT, in three places

A default is the value a column takes when nothing gives it one. It arrives with ALTER because ALTER needs it most, but the same idea already existed in two other places, and now they agree:

• ADD COLUMN gives the default to every existing row, by the reading rule above.
• CREATE TABLE can declare defaults: create table p (id int, active int not null default 1).
• INSERT gives a column it leaves out the column's default. The nulls stage made that NULL; NULL is simply the default of a column that declares none.

A DEFAULT is checked like a value an INSERT would give the column: an INT takes a number, a VARCHAR(n) a string of at most n bytes. The first column, the key, cannot have one (01104): every row needs its own key.

NOT NULL, and when it cannot be added

alter table t add column x int not null on a table with rows would give every existing row a NULL in a column that promises none. That is 01104. With a DEFAULT, not null default 0, the old rows read 0 and the promise holds. On an empty table there is no row to break the promise, so it is allowed, and every later INSERT must give x a value (01503).

A one-page change, and a transactional one

The test that this stage does no rewrite is the transaction counter from Transactions and Recovery. Inside a transaction, ALTER on a table of 300 wide rows, spread over dozens of leaves, changes exactly one page:

>begin;
Begin statement executed successfully
>alter table w add column x int default 7;
Alter table statement executed successfully
>tx status
[TX] Status: IN_TRANSACTION pages=1
>commit;
Commit statement executed successfully

Because that page is a catalog page like any other, ROLLBACK takes the ALTER back with the rest of the transaction, and the table has its old columns again.

Output contract for this stage

These are the exact lines the tests check. The full definition is on the output contract page, section 50.

alter, add, column and default are keywords. The catalog prints NOT NULL and then DEFAULT <v> after a column's type, the value written as a literal.

What you should see when done

$droid --db shop.db
Welcome to droid!
>select * from items;
id | name
1 | lamp
2 | desk
(2 rows)
[EXEC] rows_examined=2
>alter table items add column stock int not null default 0;
Alter table statement executed successfully
>alter table items add column note varchar(20);
Alter table statement executed successfully
>insert into items (id, name, stock) values (3, 'chair', 5);
Insert statement executed successfully (1 rows)
>update items set stock = 2 where id = 1;
Update statement executed successfully (1 rows)
[EXEC] rows_examined=1
>select * from items;
id | name | stock | note
1 | lamp | 2 | NULL
2 | desk | 0 | NULL
3 | chair | 5 | NULL
(3 rows)
[EXEC] rows_examined=3
>alter table items add column sku int not null;
[ERROR:01104] Cannot add a NOT NULL column without a DEFAULT to a table with rows
>.exit
exiting.. good bye!

Row 2 was never touched: it is still two columns long on disk, and reads 0 and NULL from the catalog. Row 1 was updated, so it is now stored at full width. The messages are yours to word; the tests check only the codes.

Common bugs in this stage

• Rewriting the table. Correct rows, but tx status shows dozens of pages and a large table takes as long as copying it.
• Old rows read past their end. A decoder that loops over the table's columns reads bytes that are not there. Loop over the row's column_count, then fill in.
• Defaults applied only to new rows. select * shows NULL for an old row whose column has DEFAULT 1.
• UPDATE writing the old width. It keeps the row short and loses the value just assigned to the new column.
• INSERT still writing NULL. A left-out column with a DEFAULT must get the default.
• The catalog row written but not rolled back. The catalog is pages; the transaction must own the change like any other.
• NOT NULL checked against the table's size, not its rows. An empty table takes NOT NULL without a default.

Language notes

The page above is the same for every language. These are the only language-specific hints.

• C and C++: keep the default as one of your value structs in the column's catalog entry, parsed once when the catalog is loaded, not on every row.
• Rust: an Option<Value> on each column for its DEFAULT; the decoder can extend the decoded row with the defaults of the remaining columns.
• Zig: store the default on the column struct; decoding a short row is a loop to column_count followed by a copy of the remaining defaults.