Foreign key constraints are system triggers (Postgres) about foreigner HOT 33 CLOSED

This problem looks ugly. I found an old blog post on it: http://kopongo.com/2008/7/25/postgres-ri_constrainttrigger-error

I do very little development with postgres, and I use factories rather than fixtures, so I've never hit this problem. I didn't realize that tests simply don't run on postgres.

It seems like the only option is for Foreigner to patch up the "disable_referential_integrity" method. Let me know your comments.

from foreigner.

Thanks for looking into this. I googled like crazy and never found that post. My short-term hack was to just add this to my test_helper:

class ActiveRecord::ConnectionAdapters::PostgreSQLAdapter
  def supports_disable_referential_integrity?
    false
  end
end

I only use fixtures for a single model (Role) and use factories as well for everything else, so I don't care if it gets disable/enabled to load them.

It would make sense for Foreigner to patch up the Postgres adapter (the blog post's solution, not mine), but probably isn't necessary.

Your call.

from foreigner.

I would support Foreigner overriding the PostgreSQLAdapter's default disable_referential_integrity with the Kopongo version.

from foreigner.

I'm not familiar with kopongo's version. disable_referential_integrity is from Rails, not this gem. Can't you just override the method?

from foreigner.

The Kopongo version meaning the one referenced earlier from http://kopongo.com/2008/7/25/postgres-ri_constrainttrigger-error, where disable_referential_integrity is monkey patched to do it the correct PostgreSQL way. Arguably, this fix should be in Rails proper.

from foreigner.

FWIW, this issue is biting me in that db:rollback also attempts to disable the system trigger. But it's proving difficult to override the method in the PostgreSQLAdapter because at every point in the Rails initialization process I've tried, the adapter isn't yet defined. But if you require the file for the adapter, it winds up with a bunch of missing methods.

Frustrating.

from foreigner.

This bundles the Kopongo hack and another hack to ActiveRecord::Fixtures.create_fixtures together in a gem: https://github.com/greyblake/rails3_pg_deferred_constraints , http://rubygems.org/gems/rails3_pg_deferred_constraints

from foreigner.

This is affecting my application on production environment because I can't disable foreign keys and foreigner uses disable_referential_integrity to drop tables.

I'm currently using this initializer:

module ActiveRecord
  module ConnectionAdapters
    class PostgreSQLAdapter < AbstractAdapter
      # PostgreSQL only disables referential integrity when connection
      # user is root and that is not the case.
      def disable_referential_integrity
        yield
      end
    end
  end
end

It's really necessary to disable foreign keys when dropping a table? Why?

from foreigner.

@sobrinho When you load schema.rb, it uses a :force option to drop the existing table. If the existing table is referenced by foreign key constraints, the drop fails. Ultimately, a call to "rake db:schema:load" fails.

I know that the above is true for mysql, which is why I originally disabled integrity. I am unsure about postgres.

from foreigner.

@matthuhiggins but this is the objective of foreign key constraints.

I'm dropping a table and records is still referenced in another table, then the database should raise an error.

Do you not agree?

from foreigner.

@sobrinho It occurs when the tables are completely empty.

from foreigner.

@matthuhiggins I will take a look into this ASAP.

If this is the problem, we should drop the foreign keys before dropping the tables.

drop_table should not work if table have records referenced in another table.

Also, drop_table should not fail on production deploy because my database user is not a root.

from foreigner.

Yes, one idea is to drop all foreign keys that reference the table being dropped. It will require new SQL queries for each engine, to find the names of the constraints.

from foreigner.

I ran into this very problem today. In my opinion, dropping a table that has foreign keys in other tables referencing its rows should not pass by simply disabling referential integrity.

I completely agree with @sobrinho on this, as drop_table shouldn't fail on production.
Don't confuse root and superusers in postgresql context. A linux root user has nothing to do with a postgresql superuser. The latter can perform the ALTER TABLE "tablename" DISABLE TRIGGER ALL without any errors, but if the SQL is executed under a non-superuser postgresql user/role, the command will fail.

In general, I think it would be adequate if drop_table simply fails by stating that there are still existing foreign keys in other tables that are referencing the table that we are dropping. This forces the user to think twice about what he is doing - the error can simply be fixed by issuing remove_foreign_key commands before the drop_table command. It's easier to implement and more explicit than trying to drop all foreign keys automagically.

Anyway, for now, one workaround is to make the postgresql user/role into a superuser - at least during the migrations.

from foreigner.

This actually seems to be a bug in ActiveRecord itself.
activerecord-3.1.3/lib/active_record/connection_adapters/postgresql_adapter.rb:506
contains the code:

so that dropping a table would work - it just ignores the foreign key constraints.

So actually, the foreigner gem shouldn't try to fix it. It's ActiveRecord that needs to be patched.
For now, the workaround is to simply use raw SQL to drop the table:
ActiveRecord::Base.connection.execute('DROP TABLE users');
instead of using the drop_table method

This won't disable the referential integrity check, which anyway is not a good practice.
I believe this issue can be closed, as it isn't related to the work of the foreigner gem.

from foreigner.

@hardipe If the :force option is passed to create_table, I should think that it should always pass. Otherwise, you cannot load schema.rb into an existing database. This means ignoring foreign keys.

from foreigner.

@matthuhiggins I checked this and what happens is... If you have any table using a foreign to table that you are dropping, the drop will fail.

Let use this structure:

class Category < ActiveRecord::Base
  has_many :posts
end

class Post < ActiveRecord::Base
  belongs_to :category
end

If you try to drop the category table, the drop will fail because foreign keys.

If you remove the category_id from posts and drop the categories table, it will work.

This means the expected behavior in my opinion.

Dropping a table should not drop the correspondent foreign keys because we will have orphan references.

The fix is simple, remove the wrapper on disable_referential_integrity.

from foreigner.

@sobrinho That fails when you run "rake db:schema:load". If db:schema:load is broken, then "rake test" is broken.

from foreigner.

Basically, the only time that foreign keys should be disabled for the drop is when "create_table :widgets, force: true" is used. Unfortunately, the :force option is not passed to drop_table, so we don't know our context.

from foreigner.

This is issue even in normal development cycle. I can't drop tables through migrations (tables don't have FK in them).

from foreigner.

This issue is still very real. I just spent an hour root causing why my migration was failing to drop a table with no foreign keys. I finally had to run the migration "manually"... dropping the table and creating the schema migration entry by hand.

Would the Kopongo hack work as a longer-term solution?

from foreigner.

Have a look at f70092e. This fix would solve most of the problems described in this thread. Unfortunately, it only works on Rails 3.0. If someone can get the Rails team to pass the create_table options to drop_table, then I know when to use the :force option.

from foreigner.

Try out v1.1.2 and let me know if it works for you.

from foreigner.

I added an issue to Rails (rails/rails#5052) which asks for the options hash to be re-added to drop_table. Knowing if drop_table should be :force or not will go a long way to resolving this problem.

from foreigner.

@matthuhiggins Just tried 1.1.2 on Rails 3.2 and I was able to run the same set of migrations without incident after reverting the DB. Thanks for the update & hopefully the Rails team will incorporate your request.

from foreigner.

Yay! I think this issue is fixed with 1.1.2.

from foreigner.

Still seeing this error with foreigner v1.1.6 rails v 3.2.2, unless I use an initializer like the following (modified from the one above slightly,which also works.)

module ActiveRecord
  module ConnectionAdapters
    class PostgreSQLAdapter < AbstractAdapter
      # PostgreSQL only disables referential integrity when connection
      # user is root and that is not the case.
      def supports_disable_referential_integrity?
        false
      end
    end
  end
end

from foreigner.

I have this error during running tests with rails 3.2.8 and mailbox 0.8.0 (mailbox have foreigner in depedencies). Tests fails unpredictably.

from foreigner.

Same problem with Foreigner 1.2.1 and Rails 3.0.7. Help?

from foreigner.

I should mention that I'm not using fixtures; rather, the database_cleaner gem is causing this problem when it attempts to truncate the DB.

from foreigner.

This happened also to me when Rails 3.2.13 fixtures started to failing after introducing Foreginer. My test DB was created by normal PG user (not super user) so the change by @garysweaver fixes that problem. It is going to be part of Rails 4. However you can make it work in Rails 3.x with this snippet in test_helper.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(";"))
      end
      yield
    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

from foreigner.

@pgaertig is there change that you would suggest to foreigner? I only had a chance to glance at this..

from foreigner.

@matthuhiggins I suppose Rails 4 is going to fix all above problems completely. I will also test Foreginer once Rails 4 is final. For now I would suggest writing some "Possible issues" in README with the link to this issue page. That may save others a few moments of figuring out what is going on. BTW I am using PostgreSQL 9.2.

from foreigner.