Kopongo

A problem that I recently started running into is a strange Postgres FK constraints error, which only shows up when running tests. The error looks something like this:

PGError: ERROR:  permission denied: "RI_ConstraintTrigger_XXXXX" is a system trigger

This is something that came in with FoxyFixtures, and there's a discussion about this problem and a patch in the rails trac. So what's this about?

When loading fixtures, rails tries to disable all foreign keys so that there are no problems inserting the data in whatever order. The calls work like this:

ALTER TABLE table_name DISABLE TRIGGER ALL
insert a bunch of fixtures 
ALTER TABLE table_name ENABLE TRIGGER ALL

However, postgres keeps some kind of metadata regarding the FKs to be enforced called RI_Constraint_something. Disabling the triggers on a table causes these to be deleted as well. The problems is that these other triggers belong to the superuser and not you. So, you got this permissions problem.

You may not have superuser permissions for your postgres instance, but it's probably best that the database you work with does not have superuser permissions, since that's the way it's going to be in production.

One workaround for to disable the foreign key checking in a different way. Instead of running the DISABLE TRIGGER, you can make the constraints DEFERRED postgres docs. This way, you run like this:

SET CONSTRAINTS ALL DEFERRED
insert a bunch of fixtures 
SET CONSTRAINTS ALL IMMEDIATE

This allows you to load your fixtures, but keep all contraint-checking.

The easiest way to patch this is to override the behavior of the disable_referential_integrity method. I created a new file, active_record_fk_hack.rb, stuck this in there:

module ActiveRecord
  module ConnectionAdapters
    class PostgreSQLAdapter < AbstractAdapter
      def disable_referential_integrity(&block)
         transaction {
           begin
             execute "SET CONSTRAINTS ALL DEFERRED"
             yield
           ensure
             execute "SET CONSTRAINTS ALL IMMEDIATE"
           end
         }
      end
    end
  end
end

then, in your environment.rb, add this at the end:

require 'active_record_fk_hack'