Optimize adding NOT NULL fields with initial values.
Review Request #5176 — Created Dec. 29, 2013 and submitted
Optimize adding NOT NULL fields with initial values.
Previously, adding a field with NOT NULL and an initial value (to
update existing table entries) on MySQL or PostgreSQL required three
ALTER TABLE statements:1) One to add the column (as NULL, with the DEFAULT, in order to
update the existing entries)
2) One to drop the default
3) One to set the column to NOT NULLThe first and third resulted in a table rebuild (at least on MySQL). The
second, fortunately, doesn't. Still, this meant that many table adds
would cause MySQL to do a dump of the table, a rebuild, and a load of
the table data, twice per added field.We now combine the first and third ALTER TABLE. We add the field as
NOT NULL with the DEFAULT, which will update all existing entries as
well, and then we drop the DEFAULT. There's now only one table rebuild
per added field.
Unit tests pass for all supported databases.
Tested an upgrade from RB 1.7.x to 2.0, which has plenty of these sorts of
updates. It reduced the number of ALTER TABLEs by 7 (these were 7 that would
trigger a table rebuild), and the evolutions passed. Verified in the database
that the schema was correct and that existing entries had the correct
defaults.