Rails cannot run remove_index

Question

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??!

Did you check if the indexes exist in the database using a SQL query? — Deepesh, May 15 '22 at 05:50
@Deepesh i'm not great at SQL, what's the query? but also it's on my db schema so I assume it is? — james, May 15 '22 at 06:00
https://stackoverflow.com/questions/37329561/how-to-list-indexes-created-for-table-in-postgres Yes they should exist in the database if they are in schema file but just confirming if they exist in database with the same name — Deepesh, May 15 '22 at 06:11
@Deepesh omg... they're NOT there... the only index I see is for example: `Indexes: "inventory_items_pkey" PRIMARY KEY, btree (id)` on `InventoryItems`. but so... i don't know how that got out of sync with schema... is it safe to just ... straight up modify the schema and delete the line `t.index ["product_id"], name: "index_inventory_items_on_product_id", unique: true, using: :btree` for example?? — james, May 15 '22 at 07:57
Delete the new migration you added to delete the indexes or any other one too which is throwing error and just try to run `rails db:migrate` and see if it syncs automatically — Deepesh, May 15 '22 at 08:08
@Deepesh wow that worked!!! if you put this in an answer i will accept it thank you! — james, May 15 '22 at 09:04

score 0 · Accepted Answer · answered May 15 '22 at 09:46

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.

Rails cannot run remove_index

1 Answers1