README.md in strong_migrations-0.8.0 vs README.md in strong_migrations-1.0.0

- old
+ new

@@ -2,11 +2,11 @@ Catch unsafe migrations in development &nbsp;&nbsp;✓&nbsp;&nbsp;Detects potentially dangerous operations<br />&nbsp;&nbsp;✓&nbsp;&nbsp;Prevents them from running by default<br />&nbsp;&nbsp;✓&nbsp;&nbsp;Provides instructions on safer ways to do what you want -Supports for PostgreSQL, MySQL, and MariaDB +Supports PostgreSQL, MySQL, and MariaDB :tangerine: Battle-tested at [Instacart](https://www.instacart.com/opensource) [![Build Status](https://github.com/ankane/strong_migrations/workflows/build/badge.svg?branch=master)](https://github.com/ankane/strong_migrations/actions) @@ -65,19 +65,19 @@ - [changing the type of a column](#changing-the-type-of-a-column) - [renaming a column](#renaming-a-column) - [renaming a table](#renaming-a-table) - [creating a table with the force option](#creating-a-table-with-the-force-option) - [adding a check constraint](#adding-a-check-constraint) -- [setting NOT NULL on an existing column](#setting-not-null-on-an-existing-column) - [executing SQL directly](#executing-SQL-directly) Postgres-specific checks: - [adding an index non-concurrently](#adding-an-index-non-concurrently) - [adding a reference](#adding-a-reference) - [adding a foreign key](#adding-a-foreign-key) - [adding a json column](#adding-a-json-column) +- [setting NOT NULL on an existing column](#setting-not-null-on-an-existing-column) Best practices: - [keeping non-unique indexes to three columns or less](#keeping-non-unique-indexes-to-three-columns-or-less) @@ -202,23 +202,30 @@ change_column :users, :some_column, :new_type end end ``` -A few changes don’t require a table rewrite (and are safe) in Postgres: +Some changes don’t require a table rewrite and are safe in Postgres: -- Increasing the length limit of a `varchar` column (or removing the limit) -- Changing a `varchar` column to a `text` column -- Changing a `text` column to a `varchar` column with no length limit -- Increasing the precision of a `decimal` or `numeric` column -- Making a `decimal` or `numeric` column unconstrained -- Changing between `timestamp` and `timestamptz` columns when session time zone is UTC in Postgres 12+ +Type | Safe Changes +--- | --- +`cidr` | Changing to `inet` +`citext` | Changing to `text` if not indexed, changing to `string` with no `:limit` if not indexed +`datetime` | Increasing or removing `:precision`, changing to `timestamptz` when session time zone is UTC in Postgres 12+ +`decimal` | Increasing `:precision` at same `:scale`, removing `:precision` and `:scale` +`interval` | Increasing or removing `:precision` +`numeric` | Increasing `:precision` at same `:scale`, removing `:precision` and `:scale` +`string` | Increasing or removing `:limit`, changing to `text`, changing `citext` if not indexed +`text` | Changing to `string` with no `:limit`, changing to `citext` if not indexed +`time` | Increasing or removing `:precision` +`timestamptz` | Increasing or removing `:limit`, changing to `datetime` when session time zone is UTC in Postgres 12+ -And a few in MySQL and MariaDB: +And some in MySQL and MariaDB: -- Increasing the length limit of a `varchar` column from under 255 up to 255 -- Increasing the length limit of a `varchar` column from over 255 to the max limit +Type | Safe Changes +--- | --- +`string` | Increasing `:limit` from under 255 up to 255, increasing `:limit` from over 255 to the max #### Good A safer approach is to: @@ -351,90 +358,10 @@ #### Good - MySQL and MariaDB [Let us know](https://github.com/ankane/strong_migrations/issues/new) if you have a safe way to do this (check constraints can be added with `NOT ENFORCED`, but enforcing blocks writes). -### Setting NOT NULL on an existing column - -:turtle: Safe by default available - -#### Bad - -Setting `NOT NULL` on an existing column blocks reads and writes while every row is checked. - -```ruby -class SetSomeColumnNotNull < ActiveRecord::Migration[7.0] - def change - change_column_null :users, :some_column, false - end -end -``` - -#### Good - Postgres - -Instead, add a check constraint. - -For Rails 6.1, use: - -```ruby -class SetSomeColumnNotNull < ActiveRecord::Migration[7.0] - def change - add_check_constraint :users, "some_column IS NOT NULL", name: "users_some_column_null", validate: false - end -end -``` - -For Rails < 6.1, use: - -```ruby -class SetSomeColumnNotNull < ActiveRecord::Migration[6.0] - def change - safety_assured do - execute 'ALTER TABLE "users" ADD CONSTRAINT "users_some_column_null" CHECK ("some_column" IS NOT NULL) NOT VALID' - end - end -end -``` - -Then validate it in a separate migration. A `NOT NULL` check constraint is [functionally equivalent](https://medium.com/doctolib/adding-a-not-null-constraint-on-pg-faster-with-minimal-locking-38b2c00c4d1c) to setting `NOT NULL` on the column (but it won’t show up in `schema.rb` in Rails < 6.1). In Postgres 12+, once the check constraint is validated, you can safely set `NOT NULL` on the column and drop the check constraint. - -For Rails 6.1, use: - -```ruby -class ValidateSomeColumnNotNull < ActiveRecord::Migration[7.0] - def change - validate_check_constraint :users, name: "users_some_column_null" - - # in Postgres 12+, you can then safely set NOT NULL on the column - change_column_null :users, :some_column, false - remove_check_constraint :users, name: "users_some_column_null" - end -end -``` - -For Rails < 6.1, use: - -```ruby -class ValidateSomeColumnNotNull < ActiveRecord::Migration[6.0] - def change - safety_assured do - execute 'ALTER TABLE "users" VALIDATE CONSTRAINT "users_some_column_null"' - end - - # in Postgres 12+, you can then safely set NOT NULL on the column - change_column_null :users, :some_column, false - safety_assured do - execute 'ALTER TABLE "users" DROP CONSTRAINT "users_some_column_null"' - end - end -end -``` - -#### Good - MySQL and MariaDB - -[Let us know](https://github.com/ankane/strong_migrations/issues/new) if you have a safe way to do this. - ### Executing SQL directly Strong Migrations can’t ensure safety for raw SQL statements. Make really sure that what you’re doing is safe, then use: ```ruby @@ -585,10 +512,86 @@ add_column :users, :properties, :jsonb end end ``` +### Setting NOT NULL on an existing column + +:turtle: Safe by default available + +#### Bad + +In Postgres, setting `NOT NULL` on an existing column blocks reads and writes while every row is checked. + +```ruby +class SetSomeColumnNotNull < ActiveRecord::Migration[7.0] + def change + change_column_null :users, :some_column, false + end +end +``` + +#### Good + +Instead, add a check constraint. + +For Rails 6.1, use: + +```ruby +class SetSomeColumnNotNull < ActiveRecord::Migration[7.0] + def change + add_check_constraint :users, "some_column IS NOT NULL", name: "users_some_column_null", validate: false + end +end +``` + +For Rails < 6.1, use: + +```ruby +class SetSomeColumnNotNull < ActiveRecord::Migration[6.0] + def change + safety_assured do + execute 'ALTER TABLE "users" ADD CONSTRAINT "users_some_column_null" CHECK ("some_column" IS NOT NULL) NOT VALID' + end + end +end +``` + +Then validate it in a separate migration. A `NOT NULL` check constraint is [functionally equivalent](https://medium.com/doctolib/adding-a-not-null-constraint-on-pg-faster-with-minimal-locking-38b2c00c4d1c) to setting `NOT NULL` on the column (but it won’t show up in `schema.rb` in Rails < 6.1). In Postgres 12+, once the check constraint is validated, you can safely set `NOT NULL` on the column and drop the check constraint. + +For Rails 6.1, use: + +```ruby +class ValidateSomeColumnNotNull < ActiveRecord::Migration[7.0] + def change + validate_check_constraint :users, name: "users_some_column_null" + + # in Postgres 12+, you can then safely set NOT NULL on the column + change_column_null :users, :some_column, false + remove_check_constraint :users, name: "users_some_column_null" + end +end +``` + +For Rails < 6.1, use: + +```ruby +class ValidateSomeColumnNotNull < ActiveRecord::Migration[6.0] + def change + safety_assured do + execute 'ALTER TABLE "users" VALIDATE CONSTRAINT "users_some_column_null"' + end + + # in Postgres 12+, you can then safely set NOT NULL on the column + change_column_null :users, :some_column, false + safety_assured do + execute 'ALTER TABLE "users" DROP CONSTRAINT "users_some_column_null"' + end + end +end +``` + ### Keeping non-unique indexes to three columns or less #### Bad Adding a non-unique index with more than three columns rarely improves performance. @@ -694,11 +697,11 @@ ```ruby StrongMigrations.error_messages[:add_column_default] = "Your custom instructions" ``` -Check the [source code](https://github.com/ankane/strong_migrations/blob/master/lib/strong_migrations.rb) for the list of keys. +Check the [source code](https://github.com/ankane/strong_migrations/blob/master/lib/strong_migrations/error_messages.rb) for the list of keys. ## Migration Timeouts It’s extremely important to set a short lock timeout for migrations. This way, if a migration can’t acquire a lock in a timely manner, other statements won’t be stuck behind it. We also recommend setting a long statement timeout so migrations can run for a while. @@ -715,9 +718,28 @@ ALTER ROLE myuser SET lock_timeout = '10s'; ALTER ROLE myuser SET statement_timeout = '1h'; ``` Note: If you use PgBouncer in transaction mode, you must set timeouts on the database user. + +## Lock Timeout Retries [experimental] + +There’s the option to automatically retry statements when the lock timeout is reached. Here’s how it works: + +- If a lock timeout happens outside a transaction, the statement is retried +- If it happens inside the DDL transaction, the entire migration is retried (only applicable to Postgres) + +Add to `config/initializers/strong_migrations.rb`: + +```ruby +StrongMigrations.lock_timeout_retries = 3 +``` + +Set the delay between retries with: + +```ruby +StrongMigrations.lock_timeout_retry_delay = 10.seconds +``` ## App Timeouts We recommend adding timeouts to `config/database.yml` to prevent connections from hanging and individual queries from taking up too many resources in controllers, jobs, the Rails console, and other places.