Lighthouse has a new layout. Prefer the old one? Return to the old layout, and switch back any time from the link at the top of each page.

This project is archived and is in readonly mode.

databases.rake forcing database and schema with postgres

#4181

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.establish_connection(config)
    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']
  end
end

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.

Reported by Shak · March 15th, 2010 @ 05:10 PM

State: invalid
Milestone: 3.0.2
Assigned to: Rizwan Reza Rizwan Reza
Importance: Low

Activity

  1. Shak
    Shak

    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.

    March 15th, 2010 @ 05:57 PM

  2. Shak
    Shak

    For anyone interested, more info and workarounds here:

    http://dev.rubyonrails.org/ticket/7665

    March 15th, 2010 @ 11:56 PM

  3. Rizwan Reza
    Rizwan Reza
    • State changed from new to open
    • Assigned user set to Rizwan Reza
    • Milestone cleared.

    March 29th, 2010 @ 07:38 AM

  4. Harold Giménez
    Harold Giménez

    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.

    April 1st, 2010 @ 01:01 PM

  5. Rizwan Reza
    Rizwan Reza

    Shak and Harold, can we close this ticket then?

    April 2nd, 2010 @ 10:44 AM

  6. Harold Giménez
    Harold Giménez

    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.

    April 2nd, 2010 @ 12:46 PM

  7. Rizwan Reza
    Rizwan Reza
    • State changed from open to invalid

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

    April 2nd, 2010 @ 01:46 PM

  8. Shak
    Shak

    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.

    April 8th, 2010 @ 11:18 AM

  9. Jeremy Kemper
    Jeremy Kemper
    • Milestone set to 3.0.2
    • Importance changed from to Low

    October 15th, 2010 @ 11:01 PM