This project is archived and is in readonly mode.

#4181 ✓invalid

databases.rake forcing database and schema with postgres

Reported by Shak | March 15th, 2010 @ 05:10 PM | in 3.0.2

When trying to db:test:prepare or load the following bit of code is called:

def drop_database(config)
  case config['adapter']
  when 'mysql'
    ActiveRecord::Base.connection.drop_database config['database']
  when /^sqlite/
    FileUtils.rm(File.join(RAILS_ROOT, config['database']))
  when 'postgresql'
    ActiveRecord::Base.establish_connection(config.merge('database' => 'postgres', 'schema_search_path' => 'public'))
    ActiveRecord::Base.connection.drop_database config['database']

Thus when using postgresql the connection forces a postgres database and public schema, even though these may be defined in config (which they are). The same code appears in create_database.

This makes it impossible to call some rake tasks since the correct db and schema is overwritten by these constants when the task is being run.

I'm guessing that simply removing this merge call will fix the issue, but I'm new to Rails and not sure what the wider implications are, or why this has been put in in the first place.

Comments and changes to this ticket

  • Shak

    Shak March 15th, 2010 @ 05:57 PM

    It seems that this is based on a much larger assumption about how your postgresql database is structured, assumptions which do not apply in general development and migration etc (ownership of databases etc).

    I've decided to just manage initialisation myself, although I do think that the test database should have the same requirements as prod and dev.

  • Shak

    Shak March 15th, 2010 @ 11:56 PM

    For anyone interested, more info and workarounds here:

  • Rizwan Reza

    Rizwan Reza March 29th, 2010 @ 07:38 AM

    • State changed from “new” to “open”
    • Assigned user set to “Rizwan Reza”
    • Milestone cleared.
  • Harold Giménez

    Harold Giménez April 1st, 2010 @ 01:01 PM

    I don't think this is a problem or bug at all. Every postgres database cluster will have three databases by default: postgres, template0 and template1. When doing administrative work, you probably do want to connect to the postgres database (or one of the templates) just so you can drop and create other databases. In other words, if you connect to my_app_development and try to drop my_app_development, postgres will probably complaint.

  • Rizwan Reza

    Rizwan Reza April 2nd, 2010 @ 10:44 AM

    Shak and Harold, can we close this ticket then?

  • Harold Giménez

    Harold Giménez April 2nd, 2010 @ 12:46 PM

    My vote is to close it. Although I'd like to hear from Shak if there are examples of this actually causing a problem? Some rake task that you could not run because of this?

    To add to the story, I think this is necessary in order for rake db:create to work. Otherwise the postgres adapter will try to connect to my_app_development in order to create my_app_development. You see the chicken and egg there? Therefore you need to connect to the admin DB first in order to create your app's databases.

    But as I said, I'd really like to hear how this breaks.

  • Rizwan Reza

    Rizwan Reza April 2nd, 2010 @ 01:46 PM

    • State changed from “open” to “invalid”

    If Shak needs, we can open this up again. Thanks.

  • Shak

    Shak April 8th, 2010 @ 11:18 AM

    Everything works provided you have a single schema per single database per single Rails app. It's probably just my usage, but my database can have other related schemas in it, those not managed by Rails. These are required since Postgres doesn't allow cross DB queries. As such my rails user in production shouldn't be allowed to drop anything other than its own schema, and this should be reflected in dev and test too. This is why the default rake tasks do not work for me.

    Also generally, I'm not sure it's proper to allow an application user access to the any database other than its own, but I will concede if Rails' opinion differs on that.

    Based on the links above I've patched my own database.rake to drop and create schemas instead of databases and it seems to be working quite well. I'm happy to contribute this, but as a novice Rails user I have no idea how to or whether it breaks anything else.

    I've recognised this as non-Rails usage so this ticket doesn't need to be reopened, however I do think switching to a schema aware model would bring added benefits to those using Postgres as their storage.

  • Jeremy Kemper

    Jeremy Kemper October 15th, 2010 @ 11:01 PM

    • Milestone set to 3.0.2
    • Importance changed from “” to “Low”

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

<h2 style="font-size: 14px">Tickets have moved to Github</h2>

The new ticket tracker is available at <a href=""></a>

People watching this ticket