search

DatabaseCleaner / database_cleaner-active_record

Strategies for cleaning databases using ActiveRecord. Can be used to ensure a clean state for testing.
MIT License
64 stars 63 forks source link

disable_referential_integrity does not work with PostGIS tables #19

Open workmaster2n opened 9 years ago

workmaster2n commented 9 years ago

We use the PostGIS extension and the rgeo AR gem. We use truncation for our js tests as well as our before(:suite) strategy.

The problem is that the PostGIS extension creates a table spatial_ref_sys that is owned by the user who installs the extension (namely, a super user).

Running bundle exec rspec fails with the following error:

/Users/<user>/.rvm/gems/ruby-2.2.0@<project>/gems/activerecord-4.2.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:155:in `async_exec': PG::InsufficientPrivilege: ERROR:  must be owner of relation spatial_ref_sys (ActiveRecord::StatementInvalid)
: ALTER TABLE "spatial_ref_sys" ENABLE TRIGGER USER;ALTER TABLE "filtered_tracked_points" ENABLE TRIGGER USER;ALTER TABLE "processed_tracked_points" ENABLE TRIGGER USER;ALTER TABLE "raw_tracked_points" ENABLE TRIGGER USER;ALTER TABLE "resource_intersections" ENABLE TRIGGER USER;ALTER TABLE "schema_migrations" ENABLE TRIGGER USER;ALTER TABLE "users" ENABLE TRIGGER USER;ALTER TABLE "feature_types" ENABLE TRIGGER USER;ALTER TABLE "features" ENABLE TRIGGER USER
    from /Users/<user>/.rvm/gems/ruby-2.2.0@<project>/gems/activerecord-4.2.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:155:in `block in execute'
    from /Users/<user>/.rvm/gems/ruby-2.2.0@<project>/gems/activerecord-4.2.1/lib/active_record/connection_adapters/abstract_adapter.rb:473:in `block in log'
    from /Users/<user>/.rvm/gems/ruby-2.2.0@<project>/gems/activesupport-4.2.1/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
    from /Users/<user>/.rvm/gems/ruby-2.2.0@<project>/gems/activerecord-4.2.1/lib/active_record/connection_adapters/abstract_adapter.rb:467:in `log'
    from /Users/<user>/.rvm/gems/ruby-2.2.0@<project>/gems/activerecord-4.2.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:154:in `execute'
    from /Users/<user>/.rvm/gems/ruby-2.2.0@<project>/gems/activerecord-4.2.1/lib/active_record/connection_adapters/postgresql/referential_integrity.rb:23:in `rescue in ensure in disable_referential_integrity'
    from /Users/<user>/.rvm/gems/ruby-2.2.0@<project>/gems/activerecord-4.2.1/lib/active_record/connection_adapters/postgresql/referential_integrity.rb:20:in `ensure in disable_referential_integrity'
    from /Users/<user>/.rvm/gems/ruby-2.2.0@<project>/gems/activerecord-4.2.1/lib/active_record/connection_adapters/postgresql/referential_integrity.rb:20:in `disable_referential_integrity'
    from /Users/<user>/.rvm/gems/ruby-2.2.0@<project>/gems/database_cleaner-1.4.1/lib/database_cleaner/active_record/truncation.rb:235:in `clean'
    from /Users/<user>/.rvm/gems/ruby-2.2.0@<project>/gems/database_cleaner-1.4.1/lib/database_cleaner/base.rb:40:in `clean_with'
    from /Users/<user>/.rvm/gems/ruby-2.2.0@<project>/gems/database_cleaner-1.4.1/lib/database_cleaner/configuration.rb:91:in `block in clean_with'
    from /Users/<user>/.rvm/gems/ruby-2.2.0@<project>/gems/database_cleaner-1.4.1/lib/database_cleaner/configuration.rb:91:in `each'
    from /Users/<user>/.rvm/gems/ruby-2.2.0@<project>/gems/database_cleaner-1.4.1/lib/database_cleaner/configuration.rb:91:in `clean_with'
    from /Users/<user>/Development/<company>/<project>/spec/support/database_cleaner.rb:3:in `block (2 levels) in <top (required)>'
    from /Users/<user>/.rvm/gems/ruby-2.2.0@<project>/gems/rspec-core-3.2.3/lib/rspec/core/example.rb:333:in `instance_exec'
    from /Users/<user>/.rvm/gems/ruby-2.2.0@<project>/gems/rspec-core-3.2.3/lib/rspec/core/example.rb:333:in `instance_exec'
    from /Users/<user>/.rvm/gems/ruby-2.2.0@<project>/gems/rspec-core-3.2.3/lib/rspec/core/hooks.rb:357:in `run'
    from /Users/<user>/.rvm/gems/ruby-2.2.0@<project>/gems/rspec-core-3.2.3/lib/rspec/core/configuration.rb:1559:in `block in run_hooks_with'
    from /Users/<user>/.rvm/gems/ruby-2.2.0@<project>/gems/rspec-core-3.2.3/lib/rspec/core/configuration.rb:1559:in `each'
    from /Users/<user>/.rvm/gems/ruby-2.2.0@<project>/gems/rspec-core-3.2.3/lib/rspec/core/configuration.rb:1559:in `run_hooks_with'
    from /Users/<user>/.rvm/gems/ruby-2.2.0@<project>/gems/rspec-core-3.2.3/lib/rspec/core/configuration.rb:1525:in `with_suite_hooks'
    from /Users/<user>/.rvm/gems/ruby-2.2.0@<project>/gems/rspec-core-3.2.3/lib/rspec/core/runner.rb:109:in `block in run_specs'
    from /Users/<user>/.rvm/gems/ruby-2.2.0@<project>/gems/rspec-core-3.2.3/lib/rspec/core/reporter.rb:62:in `report'
    from /Users/<user>/.rvm/gems/ruby-2.2.0@<project>/gems/rspec-core-3.2.3/lib/rspec/core/runner.rb:108:in `run_specs'
    from /Users/<user>/.rvm/gems/ruby-2.2.0@<project>/gems/rspec-core-3.2.3/lib/rspec/core/runner.rb:86:in `run'
    from /Users/<user>/.rvm/gems/ruby-2.2.0@<project>/gems/rspec-core-3.2.3/lib/rspec/core/runner.rb:70:in `run'
    from /Users/<user>/.rvm/gems/ruby-2.2.0@<project>/gems/rspec-core-3.2.3/lib/rspec/core/runner.rb:38:in `invoke'
    from /Users/<user>/.rvm/gems/ruby-2.2.0@<project>/gems/rspec-core-3.2.3/exe/rspec:4:in `<top (required)>'
    from /Users/<user>/.rvm/gems/ruby-2.2.0@<project>/bin/rspec:23:in `load'
    from /Users/<user>/.rvm/gems/ruby-2.2.0@<project>/bin/rspec:23:in `<main>'
    from /Users/<user>/.rvm/gems/ruby-2.2.0@<project>/bin/ruby_executable_hooks:15:in `eval'
    from /Users/<user>/.rvm/gems/ruby-2.2.0@<project>/bin/ruby_executable_hooks:15:in `<main>'

The error comes from the call to disable_referential_integrity because it calls all tables, ignoring any tables that might have been excluded via the truncation options.

Our database_cleaner.rb file:

RSpec.configure do |config|
  config.before(:suite) do
    DatabaseCleaner.clean_with(:truncation, {except: %w[spatial_ref_sys]})
  end

  config.before(:each) do
    DatabaseCleaner.strategy = :transaction
  end

  config.before(:each, :js => true) do
    DatabaseCleaner.strategy = :truncation, {except: %w[spatial_ref_sys]}
  end

  config.before(:each) do
    DatabaseCleaner.start
  end

  config.append_after(:each) do
    DatabaseCleaner.clean
  end
end

One workaround is to execute the tests as a db super user. Another would be to change the ownership of the spatial_ref_system table. Neither of these are very desirable.

Is there a way to skip tables in the disable_referential_integrity call?

Jeehut commented 8 years ago

Similar issue here, just that rspec is completely hanging so I needed to do this trick and found DatabaseCleaner.clean_with(:truncation, { except: %w[spatial_ref_sys] }) as the line causing the problem.

Here's the complete database_cleaner.rb file:

RSpec.configure do |config|
  config.before(:suite) do
    DatabaseCleaner.strategy = :transaction
    DatabaseCleaner.clean_with(:truncation, { except: %w[spatial_ref_sys] })
  end

  config.prepend_before(:each) do
    DatabaseCleaner.start
  end

  config.append_after(:each) do
    DatabaseCleaner.clean
  end
end

UPDATE:

Here's the entire output:

/Users/MyUser/.rvm/gems/ruby-2.2.3/gems/activerecord-5.0.0.rc2/lib/active_record/connection_adapters/postgresql/database_statements.rb:98:in `call'
/Users/MyUser/.rvm/gems/ruby-2.2.3/gems/activerecord-5.0.0.rc2/lib/active_record/connection_adapters/postgresql/database_statements.rb:98:in `async_exec'
/Users/MyUser/.rvm/gems/ruby-2.2.3/gems/activerecord-5.0.0.rc2/lib/active_record/connection_adapters/postgresql/database_statements.rb:98:in `block in execute'
/Users/MyUser/.rvm/gems/ruby-2.2.3/gems/activerecord-5.0.0.rc2/lib/active_record/connection_adapters/abstract_adapter.rb:566:in `block in log'
/Users/MyUser/.rvm/gems/ruby-2.2.3/gems/activesupport-5.0.0.rc2/lib/active_support/notifications/instrumenter.rb:21:in `instrument'
/Users/MyUser/.rvm/gems/ruby-2.2.3/gems/activerecord-5.0.0.rc2/lib/active_record/connection_adapters/abstract_adapter.rb:560:in `log'
/Users/MyUser/.rvm/gems/ruby-2.2.3/gems/activerecord-5.0.0.rc2/lib/active_record/connection_adapters/postgresql/database_statements.rb:97:in `execute'
/Users/MyUser/.rvm/gems/ruby-2.2.3/gems/activerecord-5.0.0.rc2/lib/active_record/connection_adapters/postgresql/referential_integrity.rb:15:in `block in disable_referential_integrity'
/Users/MyUser/.rvm/gems/ruby-2.2.3/gems/activerecord-5.0.0.rc2/lib/active_record/connection_adapters/abstract/database_statements.rb:232:in `block in transaction'
/Users/MyUser/.rvm/gems/ruby-2.2.3/gems/activerecord-5.0.0.rc2/lib/active_record/connection_adapters/abstract/transaction.rb:189:in `within_new_transaction'
/Users/MyUser/.rvm/gems/ruby-2.2.3/gems/activerecord-5.0.0.rc2/lib/active_record/connection_adapters/abstract/database_statements.rb:232:in `transaction'
/Users/MyUser/.rvm/gems/ruby-2.2.3/gems/activerecord-5.0.0.rc2/lib/active_record/connection_adapters/postgresql/referential_integrity.rb:14:in `disable_referential_integrity'
/Users/MyUser/.rvm/gems/ruby-2.2.3/gems/database_cleaner-1.5.3/lib/database_cleaner/active_record/truncation.rb:235:in `clean'
/Users/MyUser/.rvm/gems/ruby-2.2.3/gems/database_cleaner-1.5.3/lib/database_cleaner/base.rb:46:in `clean_with'
/Users/MyUser/.rvm/gems/ruby-2.2.3/gems/database_cleaner-1.5.3/lib/database_cleaner/configuration.rb:91:in `block in clean_with'
/Users/MyUser/.rvm/gems/ruby-2.2.3/gems/database_cleaner-1.5.3/lib/database_cleaner/configuration.rb:91:in `each'
/Users/MyUser/.rvm/gems/ruby-2.2.3/gems/database_cleaner-1.5.3/lib/database_cleaner/configuration.rb:91:in `clean_with'
/Users/MyUser/path/to/my/project/spec/support/database_cleaner.rb:4:in `block (2 levels) in <top (required)>'
/Users/MyUser/.rvm/gems/ruby-2.2.3/bundler/gems/rspec-core-fe1256b4c51e/lib/rspec/core/example.rb:441:in `instance_exec'
/Users/MyUser/.rvm/gems/ruby-2.2.3/bundler/gems/rspec-core-fe1256b4c51e/lib/rspec/core/example.rb:441:in `instance_exec'
/Users/MyUser/.rvm/gems/ruby-2.2.3/bundler/gems/rspec-core-fe1256b4c51e/lib/rspec/core/hooks.rb:350:in `run'
/Users/MyUser/.rvm/gems/ruby-2.2.3/bundler/gems/rspec-core-fe1256b4c51e/lib/rspec/core/configuration.rb:1880:in `block in run_hooks_with'
/Users/MyUser/.rvm/gems/ruby-2.2.3/bundler/gems/rspec-core-fe1256b4c51e/lib/rspec/core/configuration.rb:1880:in `each'
/Users/MyUser/.rvm/gems/ruby-2.2.3/bundler/gems/rspec-core-fe1256b4c51e/lib/rspec/core/configuration.rb:1880:in `run_hooks_with'
/Users/MyUser/.rvm/gems/ruby-2.2.3/bundler/gems/rspec-core-fe1256b4c51e/lib/rspec/core/configuration.rb:1836:in `with_suite_hooks'
/Users/MyUser/.rvm/gems/ruby-2.2.3/bundler/gems/rspec-core-fe1256b4c51e/lib/rspec/core/runner.rb:112:in `block in run_specs'
/Users/MyUser/.rvm/gems/ruby-2.2.3/bundler/gems/rspec-core-fe1256b4c51e/lib/rspec/core/reporter.rb:77:in `report'
/Users/MyUser/.rvm/gems/ruby-2.2.3/bundler/gems/rspec-core-fe1256b4c51e/lib/rspec/core/runner.rb:111:in `run_specs'
/Users/MyUser/.rvm/gems/ruby-2.2.3/bundler/gems/rspec-core-fe1256b4c51e/lib/rspec/core/runner.rb:87:in `run'
/Users/MyUser/.rvm/gems/ruby-2.2.3/bundler/gems/rspec-core-fe1256b4c51e/lib/rspec/core/runner.rb:71:in `run'
/Users/MyUser/.rvm/gems/ruby-2.2.3/bundler/gems/rspec-core-fe1256b4c51e/lib/rspec/core/runner.rb:45:in `invoke'
/Users/MyUser/.rvm/gems/ruby-2.2.3/bundler/gems/rspec-core-fe1256b4c51e/exe/rspec:4:in `<top (required)>'
/Users/MyUser/.rvm/gems/ruby-2.2.3/bin/rspec:23:in `load'
/Users/MyUser/.rvm/gems/ruby-2.2.3/bin/rspec:23:in `<main>'
/Users/MyUser/.rvm/gems/ruby-2.2.3/bin/ruby_executable_hooks:15:in `eval'
/Users/MyUser/.rvm/gems/ruby-2.2.3/bin/ruby_executable_hooks:15:in `<main>'
Jeehut commented 8 years ago

Maybe this isn't an issue with database_clenaer after all, couldn't this be an issue with the pg gem itself? See https://bitbucket.org/ged/ruby-pg/issues/75/hang-in-pgconn-block-while-waiting-for.

mezza commented 8 years ago

I believe this is a problem with ActiveRecord as mentioned here.

I've fixed this for my problems by adding the following monkey patch in test_helper.rb. I also use :truncation and no transactional fixtures

module ActiveRecord
  module ConnectionAdapters
    module PostgreSQL
      module ReferentialIntegrity # :nodoc:
        def supports_disable_referential_integrity? # :nodoc:
          false
        end
      end
    end
  end
end

Initially, my tests took 30 mins to run, but by adding the :pre_count option it dropped back to a more reasonable 7 mins.