Ecto - unable to drop custom-named unique index

Question

I'm unable to successfully run an Ecto migration to drop a unique index that, when originally created, had been provided with a :name attribute (so that the default index name was not used). However, I am now unable to drop that index, because it seems as though Ecto is trying to look for an index with the incorrect name (although I've provided it).

The unique index was originally created via a migration like so:

def change do
  create(
    unique_index("foo", [:bar_id],
      where: "rejected IS NULL AND accepted IS NULL)",
      name: :bar_pending_index
    )
  )
end

when I inspect the table in the psql shell, I see this index listed as:

"bar_pending_index" UNIQUE, btree (bar_id) WHERE rejected IS NULL AND accepted IS NULL

To drop the index, I've written the following migration:

def up do
  drop index("foo", [:bar_pending_index])
end

def down do
  create(
    unique_index("foo", [:bar_id],
      where: "rejected IS NULL AND accepted IS NULL)",
      name: :bar_pending_index
    )
  )
end

However, when I try to run this migration, I get the error

14:01:56.573 [info]  == Running 20210610173741 MyApp.Repo.Migrations.DropIndex.up/0 forward

14:01:56.576 [info]  drop index foo_bar_pending_index_index
** (Postgrex.Error) ERROR 42704 (undefined_object) index "foo_bar_pending_index" does not exist

It looks to me as though it's trying to apply what Ecto normally thinks of as the "default" naming convention, which is to say it expects to prepend the index name with the table name and append the index name with the word "index". What is the appropriate way to drop a custom-named index via an Ecto migration? Thank you!

Footsie

Answer 1

When you do drop index("foo", [:bar_pending_index]) you're invoking the same index/3 function used to create an index, similar to unique_index/3.

Looking at the documentation of those two functions you'll notice that the second argument is always the columns to be used for the index (the example on drop: drop index("posts", [:name]) is a bit ambiguous because of the column name :name).

So, what you should do is actually something very similar to the way you created the index, like:

drop index("foo", [:bar_id], name: :bar_pending_index)

Chelyuskin

Ah thanks, that makes perfect sense - it wasn't clear to me from the docs that I could provide an opts argument there, but indeed that does the trick. – Footsie