Postresql index with type casting using CAST AS is non-rollbackable

[tomash]

Steps to reproduce

The requirement: adding typecasted index, namely created_at typecasted to DATE type because we're calculating daily statistics and therefore choosing rows with WHERE payments.created_at::date='2018-03-01'.

Adding a migration with

add_index :payments, "CAST(created_at AS date)"

creates an index and the following entry in schema.rb:

t.index "((created_at)::date)", name: "index_payments_on_CAST_created_at_AS_date"

So the migration engine was smart enough to shorthand CAST AS syntax into :: syntax, BUT puts the wrong name of newly created index into schema.rb. The actual name of index in the database is also generated based on shorthand form (which is nice and consistent), it's index_payments_on_created_at_date in this case.

Unfortunately the naming inconsistency causes the migration to be non-rollbackable, because

$ rails db:rollback
== 20180418123502 AddIndexToCreatedAtAsDate: reverting ========================
-- remove_index(:payments, {:column=>"CAST(created_at AS date)"})
rails aborted!
StandardError: An error has occurred, this and all later migrations canceled:

No indexes found on payments with the options provided.
/home/tomek/.rvm/gems/ruby-2.5.0/gems/activerecord-5.1.5/lib/active_record/connection_adapters/abstract/schema_statements.rb:1236:in `index_name_for_remove'
/home/tomek/.rvm/gems/ruby-2.5.0/gems/activerecord-5.1.5/lib/active_record/connection_adapters/postgresql/schema_statements.rb:502:in `remove_index'

Or to put it shortly, the index with given name does not exist.

WORKAROUND: use shorthand form in migration, in this case

add_index :payments, "((created_at)::date)"

which creates a proper entry in schema.rb:

t.index "((created_at)::date)", name: "index_payments_on_created_at_date"

and rolls back like a charm.

Expected behavior

CAST AS form should generate rollback-able migrations.

System configuration

Rails version: 5.1.5

Ruby version: 2.5.0

[tomash]

Note: I'll make a reproducible test case with active_record_gem.rb asap.

[abaldwin88]

Reproduced case for 5.2: https://gist.github.com/abaldwin88/c24db432b7b351fafe56797b97ae6a54

Here's one against master: https://gist.github.com/abaldwin88/30be7716414315102a14d56bfb186721

I'm going to take a stab at opening a PR in the next couple days

[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 5-2-stable branch 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.

[abaldwin88]

For posterity -- The reproduction script is passing against version 7.0.5 (And may potentially succeed for versions after 5.2)