Ability to run custom cast logic in column type change

[agrieser-healthie]

Trying to convert a varchar column to integer results in the following error: PG::UndefinedFunction: ERROR: operator does not exist: integer <> character varying

The backfill_column_for_type_change method takes a type_cast_function parameter, but the only example I can find using that passing in jsonb.

I have a varchar column that contains integer id references to another table, and want to change the column type from varchar to int. I realize varchar -> int is not a safe cast, but all of the data in the table is an integer, just stored as text.

Is there a way to use the change column type recipe for this, or does this need to be a a custom migration?

[fatkodima]

Hey, thanks for using this gem and for the report! It is not currently possible, but I can implement an ability to pass raw sql literals, so you can write something like: type_cast_function: Arel.sql("column::integer").

Will explore this today/tomorrow and release a new version with this change.

[fatkodima]

Released a new version with this change.

[agrieser-healthie]

Testing this, and it works in the foreground, but fails in the background.

Example:

This runs as expected

backfill_column_for_type_change(:notifications, :associated_object, type_cast_function: Arel.sql("CAST(associated_object as int)"))

This completes the migration, but produces the job produces an error and the values are not migrated:

backfill_column_for_type_change_in_background(:notifications, :associated_object, type_cast_function: Arel.sql("CAST(associated_object as int)"))

id             | 2
migration_id   | 2
min_value      | 1
max_value      | 33
batch_size     | 20000
sub_batch_size | 1000
pause_ms       | 100
started_at     | 2023-11-16 20:21:35.47218
finished_at    | 2023-11-16 20:21:35.485106
status         | failed
max_attempts   | 5
attempts       | 5
error_class    | ActiveRecord::StatementInvalid
error_message  | PG::SyntaxError: ERROR:  syntax error at or near "("                      +
               | LINE 1: ..._for_type_change" = CAST(associated_object as int)("notifica...+
               |                                                              ^            +
               | 
backtrace      | {}
created_at     | 2023-11-16 20:21:35.296277
updated_at     | 2023-11-16 20:21:35.485727

[fatkodima]

It is not correctly serialized when saved to the database in the jsonb column, because it is Arel.sql instead of just string. I will take a look what I can do. Thanks for reporting!

[fatkodima]

Pushed a fix. Can you verify master works for you?

[agrieser-healthie]

That part worked, but now running into another error:

-- backfill_column_for_type_change_in_background(:notifications, :associated_object, {:type_cast_function=>"CAST(associated_object as int)"})
rails aborted!
StandardError: An error has occurred, this and all later migrations canceled:

Validation failed: Rows count must be greater than or equal to 0

My assumption is this is related to multi-db and ActiveRecord::Base vs ApplicationRecord: https://guides.rubyonrails.org/active_record_multiple_databases.html#horizontal-sharding

But I haven't gotten to the bottom of if yet

[fatkodima]

That comes from https://github.com/fatkodima/online_migrations/blob/c77f430fe931f4ce0046b3f0ab57b972b44839d8/lib/online_migrations/background_migrations/migration.rb#L35, but I currently didn't get how that is possible.

What value this query returns for the table where column is modified https://github.com/fatkodima/online_migrations/blob/c77f430fe931f4ce0046b3f0ab57b972b44839d8/lib/online_migrations/utils.rb#L126-L131?