Mix ecto.migrate doesn't finish in terminal

Hello, mix ecto.migrate doesn’t finish when I run it, but it works and I’m forced to push CTRL + C to stop this, please see my pic:

this was not like this before , it works after updating Elixir and phoenix to last version that there are.

my sample DB schema:

defmodule WeddingCard.Repo.Migrations.WeddingCardCategory do
  use Ecto.Migration
  @disable_ddl_transaction true

  def change do
    create table(:wedding_card_category, primary_key: false) do
      add :id, :uuid, primary_key: true

      add :title, :string, size: 100, null: false
      add :short_description, :text,  null: false
      add :pic_one_link, :string, null: false
      add :seo_alias_link, :string, null: false
      add :seo_words, :string, null: false
      add :seo_description, :string, null: false
      add :pic_one_two, :string, null: false
      add :description, :text,  null: false
      add :text_link, :string, null: false

      timestamps()
    end
    create index(:wedding_card_category, [:seo_alias_link], concurrently: true, name: :index_of_card_alias_link, unique: true)
  end
end

update

it doesn’t go to next migration

Hi,
you could try to comment out the create_index... line and see if it works.
If it does then perhaps try to remove concurrently: true.
At least that might help to narrow down the problem.

Also there is a note of caution here about running migrations outside of transactions…
https://hexdocs.pm/ecto_sql/Ecto.Migration.html#module-transactions

2 Likes

Hello, thanks.

I disabled that line and the mix worked , but why does it have this behavior, it worked on my old project,

what will I do instead of that old way ?

update

I removed concurrently: true , and my code worked, why ?

Hi,
Glad that you are making some progress :slight_smile:

My guess it that your code was initially getting stuck due to a race condition when trying to create a table and add an index to it concurrently.

In any case @disable_ddl_transaction true and create index(... concurrently: true ...) would only be used in special circumstances such as when indexing an existing table in a live system in production.

See…
https://hexdocs.pm/ecto_sql/Ecto.Migration.html#index/3
And …
https://www.postgresql.org/docs/9.4/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY

2 Likes

creating an index concurrently in postgres is not respectful of a transaction boundary. Its separate and incredibly useful but definitely different. This might have something to do with it. I’d imagine that the response from Postgres is not what the migrate code is expecting on that SQL but I’d have to go look to confirm. It’d return immediately but the thing that the command is asking to be created is not created yet. Its async.

Running it inside a transaction will actually give an error:

ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block

here’s the pull request that added the functionality. https://github.com/elixir-ecto/ecto/pull/424

3 Likes

@shahryarb Can you put that index creation into its own migration file and retry it with your table creation? I’m thinking the table isn’t created (its in a transaction) and so the create index concurrently hangs waiting for the table create to finish.

1 Like

are you looking for the code like this ?

defmodule WeddingCard.Repo.Migrations.IndexOfCard do
  use Ecto.Migration

  def up do
    create index("wedding_card_category", [:seo_alias_link], concurrently: true, name: :index_of_card_alias_link, unique: true)
  end
end

I have the error:

** (Postgrex.Error) ERROR 25001 (active_sql_transaction) CREATE INDEX CONCURRENTLY cannot run inside a transaction block

or

defmodule WeddingCard.Repo.Migrations.IndexOfCard do
  use Ecto.Migration
  @disable_ddl_transaction true
  
  alter table("wedding_card_category") do

  end
  create index("wedding_card_category", [:seo_alias_link], concurrently: true, name: :index_of_card_alias_link, unique: true)
end

the error:

** (RuntimeError) could not find migration runner process for #PID<0.91.0>

@disable_ddl_transaction true

after your use Ecto.Migration should do the trick. Try it with your first version with the up.

I have written in my all code or deleted it, but it does nothing different . for my code it doesn’t matter that there is or isn’t in these lines

very odd. I’m sorry this is causing you so much trouble. I tried your code on a test db and it worked fine.

  defmodule AwsDetective.Repo.Migrations.ConcurrentTest do
  use Ecto.Migration
  @disable_ddl_transaction true

  def change do
    create table(:wedding_card_category, primary_key: false) do
      add(:id, :uuid, primary_key: true)

      add(:title, :string, size: 100, null: false)
      add(:short_description, :text, null: false)
      add(:pic_one_link, :string, null: false)
      add(:seo_alias_link, :string, null: false)
      add(:seo_words, :string, null: false)
      add(:seo_description, :string, null: false)
      add(:pic_one_two, :string, null: false)
      add(:description, :text, null: false)
      add(:text_link, :string, null: false)

      timestamps()
    end

    create(
      index(:wedding_card_category, [:seo_alias_link],
        concurrently: true,
        name: :index_of_card_alias_link,
        unique: true
      )
    )
  end
end

mix ecto.migrate
[info] == Running Tester.Repo.Migrations.ConcurrentTest.change/0 forward
[info] create table wedding_card_category
[info] create index index_of_card_alias_link
[info] == Migrated in 0.0s

if you try this same code and it hangs can you open up a psql session and do:

SELECT s."state",  s.pid, s.query
FROM pg_stat_activity s
WHERE  s.pid != pg_backend_pid();

if its being blocked then this query should show what is blocking:

 SELECT bl.pid                 AS blocked_pid,
         a.usename              AS blocked_user,
         ka.query               AS blocking_statement,
         now() - ka.query_start AS blocking_duration,
         kl.pid                 AS blocking_pid,
         ka.usename             AS blocking_user,
         a.query                AS blocked_statement,
         now() - a.query_start  AS blocked_duration
  FROM  pg_catalog.pg_locks         bl
   JOIN pg_catalog.pg_stat_activity a  ON a.pid = bl.pid
   JOIN pg_catalog.pg_locks         kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
   JOIN pg_catalog.pg_stat_activity ka ON ka.pid = kl.pid
  WHERE NOT bl.GRANTED;
2 Likes

Thank you, but it didn’t work for me, please see the pic. it can’t continue itself.

my code is:

have I made a mistake ?

nope. that should work. open up another console. start a psql session to connect to your db and execute that SQL I gave you. It might give a hint of what is going wrong. What version of Postgres are using?

1 Like

I have tested it in 11 and 10, those have the problem which I have. I think ecto has a problem and it should fix I think, because I do this with mix ecto not with PSQL.
https://github.com/elixir-ecto/ecto/issues/2845

I finally had time to go reproduce this with your version. pg stat activity shows that the select from schema migrations is “idle in transaction”. I guess that must be blocking the creation of the index. This is a bug that was added in newer versions of Ecto. I was using the Ecto 2.x before and that’s why I couldn’t reproduce. You did the right thing to open up bug report.

idle in transaction, SELECT s0."version"::bigint FROM "schema_migrations" AS s0 FOR UPDATE
active, CREATE UNIQUE INDEX CONCURRENTLY "index_of_card_alias_link" ON "wedding_card_category" ("seo_alias_link")
3 Likes

Thank you all, the new version is out!
https://github.com/elixir-ecto/ecto/issues/2845#issuecomment-442952349

4 Likes

If you opt for creating indexes concurrently, you have to add:

@disable_migration_lock true

The default setting is to only let one node succeed in running the migration. That prevents you from creating indexes concurrently.

1 Like

So it turns out if you only add @disable_migration_lock true the migration will indeed succeed, but it’s status (as reported by e.g. mix ecto.migrations) won’t be tracked properly: it will be reported as down, so if you mix ecto.rollback you’ll actually be rolling back 2 migrations (the one with the concurrent index, and the one before it). Or if you run mix ecto.migrate it will run the same previously-run migration again…

To get this working properly, you need to add both (per docs):

@disable_ddl_transaction true
@disable_migration_lock true

The migration will then behave as expected.