Documentation for tri state problem for booleans

[sfahlberg]

Hello,
It's not clear to me how to add null: false and default: false to a boolean column using strong_migrations. Using your approach, I would 1) create the column 2) add a default 3) flip everything to false 4) add null: false but when I add try to add the null: false, it complains. How would you recommend handling this?

[ankane]

Hey @sfahlberg, that's the recommended way. With change_column_null, be sure not to pass a 4th argument, which makes that operation dangerous.

[sfahlberg]

@ankane thanks!

[ABewsher]

FYI

I've just run the following on a postgres DB with 7M rows.

class AddUserVisibleToEvents < ActiveRecord::Migration[5.1]
  def up
    add_column :events, :user_visible, :boolean, null: true
    change_column_default :events, :user_visible, false
  end

  def down
    remove_column :events, :user_visible
  end
end

The above was fast. 👍

Then I ran:

class BackfillAddUserVisibleToEvents < ActiveRecord::Migration[5.1]
  disable_ddl_transaction!

  def change
    Event.in_batches.update_all user_visible: true
  end
end

As expected, this took several hours, however it didn't stop the production DB running 👍

Then I ran this:

class AddNullFalseToUserVisibleInEvents < ActiveRecord::Migration[5.1]	
  def change	
    change_column_null(:events, :user_visible, false)	
  end	
end

... and it locked the DB 👎

Note that I have not used the 4th argument but it was still "dangerous".

I removed that migration and then ran:

class AddIndexToUserVisibleInEvents < ActiveRecord::Migration[5.1]
  disable_ddl_transaction!

  def change
    add_index :events, :user_visible, algorithm: :concurrently
  end
end

And it ran for about 15 minutes and did not lock the DB 👍

So my question... now that the index is in place, will change_column_null(:events, :user_visible, false) be faster now (since there are no NULL values!) or is there some other way I can SET NOT NULL on the column?

Thanks