Are migrations executed within a transaction?

Hi all,

Can I safely assume that each migration is excuted within a
transaction, so that’s it’s safe to have migrations executing failing
statements?

Thanks,

  • Rowan

Hi,

On 1/6/06, BigSmoke [email protected] wrote:

Can I safely assume that each migration is excuted within a
transaction, so that’s it’s safe to have migrations executing failing
statements?

Migrations aren’t run inside a transaction because they can’t be.
None of the databases supported by Rails are able to perform DDL
operations (CREATE TABLE, ALTER TABLE, etc.) inside a transaction
(some raise errors, the others silently fail).

Always run your migrations on test data first!


sam

On 1/7/06, Sam S. [email protected] wrote:

(some raise errors, the others silently fail).
I always had the impression that this did work in my Postgres
installation. Perhaps this could be because I created the schema’s in
the same transaction as in which I created the tables, views and
functions?

Always run your migrations on test data first!

No worries! I was going to do that anyway :slight_smile: It can’t be pointed out
often enough, though. I just thought it would be an easy way to undo
incomplete migrations in case of errors. Luckily, now that I’ll use
migrations to load the initial (schema) data, reloading all my schemas
if something goes wrong will be less of a pain. AR makes life a whole
lot easier again :slight_smile:

  • Rowan

Sam S. wrote:
> Migrations aren’t run inside a transaction because they can’t be.
> None of the databases supported by Rails are able to perform DDL
> operations (CREATE TABLE, ALTER TABLE, etc.) inside a transaction

Additionally, migration can do more than DDL, even non-DB related
operations (like generating a scaffold, executing plain ruby code,…).
So, the DB part of the migration can work fine, while the rest failed.
rolling back in that case can be tricky. My workaround is to write many
smaller migrations, instead of a big one.

Alain

Sam S. wrote:

Migrations aren’t run inside a transaction because they can’t be.
None of the databases supported by Rails are able to perform DDL
operations (CREATE TABLE, ALTER TABLE, etc.) inside a transaction
(some raise errors, the others silently fail).

I just ran a test on my postgres DB:

sicirec_dev=# select * from investment_forms;
id | name_id
----±--------
1 | 34
2 | 35
(2 rows)

sicirec_dev=# BEGIN;
BEGIN
sicirec_dev=# alter table investment_forms add column foobar INTEGER;
ALTER TABLE
sicirec_dev=# select * from investment_forms;
id | name_id | foobar
----±--------±-------
1 | 34 |
2 | 35 |
(2 rows)

sicirec_dev=# ROLLBACK;
ROLLBACK
sicirec_dev=# select * from investment_forms;
id | name_id
----±--------
1 | 34
2 | 35
(2 rows)

So, it does seem to work. Am I being too simple with my alter table
perhaps?