Cucumber and RSpec testing with zeus: Postgres is being accessed by other users

Question

In my Rails 3.2.13 app, I'm using Zeus. In the test environment I use PostgreSQL. When I run Cucumber and then RSpec (or the other way around), 9 out of 10 times I get the message:

PG::Error: ERROR:  database "bp_test" is being accessed by other users
DETAIL:  There are 1 other session(s) using the database.
: DROP DATABASE IF EXISTS "bp_test"

Tasks: TOP => db:test:load => db:test:purge
(See full trace by running task with --trace)

It takes a whole non-deterministic circus of trying to kill database connections to get this to work again, as described here. But this does not always work and is a big hassle as well. There must be a better solution for this. Does anyone know?

Ogdoad

It might be connected to: #16665826 – Pr

Yeah, I already check that. I don't have autorun anywher in my project. – Ogdoad

The fact that it works sometimes suggests that the postgres session shutdown is being done, but is taking place asynchronously and the zeus-related speedup for running your next test is enough to prevent the completion under normal circumstances. If this is the case, however, it's hard to believe there isn't more having been written about it. I don't know if it's related, but you might want to look at platformonrails.wordpress.com/2013/04/06/… – Ambassadress

It is amazingly arbitrary. I just tried the custom_plan.rb from the blog post and everything ran fine... I could run Cucumber, RSpec and everything. Now I just rebooted my MacBook and I tried running zeus rake spec and I got the error again. Even though this was the first zeus command after the reboot. Now I'm completely puzzled. Anyone? – Ogdoad

Answer 1

Inspired by this answer, we created the following database.rake file. Where the original answer worked only for PostgreSQL 9.1, this one is modified to work for PostgreSQL 9.2 as well. The mechanism is not the prettiest: when the 9.1 command fails, it simply executes the 9.2 command. But the most important thing: it works!

#{Rails.root}/lib/tasks/databases.rake
# monkey patch ActiveRecord to avoid There are n other session(s) using the database.
def drop_database(config)
  case config['adapter']
  when /mysql/
    ActiveRecord::Base.establish_connection(config)
    ActiveRecord::Base.connection.drop_database config['database']
  when /sqlite/
    require 'pathname'
    path = Pathname.new(config['database'])
    file = path.absolute? ? path.to_s : File.join(Rails.root, path)

    FileUtils.rm(file)
  when /postgresql/
    begin
      ActiveRecord::Base.establish_connection(config.merge('database' => 'postgres', 'schema_search_path' => 'public'))
      ActiveRecord::Base.connection.select_all("select * from pg_stat_activity order by procpid;").each do |x|
        if config['database'] == x['datname'] && x['current_query'] =~ /<IDLE>/
          ActiveRecord::Base.connection.execute("select pg_terminate_backend(#{x['procpid']})")
        end
      end
      ActiveRecord::Base.connection.drop_database config['database']
    rescue # in PG 9.2 column procpid was renamed pid and the query status is checked not using 'current_query' but using 'state'
      ActiveRecord::Base.establish_connection(config.merge('database' => 'postgres', 'schema_search_path' => 'public'))
      ActiveRecord::Base.connection.select_all("select * from pg_stat_activity order by pid;").each do |x|
        if config['database'] == x['datname'] && x['state'] =~ /idle/
          ActiveRecord::Base.connection.execute("select pg_terminate_backend(#{x['pid']})")
        end
      end
      ActiveRecord::Base.connection.drop_database config['database']
    end
  end
end

Ogdoad

Answer 2

namespace :db do

  desc 'Clear the database'
  task :clear_db => :environment do |t,args|
    ActiveRecord::Base.establish_connection
    ActiveRecord::Base.connection.tables.each do |table|
      next if table == 'schema_migrations'
      ActiveRecord::Base.connection.execute("TRUNCATE #{table}")
    end
  end

  desc 'Delete all tables (but not the database)'
  task :drop_schema => :environment do |t,args|
    ActiveRecord::Base.establish_connection
    ActiveRecord::Base.connection.execute("DROP SCHEMA public CASCADE")
    ActiveRecord::Base.connection.execute("CREATE SCHEMA public")
    ActiveRecord::Base.connection.execute("GRANT ALL ON SCHEMA public TO postgres")
    ActiveRecord::Base.connection.execute("GRANT ALL ON SCHEMA public TO public")
    ActiveRecord::Base.connection.execute("COMMENT ON SCHEMA public IS 'standard public schema'")
  end

  desc 'Recreate the database and seed'
  task :redo_db => :environment do |t,args|
    # Executes the dependencies, but only once
    Rake::Task["db:drop_schema"].invoke
    Rake::Task["db:migrate"].invoke
    Rake::Task["db:migrate:status"].invoke
    Rake::Task["db:structure:dump"].invoke
    Rake::Task["db:seed"].invoke
  end

end

Twospot