Using pg_advisory_lock for limit multi-node migrators?

[David Bernheisel]

👋 Heyo,

A friend Lucas Mendolowski asked if there was a way to ensure safe migrations in multi-node environments when running concurrent database operations, such as adding indexes concurrently, when using Postgres. We discussed more here: https://github.com/fly-apps/safe-ecto-migrations/issues/2

It seems that it might be a good idea to switch the PG adapter in EctoSQL to using `pg_advisory_lock` and `pg_advisory_unlock` (similar to how the MyXQL adapter currently works) so that we could de-couple database transactions from ensuring one migrator is running one at a time.

I'm not aware of what downsides there could be, I'll need to think about that some more. For reference ActiveRecord has been using `pg_try_advisory_lock` since Rails 5:

- https://github.com/rails/rails/pull/22122

- https://github.com/rails/rails/blob/v5.0.0/activerecord/lib/active_record/migration.rb#L1121-L1135

[José Valim]

A PR would be welcome, yes!

--
You received this message because you are subscribed to the Google Groups "elixir-ecto" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elixir-ecto...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/d43db622-ae5c-46d4-8423-b618efb27816n%40googlegroups.com.

[Hubert Łępicki]

hold on, doesn't Ecto already do that? I was sure that migrations are safe currently in multi-node set up, but does it only mean they're safe when you're using MySQL?

To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/CAGnRm4KM08u03zVEhm07apFdCn72SqFfsb1bsRU9CdQKaRy%3D5g%40mail.gmail.com.

--

Pozdrawiam,
Hubert Łępicki
 -----------------------------------------------
[ http://hubertlepicki.com ]

[José Valim]

We do but we use another implementation.

To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/CABKQyRodx%2BDr7tou%3DpvMV4O94MrX0zz6HLNbybqOXR4gdJSYww%40mail.gmail.com.

[Allen Madsen]

One thing to consider is that some people use the postgres adapter for CockroachDB, so the solution needs to be portable for that or have a way to be disabled.

Allen Madsen
http://www.allenmadsen.com

To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/CAGnRm4Jx%2BcuBeV%2BWZWM9zTU13L4zLphKf8BDxJU1zt37MfQ87g%40mail.gmail.com.

[José Valim]

I believe we are going to keep the current mode as behind an option.

To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/CAK-y3CvgWTw_kkv9OwD3gS5x7tzZOqvgt16zxXpinROO8djSNg%40mail.gmail.com.

[Hubert Łępicki]

ok thanks for explaining. I had a minor panic attack xD

To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/CAGnRm4Jcut_4MKK-AF19MhZjf0fUErywD0QnUwE2Uw9N%2BxXOQw%40mail.gmail.com.