4.0.0.rc2, Postgres UUID primary keys: db:schema:load doesn't create the table properly; db:migrate does #11016

turboladen · 2013-06-19T18:30:21Z

I'm using Rails 4.0.0.rc2 with Postgres 9.2.4 and trying to use the new UUID-as-primary-key feature, but I'm running into some inconsistencies with rake tasks. I'd initially posted the problem here: http://stackoverflow.com/questions/17178497/rspec-rails-4-postgres-uuid-primary-keys-id-is-null-with-rake-rspec-but-i, but in a nutshell the problem is this: when I use rake db:migrate to create the table with the UUID primary keys, I can then create new model objects just fine; when I use rake db:schema:load, I cannot then create new model objects.

My migration:

class CreateGrowers < ActiveRecord::Migration
  def change
    enable_extension 'uuid-ossp'

    create_table :growers, id: :uuid do |t|
      t.string :name
      t.string :code

      t.timestamps
    end
  end
end

...creates this schema:

ActiveRecord::Schema.define(version: 20130613174601) do
  # These are extensions that must be enabled in order to support this database
  enable_extension "plpgsql"
  enable_extension "uuid-ossp"

  create_table "growers", id: false, force: true do |t|
    t.uuid     "id",         null: false
    t.string   "name"
    t.string   "code"
    t.datetime "created_at"
    t.datetime "updated_at"
  end
end

When I rake db:drop && rake db:create && rake db:migrate, I see:

==  CreateGrowers: migrating ==================================================
-- enable_extension("uuid-ossp")
   -> 0.0050s
-- create_table(:growers, {:id=>:uuid})
   -> 0.0052s
==  CreateGrowers: migrated (0.0103s) =========================================

...and thus:

irb> Grower.create
   (0.1ms)  BEGIN
  SQL (5.4ms)  INSERT INTO "growers" ("created_at", "updated_at") VALUES ($1, $2) RETURNING "id"  [["created_at", Wed, 19 Jun 2013 18:24:00 UTC +00:00], ["updated_at", Wed, 19 Jun 2013 18:24:00 UTC +00:00]]
   (0.4ms)  COMMIT
=> #<Grower id: "2dad7361-8df0-4ea9-89d1-ccab5ee2b122", name: nil, code: nil, created_at: "2013-06-19 18:24:00", updated_at: "2013-06-19 18:24:00">

(horray!) ...yet when I rake db:drop && rake db:create && rake db:schema:load, I see:

-- enable_extension("plpgsql")
   -> 0.0161s
-- enable_extension("uuid-ossp")
   -> 0.0063s
-- create_table("growers", {:id=>false, :force=>true})
   -> 0.0049s
-- initialize_schema_migrations_table()
   -> 0.0062s

Notice how the growers table's :id is created using { :id => false, :force => true } instead of { :id => :uuid }. ...and consequently, when I try to create a Grower object, I get:

irb> Grower.create
   (0.1ms)  BEGIN
  SQL (3.7ms)  INSERT INTO "growers" ("created_at", "updated_at") VALUES ($1, $2)  [["created_at", Wed, 19 Jun 2013 18:20:39 UTC +00:00], ["updated_at", Wed, 19 Jun 2013 18:20:39 UTC +00:00]]
PG::Error: ERROR:  null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, null, null, 2013-06-19 18:20:39.798393, 2013-06-19 18:20:39.798393).
: INSERT INTO "growers" ("created_at", "updated_at") VALUES ($1, $2)
   (0.1ms)  ROLLBACK
ActiveRecord::StatementInvalid: PG::Error: ERROR:  null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, null, null, 2013-06-19 18:20:39.798393, 2013-06-19 18:20:39.798393).
: INSERT INTO "growers" ("created_at", "updated_at") VALUES ($1, $2)
    from /Users/sloveless/.rbenv/versions/2.0.0-p195/lib/ruby/gems/2.0.0/gems/activerecord-4.0.0.rc2/lib/active_record/connection_adapters/postgresql_adapter.rb:780:in `get_last_result'
    from /Users/sloveless/.rbenv/versions/2.0.0-p195/lib/ruby/gems/2.0.0/gems/activerecord-4.0.0.rc2/lib/active_record/connection_adapters/postgresql_adapter.rb:780:in `exec_cache'
    from /Users/sloveless/.rbenv/versions/2.0.0-p195/lib/ruby/gems/2.0.0/gems/activerecord-4.0.0.rc2/lib/active_record/connection_adapters/postgresql/database_statements.rb:139:in `block in exec_query'
    from /Users/sloveless/.rbenv/versions/2.0.0-p195/lib/ruby/gems/2.0.0/gems/activerecord-4.0.0.rc2/lib/active_record/connection_adapters/abstract_adapter.rb:425:in `block in log'
    from /Users/sloveless/.rbenv/versions/2.0.0-p195/lib/ruby/gems/2.0.0/gems/activesupport-4.0.0.rc2/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
    from /Users/sloveless/.rbenv/versions/2.0.0-p195/lib/ruby/gems/2.0.0/gems/activerecord-4.0.0.rc2/lib/active_record/connection_adapters/abstract_adapter.rb:420:in `log'
    from /Users/sloveless/.rbenv/versions/2.0.0-p195/lib/ruby/gems/2.0.0/gems/activerecord-4.0.0.rc2/lib/active_record/connection_adapters/postgresql/database_statements.rb:137:in `exec_query'
    from /Users/sloveless/.rbenv/versions/2.0.0-p195/lib/ruby/gems/2.0.0/gems/activerecord-4.0.0.rc2/lib/active_record/connection_adapters/postgresql/database_statements.rb:183:in `exec_insert'
    from /Users/sloveless/.rbenv/versions/2.0.0-p195/lib/ruby/gems/2.0.0/gems/activerecord-4.0.0.rc2/lib/active_record/connection_adapters/abstract/database_statements.rb:96:in `insert'
    from /Users/sloveless/.rbenv/versions/2.0.0-p195/lib/ruby/gems/2.0.0/gems/activerecord-4.0.0.rc2/lib/active_record/connection_adapters/abstract/query_cache.rb:14:in `insert'
    from /Users/sloveless/.rbenv/versions/2.0.0-p195/lib/ruby/gems/2.0.0/gems/activerecord-4.0.0.rc2/lib/active_record/relation.rb:76:in `insert'
    from /Users/sloveless/.rbenv/versions/2.0.0-p195/lib/ruby/gems/2.0.0/gems/activerecord-4.0.0.rc2/lib/active_record/persistence.rb:498:in `create_record'
    from /Users/sloveless/.rbenv/versions/2.0.0-p195/lib/ruby/gems/2.0.0/gems/activerecord-4.0.0.rc2/lib/active_record/attribute_methods/dirty.rb:78:in `create_record'
    from /Users/sloveless/.rbenv/versions/2.0.0-p195/lib/ruby/gems/2.0.0/gems/activerecord-4.0.0.rc2/lib/active_record/callbacks.rb:303:in `block in create_record'
    from /Users/sloveless/.rbenv/versions/2.0.0-p195/lib/ruby/gems/2.0.0/gems/activesupport-4.0.0.rc2/lib/active_support/callbacks.rb:373:in `_run__840981692304956113__create__callbacks'
    from /Users/sloveless/.rbenv/versions/2.0.0-p195/lib/ruby/gems/2.0.0/gems/activesupport-4.0.0.rc2/lib/active_support/callbacks.rb:80:in `run_callbacks'
... 11 levels...
    from /Users/sloveless/.rbenv/versions/2.0.0-p195/lib/ruby/gems/2.0.0/gems/activerecord-4.0.0.rc2/lib/active_record/transactions.rb:326:in `block in with_transaction_returning_status'
    from /Users/sloveless/.rbenv/versions/2.0.0-p195/lib/ruby/gems/2.0.0/gems/activerecord-4.0.0.rc2/lib/active_record/connection_adapters/abstract/database_statements.rb:202:in `block in transaction'
    from /Users/sloveless/.rbenv/versions/2.0.0-p195/lib/ruby/gems/2.0.0/gems/activerecord-4.0.0.rc2/lib/active_record/connection_adapters/abstract/database_statements.rb:210:in `within_new_transaction'
    from /Users/sloveless/.rbenv/versions/2.0.0-p195/lib/ruby/gems/2.0.0/gems/activerecord-4.0.0.rc2/lib/active_record/connection_adapters/abstract/database_statements.rb:202:in `transaction'
    from /Users/sloveless/.rbenv/versions/2.0.0-p195/lib/ruby/gems/2.0.0/gems/activerecord-4.0.0.rc2/lib/active_record/transactions.rb:209:in `transaction'
    from /Users/sloveless/.rbenv/versions/2.0.0-p195/lib/ruby/gems/2.0.0/gems/activerecord-4.0.0.rc2/lib/active_record/transactions.rb:323:in `with_transaction_returning_status'
    from /Users/sloveless/.rbenv/versions/2.0.0-p195/lib/ruby/gems/2.0.0/gems/activerecord-4.0.0.rc2/lib/active_record/transactions.rb:270:in `block in save'
    from /Users/sloveless/.rbenv/versions/2.0.0-p195/lib/ruby/gems/2.0.0/gems/activerecord-4.0.0.rc2/lib/active_record/transactions.rb:281:in `rollback_active_record_state!'
    from /Users/sloveless/.rbenv/versions/2.0.0-p195/lib/ruby/gems/2.0.0/gems/activerecord-4.0.0.rc2/lib/active_record/transactions.rb:269:in `save'
    from /Users/sloveless/.rbenv/versions/2.0.0-p195/lib/ruby/gems/2.0.0/gems/activerecord-4.0.0.rc2/lib/active_record/persistence.rb:37:in `create'
    from (irb):1
    from /Users/sloveless/.rbenv/versions/2.0.0-p195/lib/ruby/gems/2.0.0/gems/railties-4.0.0.rc2/lib/rails/commands/console.rb:90:in `start'
    from /Users/sloveless/.rbenv/versions/2.0.0-p195/lib/ruby/gems/2.0.0/gems/railties-4.0.0.rc2/lib/rails/commands/console.rb:9:in `start'
    from /Users/sloveless/.rbenv/versions/2.0.0-p195/lib/ruby/gems/2.0.0/gems/railties-4.0.0.rc2/lib/rails/commands.rb:66:in `<top (required)>'
    from bin/rails:4:in `require'
    from bin/rails:4:in `<main>'

This seems to make sense based on the given schema (I think?), and thus it seems like the schema.rb that's generated is incorrect.

Initially, I encountered this because I'm using RSpec and rake spec uses the db:schema:load task, which was thus causing much head-to-wall-banging as I couldn't figure out why things seemed fine until I ran my tests using Rake. I can continue running the tests without using rake, but it'd be really nice to use that again.

The text was updated successfully, but these errors were encountered:

decasia · 2013-06-25T17:42:32Z

I'm seeing this issue too, and I just updated to Rails 4.0.0. It's a bit curious because there is also this issue:
#10451
which seems to have led to a successful commit on the 4.0-stable branch that patched the issue.
4d3eee6

Unfortunately, now that I check this out, it doesn't look like it made it into the 4.0.0 release; the released version in 4.0.0 still doesn't have the fix:

https://github.com/rails/rails/blob/4-0-0/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb

I'm guessing that this will get fixed in a bug release relatively soon. Until then, I guess we have to use db:migrate to set up our test db.

turboladen · 2013-06-26T01:01:41Z

Whew! Glad to know I'm not the only one here! Thanks for the info, @decasia!

steveklabnik · 2013-06-26T19:24:05Z

Yes, that fix will go out in 4.0.1. Thanks so much for digging into it, @decasia

joshuacronemeyer · 2013-10-18T23:54:33Z

Thanks for all the detail in here guys. Great work. And here is my workaround until 4.0.1 drops https://gist.github.com/joshuacronemeyer/7049936 in case it helps anybody else.

johanb · 2013-11-25T15:21:40Z

I'm still having this issue in 4.0.1. Didn't it make the cut to be included ?

For the record @joshuacronemeyer's solution works.

dmathieu · 2013-12-10T08:23:25Z

No, 4d3eee6 doesn't seem to be included in 4.0.1, nor in 4.0.2 (since it was a security release).

turboladen · 2013-12-30T21:45:27Z

@steveklabnik any objection to reopening this since it hasn't gone out in a release yet?

steveklabnik · 2013-12-31T00:17:27Z

It doesn't need to be re-opened, as the fix has landed. I'm not sure why it wasn't in 4.0.1, exactly. @carlosantoniodasilva do you know?

vikks · 2014-01-09T07:22:27Z

Unless i am missing something u can always use structure.sql (rake db:structure:dump) , thereby removing the need for using schema.rb (preferred way when using postgres).

Remove schema.rb and set in config/application.rb

 config.active_record.schema_format :sql

Rspec will use db:structure.load instead of db:schema:load

johanb · 2014-03-21T08:50:51Z

any news on this ?

robin850 · 2014-03-29T16:23:48Z

@johanb : What do you mean by any news please ? It looks like 4d3eee6 has been included in 4.0.4 (for a strange reason this is not shown on the commit page though).

willscripted · 2014-05-11T14:08:44Z

Unless i am missing something u can always use structure.sql (rake db:structure:dump) , thereby removing the need for using schema.rb (preferred way when using postgres).

Worked for me. I'm using pg's uuid_ossp extension to generate uuids on the fly.
@vikks - thank you!

jessmartin mentioned this issue Jun 24, 2013

Some sort of postgres extension crap enroll/enroll#12

Closed

steveklabnik closed this as completed Jun 26, 2013

tovodeverett mentioned this issue Aug 27, 2013

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

Open

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

4.0.0.rc2, Postgres UUID primary keys: db:schema:load doesn't create the table properly; db:migrate does #11016

4.0.0.rc2, Postgres UUID primary keys: db:schema:load doesn't create the table properly; db:migrate does #11016

turboladen commented Jun 19, 2013

decasia commented Jun 25, 2013

turboladen commented Jun 26, 2013

steveklabnik commented Jun 26, 2013

joshuacronemeyer commented Oct 18, 2013

johanb commented Nov 25, 2013

dmathieu commented Dec 10, 2013

turboladen commented Dec 30, 2013

steveklabnik commented Dec 31, 2013

vikks commented Jan 9, 2014

johanb commented Mar 21, 2014

robin850 commented Mar 29, 2014

willscripted commented May 11, 2014

4.0.0.rc2, Postgres UUID primary keys: db:schema:load doesn't create the table properly; db:migrate does #11016

4.0.0.rc2, Postgres UUID primary keys: db:schema:load doesn't create the table properly; db:migrate does #11016

Comments

turboladen commented Jun 19, 2013

decasia commented Jun 25, 2013

turboladen commented Jun 26, 2013

steveklabnik commented Jun 26, 2013

joshuacronemeyer commented Oct 18, 2013

johanb commented Nov 25, 2013

dmathieu commented Dec 10, 2013

turboladen commented Dec 30, 2013

steveklabnik commented Dec 31, 2013

vikks commented Jan 9, 2014

johanb commented Mar 21, 2014

robin850 commented Mar 29, 2014

willscripted commented May 11, 2014