Artur-Sulej / excellent_migrations

An Elixir tool for checking safety of database migrations.
MIT License
232 stars 25 forks source link

Detecting column with volatile default #18

Closed Artur-Sulej closed 1 year ago

Artur-Sulej commented 1 year ago

This PR adds a new check column_volatile_default, which detects adding a volatile default to an existing column or adding a new column with a volatile default.

It's also important to differentiate this check from column_added_with_default, because this one is safe from certain db versions and users may want to skip this check with:

config :excellent_migrations, skip_checks: [:column_added_with_default]

From PostgreSQL docs:

From PostgreSQL 11, adding a column with a constant default value no longer means that each row of the table needs to be updated when the ALTER TABLE statement is executed. Instead, the default value will be returned the next time the row is accessed, and applied when the table is rewritten, making the ALTER TABLE very fast even on large tables.

However, if the default value is volatile (e.g., clock_timestamp()) each row will need to be updated with the value calculated at the time ALTER TABLE is executed. To avoid a potentially lengthy update operation, particularly if you intend to fill the column with mostly nondefault values anyway, it may be preferable to add the column with no default, insert the correct values using UPDATE, and then add any desired default as described below.