Transaction during all migrations instead of each migrations

Hey,

What you think about using a transaction during the entire db:migrate instead of each migration?

Currently if we deploy 10 migrations and the latest fails, the application may be down because the database was changed but one of these migrations have failed.

Think worse, one of these migrations can't be rollbacked so we can't rollback the application to a good state without a human interaction.

Using just one transaction will make rails more "high available" during deploys because the migrations only will be committed if everything is ok.

Note that will apply only to postgresql because mysql do not support ddl transactions.

What you think, guys?

Cheers,

Gabriel Sobrinho

AFAIK I think Rails already has that enabled by default on Postgresql. I think MySQL doesn't support DDL migration, so it's not enabled.

Aaron, amirite?

+1

Hello guys,

just want to add that a new pull request was made adding this feature for supported databases: https://github.com/rails/rails/pull/6768

Please feel free to comment there.

Cheers.

I would be -1 on this sort of change.

Changing the schema of a RDBS and deploying the dependent code should be performed with great care, the basic concept of each migration being “whole” change keeps things tidy. Giving any impression to a developer that enclosing a bunch of migrations as a transaction creates a highly-available deploy would be a disservice. There are so many other factors that go into making HA happen. Very few sites actually require anything close to HA, those that do are going to be putting all sorts of tools around their deploy process.

The suggested code/schema upgrade should go this way, hopefully in as short a period as possible, with some sanity checks between each step:

  • Disable website
  • Dump current database
  • Migrate database
  • Update code
  • Re-enable website

Lets say there were 10 migrations to do and #5 failed, being in the state that 1-4 have run is where you want to be. Now you can look at why #5 failed, fix it (check it in), and continue.

This becomes particularly important if one of 1-4 were very long running statements, large import, index updating, etc. In these cases it becomes even more important that the website be shut down because the DB is going to be pinning its CPU and the user experience is going to suffer dramatically.

If the change was implemented I would suggest that it be off by default in development, where the situation of wanting the first few migrations to take and then to begin debugging is the much more normal behaviour.

Simon

Hey Simon, thanks for your comments.

Would you mind “copying & pasting” them to the issue I linked before? This way others can see it there while checking the related pull request, to decide whether or not the feature should be in.

Thanks!