CODEWITHSUNDEEP
ruby-on-railspostgresqlruby-on-rails-4activerecordhstore
Sasha
Sasha

Reputation: 6466

Changing HStore Column to Array

I have a column in my Rails/Postgres app that I want to change from Hstore to an Array. (I was storing phones as a hash, so I could do {default: 123, mobile: 1234}, but decided it wasn't necessary/useful)

So I did the following migration:

class ChangePhonesToArray < ActiveRecord::Migration
  def up
    new_phones = {}
    Place.find_each do |p|
      new_phones[p.id] = p.phones.values.map{ |v| v.gsub(%r!\D!, '') } # get rid of non-number characters while I'm at it
    end

    remove_column :places, :phones

    add_column :places, :phones, :string, array: true, default: []

    new_phones.each do |k, v|
      p = Place.find(k)
      p.update_attributes!(phones: v)
    end
  end
  ...
end

When I do that, however, I get this nasty DB error, suggesting that phones is still an Hstore column!

StandardError: An error has occurred, this and all later migrations canceled:

can't cast Array to hstore/Users/sasha/.rvm/gems/ruby-2.1.2/gems/activerecord-4.1.4/lib/active_record/connection_adapters/abstract/quoting.rb:76:in `type_cast'
/Users/sasha/.rvm/gems/ruby-2.1.2/gems/activerecord-4.1.4/lib/active_record/connection_adapters/postgresql/quoting.rb:111:in `type_cast'
/Users/sasha/.rvm/gems/ruby-2.1.2/gems/activerecord-4.1.4/lib/active_record/connection_adapters/postgresql_adapter.rb:828:in `block in exec_cache'
/Users/sasha/.rvm/gems/ruby-2.1.2/gems/activerecord-4.1.4/lib/active_record/connection_adapters/postgresql_adapter.rb:827:in `map'
/Users/sasha/.rvm/gems/ruby-2.1.2/gems/activerecord-4.1.4/lib/active_record/connection_adapters/postgresql_adapter.rb:827:in `exec_cache'
/Users/sasha/.rvm/gems/ruby-2.1.2/gems/activerecord-4.1.4/lib/active_record/connection_adapters/postgresql/database_statements.rb:155:in `exec_delete'
/Users/sasha/.rvm/gems/ruby-2.1.2/gems/activerecord-4.1.4/lib/active_record/connection_adapters/abstract/database_statements.rb:101:in `update'
/Users/sasha/.rvm/gems/ruby-2.1.2/gems/activerecord-4.1.4/lib/active_record/connection_adapters/abstract/query_cache.rb:14:in `update'

I assumed the column wasn't being removed correctly, so I threw a second remove_column in there right after the first, and that threw the below error suggesting that the column had already been removed! (But apparently not completely).

StandardError: An error has occurred, this and all later migrations canceled:

PG::UndefinedColumn: ERROR:  column "phones" of relation "places" does not exist
: ALTER TABLE "places" DROP "phones"/Users/sasha/.rvm/gems/ruby-2.1.2/gems/rack-mini-profiler-0.9.2/lib/patches/sql_patches.rb:160:in `exec'
/Users/sasha/.rvm/gems/ruby-2.1.2/gems/rack-mini-profiler-0.9.2/lib/patches/sql_patches.rb:160:in `async_exec'
/Users/sasha/.rvm/gems/ruby-2.1.2/gems/activerecord-4.1.4/lib/active_record/connection_adapters/postgresql/database_statements.rb:128:in `block in execute'
/Users/sasha/.rvm/gems/ruby-2.1.2/gems/activerecord-4.1.4/lib/active_record/connection_adapters/abstract_adapter.rb:373:in `block in log'
/Users/sasha/.rvm/gems/ruby-2.1.2/gems/activesupport-4.1.4/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
/Users/sasha/.rvm/gems/ruby-2.1.2/gems/activerecord-4.1.4/lib/active_record/connection_adapters/abstract_adapter.rb:367:in `log'
/Users/sasha/.rvm/gems/ruby-2.1.2/gems/activerecord-4.1.4/lib/active_record/connection_adapters/postgresql/database_statements.rb:127:in `execute'

Any idea what's going on here, and how to fix/get around it?

Upvotes: 0

Views: 424

Answers (1)

mu is too short
mu is too short

Reputation: 434665

Just because you changed the table schema inside the database doesn't mean that your Place class knows about those changes. ActiveRecord::Base subclasses only load the column information once to avoid needlessly hitting the database over and over again. As soon as you do this:

Place.find_each

your Place will know what the column types are. Then you change the schema behind Place's back and try to write new values but Place doesn't know about the changes. The usual way around this is to call reset_column_information:

Resets all the cached information about columns, which will cause them to be reloaded on the next request.

The most common usage pattern for this method is probably in a migration...

so you'd just say:

#...
remove_column :places, :phones
add_column :places, :phones, :string, array: true, default: []
Place.reset_column_information
#...

BTW, there's no guarantee that hstore will maintain any sort of order so you might want to p.phones.values_at(:default, :mobile) instead of p.phones.values to make sure things are in the right order in your array.

Upvotes: 3

Related Questions