add_column for postgres table with {array: true} option does not create array column in 4.0.0.rc1 #10432

Closed
pbollenbeck opened this Issue May 2, 2013 · 16 comments

Comments

Projects
None yet

I have the following migration to add a postgres array column to an existing table:

class AddIntArrayToGremlins < ActiveRecord::Migration
  def change
    add_column :gremlins, :int_array, :integer, array: true
  end
end

rake db:migrate succeeds, but the resulting table does not contain the array type.

\d gremlins; in psql shows:

  Column   |  Type   |                       Modifiers
-----------+---------+-------------------------------------------------------
 id        | integer | not null default nextval('gremlins_id_seq'::regclass)
 int_array | integer |

The dumped db/schema.rb shows:

create_table "gremlins", force: true do |t|
  t.integer "int_array"
end

I believe it worked in 4.0.0.beta1, as I recognized the regression just after upgrading to rc1.

Contributor

scudco commented May 4, 2013

Is it possible this commit could've created this regression? 072dbbf

Owner

rafaelfranca commented May 4, 2013

Not sure. Needs to investigate more. This is not a regression since this behavior is not present in 3.2.x, so I removed the milestone and the regression tag.

Member

neerajdotname commented May 5, 2013

When create_table statement is executed then following line adds the array option to true.

https://github.com/rails/rails/blob/master/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb#L371

However when add_column is executed then following line is executed

https://github.com/rails/rails/blob/master/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb#L378-L381

In the latter case super statement not only defines the attributes of the column but it also adds it to adds .

https://github.com/rails/rails/blob/master/activerecord/lib/active_record/connection_adapters/abstract/schema_definitions.rb#L308-L312

It means pg adapter never gets a chance to add the array option on the newly created column record.

I can fix this issue by making a few changes to the way call is being made. But that fix would duplicate the information about how array option should be handled. So I'm going to spend some time looking at the code to see if there is a way to consolidate the array handling between create_table and add_column.

yagooar commented May 27, 2013

I have noticed change_column does not work properly, showing the same behavior as add_column before the fix.

Contributor

kitop commented May 29, 2013

I came across this same issue today, with a string array column, in Rails 4.0.0.rc1
Is this fixed in any commit newer commit?

@yagooar The fix hasn't been yet been merged, so I would expect master to still exhibit this issue. Maybe you thought #10493 closure meant this was fixed?

Just updated from 4.0.0 beta to rc1 and encountered the same issue. It was with a string array though.

The error I got was "can't cast Array to string". "can't cast Array to integer" will probably be the same. Commenting here so any other devs googling for the error hopefully find this thread.

Contributor

nfm commented Jun 3, 2013

I tried to work around this by adding the column manually using execute in my migration, but ran into problems when trying to save records. I might have borked up the migration though - I'm new to this postgres feature.

Here's my migration (I also tried text[] data type and hit the same issue below):

def self.up
  execute "ALTER TABLE users ADD COLUMN languages varchar[] DEFAULT '{English}'"
end

And an attempt to update a record with languages:

[1] pry(main)> user = User.first;
User Load (1.4ms)  SELECT "users".* FROM "users" ORDER BY "users"."id" ASC LIMIT 1
[2] pry(main)> user.languages
=> ["English"]
[3] pry(main)> user.languages = ["English", "Chinese"]
=> ["English", "Chinese"]
[4] pry(main)> u.save
   (0.2ms)  BEGIN
  SQL (0.8ms)  UPDATE "users" SET "languages" = $1, "updated_at" = $2 WHERE "users"."id" = 1  [["languages", ["foo", "bar"]], ["updated_at", Mon, 03 Jun 2013 06:21:58 UTC +00:00]]
PG::Error: ERROR:  column "languages" is of type character varying[] but expression is of type character varying at character 34
HINT:  You will need to rewrite or cast the expression.
: UPDATE "users" SET "languages" = $1, "updated_at" = $2 WHERE "users"."id" = 1
   (0.2ms)  ROLLBACK
    ActiveRecord::StatementInvalid: PG::Error: ERROR:  column "languages" is of type character varying[] but expression is of type character varying at character 34
HINT:  You will need to rewrite or cast the expression.
: UPDATE "users" SET "languages" = $1, "updated_at" = $2 WHERE "users"."id" = 1
from /home/nfm/.rbenv/versions/2.0.0-p195/lib/ruby/gems/2.0.0/gems/activerecord-4.0.0.rc1/lib/active_record/connection_adapters/postgresql_adapter.rb:751:in `get_last_result'

Edit: I'm having trouble reproducing the above error, so I might have just ended up in a bad state with my database.

Contributor

scudco commented Jun 4, 2013

Can someone add this to the 4.0.0 milestone? https://twitter.com/rails/status/341618573518704640 is misleading and there were a few replies asking whether this would be included.

@lsylvester lsylvester added a commit to lsylvester/rails that referenced this issue Jun 5, 2013

@scudco @lsylvester scudco + lsylvester Fixes #10432 add_column not creating array columns in PostgreSQL
When then PostgreSQL visitor was [added](rails@6b7fdf3)
`add_column` was no longer receiving the column options directly. This
caused the options to be lost along the way.

Conflicts:
	activerecord/CHANGELOG.md
823dd26

tenderlove closed this in e4fe497 Jun 5, 2013

Contributor

pawel2105 commented Jun 20, 2013

I'm getting something similar. On Rails 4.0.0.rc2

class ChangeStoresInUsers < ActiveRecord::Migration
  def change
    add_column :users, :friends, :string, array: true
    add_column :users, :interests, :string, array: true
  end
end

The migration runs:

rake db:migrate
==  ChangeStoresInUsers: migrating ============================================
-- add_column(:users, :friends, :string, {:array=>true})
   -> 0.0033s
-- add_column(:users, :interests, :string, {:array=>true})
   -> 0.0005s
==  ChangeStoresInUsers: migrated (0.0202s) ===================================

Running something like User.last.update_attributes(friends: ["Bob","Bill"]) returns a TypeError: TypeError: can't cast Array to string

Contributor

pawel2105 commented Jun 20, 2013

I was mistaken, I was on Rails 4.0.0.rc1 - working now with rc2

👍

I'm not sure this issue is fixed. Using the most recent version of Rails 4.0.0 and the following migration:
change_column :articles, :txst_array, :text, array: true, default: '{}'

causes my scema to go from this:
t.string "text_array", default: [], array: true

to this:
t.text "text_array", default: "{}"

Owner

senny commented Jul 16, 2013

@lmcardle this issue was about add_column. I can confirm your reports about change_column. Please open a new issue.

Owner

senny commented Jul 16, 2013

@lmcardle I pushed a fix for the problem you described.

master: 754a373

4-0-stable: c940b14

p29892p commented Dec 1, 2015

The below statements works well for me
t.integer :sector_ids, default: [], array: true

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