dbernheisel
commented
2 years ago It's possible but it's code that you'd have to roll yourself right now-- I don't see a configurable option in Ecto currently to use pg_advisory_lock or pg_advisory_xact_lock during migrations instead of share update exclusive locks. I'm assuming you're talking about Postgres.
https://www.postgresql.org/docs/current/functions-admin.html
If you wanted to use application-side advisory locks, you could set up a a module to wrap this logic up yourself. I'm curious though, why would you prefer advisory locks?
For example (this is untested -- don't use this in production until you do way more testing on this):
defmodule MyConcurrentIndexMigration do
defmacro __using__(_opts) do
quote do
use Ecto.Migration
@disable_migration_lock true
@disable_ddl_transaction true
def up do
lock_name = "\"ecto_#{inspect(repo())}\""
try do
repo().query!("SELECT pg_advisory_lock(#{lock_name})")
do_up()
after
repo().query!("SELECT pg_advisory_unlock(#{lock_name})")
end
end
def down do
lock_name = "\"ecto_#{inspect(repo())}\""
try do
repo().query!("SELECT pg_advisory_lock(#{lock_name})")
do_down()
after
repo().query!("SELECT pg_advisory_unlock(#{lock_name})")
end
end
end
end
end
## Then in your migration
defmodule MyApp.Migrations202020202020 do
use MyConcurrentIndexMigration
def do_up do
create index("foo"), concurrently: true
end
def do_down do
drop index("foo"), concurrently: true
end
endThis is taking inspiration from the MyXQL Ecto adapter for obtaining locks: https://github.com/elixir-ecto/ecto_sql/blob/v3.7.1/lib/ecto/adapters/myxql.ex#L227-L249
lcmen
wojtekmach
Hello, Not sure if this is the best place to ask but I will shoot it anyway ;).
I'm Rails developer learning Elixir and I have a question about safe Ecto migrations. Is it possible to use advisory lock instead of share update exclusive on
schema_migrationstable (like Rails does it) when creating a migration record?I'm asking because (as fair as I understand) advisory lock would allow to disable ddl transaction for the migration itself (i.e. adding index concurrently) but it would still make sure that only one node can execute it (in multi node setup).