Making yaml_db work with foreign key constraints in PostgreSQL

Making yaml_db work with foreign key constraints in PostgreSQL

Foreign key constraints can cause problems for utilities that assume that data can be loaded in any order. Even ordering the tables can be insufficient when there are intra-table foreign keys. One solution for loading data in a situation like this is to defer the constraints until all the data is loaded and then to enforce the constraints. Unfortunately, there are some issues in the current Rails support for this, and many utilities don't know to take advantage of this support.

Here are some recommendations for making yaml_db work with foreign key constraints. I based this on a blog posting I made at http://tovodeverett.wordpress.com/2013/06/07/making-yaml_db-work-with-schema_plus-constraints/.

Mark foreign keys as deferrable

Change all your foreign keys to use deferrable: true. If you want, you can use deferrable: :initially_deferred instead. Here is a sample migration for a single table.

class MakeForeignKeyConstraintsDeferrable < ActiveRecord::Migration
  def up
    change_table :abilities do |t|
      t.change :parent_ability_id,  :integer, foreign_key: nil
    end

    change_table :abilities do |t|
      t.change :parent_ability_id,  :integer, foreign_key: { references: :abilities, deferrable: true }
    end
  end

  def down
    change_table :abilities do |t|
      t.change :parent_ability_id,  :integer, foreign_key: nil
    end

    change_table :abilities do |t|
      t.change :parent_ability_id,  :integer, foreign_key: { references: :abilities }
    end
  end
end

Note how I drop the foreign key using foreign_key: nil in one change_table before I reinstate it in the next – I’m not sure if this is 100% required, but I wanted to be absolutely certain. I used a copy of my schema.rb file to do before and after comparisons and to ensure that backing out the migration resulted in the same schema.rb file. To generate this file (with all 15 foreign keys) I copied the appropriate lines from schema.rb and then did some clever parallel editing in Sublime Text.

Monkey-patch Rails 3.2.13 to support SET CONSTRAINTS ALL DEFERRED:

Add the following monkey-patch somewhere in your code base. I use config/initializers/application.rb.

if Rails::VERSION::MAJOR < 4
  #Fix fixtures with foreign keys, fixed in Rails4
  class ActiveRecord::ConnectionAdapters::PostgreSQLAdapter
    def disable_referential_integrity #:nodoc:
      if supports_disable_referential_integrity? then
        execute(tables.collect { |name| "ALTER TABLE #{quote_table_name(name)} DISABLE TRIGGER USER" }.join(";"))
        transaction do
          execute("SET CONSTRAINTS ALL DEFERRED")
          yield
        end
      else
        yield
      end
    ensure
      if supports_disable_referential_integrity? then
        execute(tables.collect { |name| "ALTER TABLE #{quote_table_name(name)} ENABLE TRIGGER USER" }.join(";"))
      end
    end
  end
end

Note that Rails 4 does not yet support SET CONSTRAINTS ALL DEFERRED. It does have changes that avoid the problem where superuser privs on the database are required to do ALTER TABLE DISABLE TRIGGER ALL, but it doesn't address all the issues. See https://github.com/rails/rails/issues/10885 for more information.

Monkey-patch yaml_db 0.2.3 to take advantage of disable_referential_integrity

Add the following monkey-patch somewhere in your code base. I use config/initializers/application.rb.

module SerializationHelper
  class Base
    def load(filename, truncate = true)
      disable_logger
      ActiveRecord::Base.connection.disable_referential_integrity do
        @loader.load(File.new(filename, "r"), truncate)
      end
      reenable_logger
    end
  end

  class Load
    def self.truncate_table(table)
      begin
        ActiveRecord::Base.connection.execute("SAVEPOINT before_truncation")
        ActiveRecord::Base.connection.execute("TRUNCATE #{SerializationHelper::Utils.quote_table(table)} CASCADE")
      rescue Exception
        ActiveRecord::Base.connection.execute("ROLLBACK TO SAVEPOINT before_truncation")
        ActiveRecord::Base.connection.execute("DELETE FROM #{SerializationHelper::Utils.quote_table(table)}")
      end
    end
  end
end

Note that these patches are PostgreSQL specific and will probably not work against other databases. If the yaml_db community decides they are interested in supporting this functionality, I will work up a pull request that special-cases for PostgreSQL while leaving the normal code paths alone.

The above patch modifies SerializationHelper::Base#load to take advantage of disable_referential_integrity as well as SerializationHelper::Load.truncate_table to address some issues with maintaining the reliability of the rescue clause when operating from within a transaction. See the blog posting mentioned above for more information. The blog posting also has some other patches for yaml_db to ensure consistent database dump ordering as well as reliable transfer of empty-table state.