Migrations work fine.. db:setup/reset from the schema.rb file does not work.

[Altonymous]

It appears as if the SQL being generated by the schema.rb file is very different from the SQL generated from the migrations. Because of this I have to constantly rollback all migrations before committing to make sure that the schema.rb file is never used for anything other than database creation.

[ronen]

@Altonymous The schema.rb file normally works fine. Will need more details to understand what problem you're having.

[Altonymous]

@ronen,

Sure, I'll try and give more details.

I am getting this error on my output when trying to do a rake db:reset

PG::InvalidForeignKey: ERROR:  there is no unique constraint matching given keys for referenced table "users"
: CREATE TABLE "users" ("id" uuid DEFAULT uuid_generate_v4() NOT NULL, "username" character varying(255) DEFAULT '', "email" character varying(255) DEFAULT '' NOT NULL, "encrypted_password" character varying(255) DEFAULT '', "reset_password_token" character varying(255), "reset_password_sent_at" timestamp, "remember_created_at" timestamp, "sign_in_count" integer DEFAULT 0, "current_sign_in_at" timestamp, "last_sign_in_at" timestamp, "current_sign_in_ip" character varying(255), "last_sign_in_ip" character varying(255), "failed_attempts" integer DEFAULT 0, "unlock_token" character varying(255), "locked_at" timestamp, "confirmation_token" character varying(255), "confirmed_at" timestamp, "confirmation_sent_at" timestamp, "unconfirmed_email" character varying(255), "invitation_token" character varying(255), "invitation_created_at" timestamp, "invitation_sent_at" timestamp, "invitation_accepted_at" timestamp, "invitation_limit" integer, "invited_by_id" uuid, "invited_by_type" character varying(255), "authentication_token" character varying(255), "last_changed_by_id" uuid, CONSTRAINT fk_users_invited_by_id FOREIGN KEY ("invited_by_id") REFERENCES "users" ("id") ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT fk_users_last_changed_by_id FOREIGN KEY ("last_changed_by_id") REFERENCES "users" ("id") ON UPDATE NO ACTION ON DELETE NO ACTION)

This is my migration...

class DeviseCreateUsers < ActiveRecord::Migration
  def change
    create_table :users, id: :uuid do |t|
      ## Database authenticatable
      t.string :username, default: '', index: { case_sensitive: false, unique: true }
      t.string :email, null: false, default: '', index: { case_sensitive: false, unique: true }
      t.string :encrypted_password, default: ''

      ## Recoverable
      t.string :reset_password_token, index: { unique: true }
      t.datetime :reset_password_sent_at

      ## Rememberable
      t.datetime :remember_created_at

      ## Trackable
      t.integer :sign_in_count, default: 0
      t.datetime :current_sign_in_at
      t.datetime :last_sign_in_at
      t.string :current_sign_in_ip
      t.string :last_sign_in_ip

      ## Lockable
      t.integer :failed_attempts, default: 0 # Only if lock strategy is :failed_attempts
      t.string :unlock_token, index: { unique: true } # Only if unlock strategy is :email or :both
      t.datetime :locked_at

      ## Confirmable
      t.string :confirmation_token, index: { unique: true }
      t.datetime :confirmed_at
      t.datetime :confirmation_sent_at
      t.string :unconfirmed_email

      ## Invitable
      t.string :invitation_token, index: { unique: true }
      t.datetime :invitation_created_at
      t.datetime :invitation_sent_at
      t.datetime :invitation_accepted_at
      t.integer :invitation_limit
      t.uuid :invited_by_id, foreign_key: { references: :users }
      t.string :invited_by_type

      ## Token authenticatable
      t.string :authentication_token, index: { unique: true }

      t.uuid :last_changed_by_id, foreign_key: { references: :users }
    end
  end
end

[Altonymous]

If I switch to using the structure.sql it works dumps and imports fine.

*The pg_dump isn't exporting the Extensions for some reason, but the rest works.

[Altonymous]

The schema.rb file looks like this...

create_table "users", id: false, force: true do |t|
  t.uuid     "id", :default => { :expr => "uuid_generate_v4()" },                                  null: false
  t.string   "username",               default: ""
  t.string   "email",                  default: "", null: false
  t.string   "encrypted_password",     default: ""
  t.string   "reset_password_token"
  t.datetime "reset_password_sent_at"
  t.datetime "remember_created_at"
  t.integer  "sign_in_count",          default: 0
  t.datetime "current_sign_in_at"
  t.datetime "last_sign_in_at"
  t.string   "current_sign_in_ip"
  t.string   "last_sign_in_ip"
  t.integer  "failed_attempts",        default: 0
  t.string   "unlock_token"
  t.datetime "locked_at"
  t.string   "confirmation_token"
  t.datetime "confirmed_at"
  t.datetime "confirmation_sent_at"
  t.string   "unconfirmed_email"
  t.string   "invitation_token"
  t.datetime "invitation_created_at"
  t.datetime "invitation_sent_at"
  t.datetime "invitation_accepted_at"
  t.integer  "invitation_limit"
  t.uuid     "invited_by_id"
  t.string   "invited_by_type"
  t.string   "authentication_token"
  t.uuid     "last_changed_by_id"
  t.index ["authentication_token"], :name => "index_users_on_authentication_token", :unique => true, :order => {"authentication_token" => :asc}
  t.index ["confirmation_token"], :name => "index_users_on_confirmation_token", :unique => true, :order => {"confirmation_token" => :asc}
  t.index ["email"], :name => "index_users_on_email", :unique => true, :case_sensitive => false, :order => {"email" => :asc}
  t.index ["invitation_token"], :name => "index_users_on_invitation_token", :unique => true, :order => {"invitation_token" => :asc}
  t.index ["reset_password_token"], :name => "index_users_on_reset_password_token", :unique => true, :order => {"reset_password_token" => :asc}
  t.index ["unlock_token"], :name => "index_users_on_unlock_token", :unique => true, :order => {"unlock_token" => :asc}
  t.index ["username"], :name => "index_users_on_username", :unique => true, :case_sensitive => false, :order => {"username" => :asc}
end

[Altonymous]

This is the DDL reported by the database after rake db:migrate is run...

CREATE TABLE "core"."users" (
  "id" uuid NOT NULL DEFAULT uuid_generate_v4(),
  "username" varchar(255) DEFAULT ''::character varying,
  "email" varchar(255) NOT NULL DEFAULT ''::character varying,
  "encrypted_password" varchar(255) DEFAULT ''::character varying,
  "reset_password_token" varchar(255),
  "reset_password_sent_at" timestamp(6) NULL,
  "remember_created_at" timestamp(6) NULL,
  "sign_in_count" int4 DEFAULT 0,
  "current_sign_in_at" timestamp(6) NULL,
  "last_sign_in_at" timestamp(6) NULL,
  "current_sign_in_ip" varchar(255),
  "last_sign_in_ip" varchar(255),
  "failed_attempts" int4 DEFAULT 0,
  "unlock_token" varchar(255),
  "locked_at" timestamp(6) NULL,
  "confirmation_token" varchar(255),
  "confirmed_at" timestamp(6) NULL,
  "confirmation_sent_at" timestamp(6) NULL,
  "unconfirmed_email" varchar(255),
  "invitation_token" varchar(255),
  "invitation_created_at" timestamp(6) NULL,
  "invitation_sent_at" timestamp(6) NULL,
  "invitation_accepted_at" timestamp(6) NULL,
  "invitation_limit" int4,
  "invited_by_id" uuid,
  "invited_by_type" varchar(255),
  "authentication_token" varchar(255),
  "last_changed_by_id" uuid,
  CONSTRAINT "users_pkey" PRIMARY KEY ("id") NOT DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT "fk_users_invited_by_id" FOREIGN KEY ("invited_by_id") REFERENCES "core"."users" ("id") ON UPDATE NO ACTION ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT "fk_users_last_changed_by_id" FOREIGN KEY ("last_changed_by_id") REFERENCES "core"."users" ("id") ON UPDATE NO ACTION ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE
)
WITH (OIDS=FALSE);
ALTER TABLE "core"."users" OWNER TO "console";
CREATE INDEX "fk__users_invited_by_id" ON "core"."users" USING btree(invited_by_id ASC NULLS LAST);
CREATE INDEX "fk__users_last_changed_by_id" ON "core"."users" USING btree(last_changed_by_id ASC NULLS LAST);
CREATE UNIQUE INDEX "index_users_on_authentication_token" ON "core"."users" USING btree(authentication_token ASC NULLS LAST);
CREATE UNIQUE INDEX "index_users_on_confirmation_token" ON "core"."users" USING btree(confirmation_token ASC NULLS LAST);
CREATE UNIQUE INDEX "index_users_on_email" ON "core"."users" USING btree(lower(email::text) ASC NULLS LAST);
CREATE UNIQUE INDEX "index_users_on_invitation_token" ON "core"."users" USING btree(invitation_token ASC NULLS LAST);
CREATE UNIQUE INDEX "index_users_on_reset_password_token" ON "core"."users" USING btree(reset_password_token ASC NULLS LAST);
CREATE UNIQUE INDEX "index_users_on_unlock_token" ON "core"."users" USING btree(unlock_token ASC NULLS LAST);
CREATE UNIQUE INDEX "index_users_on_username" ON "core"."users" USING btree(lower(username::text) ASC NULLS LAST);

[Altonymous]

It looks like it's taking the id: :uuid and properly creating it as a uuid column, but not marking it as the primary_key.

[tovodeverett]

It looks to me that the problem is in Rails 4.0.0.

I created a generic Rails 4.0.0 app with postgresql as the database.

I create the following migration:

class CreateUsers < ActiveRecord::Migration
  def change
    enable_extension 'uuid-ossp'
    create_table :users, id: :uuid do |t|
    end
  end
end

If I use db:migrate to create the database, I get the following out of \d users in psql:

                Table "public.users"
 Column | Type |              Modifiers
--------+------+-------------------------------------
 id     | uuid | not null default uuid_generate_v4()
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)

If I then use db:reset to recreate the database, I get the following out of \d users in psql:

   Table "public.users"
 Column | Type | Modifiers
--------+------+-----------
 id     | uuid | not null

Note that the PRIMARY KEY index disappears! I'm seeing this without schema_plus in the Gemfile. I can't seem to find any existing tickets regarding this in rails/rails, though.

[tovodeverett]

See the tail end of rails/rails#11016. It looks like it's fixed in 4.1.0beta and I'm currently trying to test 4-0-stable.

[tovodeverett]

It looks like it works with 4-0-stable. I don't know what I'm doing, but this is how I got 4-0-stable to work from my Gemfile:

gem 'rails', '4.0.0', github: "rails/rails", branch: '4-0-stable'
gem 'sass-rails', '4.0.0', github: 'rails/sass-rails'
gem 'coffee-rails', '4.0.0', github: 'rails/coffee-rails'

Both sass-rails and coffee-rails have dependencies on 4.0.0.rc2 that are fixed in their master but not in the released gem. See rails/coffee-rails#48 for an open issue.

I did a quick test with the following migration using 4-0-stable and schema_plus and it appeared to work:

class CreateUsers < ActiveRecord::Migration
  def change
    enable_extension 'uuid-ossp'
    create_table :users, id: :uuid do |t|
      t.uuid :invited_by_id, foreign_key: { references: :users }
    end
  end
end

[Altonymous]

The migration works fine. It's trying to use the db:reset or db:setup tasks that the db:migrate generates that is a problem. I came to the same conclusion as you and just opened a pull request against 4-0-stable for rails.

rails/rails#12039

[tovodeverett]

I know, but when I tried with 4-0-stable, I was able to successfully do a db:reset. Under 4-0-stable, the generated schema.rb was:

ActiveRecord::Schema.define(version: 20130827025617) do

  # These are extensions that must be enabled in order to support this database
  enable_extension "plpgsql"
  enable_extension "uuid-ossp"

  create_table "users", id: :uuid, force: true do |t|
    t.uuid "invited_by_id"
    t.index ["invited_by_id"], :name => "fk__users_invited_by_id", :order => {"invited_by_id" => :asc}
    t.foreign_key ["invited_by_id"], "users", ["id"], :on_update => :no_action, :on_delete => :no_action, :name => "fk_users_invited_by_id"
  end

end

Note that under 4-0-stable, the schema.rb file is using id: :uuid instead of manually generating the id column.

[Altonymous]

I'll double check that, because I didn't think it was.. which is why I went through the trouble of chasing down that issue. I can always point back to the 4-0-stable branch on the rails repo instead of my fork and see if it does indeed work. If it does, then that would be great, I hate maintaining a fork. :P

[tovodeverett]

Note that because the core issue is in the schema dumping code, simply switching to 4-0-stable and then attempting a db:reset wouldn't be sufficient. It takes triggering the generation of schema.rb after switching to 4-0-stable to demonstrate that db:reset no longer fails.