migrations aren't transactional

[yogthos]

When multiple migrations are placed in a single file using the --;; separator, then you can end up with partially applied migrations. For example, if you have the following migration:

CREATE TABLE foo (id int);
--;;
CREATE TABLE bar id int);

The foo table will be created, then bar table creation statement will cause an exception. However, the first statement will not be rolled back and foo table remains after the migration fails.

[weavejester]

Isn't the solution to this to use a transaction? (For databases that support transactions with CREATE and DROP)

If the migration "up" partially applies, the "down" may also fail because the database isn't in a valid state. The only way I can see of solving this is to either make migrations more fine-grained, or use database transactions.

[yogthos]

The way I handle it in migratus is to break the migration up into a sequence of commands and run those in a transaction as seen here. This way either all statements are applied or the entire transaction is rolled back. The migrations would not be marked as applied in the latter case, and the down migration would not be run.

So, using database transactions is the solution, but I don't think it makes sense to expose this to the users.

[danielcompton]

The trouble as @weavejester has alluded to is that not all DB's support transactional DDL's. e.g. http://stackoverflow.com/questions/4711447/oracle-ddl-and-transaction-rollback
http://stackoverflow.com/questions/28197013/transactional-ddl-workflow-for-mysql
http://stackoverflow.com/questions/1043598/is-it-possible-to-run-multiple-ddl-statements-inside-a-transaction-within-sql-s

AFAICT Postgres is the only major DB that supports this well. ragtime implicitly running migrations in a transaction probably wouldn't give the guarantees that people expect, except under Postgres?

[weavejester]

Some databases have limitations on what can inside a transaction. For instance, the CREATE TABLE command in MySQL forces the transaction to commit, so it can no longer be rolled back. Postgres has good support for transactional DDL, but not all databases support it comprehensively.

If an exception occurs within a database migration, and that puts the database in an invalid state, then I don't think there's a good solution that works in all cases. A transaction rollback would be ideal, but doesn't work with all databases, and running the "down" script of the migration may fail, as it assumes the the "up" script was applied in full.

[weavejester]

And everything @danielcompton said as well :)

[yogthos]

That makes sense, but wouldn't it be better to attempt to do the transaction as opposed to not. If the database doesn't support transactional DDL you're just back to the default case, so no harm done, or am I missing something? :)

[yogthos]

Also, it looks like really it's mainly a problem with older versions of Oracle and MySQL, while every other DB supports transactional DDLs.

[weavejester]

I seem to recall there was some reason why putting migrations in a transaction caused errors on some databases, but I can't remember exactly what it was. Maybe there isn't any harm to putting everything inside a transaction, but I think we'd have to try it with several databases to be certain.

[yogthos]

That sounds reasonable, FWIW I haven't had any reports of problems in Migratus regarding this.

[hypirion]

Also, for reference: Postgres has only partial support for transactional DDLs, e.g. ALTER TYPE cannot add values inside a transaction.

[erez-rabih]

I would also like to mention that postgresql doesn't support creating index concurrenctly within a transaction:

org.postgresql.util.PSQLException: ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block

Is there a way to run a specific migration outside a transaction?

[viesti]

Old thread, but wanted to say, that Flyway also by default runs migrations in transaction, but for each database it supports, also parses the SQL scripts and detects if they use non-transactional features [1] [2] and prevents running the migration in that case.

I was a bit worried that if people (like me) have prior Flyway experience, and then use Ragtime, the defaults from another tool don't apply. Then again, it is always good to check one's assumptions with different tools :)

[1] https://documentation.red-gate.com/fd/migration-transaction-handling-273973399.html#:~:text=If%20Flyway%20detects%20that%20a,be%20marked%20as%20non%2Dtransactional.
[2] https://github.com/flyway/flyway/blob/main/flyway-database/flyway-database-postgresql/src/main/java/org/flywaydb/database/postgresql/PostgreSQLParser.java#L111-L133