Foreign key constraints

[moiristo]

When using foreign key constraints (for example when using foreign_key_migrations or redhillonrails) in your database, 'rake db:load' may fail due to the fact that a load order is required when truncating or loading tables. This may result in errors like this (postgres):

PGError: ERROR: current transaction is aborted, commands ignored until end 
of transaction block 
: DELETE FROM schema_info 

I don't know if there's a way to temporarily disable foreign key migrations in postgres (maybe 'SET CONSTRAINTS ALL DEFERRED' could work), but nonetheless this issue might help others experiencing the same problem.

[tovodeverett]

I have a set of monkey-patches to get yaml_db working with foreign key constraints under PostgreSQL. It involves monkey-patches to both ActiveRecord and yaml_db, as well as requiring that all the foreign key constraints be marked as DEFERRABLE. See http://tovodeverett.wordpress.com/2013/06/07/making-yaml_db-work-with-schema_plus-constraints/ for my write up on the issue and my solution. If there is a consensus that yaml_db should be modified to support this approach, I'd be happy to work on getting a proper pull request created.

[jenrzzz]

👍

mysqldump disables foreign key checks in its dump output to prevent these issues when restoring with something like this:

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

[slhck]

I am getting a similar error with Rails 4.5.2.1 and yaml_db 0.3.0:

# rake db:data:load
rake aborted!
ActiveRecord::InvalidForeignKey: PG::ForeignKeyViolation: ERROR:  insert or update on table "experiment_progresses" violates foreign key constraint "fk_rails_7dc065b167"
DETAIL:  Key (user_id)=(1) is not present in table "users".
: INSERT INTO "experiment_progresses" ("id","user_id","experiment_id","status","joined","started","finished","completed","created_at","updated_at") VALUES (1,1,1,1,'2016-02-24 08:09:07.795156','2016-02-24 08:09:10.083996','2016-02-24 07:45:30.033186','2016-02-24 07:45:31.963079','2016-02-24 07:11:04.841216','2016-02-24 08:10:49.292503')

I have this in my 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

And I've created all my tables with foreign_key: { deferrable: true }.

I've also tried running ALTER TABLE xxx DISABLE TRIGGER USER; for all my tables before in the Rails database console.

Any idea on how to easily load the data back in? I really don't care about the FK constriants—this is not a production database or anything where those constraints would be necessary. If I'd known that there could be such problems loading data back in, I would have done the dump differently, but unfortunately, this is the only database dump I have.

@tovodeverett, you seem to know most about this – is there anything in Rails 4 that has changed?

[binyamindavid]

@slhck did you manage to fix this ?

[slhck]

@benjaminsigidi I did, but I don't remember exactly how it worked in the end. I think I disabled all foreign key checks in Postgres entirely (SET CONSTRAINTS ALL DEFERRED).

It doesn't matter for my case since it's a small internal app, but I guess if I wanted to work with this Gem in the future I'd just switch to MySQL again or dump data using .sql files directly from the database app.

[binyamindavid]

Thank you for your response. @slhck

[kolasss]

I had to comment all foreign key lines in schema.rb. Sad.

[tovodeverett]

Sorry for my delinquency in following up on this thread - my only connection to Ruby and Rails these days is a home photo archive/sharing site, so it's only when I'm working on it that I manage to reconnect with Ruby.

I am still able to do imports with referential integrity in place under Postgres with Rails 4.2.7.1 and schema_plus. I am hoping to move to 5.0 within the next few months, but I'm waiting for all the gems I use to stabilize. I use three things:

First: Specify deferrable: true on all referential integrity (I'm assuming you are using schema_plus)

Second: Update the Rails 4 Postgres drivers to support deferring referential integrity. Here is my current monkey patch:

# Adapted from my patch to fix disable_referential_integrity for the PostgreSQLAdapter in Rails
# See https://github.com/rails/rails/pull/10939

if Rails::VERSION::MAJOR == 4
  module ActiveRecord
    module ConnectionAdapters
      module PostgreSQL
        module ReferentialIntegrity
          def disable_referential_integrity #:nodoc:
            @referential_integrity_depth = 0 unless instance_variable_defined?(:@referential_integrity_depth)

            if supports_disable_referential_integrity?
              referential_integrity_savepoint_name = "disable_referential_integrity_#{@referential_integrity_depth}"


              create_savepoint(referential_integrity_savepoint_name) if open_transactions > 0
              begin
                execute(tables.collect { |name| "ALTER TABLE #{quote_table_name(name)} DISABLE TRIGGER ALL" }.join(";"))
                have_superuser_privs = true
              rescue
                rollback_to_savepoint(referential_integrity_savepoint_name) if open_transactions > 0
                execute(tables.collect { |name| "ALTER TABLE #{quote_table_name(name)} DISABLE TRIGGER USER" }.join(";"))
                have_superuser_privs = false
              end
              release_savepoint(referential_integrity_savepoint_name) if open_transactions > 0
              @referential_integrity_depth += 1

              create_savepoint(referential_integrity_savepoint_name) if open_transactions > 0
              begin
# This was added in to fix constraints
                transaction {
                  execute("SET CONSTRAINTS ALL DEFERRED")
                  yield
                }
# to replace this:
                # yield
              ensure
                begin
                  release_savepoint(referential_integrity_savepoint_name) if open_transactions > 0
                rescue ActiveRecord::StatementInvalid
                  rollback_to_savepoint(referential_integrity_savepoint_name) if open_transactions > 0
                  release_savepoint(referential_integrity_savepoint_name) if open_transactions > 0
                end
                @referential_integrity_depth -= 1
                if @referential_integrity_depth <= 0
                  execute(tables.collect { |name| "ALTER TABLE #{quote_table_name(name)} ENABLE TRIGGER #{have_superuser_privs ? 'ALL' : 'USER'}" }.join(";"))
                end
              end
            else
              yield
            end
          end
        end
      end
    end
  end
end

Third: Update YamlDb to use disable_referential_integrity, to switch from TRUNCATE to DELETE FROM when necessary, etc. There's a bunch of stuff in my monkey patches and some of it may no longer be necessary (or I may not be up-to-date with the latest yaml_db), so use at your own risk.

# Patch YamlDb to work with schema_plus, empty tables, binary data, etc.
module YamlDb
  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

      def self.load_records(table, column_names, records)
        if column_names.nil?
          return
        end
        columns = column_names.map{|cn| ActiveRecord::Base.connection.columns(table).detect{|c| c.name == cn}}
        quoted_column_names = column_names.map { |column| ActiveRecord::Base.connection.quote_column_name(column) }.join(',')
        quoted_table_name = Utils.quote_table(table)
        (records || []).each do |record|
          quoted_values = record.zip(columns).map{ |(v, c)|
              ActiveRecord::Base.connection.quote(
                  c.type == :binary && !v.nil? && v.start_with?('\\x') ? [v[2..-1]].pack('H*') : v,
                  c
                )
            }.join(',')
          ActiveRecord::Base.connection.execute("INSERT INTO #{quoted_table_name} (#{quoted_column_names}) VALUES (#{quoted_values})")
        end
      end

    end

    class Dump
      def self.dump_table(io, table)
        dump_table_columns(io, table)
        dump_table_records(io, table)
      end
    end
  end
end

Good luck!

[Radzhab]

How solve this issue without monkey code?

[Radzhab]

And how use this code without schema_plus

[99wesley99]

I really need a solve for this on the yaml_db package level. Unless there is a solve for rails 5.1 rails

[akshayrawat]

Would love to get this sorted out.

[ultrawebmarketing]

using part of @tovodeverett monkey-patch, i was able to get pass the foreign key errors with MySQL:

module YamlDb
  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
end

[tovodeverett]

For Rails 6, I switched from using deferrable foreign keys (since Rails 6 doesn't support them, and schema_plus is winding down as most of the features get rolled into Rails) to sequencing the tables to respect foreign keys when truncating and loading. Note that this approach will not work if there are loops in the foreign keys, either within a table (i.e. simple hierarchies, like manager_id, where a table has a foreign key to itself) or between tables (i.e. TableA references TableB and TableB references TableA).

This set of monkey patches addresses the following:

• ar_internal_metadata is excluded from the dump list
• All excluded tables are excluded from the load list (this avoids problems loading dumps that include ar_internal_metadata)
• Empty tables are dumped so that they get truncated during load
• Empty tables don't cause load_records to crash
• Use of SAVEPOINT when attempting to truncate a table - this may be Postgresql-only
• Instead of loading tables in alphabetical order, the entire YAML file is ready into memory, then tables are truncated in reverse foreign key dependency order, and finally the the tables are loaded in foreign key dependency order.

Here are the monkey patches:

# Patch YamlDb to work with foreign keys and to dump empty tables
module YamlDb
  class Load
# Monkey path to reorder truncatation and table loads to respect foreign key dependencies
    def self.load_documents(io, truncate = true)
      yall = {}
      YAML.load_stream(io) do |ydoc|
        yall.merge!(ydoc)
      end

      unordered_tables = yall.keys.reject { |table| ['ar_internal_metadata', 'schema_info', 'schema_migrations'].include?(table) }.sort
      tables = []
      while unordered_tables.any?
        loadable_tables = unordered_tables.find_all do |table|
          foreign_keys = ActiveRecord::Base.connection.foreign_keys(table)
          foreign_keys.reject { |foreign_key| tables.include?(foreign_key.to_table) }.empty?
        end

        if loadable_tables.empty?
          abort("Unable to sequence the following tables for loading: " + unordered_tables.join(', '))
        end

        tables += loadable_tables
        unordered_tables -= loadable_tables
      end

      if truncate == true
        tables.reverse.each do |table|
          truncate_table(table)
        end
      end

      tables.each do |table|
        next if yall[table].nil?
        load_table(table, yall[table], truncate)
      end
    end
  end

  module SerializationHelper
    class Load
# Monkey patch to use a SAVEPOINT so that the fallback to DELETE FROM actually works!
      def self.truncate_table(table)
        begin
          ActiveRecord::Base.connection.execute("SAVEPOINT before_truncation")
          ActiveRecord::Base.connection.execute("TRUNCATE #{Utils.quote_table(table)}")
        rescue Exception
          ActiveRecord::Base.connection.execute("ROLLBACK TO SAVEPOINT before_truncation")
          ActiveRecord::Base.connection.execute("DELETE FROM #{Utils.quote_table(table)}")
        end
      end

# Monkey patch to enable successful load when empty record set
      def self.load_records(table, column_names, records)
        return if records.nil?
        if column_names.nil?
          return
        end
        quoted_column_names = column_names.map { |column| ActiveRecord::Base.connection.quote_column_name(column) }.join(',')
        quoted_table_name = Utils.quote_table(table)
        records.each do |record|
          quoted_values = record.map{|c| ActiveRecord::Base.connection.quote(c)}.join(',')
          ActiveRecord::Base.connection.execute("INSERT INTO #{quoted_table_name} (#{quoted_column_names}) VALUES (#{quoted_values})")
        end
      end
    end

    class Dump
# Monkey patch to dump empty tables, otherwise they won't get purged on restore!
      def self.dump_table(io, table)
        dump_table_columns(io, table)
        dump_table_records(io, table)
      end

# Monkey patch to exclude ar_internal_metadata from the table list as well
      def self.tables
        ActiveRecord::Base.connection.tables.reject { |table| ['ar_internal_metadata', 'schema_info', 'schema_migrations'].include?(table) }.sort
      end
    end
  end
end

If there is a consensus that I should put together a proper pull request for this, I will do the leg work to build out the test suite, fine-tune the code to be more database server agnostic, etc.

Finally, my Ruby is super-rusty, so I apologize for non-idiomatic code, general inefficiency, etc.

[robotex82]

Thank you for the effort! I'm using rails 6.0.3.2 and yaml_db 0.7.0 and without this patch i can't load the data due to foreign key errors on active storage tables. So any rails application using this feature (that ships with rails by default) would not be able to use yaml_db. That would be sad.

TLDR: Please include these patches, they help a lot.

Thank you!