• 
      

    Optimize adding NOT NULL fields with initial values.

    Review Request #5176 — Created Dec. 29, 2013 and submitted

    Information

    Django Evolution
    master

    Reviewers

    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 NULL

    The 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.