Deferrable constraints in Migrations DSL

[Rodrigo_Rosenfeld_R1 (Rodrigo Rosenfeld Rosas)]

Although I don’t use ActiveRecord as an ORM, I do use its migrations framework for evolving my database schema through standalone_migrations.

But I'm finding the current DSL limited with regards to deferrable

constraints support.

Let me demonstrate with a common use-case, like trees

implementation, with SQL written for PostgreSQL:

create table tree(parent_id integer not null, position integer not

null, unique(parent_id, position)); insert into tree(parent_id, position) select generate_series(1, 3), 1; insert into tree(parent_id, position) select generate_series(1, 3), 2;

update tree set position = position + 1;

ERROR:  duplicate key value violates unique constraint

“tree_parent_id_position_key”

In PostgreSQL the constraint check is performed during the UPDATE,

so its success will depend on what order PG will try to update the “position” field.

Since we want PG to defer the constraint check to the end of the

UPDATE statement (it is also possible to specify constraints to be check on transaction commit, but I’ll keep this short) we must create this “tree” table in PG like this:

create table tree(parent_id integer not null, position integer not

null, unique(parent_id, position) deferrable);

But there is no DSL support in migrations to tell the database to

use a deferrable constraint when supported by the database.

Also, I believe that deferrable should be the default and one should

specify “deferrable: false” if this is not desired in case add_index would support a “deferrable” option. In such case, it should generate a statement like:

alter table :table_name add constraint :constraint_name unique

(parent_id, position) deferrable

Another option would be to introduce some "add_constraint" DSL to

Migrations. Anyway, there should be some way of stating this deferrable information in schema.rb.

Each approach would be fine to me as long as I don't need to do it

myself using PG-specific SQL with “execute” method.

Thanks for considering,

Rodrigo.

[Gary_Weaver]

afaik using foreigner is still the recommended way to handle fkeys in Rails. Here is the pull request in foreigner where they discuss deferrable a few years ago: https://github.com/matthuhiggins/foreigner/pull/32

Using Foreigner v0.9.1 or later and using structure.sql vs. schema.rb then can specify :options in foreigner’s add_foreign_key, e.g.

add_foreign_key(:employees, :companies, :options => ‘deferrable’)

Related rails ticket which had no follow through, mostly related to deferred use in fixtures: https://github.com/rails/rails/issues/5523

Unrelated, but for those interested, sequel just added support for deferrable constraints in pg: https://github.com/jeremyevans/sequel/pull/583

Hope that helps.

[Gary_Weaver]

For the create_table though, I’m not sure there is a way to specify options that go inside the parenthesis.

For example this fails because it puts deferrable on the outside of the parenths:

create_table(:test3, :options => 'deferrable') do |t|
  t.string :some_id
end

$ rake db:migrate == TestMigration: migrating ================================================== – create_table(:test3, {:options=>“deferrable”}) rake aborted! An error has occurred, this and all later migrations canceled:

PG::Error: ERROR: syntax error at or near “deferrable” LINE 1: …al primary key, “some_id” character varying(255)) deferrable

Maybe there should be a concept like column_options: ‘deferrable’

create_table(:test3, :column_options => 'deferrable') do |t|
  t.string :some_id
end

?

[Gary_Weaver]

So proposal would be in activerecord/lib/active_record/connection_adapters/abstract/schema_statements.rb

You would add :column_options support, like:

  def create_table(table_name, options = {})
    td = table_definition
    td.primary_key(options[:primary_key] || Base.get_primary_key(table_name.to_s.singularize)) unless options[:id] == false

    yield td if block_given?

    if options[:force] && table_exists?(table_name)
      drop_table(table_name, options)
    end

    create_sql = "CREATE#{' TEMPORARY' if options[:temporary]} TABLE "
    create_sql << "#{quote_table_name(table_name)} ("
    create_sql << td.to_sql
    create_sql << "#{options[:column_options]}"
    create_sql << ") #{options[:options]}"
    execute create_sql
    td.indexes.each_pair { |c,o| add_index table_name, c, o }
  end

Not sure if would want to add similar option in other places to be consistent, though…

[Gary_Weaver]

Forgot the preceding space, so that line should really be:

    create_sql << " #{options[:column_options]}" if options.has_key?(:column_options)

That way the outputted sql will be same as was before, even if not specifying that option.

[Gary_Weaver]

Changed it to table_options because otherwise could conflict with create_join_table which already used column_options.

Pull request: https://github.com/rails/rails/pull/8353

[Jeremy_Evans]

DEFERRABLE is a per-constraint setting, not a per-table setting, so the idea of using :column_options as a per-table option makes no sense. It needs to be added as a per constraint setting if it's added at all. I didn't think ActiveRecord supported creating constraints at all, other than unique constraints implicitly created via unique indexes (and I don't think you can mark those as deferrable using the CREATE UNIQUE INDEX syntax, at least in PostgreSQL), so talking about supporting deferrable constraints when you don't support constraints at all puts the cart before the horse.

Jeremy

[Gary_Weaver]

Jeremy,

I looked at the postgres documentation and it looked like it is both column level and table level, but maybe I misread it- could you look?

CREATE TABLE

[Jeremy_Evans]

It's neither column-level nor table-level, it's constraint-level. Tables can have multiple constraints, each with different DEFERRABLE values, and those constraints are not necessarily tied to specific columns.

Jeremy

[rafaelfranca (Rafael França)]

I don’t think this is a good fit for the core.

You are able to use SQL statements inside your migrations, so we already support this.

[Gary_Weaver]

Jeremy, you’re right- my example was incorrect.

So, I’m unsure whether we should allow the user in the migration to specify additional sql for the constraints like this:

create_table(:foos, table_options: "FOREIGN KEY(column2) REFERENCES table1(column1) DEFERRABLE, FOREIGN KEY(column3) REFERENCES table2(column2) DEFERRABLE, ")

since people should maybe be doing that through foreigner. Should my patch gun be holstered?

[Gary_Weaver]

Was thinking this was something that might allow easier specification of DEFERRABLE in table creation, but I misread. I agree that having yet another way to specify custom SQL for constraints is not a good idea.

[Rodrigo_Rosenfeld_R1 (Rodrigo Rosenfeld Rosas)]

I think I’m late at the party and lost a lot happening here yesterday…

At least it is good to know what core considers a good fit or not

for AR Migrations. Since constraints are a fundamental piece in my projects I get the warning “you should avoid AR at all, including AR migrations”. Currently it is not a big deal for me as I don’t plan to support any other database vendor than PG so I can keep using “execute” in my migrations but I thought that maybe some OSS projects could benefit from some built-in support in AR migrations when using a popular plugin like this one:

https://github.com/collectiveidea/awesome_nested_set/blob/master/lib/awesome_nested_set/awesome_nested_set.rb#L655

I don't really believe the method above will always work in PG, for

example, if some OSS project has created an index like:

add_index :some_table, [:parent_id, :lft], unique: true
add_index :some_table, [:parent_id, :rgt], unique: true

The Rails community still doesn't seem to be mature enough yet with

regards to database constraints, which is unfortunate, so I believe most people using awesome_nested_set (I never used, just took some popular gem as an example) don’t have the indices above in their migration. And if it happens for an OSS project to have an unique index like above it is likely that it won’t always work in PG unless the OSS project uses a custom “add_unique_index” that would be portable among database vendors to include the deferrable constraint instead for databases like PG.

I just suggested that unique constraints/indices should be

deferrable by default to avoid the problem above, which seems to be a very common use-case. Otherwise the message that comes to me is “if you’re not doing MySql, get away from AR. we just pretend to support PG, but if you need first-class support, you should be really considering a more robust ORM, like Sequel”.

[Gary_Weaver]

Rodrigo,

We’re using postgres with structure.sql and a legacy DB that has plenty of constraints (fkey, unique) and in the Rails app using that schema for new migrations we’re using foreigner ( https://github.com/matthuhiggins/foreigner ) to add new fkeys, and iirc using add_index for some unique constraints (a la ruby on rails - A migration to add unique constraint to a combination of columns - Stack Overflow ). I’ve read mixed things about using deferrable for everything, but it might be appropriate in some apps.

AR supports using execute to add/remove constraints, etc. also, and foreigner supports deferrable via specifying new constraints like:

add_foreign_key(:employees, :companies, :options => ‘deferrable’)

Rails create_table I think may be the only place that doesn’t allow custom SQL for constraint definition, and I got schooled on that yesterday when trying to allow a spot for custom SQL within the parenths of the generated SQL for create table.

I agree that Rails could be more constraint-friendly, but I think you can accomplish what you are trying to do with what is there?

Gary

[Rodrigo_Rosenfeld_R1 (Rodrigo Rosenfeld Rosas)]

Rodrigo,

  We're using postgres with structure.sql and a legacy DB that has

plenty of constraints (fkey, unique) and in the Rails app using that schema for new migrations we’re using foreigner ( ) to add new fkeys, and iirc using add_index for some unique constraints (a la ). I’ve read mixed things about using deferrable for everything, but it might be appropriate in some apps. AR supports using execute to add/remove constraints, etc. also, and foreigner supports deferrable via specifying new constraints like: add_foreign_key(:employees, :companies, :options => ‘deferrable’) Rails create_table I think may be the only place that doesn’t allow custom SQL for constraint definition, and I got schooled on that yesterday when trying to allow a spot for custom SQL within the parenths of the generated SQL for create table. I agree that Rails could be more constraint-friendly, but I think you can accomplish what you are trying to do with what is there?

I really don't understand this argument. You could really just get

rid of all DSL in migrations and keep only “execute” with no loss of functionality, right? You could as well just use SQL everywhere in your application instead of relying on any kind of ORM, right?

Here is another common usage example from another popular gem that

would have problem when “add_index [:parent_id, :position], unique:true” is used:

acts_as_list scope: :parent (see meaning here: [https://github.com/swanandp/acts_as_list/blob/master/lib/acts_as_list/active_record/acts/list.rb#L20](https://github.com/swanandp/acts_as_list/blob/master/lib/acts_as_list/active_record/acts/list.rb#L20))

https://github.com/swanandp/acts_as_list/blob/master/lib/acts_as_list/active_record/acts/list.rb#L179 (problematic implementation when add_index is used with unique: true)

I don't understand why AR migrations insists that foreign keys are

not much important to be included by default. There have always been external gems to add support DSL to foreign keys but some of them are no longer maintained after a while. This is the main problem in my opinion. You rely on another gem, like foreigner, and then some years later when a new AR is released at some point you don’t get more support from that gem. I don’t recall the name right now but there was a quite popular gem in Rails 1 era to add foreign keys constraints to AR migrations DSL that is no longer maintained.

If the Rails community can't convince itself about the importance of

basic things in ACID databases like transactions, foreign keys and other constraints than I think I’m out of luck with regards to deferrable constraints… (yes, when I talk about transactions I mean the huge amount of Rails applications out there running MySql with MyISAM engine, that used to be the default one until recently in 5.5 when the InnoDB is now the default one).

When developing open-source applications (like Redmine, Gitorious,

etc) you usually want to support as many database vendors as possible. So, what happens if some application like this uses your proposed solution?

add_foreign_key(:employees, :companies, :options => 'deferrable')

What is the effect of the code above in such scenario if a user

wants to run the code on MySql or MS SQL Server?

[Gary_Weaver]

add_foreign_key(:employees, :companies, :options => ‘deferrable’)

What is the effect of the code above in such scenario if a user

wants to run the code on MySql or MS SQL Server?

Good point. You should bring this up as a foreigner issue and do a pull request to add common options to its adapters like:

matthuhiggins/foreigner/blob/master/lib/foreigner/connection_adapters/postgresql_adapter.rb

and oracle and whatever else supports similar options.

However, I don’t think you always would want to specify deferrable, and if you specify a deferrable option, and it isn’t supported then should it just ignore it? Then to be DB agnostic you’d have to include all relevant options for each DB, and maybe some share the same name that you wouldn’t want to use for others, so then you’d end up with something like (maybe):

add_foreign_key(:employees, :companies, :pg_specific_options => [:deferrable, :some_other_pg_option], :oracle_specific_options => [:some_oracle_option])

but that isn’t much different than putting logic in the migration like:

case ActiveRecord::Base.connection.adapter_name
when 'PostgreSQL', 'Oracle'
  add_foreign_key(:employees, :companies, :options => 'deferrable')
when 'MySQL'
else
  raise "Database adapter not supported. Please file issue at (url to your github repo issues)"

end

but, I still see your point. It would be nice for sure to not have to worry about it.

[Rodrigo_Rosenfeld_R1 (Rodrigo Rosenfeld Rosas)]

Sorry, but I’ve just became aware of this video and didn’t resist posting it here I’m hoping Rails core members that still use MySQL could open their minds after watching this video:

Why Not MySQL?

http://www.youtube.com/watch?v=1PoFIohBSM4

[rafaelfranca (Rafael França)]

Rodrigo, sorry but I think you misunderstood. I don’t use MySQL, actually I even don’t like it. I prefer to use PostgreSQL. If you take 10 minutes you can see a lot of pull requests adding features for PostgreSQL in Rails.

What I said is that I don’t think the feature as it was implemented is a good fit for core. I’ve been using database constraints but I’ve always used SQL to create them.

Also I don’t like to discuss features without seeing the code. I need to see how the feature was implemented to say if I would accept or not. It don’t need to be a full patch but something to, at least, make explicit what are the benefits and the drawbacks of adding a feature to the framework.

We should always look after the cost of maintainability. Add a new feature to Rails is as easy as pressing a green button. Discuss it is even easier. Maintain it is not. I prefer to put into Rails features that I want to maintain in the future and I believe that are good for the framework. This is how Rails work since the beginning.

I’m not saying that I don’t believe in your proposed feature, neither that I don’t want constraints in the framework. But, without seeing the code I can’t discuss anything.

That said, lets see that patch. At least, if it is not accepted, you can easily create a plugin that you can maintain and don’t need to worry if it will break in the next Rails release.

[Rodrigo_Rosenfeld_R1 (Rodrigo Rosenfeld Rosas)]

Well, then we have a real problem here. I don’t start coding anything before discussing them. I consider it a waste of time (in the case the feature has been rejected).

So I don't think how I could ever contribute to Rails. I won't ever

write a patch before getting it accepted first. I’ve done it once after some previous discussion and after the issue became totally abandoned with no feedback I decided that I wouldn’t ever do it again. Too much effort to code to get it rejected or ignored later.

I don't understand why code is needed to discuss a feature or a new

API. Some people have a hard work trying to get RSpec to read as plain English but if you try to spec your requested API in plain English, since it is not code, people won’t even consider it.

Just pretend my feature requests are Cucumber features ;)

[Steve_Klabnik1 (Steve Klabnik)]

Things only happen in Rails if someone implements them. You have to convince someone to actually do the implementation. It's simplest if that person is yourself. If nobody else cares enough to implement it, well, it's not gonna get done.