Heroku CI with rails fixtures

Question

I'm using Heroku CI (Beta) with my rails app and when my tests run they all fail with the following error:

WARNING: Rails was not able to disable referential integrity.
This is most likely caused due to missing permissions.
Rails needs superuser privileges to disable referential integrity.
    cause: PG::InsufficientPrivilege: ERROR:  permission denied: "RI_ConstraintTrigger_a_5199633" is a system trigger

This relates to how rails handles the deletion of fixture records. Instead of trying to figure out the order in which records are deleted, they simply turn off the triggers that maintain referential integrity.

This isn't an option when running tests on Heroku CI. Does anyone have a proper solution for this?

Answer 1

Rails PR which fixed this issue was reverted: https://github.com/rails/rails/pull/27636

Workaround is monkey patch:

# config/initializers/disable_referential_integrity.rb
require "activerecord/lib/active_record/connection_adapters/postgresql/referential_integrity.rb"
require "activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb"

Add to your lib/ two files (code from rails PR):

# lib/activerecord/lib/active_record/connection_adapters/postgresql/referential_integrity.rb
module ActiveRecord
  module ConnectionAdapters
    module PostgreSQL
      module ReferentialIntegrity # :nodoc:
        def supports_disable_referential_integrity? # :nodoc:
          true
        end

        def disable_referential_integrity(&block) # :nodoc:
          if supports_disable_referential_integrity?
            if supports_alter_constraint?
              disable_referential_integrity_with_alter_constraint(&block)
            else
              disable_referential_integrity_with_disable_trigger(&block)
            end
          else
            yield
          end
        end

        private

          def disable_referential_integrity_with_alter_constraint
            tables_constraints = execute(<<-SQL).values
              SELECT table_name, constraint_name
              FROM information_schema.table_constraints
              WHERE constraint_type = 'FOREIGN KEY'
              AND is_deferrable = 'NO'
            SQL

            execute(
              tables_constraints.collect { |table, constraint|
                "ALTER TABLE #{quote_table_name(table)} ALTER CONSTRAINT #{constraint} DEFERRABLE"
              }.join(";")
            )

            begin
              transaction do
                execute("SET CONSTRAINTS ALL DEFERRED")

                yield
              end
            ensure
              execute(
                tables_constraints.collect { |table, constraint|
                  "ALTER TABLE #{quote_table_name(table)} ALTER CONSTRAINT #{constraint} NOT DEFERRABLE"
                }.join(";")
              )
            end
          end

          def disable_referential_integrity_with_disable_trigger
            original_exception = nil

            begin
              transaction(requires_new: true) do
                execute(tables.collect { |name| "ALTER TABLE #{quote_table_name(name)} DISABLE TRIGGER ALL" }.join(";"))
              end
            rescue ActiveRecord::ActiveRecordError => e
              original_exception = e
            end

            begin
              yield
            rescue ActiveRecord::InvalidForeignKey => e
              warn <<-WARNING
WARNING: Rails was not able to disable referential integrity.
This is most likely caused due to missing permissions.
Rails needs superuser privileges to disable referential integrity.
    cause: #{original_exception.try(:message)}
              WARNING
              raise e
            end

            begin
              transaction(requires_new: true) do
                execute(tables.collect { |name| "ALTER TABLE #{quote_table_name(name)} ENABLE TRIGGER ALL" }.join(";"))
              end
            rescue ActiveRecord::ActiveRecordError
            end
          end
      end
    end
  end
end

and second file:

# lib/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb
module ActiveRecord
  module ConnectionAdapters
    class PostgreSQLAdapter < AbstractAdapter

      def supports_alter_constraint?
        # PostgreSQL 9.4 introduces ALTER TABLE ... ALTER CONSTRAINT but it has a bug and fixed in 9.4.2
        # https://www.postgresql.org/docs/9.4/static/release-9-4-2.html
        postgresql_version >= 90402
      end

    end
  end
end