Migration helper `add_check_constraint` forbids use of transactions
The migration helper add_check_constraint
fails if run inside a transaction.
This is also being used for add_text_limit
which in turn often needs to be used with new tables we create. Example migration then look like so:
disable_ddl_transaction!
def up
unless table_exists?(:background_migration_jobs)
create_table :background_migration_jobs do |t|
t.timestamps_with_timezone
t.integer :status, null: false, limit: 2, default: 0
t.text :class_name, null: false
t.jsonb :arguments, null: false
t.index [:class_name, :arguments]
t.index [:class_name, :status, :id]
end
end
add_text_limit :background_migration_jobs, :class_name, 200
end
(from !35913 (merged))
or another example is from here: !35526 (diffs)
disable_ddl_transaction!
def up
unless table_exists?(:analytics_cycle_analytics_group_value_streams)
with_lock_retries do
create_table :analytics_cycle_analytics_group_value_streams do |t|
t.timestamps_with_timezone
t.references(:group, {
null: false,
index: false,
foreign_key: { to_table: :namespaces, on_delete: :cascade }
})
t.text :name, null: false
end
end
end
add_index :analytics_cycle_analytics_group_value_streams, [:group_id, :name], unique: true, name: INDEX_NAME
add_text_limit :analytics_cycle_analytics_group_value_streams, :name, 255
end
This is problematic because disabling transactions renders those migrations non-atomic. That in turn means, we'll have to be prepared to retry the full migration, even though some steps have already been committed on a previous attempt.
In the latter example, add_index
would also need a guard clause, so it doesn't fail if the index already exists.
This makes it less convenient to use the migration helper and it makes it more difficult to reason about the state of a migration.
In these example, we just created that table, so adding a constraint to it is a quick operation anyways (it's empty).
From a SQL perspective, constraints can even get mixed into the CREATE TABLE
statement:
create table test (id serial primary key, name text, CHECK(LENGTH(name) < 200), UNIQUE (name));
This issue is about finding a more convenient solution to add (text) constraints, particularly for newly created tables, so that we don't need to disable transactions and introduce guard clauses anymore.