Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Disable transaction for a particular migration #655

Closed
anthonygalea opened this issue Dec 3, 2013 · 13 comments
Closed

Disable transaction for a particular migration #655

anthonygalea opened this issue Dec 3, 2013 · 13 comments

Comments

@anthonygalea
Copy link

@anthonygalea anthonygalea commented Dec 3, 2013

Since some SQL queries cannot run within a transaction (like adding an index concurrently in postgres) it would be useful to be able to turn transactions off when necessary.

The equivalent in ActiveRecord is disable_ddl_transaction! rails/rails@b337390

@perryp
Copy link

@perryp perryp commented Apr 5, 2014

+1

@axelfontaine axelfontaine added this to the Flyway 3.0 milestone Apr 15, 2014
@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Apr 16, 2014

Thanks for suggesting this. I've now added a basic implementation that makes this possible. See code from commit.

It is not exposed to SQL and Java migrations yet (might do in the future). But the custom MigrationResolver I added to the test code in the commit should be simple enough to get you through most of the situations. I can easily be extended to load the data from an external source if you nedd that.

@falschparker82
Copy link

@falschparker82 falschparker82 commented May 26, 2015

Any plans on integrating this into the SQL migrations API? If nobody is on it, we could have a go at it (or sponsor a bounty). Problem is of course, which interface should be used. Any preferences on the design side? Magic string inside the file name? SQL Comment inside the file?

@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented May 26, 2015

@falschparker82 Ideally I would like to implement this so it is transparent to the user. Please contact me by email at "axel at boxfuse.com" so we can discuss your needs and the bounty details.

Cheers
Axel

@yevah
Copy link

@yevah yevah commented Sep 3, 2015

@axelfontaine thanks for nice feature. However we have a problem when having "create index concurrently...." queries in migrations. We are using postgresql and after this migration flyway just hangs. Actually index is created but in this point code execution just being kind of "paused" forever. Migration is not recorded in schema_version and running of next migration is not started.
It seems that problem appears because in this case Flyway has open transaction which locks metadata table. So maybe when executeIntransaction returns false, metadata table should not be locked too. You can easilty reporosuce the issue adding any "create index concurrently " statement to script file in your Junit test
Cheers,
Yeva

@jimm-porch
Copy link

@jimm-porch jimm-porch commented Dec 2, 2015

How do we tag a particular migration script so it gets run in auto-commit instead of in a transaction? From the code change it looks like this can be disabled in Java but I would prefer if the migration script could control this.

For example the script could have a special name which triggers this, like ending in ".autocommit.sql" intead of ".sql", or the first line of the script could have a special comment like -- @autocommit, etc.

@jimm-porch
Copy link

@jimm-porch jimm-porch commented Dec 2, 2015

Another alternative would be to allow the FlywayCallback to control this, so that anyone could implement their own rules.

/** @return true if this script should be run in a transaction, false to use auto-commit. */
default boolean isTransactional(Connection connection, URI script) {
    return true;
}
@jimm-porch
Copy link

@jimm-porch jimm-porch commented Dec 2, 2015

I'm guessing that the support for non-transactional migration is still actually running inside a transaction. When I break into this in a debugger it still looks like this code is being run in a transaction from DbMigrate.migrate() line 156

Here's a summary of the relevant part of the stack:

DbMigrate.applyMigration():293
DbMigrate.access$800():46 (not sure what this is)
DbMigrate.doInTransaction():207
DbMigrate.doInTransaction():156
TransactionTemplate.execute():72
DbMigrate.migrate():156
@ntrrgc
Copy link

@ntrrgc ntrrgc commented Jul 21, 2016

How would a user disable transactions for a particular transaction? All I see in the commit is a bunch of Java files.

Should the user write a custom Java application instead of e.g. using the Flyway CLI tool?

@Strandedpirate
Copy link

@Strandedpirate Strandedpirate commented Oct 3, 2017

For the CLI this commit added support for PostgreSQL and no other database in terms of detecting non-transactional statements in an automated fashion. In addition there appear to be no "hooks" via the command line to turn this option on for a particular file.

This project needs to add a configuration based mechanism for the CLI for turning options on/off on a per sql file basis such as parsing the beginning of the file as YAML. The standard triple dash/period in the YAML spec can be used to delineate between the YAML configuration and the SQL.

e.g.

---
executeInTransaction: true
someOtherOption: "run faster!"
...
CREATE FULLTEXT CATALOG clipart
WITH ACCENT_SENSITIVITY = ON
GO
@Rogach
Copy link

@Rogach Rogach commented Jul 26, 2019

In case somebody finds this useful, there is a workaround for SQL migrations. Add the following statements to the migration:

create type temp_enum as enum ('a');
alter type temp_enum add value 'b';
drop type temp_enum;

These statements will force Flyway to fall back to non-transactional mode.

@MikielAgutu
Copy link
Member

@MikielAgutu MikielAgutu commented Aug 1, 2019

@Strandedpirate Flyway is designed to automatically detect statements which cannot run inside of a transaction. This is to reduce the load on users, so you don't have to configure every script if it's suspected it might not work inside a transaction. If you have found a scenario where this isn't working, please create an issue in this repo with reproduction steps.

@Rogach This isn't a workaround so much as expected behavior (for Postgres). As I said above, Flyway is designed to detect non-transactional statements. If you are finding cases where Flyway isn't picking up non-transcational statements, please create an issue.

@Rogach
Copy link

@Rogach Rogach commented Aug 1, 2019

@MikielAgutu In my case there were no statements that were non-transactional, so Flyway worked absolutely correctly in that regard. However, I still wanted to run that migration in non-transactional mode so that the application that was running concurrently would not experience problems due to the fact that all rows in the table were locked up for the duration of the transaction.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
10 participants