Forum: Ruby on Rails single transaction migrations

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Esad H. (Guest)
on 2006-03-01 13:52
Hi!

It seems that migration doesn't use single transcation to execute the
needed migrations for the database upgrade, so if I have database at
version 5, and I wrote some migrations 6..10, and error occurs while
executing migration 7, the database stays in state 6?

I also think that migration taks could use some more verbosity, for
example if migration fails, there's no reference to migration number nor
statement, only something like:

rake aborted!
uninitialized constant SalePoint

Are there any plans for improving on this or maybe even some code in
trunk already? ;-)

Greetings,

Esad
Kevin O. (Guest)
on 2006-03-01 16:07
(Received via mailing list)
As I discovered last night, you can wrap your up and down methods in a
transaction like so..

def up
  transaction do
    stuff
  end
end

It's not done automatically because some actions don't work in a
transaction (like creating a table in MySQL, I think).

Better error handling would be nice, but it probably needs to be handled
by the author.  Perhaps a 'rescue' at the end of the up or down method
would be sufficient.

_Kevin
Damon C. (Guest)
on 2006-03-01 19:42
Esad H. wrote:

> It seems that migration doesn't use single transcation to execute the
> needed migrations for the database upgrade, so if I have database at
> version 5, and I wrote some migrations 6..10, and error occurs while
> executing migration 7, the database stays in state 6?

DDL (table operations) each happen inside a single COMMIT.  You can wrap
data operations inside a transaction.do .  This is an argument for
testing your migrations one at a time and keeping steps simple.  Once
you are confident in them, you can try running them in a big series.

> I also think that migration taks could use some more verbosity, for
> example if migration fails, there's no reference to migration number nor
> statement, only something like:
Install this:

http://svn.jamisbuck.org/rails-plugins/verbose_mig...

You'll be a little happier.  A little more spring in your step.

-damon
http://damonclinkscales.com/
Esad H. (Guest)
on 2006-03-01 21:19
Hi!

It is really true that MySQL/Inno doesn't support transactions on DDL
statements (I've read somewhere that pg and mssql do!), but why not
simply offer the user the possibility to go back to original version if
an error occurs? If I add migrations 5,6 and migration 7 fails, I could
simply call down() on 6,5 and be there where I began? Of course, there
are IrreversibleMigrations, but they could be skipped or used could be
warned?

I checked the ActiveRecords::Migration and ::Migrator source, they seem
pretty clear and straightforward, this wouldn't be much of a problem to
implement. What do you think would be the best thing to do? To submit
patch or to make a plugin?

Greetings,

Esad
Damon C. (Guest)
on 2006-03-03 10:25
Esad H. wrote:
> It is really true that MySQL/Inno doesn't support transactions on DDL
> statements (I've read somewhere that pg and mssql do!), but why not
> simply offer the user the possibility to go back to original version if
> an error occurs? If I add migrations 5,6 and migration 7 fails, I could
> simply call down() on 6,5 and be there where I began? Of course, there
> are IrreversibleMigrations, but they could be skipped or used could be
> warned?
My understanding is that each DDL statement is in its own transaction,
even for non-MySQL databases.  But I'm not sure on that.   For MySQL,
even with InnoDB, the DDL-based statements are executed one at a time.

> I checked the ActiveRecords::Migration and ::Migrator source, they seem
> pretty clear and straightforward, this wouldn't be much of a problem to
> implement. What do you think would be the best thing to do? To submit
> patch or to make a plugin?
If you can make it work, I would suggest starting with a plugin.  This
is a great way to test new functionality and prove to the core team that
it could work as a patch.

I believe that it is this way in Rails is because the underlying
databases aren't offering DDL statements to be grouped into a
transaction and rolled back.  If you find a good reference which says
otherwise, please post back!

-damon
http://damonclinkscales.com/
Wiebe C. (Guest)
on 2006-03-03 17:59
Damon C. wrote:
> My understanding is that each DDL statement is in its own transaction,
> even for non-MySQL databases.  But I'm not sure on that.   For MySQL,
> even with InnoDB, the DDL-based statements are executed one at a time.

(snip)

> I believe that it is this way in Rails is because the underlying
> databases aren't offering DDL statements to be grouped into a
> transaction and rolled back.  If you find a good reference which says
> otherwise, please post back!

I was involved in a similair discussion (to which replies stopped...) a
while back. Postgres seems to put alter table in a transaction just
fine. The search is disabled (of the web-bridge), but my post about it
should be findable through other archives. Just search for posts by me,
there aren't many :)

Anyway, I just tried this quick test with psql 8.0.4:

===========
sicirec_dev=# begin;
BEGIN
sicirec_dev=# create table bla();
CREATE TABLE
sicirec_dev=# select * from bla;

--
(0 rows)

sicirec_dev=# rollback;
ROLLBACK
sicirec_dev=# select * from bla;
ERROR:  relation "bla" does not exist
sicirec_dev=#
===========

We (my collegue and me) load migrations from seperate .pgsql files,
which we start and end with BEGIN and COMMIT. The corresponding "up"
method, to run a .psql file with the same name as the .rb file (without
extension of course), would be:

def self.up
  # Execute the contents of the accompanying .pgsql file
  execute File.open( __FILE__.gsub(/\.rb$/, '.pgsql') ).read
end
This topic is locked and can not be replied to.