ActiveRecord Postgres adapter: Column rename fails when index with expression exists

[tiagotex]

In ActiveRecord migrations using PostgresSQL when a table has an index defined using an expression, further renaming migrations fail because the regex to parse the index doesn't support expressions with line breaks.

This seems to be caused by PostgresSQL formatting the expression when creating the index. Move about this in the debugging area at the bottom.

Steps to reproduce

https://gist.github.com/tiagotex/364712d6501e77456d735878568172e7

Expected behavior

Renaming a column in a table with an index with an expression should work.

Actual behavior

An exception is raised:

undefined method `to_sym' for nil:NilClass
user/.rvm/gems/ruby-2.4.2/gems/activerecord-5.0.6/lib/active_record/connection_adapters/postgresql/schema_statements.rb:222:in `block in indexes'
user/.rvm/gems/ruby-2.4.2/gems/activerecord-5.0.6/lib/active_record/connection_adapters/postgresql/schema_statements.rb:193:in `map'
user/.rvm/gems/ruby-2.4.2/gems/activerecord-5.0.6/lib/active_record/connection_adapters/postgresql/schema_statements.rb:193:in `indexes'
user/.rvm/gems/ruby-2.4.2/gems/activerecord-5.0.6/lib/active_record/connection_adapters/abstract/schema_statements.rb:1242:in `rename_column_indexes'
user/.rvm/gems/ruby-2.4.2/gems/activerecord-5.0.6/lib/active_record/connection_adapters/postgresql/schema_statements.rb:555:in `rename_column'
user/.rvm/gems/ruby-2.4.2/gems/activerecord-5.0.6/lib/active_record/migration.rb:846:in `block in method_missing'
user/.rvm/gems/ruby-2.4.2/gems/activerecord-5.0.6/lib/active_record/migration.rb:815:in `block in say_with_time'
user/.rvm/gems/ruby-2.4.2/gems/activerecord-5.0.6/lib/active_record/migration.rb:815:in `say_with_time'
user/.rvm/gems/ruby-2.4.2/gems/activerecord-5.0.6/lib/active_record/migration.rb:835:in `method_missing'
user/project/db/migrate/20171211161026_migration.rb:9:in `up'

System configuration

Rails version:
5.0.6 and master

Ruby version:
2.4.2

Debugging

The error is raised in master/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb:141, using is nil because of the expression on Line 110

using, expressions, where = inddef.scan(/ USING (\w+?) \((.+?)\)(?: WHERE (.+))?\z/).flatten

In this case inddef is:

"CREATE UNIQUE INDEX  index_posts_on_date_and_user_id ON posts (date, (\nCASE\n    WHEN user_id IS NULL THEN false\n    ELSE true\nEND))"

In Postgres when we set an index that contains a CASE expression, it will try to be nice and format the expression for us.

CASE WHEN user_id IS NULL THEN false ELSE true END

# Becomes

CASE 
  WHEN user_id IS NULL THEN false
  ELSE true
END

This formatting breaks the Regex. I've implemented a quick fix by using squish to remove new lines and extra spacing before doing the regex match:

using, expressions, where = inddef.squish.scan(/ USING (\w+?) \((.+?)\)(?: WHERE (.+))?\z/).flatten

This fixes the problem. If you think this solution is good enough or have another suggestion I'm available to submit a fix.

Thank you for the awesome work 💚

[fatkodima]

👍 please, submit a fix