Rails cannot run remove_index-CodePudding

I accidentally added indexes as so:

class AddRandomIndices < ActiveRecord::Migration[5.0]
  def change
    add_index :charges, :pp_charge_id
    add_index :new_refunds, :pp_refund_id
    add_index :inventory_items, :product_id, unique: true
    add_index :prestock_items, :product_id, unique: true
  end
end

Here's the result in schema:

create_table "charges", force: :cascade do |t|
  ...
  t.index ["pp_charge_id"], name: "index_charges_on_pp_charge_id", using: :btree
end

create_table "new_refunds", force: :cascade do |t|
  ...
  t.index ["pp_refund_id"], name: "index_new_refunds_on_pp_refund_id", using: :btree
end
  

create_table "prestock_items", force: :cascade do |t|
  ...
  t.index ["product_id"], name: "index_prestock_items_on_product_id", unique: true, using: :btree
end

create_table "inventory_items", force: :cascade do |t|
  ...
  t.index ["product_id"], name: "index_inventory_items_on_product_id", unique: true, using: :btree
end

Here's remove file I tried to run:

class RemoveIndex < ActiveRecord::Migration[5.0]
  def change
    remove_index :charges, :pp_charge_id
    remove_index :new_refunds, :pp_refund_id
    remove_index :inventory_items, :product_id
    remove_index :prestock_items, :product_id
  end
end

Here's the error

> rake db:migrate
== 20220515045335 RemoveIndex: migrating ======================================
-- remove_index(:charges, :pp_charge_id)
rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:

No indexes found on charges with the options provided.

In this other SO answer: How to remove index in rails, there are lots of suggested options... none of them worked.

Here I try to run a file where the column and name are explicitly specified

class RemoveIndex < ActiveRecord::Migration[5.0]
  def change
    remove_index :charges, column: :pp_charge_id, name: :index_charges_on_pp_charge_id
    remove_index :new_refunds, column: :pp_refund_id, name: :index_new_refunds_on_pp_refund_id
    remove_index :inventory_items, column: :product_id, name: :index_prestock_items_on_product_id
    remove_index :prestock_items, column: :product_id, name: :index_inventory_items_on_product_id
  end
end

Same error:

> rake db:migrate
== 20220515045335 RemoveIndex: migrating ======================================
-- remove_index(:charges, {:column=>:pp_charge_id, :name=>:index_charges_on_pp_charge_id})
rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:

No indexes found on charges with the options provided.

And then I tried to run the file with just names, no columns

class RemoveIndex < ActiveRecord::Migration[5.0]
  def change
    remove_index :charges, name: :index_charges_on_pp_charge_id
    remove_index :new_refunds, name: :index_new_refunds_on_pp_refund_id
    remove_index :inventory_items, name: :index_prestock_items_on_product_id
    remove_index :prestock_items, name: :index_inventory_items_on_product_id
  end
end

Different error:

> rake db:migrate
== 20220515045335 RemoveIndex: migrating ======================================
-- remove_index(:charges, {:name=>:index_charges_on_pp_charge_id})
rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:

PG::UndefinedObject: ERROR:  index "index_charges_on_pp_charge_id" does not exist
: DROP INDEX  "index_charges_on_pp_charge_id"

I've tried passing strings as well, and will spare the continued copy/pasting...

What is happening??!

CodePudding user response：

The syntax you are using is correct to remove the indexes, not sure what would have happened but it is good to check if the indexes actually exist in the database or not using a SQL query, you can find it here: How to list indexes created for table in postgres

The schema synchronizes with the database automatically but in your case, you have a migration that is throwing an error that is why it is not being updated so you can try deleting the migrations which are throwing errors and then running rails db:migrate, if the indexes don't exist in the database they will be removed from the schema too.

Usually, if you have added some migration by mistake and not pushed it yet to version control or have not shared it with anyone else then you can run rails db:rollback if it is the last migration or run rails db:migrate:status to get the version number of migration you want to rollback and then run rails db:migrate:down VERSION=20220515045335 (this will run only if the migration status is up) which will rollback the migration and then you can safely delete the file.