Problem with the async migrations

[kelvinst]

Environment

• Elixir version (elixir -v):

Erlang/OTP 21 [erts-10.0] [source] [64-bit] [smp:4:4] [ds:4:4:10] [async-threads:1] [hipe]

Elixir 1.8.1 (compiled with Erlang/OTP 20)

• Database and version (PostgreSQL 9.4, MongoDB 3.2, etc.): PostgreSQL 9.6
• Ecto version (mix deps):

* ecto (Hex package) (mix)
  locked at 3.0.1 (ecto) a26605ee
* ecto_sql (Hex package) (mix)
  locked at 3.0.0 (ecto_sql) 8d188337

• Database adapter and version (mix deps):

* postgrex (Hex package) (mix)
  locked at 0.14.0 (postgrex) f3d6ffea

• Operating system: MacOS 10.14.3

Current behavior

So, we are trying to support ecto 3 on triplex (PR ateliware/triplex#61), and we stumbled in a problem: we cannot run a CREATE SCHEMA "a" and the migrations for the prefix "a" inside the same transaction, because now the migrations are ran inside a Task.async, so the db connection will not be the same and then we get a ** (Postgrex.Error) ERROR 3F000 (invalid_schema_name) schema "a" does not exist for the migrations.

A sample code would be:

Repo.transaction fn ->
  Ecto.Adapters.SQL.query!(Repo, "CREATE SCHEMA \"a\"", [])
  Ecto.Migrator.run(Repo, :up, all: true, prefix: "a")
end

And the error you will get on ecto 3 is:

** (Postgrex.Error) ERROR 3F000 (invalid_schema_name) schema "a" does not exist
    (ecto_sql) lib/ecto/adapters/sql.ex:590: Ecto.Adapters.SQL.raise_sql_call_error/1
    (elixir) lib/enum.ex:1327: Enum."-map/2-lists^map/1-0-"/2
    (ecto_sql) lib/ecto/adapters/sql.ex:677: Ecto.Adapters.SQL.execute_ddl/4
    (ecto_sql) lib/ecto/migration/runner.ex:272: Ecto.Migration.Runner.log_and_execute_ddl/3
    (ecto_sql) lib/ecto/migration/runner.ex:105: anonymous fn/2 in Ecto.Migration.Runner.flush/0
    (elixir) lib/enum.ex:1940: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto_sql) lib/ecto/migration/runner.ex:103: Ecto.Migration.Runner.flush/0
    (stdlib) timer.erl:181: :timer.tc/2

Expected behavior

So with ecto 2 the sample code runs smoothly, and migrates everything, because all the migrations are run inside of the same process of the transaction.

My suggestion for a solution would be an async option for Ecto.Migrator.up/4, Ecto.Migrator.down/4 and Ecto.Migrator.run/3,4, that could be true by default, but if false, it will not run the migrations on a Task.async.

I can work on that if you want.

[josevalim]

The tasks are necessary, otherwise the migrations will run into a deadlock now that we lock the migrations table. So you would need to disable the migration lock too. Furthermore, the code is already complex, so I am a bit worried about adding more cases. I wonder if there are other things we could do? We have recently added a before_migration callback, maybe that can be used instead?

[kelvinst]

The thing about triplex is that every time we create a new tenant (which is a schema on postgres) we run the migrations from a priv/repo/tenant_migrations folder for that schema, otherwise new tenant schemas will not have the tables. Not sure if before_migration would help, but I can test that for sure!

One solution maybe would be changing triplex to not run migrations when creating a new tenant at all, and instead use ecto.load. I would like do that sometime in the future anyway, since running migrations make the tenant creation quite slow and that make's testing it annoying. I will check how could I do that and get back to you if it didn't solve.

Closing for now, as there are 2 possible solutions. Thanks for the quick answer!