Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

disable_referential_integrity does not work with PostGIS tables #19

Open
workmaster2n opened this issue Jun 30, 2015 · 3 comments
Open

disable_referential_integrity does not work with PostGIS tables #19

workmaster2n opened this issue Jun 30, 2015 · 3 comments

Comments

@workmaster2n
Copy link

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
Copy link

Jeehut commented Jun 29, 2016 •
edited
Loading

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
Copy link

Jeehut commented Jun 29, 2016

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
Copy link

mezza commented Jul 7, 2016 •
edited
Loading

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.

@botandrose botandrose transferred this issue from DatabaseCleaner/database_cleaner Feb 18, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@mezza @workmaster2n @Jeehut