I recently started using postgres more and using schemas in postgres to keep the number of databases under control.
When using schemas in a database and using rails, if two tables in two different schemas have the same name , the output of rake db:schema:dump is incorrect
To reproduce (the following steps assume you have a database railstest with public, dev and test schemas, you may want to edit the database.yml differently if its not the case):
$>rails test
$>cd test
$>vi database.yml
development:
adapter: postgresql
database: railstest
schema_search_path: dev
encoding: utf8
username: railstest
password: test
host: localhost
test:
adapter: postgresql
database: railstest
schema_search_path: test
encoding: utf8
username: railstest
password: test
host: localhost
production:
adapter: postgresql
database: railstest
encoding: utf8
username: railstest
password: test
host: localhost
$>rake db:sessions:create
$>rake db:migrate
$>rake db:schema:dump
With the help of someone on IRC we found that the schema generated a this point is incorrect when using schemas and not when using different database (look at the add_index lines for this schema based dump):
# This file is autogenerated. Instead of editing this file, please use the
# migrations feature of ActiveRecord to incrementally modify your database, and
# then regenerate this schema definition.
ActiveRecord::Schema.define(:version => 1) do
create_table "sessions", :force => true do |t|
t.column "session_id", :string
t.column "data", :text
t.column "updated_at", :datetime
end
add_index "sessions", ["session_id", "session_id"], :name => "index_sessions_on_session_id"
add_index "sessions", ["updated_at", "updated_at"], :name => "index_sessions_on_updated_at"
end
I tracked this down to postgresql_adapter.rb in the indexes method, which does not differenciate between schemas when it queries the postgres system tables to get the indexes names and columns for a given table.
This ticket contains a failing test and a small patch to the indexes method of the postgresql adapter wich makes the test pass.
