ActiveRecord::StatementInvalid: PG::ObjectInUse: ERROR: cannot drop the currently open database

[noff]

Hello. Rails 4.2.5. Octopus used for sharding. PostgreSQL 9.4 for database. Local server (this) switched off.

When run RAILS_ENV=test rake db:test:load_schema

Receive this error:

$ RAILS_ENV=test rake db:test:load_schema
rake aborted!
ActiveRecord::StatementInvalid: PG::ObjectInUse: ERROR:  cannot drop the currently open database
: DROP DATABASE IF EXISTS "rdb_test"
/Users/noff/.rvm/gems/ruby-2.2.3/gems/activerecord-4.2.5/lib/active_record/connection_adapters/postgresql/database_statements.rb:155:in `async_exec'
/Users/noff/.rvm/gems/ruby-2.2.3/gems/activerecord-4.2.5/lib/active_record/connection_adapters/postgresql/database_statements.rb:155:in `block in execute'
/Users/noff/.rvm/gems/ruby-2.2.3/gems/activerecord-4.2.5/lib/active_record/connection_adapters/abstract_adapter.rb:472:in `block in log'
/Users/noff/.rvm/gems/ruby-2.2.3/gems/activesupport-4.2.5/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
/Users/noff/.rvm/gems/ruby-2.2.3/gems/ar-octopus-0.8.6/lib/octopus/abstract_adapter.rb:15:in `instrument'
/Users/noff/.rvm/gems/ruby-2.2.3/gems/activerecord-4.2.5/lib/active_record/connection_adapters/abstract_adapter.rb:466:in `log'
/Users/noff/.rvm/gems/ruby-2.2.3/gems/activerecord-4.2.5/lib/active_record/connection_adapters/postgresql/database_statements.rb:154:in `execute'
/Users/noff/.rvm/gems/ruby-2.2.3/gems/activerecord-4.2.5/lib/active_record/connection_adapters/postgresql/schema_statements.rb:86:in `drop_database'
/Users/noff/.rvm/gems/ruby-2.2.3/gems/ar-octopus-0.8.6/lib/octopus/proxy.rb:303:in `method_missing'
/Users/noff/.rvm/gems/ruby-2.2.3/gems/activerecord-4.2.5/lib/active_record/tasks/postgresql_database_tasks.rb:28:in `drop'
/Users/noff/.rvm/gems/ruby-2.2.3/gems/activerecord-4.2.5/lib/active_record/tasks/postgresql_database_tasks.rb:41:in `purge'
/Users/noff/.rvm/gems/ruby-2.2.3/gems/activerecord-4.2.5/lib/active_record/tasks/database_tasks.rb:163:in `purge'
/Users/noff/.rvm/gems/ruby-2.2.3/gems/activerecord-4.2.5/lib/active_record/railties/databases.rake:356:in `block (3 levels) in <top (required)>'
/Users/noff/.rvm/gems/ruby-2.2.3/bin/ruby_executable_hooks:15:in `eval'
/Users/noff/.rvm/gems/ruby-2.2.3/bin/ruby_executable_hooks:15:in `<main>'
PG::ObjectInUse: ERROR:  cannot drop the currently open database
/Users/noff/.rvm/gems/ruby-2.2.3/gems/activerecord-4.2.5/lib/active_record/connection_adapters/postgresql/database_statements.rb:155:in `async_exec'
/Users/noff/.rvm/gems/ruby-2.2.3/gems/activerecord-4.2.5/lib/active_record/connection_adapters/postgresql/database_statements.rb:155:in `block in execute'
/Users/noff/.rvm/gems/ruby-2.2.3/gems/activerecord-4.2.5/lib/active_record/connection_adapters/abstract_adapter.rb:472:in `block in log'
/Users/noff/.rvm/gems/ruby-2.2.3/gems/activesupport-4.2.5/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
/Users/noff/.rvm/gems/ruby-2.2.3/gems/ar-octopus-0.8.6/lib/octopus/abstract_adapter.rb:15:in `instrument'
/Users/noff/.rvm/gems/ruby-2.2.3/gems/activerecord-4.2.5/lib/active_record/connection_adapters/abstract_adapter.rb:466:in `log'
/Users/noff/.rvm/gems/ruby-2.2.3/gems/activerecord-4.2.5/lib/active_record/connection_adapters/postgresql/database_statements.rb:154:in `execute'
/Users/noff/.rvm/gems/ruby-2.2.3/gems/activerecord-4.2.5/lib/active_record/connection_adapters/postgresql/schema_statements.rb:86:in `drop_database'
/Users/noff/.rvm/gems/ruby-2.2.3/gems/ar-octopus-0.8.6/lib/octopus/proxy.rb:303:in `method_missing'
/Users/noff/.rvm/gems/ruby-2.2.3/gems/activerecord-4.2.5/lib/active_record/tasks/postgresql_database_tasks.rb:28:in `drop'
/Users/noff/.rvm/gems/ruby-2.2.3/gems/activerecord-4.2.5/lib/active_record/tasks/postgresql_database_tasks.rb:41:in `purge'
/Users/noff/.rvm/gems/ruby-2.2.3/gems/activerecord-4.2.5/lib/active_record/tasks/database_tasks.rb:163:in `purge'
/Users/noff/.rvm/gems/ruby-2.2.3/gems/activerecord-4.2.5/lib/active_record/railties/databases.rake:356:in `block (3 levels) in <top (required)>'
/Users/noff/.rvm/gems/ruby-2.2.3/bin/ruby_executable_hooks:15:in `eval'
/Users/noff/.rvm/gems/ruby-2.2.3/bin/ruby_executable_hooks:15:in `<main>'
Tasks: TOP => db:test:load_schema => db:test:purge
(See full trace by running task with --trace)

How to avoid it?

database.yml

test:
  adapter: postgresql
  encoding: unicode
  database: rdb_test
  pool: 5
  username: recommender
  password: 123
  host: localhost
  timeout: 5000

shards.yml

octopus:
  environments:
    - test
  test:
    shard_0:
      database: rdb_shard_0_test
      host: localhost
      username: <%= ENV['PG_USER'] %>
      password: <%= ENV['PG_PASSWORD'] %>
      adapter: postgresql
      encoding: unicode
      pool: 5
      timeout: 5000
      port: 5432
    shard_1:
      database: rdb_shard_1_test
      host: localhost
      username: <%= ENV['PG_USER'] %>
      password: <%= ENV['PG_PASSWORD'] %>
      adapter: postgresql
      encoding: unicode
      pool: 5
      timeout: 5000
      port: 5432

There is no additional connections to database:

 ps ax  | grep postg
 9833   ??  S      0:27.14 /usr/local/opt/postgresql/bin/postgres -D /usr/local/var/postgres -r /usr/local/var/postgres/server.log
 9837   ??  Ss     0:06.36 postgres: checkpointer process       
 9838   ??  Ss     0:03.59 postgres: writer process       
 9839   ??  Ss     0:05.14 postgres: wal writer process       
 9840   ??  Ss     0:24.70 postgres: autovacuum launcher process       
 9841   ??  Ss     1:20.52 postgres: stats collector process       
61299 s002  R+     0:00.00 grep posts

db.rake

namespace :db do

  namespace :test do
    task :prepare => [:environment, :load_config] do
      ActiveRecord::Base.establish_connection(:"#{Rails.env}")
      ActiveRecord::Base.connection.execute("TRUNCATE TABLE users")
    end

    # tests_schema is the schema.db from other project, located on shards.
    desc 'load tests schema'
    task :load_schema do
      ActiveRecord::Base.establish_connection(:"#{Rails.env}")
      load("#{Rails.root}/db/tests_schema.rb")
    end
  end

  desc 'Dump tests database schema'
  task :test_dump_schema do
    filename = "#{Rails.root}/db/tests_schema.rb"
    File.open(filename, 'w:utf-8') do |file|
      ActiveRecord::Base.establish_connection(MASTER_DB)
      ActiveRecord::SchemaDumper.dump(ActiveRecord::Base.connection, file)
    end
  end

  desc 'Create database'
  task :test_create => [:environment, :load_config] do
    ActiveRecord::Base.connection.execute("CREATE DATABASE #{MASTER_DB['database']}")
  end

  desc 'Prepare config for codeship'
  task :test_codeship_prepare do
    config = YAML.load(ERB.new(File.read("config/shards.yml.example")).result).to_yaml
    filename = "#{Rails.root}/config/shards.yml"
    File.open(filename, 'w:utf-8') do |file|
      file.write(config)
    end
  end


end

[noff]

Any updates?

[mtrewartha]

@noff, did you ever find anything that was causing this for you or that fixed it?

[noff]

No. It's not fixed and I can't do experiments on production server.

[mtrewartha]

@noff, we wasted many hours on this yesterday. Thankfully, we ended up finding that we were missing the shards key in the YML file. So, instead of the shards.yml you have above, use this:

octopus:
  environments:
    - test
  shards:
    test:
      shard_0:
        database: rdb_shard_0_test
        host: localhost
        username: <%= ENV['PG_USER'] %>
        password: <%= ENV['PG_PASSWORD'] %>
        adapter: postgresql
        encoding: unicode
        pool: 5
        timeout: 5000
        port: 5432
      shard_1:
        database: rdb_shard_1_test
        host: localhost
        username: <%= ENV['PG_USER'] %>
        password: <%= ENV['PG_PASSWORD'] %>
        adapter: postgresql
        encoding: unicode
        pool: 5
        timeout: 5000
        port: 5432

We also had the following in a lib/tasks/db.rake file:

Rake::Task["db:load_config"].enhance [:environment]

in order to ensure that the Rails environment was reloaded between rake tasks run in serial (e.g. rake db:drop db:create db:migrate) on the command line. Once we removed that and fixed the config above, everything was golden!

[mtrewartha]

EDIT: We were confused by a few different configs we had seen for this, but it turns out that you do not needs shards in there.

@thiagopradi Can we get the wiki page changed for that? The example shards.yml file is missing the shards key, as I mentioned above. It caused a ton of headache for us before we finally found it.

[noff]

If I add "shards" option to config, I receive this:

RuntimeError - Nonexistent Shard Name: shard_0:
  ar-octopus (0.8.6) lib/octopus/proxy.rb:140:in `current_shard='
  ar-octopus (0.8.6) lib/octopus/proxy.rb:507:in `using_shard'
  ar-octopus (0.8.6) lib/octopus/proxy.rb:245:in `block in run_queries_on_shard'
  ar-octopus (0.8.6) lib/octopus/proxy.rb:492:in `keeping_connection_proxy'
  ar-octopus (0.8.6) lib/octopus/proxy.rb:244:in `run_queries_on_shard'
  ar-octopus (0.8.6) lib/octopus/shard_tracking.rb:30:in `run_on_shard'
  ar-octopus (0.8.6) lib/octopus/scope_proxy.rb:43:in `method_missing'
...

It causes in the first line of code with "using" statement:

MyModel.using("shard_0")...

[mtrewartha]

Yeah, sorry, we actually just found out that adding shards does not fix it. It was definitely the Rake::Task["db:load_config"].enhance [:environment] bit that was hosing us. We also ended up finding out that we had some custom connection reaping and pool size settings that were being done in an initializer so that Heroku wouldn't wipe them out:

Rails.application.config.after_initialize do
  ActiveRecord::Base.connection_pool.disconnect!

  ActiveSupport.on_load(:active_record) do
    config = ActiveRecord::Base.configurations[Rails.env] || Rails.application.config.database_configuration[Rails.env]
    config['reaping_frequency'] = ENV['DB_REAP_FREQ'] || 10 # seconds
    config['pool']              = ENV['DB_POOL'] || 5
    ActiveRecord::Base.establish_connection(config)
  end
end

Removing that bit and moving the reaping_frequency and pool settings into an octopus.rb initializer instead helped us.

[octalmage]

I'm struggling with this now, just trying to run rspec. I tracked it down to this:

ActiveRecord::Migration.maintain_test_schema!

When this runs we get the PG::ObjectInUse: ERROR: cannot drop the currently open database error.

Any ideas?

[octalmage]

Figured it out right after I posted! We were missing the default migration group in the shards.yml :

octopus:
  replicated: true
  fully_replicated: false
  default_migration_group: master
  environments:

Found here:

https://github.com/thiagopradi/octopus#migrations

[octalmage]

Not fixed, the environments options was messed up with made it seem like it was working. Still trying to figure it out.

[pedrocarmona]

I also have error locally for rake db:drop, when using the instructions of octopus heroku follower

So far I found that active record postgres establish_master_connection is establishing also proxy connections ActiveRecord::Base.connection_proxy.connected?, and therefor establishing multiple connections on that database.

[gabrielqueiroz]

Did anyone figure out a way to fix this? I am facing the same problem now.