Support dynamic postgres column defaults #14300

Closed
searls opened this Issue Mar 6, 2014 · 10 comments

8 participants

@searls

I was surprised to find that I couldn't write the following migration when using postgresql:

class CreateMessages < ActiveRecord::Migration
  def change
    create_table :messages do |t|
      t.timestamp :received_at, default: "now()"
    end
  end
end

Doing so will immediately evaluate now() such that a psql description of the table will report the default is something like default '2014-03-06 16:16:45.108266'::timestamp without time zone.

I worked around this in my migration by using an alter statement immediately after creating the table:

ActiveRecord::Base.connection.execute "ALTER TABLE messages ALTER COLUMN received_at SET DEFAULT now()"

This seemed to solve our issue (the description of the column default was default now() and it worked fine).

However, any rake command dependent on db:schema:load and the schema.rb file are causing exactly the same issue, because db:schema:dump records this into my db/schema.rb file:

t.datetime "received_at", default: "now()"

Because db/schema.rb is generated and checked into source control, I'm struggling to think of any graceful workarounds to the issue.

@davekapp

Would using the schema dump in .sql rather than .rb help? We had to use that to get support for some other PostgreSQL stuff in the past. I'm not sure if this will fix your issue but I figured it was worth asking. :)

@searls

Wow @davekapp, fantastic! That totally did the trick. Great workaround idea. For anyone finding this issue via Google, I worked around the issue by setting in my config/application.rb:

#...
  class Application < Rails::Application
    config.active_record.schema_format = :sql
  end
#...

And running:

$ git rm db/schema.rb
$ bundle exec db:drop
$ bundle exec db:create db:migrate db:test:prepare

I verified it worked by checking that my test database's default for the column was set to now()

@davekapp

Woo hoo, glad it helped! 👍

Friendly warning that .sql schema dumps can scare Heroku. It's not too hard to get around though, here's what we did when I needed to take care of it:

In application.rb:

    # since we're using some PG-specific indexing we want SQL schema dumping
    # note that Heroku doesn't like doing migrations if the format is :sql so in production mode
    # leave it at :ruby (this makes no difference as Heroku can't do anything with the dump anyways,
    # see here http://stackoverflow.com/questions/17300341/migrate-not-working-on-heroku)
    if Rails.env == "production"
      config.active_record.schema_format = :ruby
    else
      config.active_record.schema_format = :sql
    end
@tenderlove tenderlove self-assigned this Mar 7, 2014
@senny senny added the PostgreSQL label Mar 30, 2014
@robin850
Ruby on Rails member

Hello there,

Great work here! What do you think about mentioning this is the application.rb file when generating a new application ? May be something along these lines. Feel free to send a pull request. :-)

Have a nice day.

@davekapp

Hi Robin,

Thanks, I appreciate your kind words! :) Things are really hectic right during the first part of this week due to a release we're tying to wrap up but I'll work on a pull request shortly thereafter, likely Wednesday. Could I contact you for feedback on the PR formatting (or should I just do the first pass at it and then ask for feedback afterwards)?

@robin850
Ruby on Rails member

Could I contact you for feedback on the PR formatting (or should I just do the first pass at it and then ask for feedback afterwards)?

Generally you should open the pull request and then people discuss about the change. To be honest I'm not a merger so the decision is not up to me.

@rails-bot rails-bot added the stale label Aug 19, 2014
@rails-bot

This issue has been automatically marked as stale because it has not been commented on for at least
three months.

The resources of the Rails team are limited, and so we are asking for your help.

If you can still reproduce this error on the 4-1-stable, 4-0-stable branches or on master,
please reply with all of the information you have about it in order to keep the issue open.

Thank you for all your contributions.

@rafaelfranca rafaelfranca removed the stale label Nov 19, 2014
@rails-bot rails-bot added the stale label Jun 25, 2015
@rails-bot

This issue has been automatically marked as stale because it has not been commented on for at least
three months.

The resources of the Rails team are limited, and so we are asking for your help.

If you can still reproduce this error on the 4-2-stable, 4-1-stable branches or on master,
please reply with all of the information you have about it in order to keep the issue open.

Thank you for all your contributions.

@searls

I can still reproduce this on 4.2.

@rafaelfranca rafaelfranca added pinned and removed stale labels Jun 26, 2015
@tejasbubane

@searls Yes, the Column Modifiers section on rails-guides states that when using dynamic values like date, the default will only be calculated for the first time.

@davekapp Thanks for the workaround.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment