Provide a configuration property for configuring Flyway's use of transactional locks with PostgreSQL

[radeklos]

Hi,

In the project which I'm working on, we need to run non-transactional migration, but it seems that spring flyway autoconfigure doesn't allow configuring the option flyway.postgresql.transactional.lock - flyway docs.

Is there any other option how for configuring transaction lock with spring config?

[wilkinsona]

As described in the Spring Boot documentation, you can use a FlywayConfigurationCustomizer bean to fine-tune Flyway's configuration. The customizer can use the API that Flyway documents.

If you have any further questions, please follow up on Stack Overflow or Gitter. As mentioned in the guidelines for contributing, we prefer to use GitHub issues only for bugs and enhancements.

[wleese]

@wilkinsona , this issue (flyway/flyway#3492) seems to be quite a big one. I think it would be a good idea to have this property, as I'm assuming that as of Spring Boot 3.x, it will be an issue for many users.

[wleese]

In our Spring Boot based framework, we provided a FlywayConfigurationCustomizer bean that set flyway.postgresql.transactional.lock to false. We see however that when our users use e.g. @JdbcTest for integration tests, they can accidentally run into issues again where flyway.postgresql.transactional.lock is true (the default).

Note that the migration script that was hanging was due to a CREATE INDEX statement, which has been documented as being incompatible with the default flyway.postgresql.transactional.lock setting.

[radek-los-s1]

You can put in gradle.properties:

flyway.postgresql.transactional.lock=false

[wleese]

Thanks @radek-los-s1 , however:

There are 3 ways I'm aware of how to run flyway migrations:

• Via a build (maven/gradle) plugin
• Via the flyway binary
• Programmatically, like how Spring Boot runs your migrations during application startup

Out of all the methods of configuring flyway migrations (https://flywaydb.org/documentation/configuration/parameters/postgresqlTransactionalLock), the only way to change a Spring Boot triggered flyway migration configuration, is via the provided Spring Boot Flyway properties / programmatically via the api.

We'd strongly prefer Spring Boot provides a property to configure this setting, as it has become a common source of issues for users.

[philwebb]

There is a configuration(Map<String,String> ...) method on FluentConfiguration. We might be able to surface properties to fill this map, however, we're not totally sure yet of the format theses properties take and if they'll fit with our application.properties style.

[wilkinsona]

The map takes properties in the standard Flyway format which is dot-separated with camel case parts.

If we had a Map<String, String> spring.flyway.configuration property, you would disable the transactional lock by configuring spring.flyway.configuration.flyway.postgresql.transactional.lock=false.

The map would allow any Flyway property to be configured, the vast majority of which would clash with our existing properties, for example:

spring.flyway.connect-retries=2
spring.flyway.configuration.flyway.connectRetries=3

Here connect retries would be either 2 or 3 depending on whether we applied the single spring.flyway.connect-retries property or the spring.flyway.configuration map of properties first.

My feeling is that if we're going to do something here, it should be for specific properties rather than opening up the Map<String, String> option.

[mathieu-pousse]

For information, this works fine:

    @Bean
    public FlywayConfigurationCustomizer flywayCustomizer() {
        return configuration -> configuration.configuration(Map.of("flyway.postgresql.transactional.lock", "false"));
    }

As org.springframework.boot.autoconfigure.flyway.FlywayProperties does not have any field to receive additional settings (to gather spring.flyway.configuration.*) and pass them to Flyway, I think that using a customizer is the only way so far

[nunotavaresbmw]

How do you use this property in application.properties?

flyway.postgresql.transactional.lock=false

[wilkinsona]

The application property is spring.flyway.postgresql.transactional-lock. This is also mentioned in the release notes which are recommended reading when upgrading to a new version of Spring Boot.

[jluiz20]

I am using Spring Boot 3.2.4 and Flyway 9.22.3 and have tested both spring.flyway.postgresql.transactional-lock=false from #32629 (comment) and flyway.postgresql.transactional.lock=false from flyway/flyway#3682 (comment) but nothing worked.

I have even tried

    @Bean
    public FlywayConfigurationCustomizer flywayCustomizer() {
        return configuration -> configuration.configuration(Map.of("flyway.postgresql.transactional.lock", "false"));
    }

from #32629 (comment)

However, my issue was that I was using a custom Flyway Bean, and because of that, the properties were not being loaded automatically.

For me, what worked was to build up from the previous answers, so I added
.configuration(Map.of("flyway.postgresql.transactional.lock", "false")) to my bean and it worked.

Below is a reduced form of it.

 @Bean
    public Flyway flyway(FlywayProperties flywayProperties)
    {
        
        Flyway flyway = Flyway.configure()
               // previous configurations

               // below the added line
                .configuration(Map.of("flyway.postgresql.transactional.lock", "false"))
                .load();

        flyway.migrate();

        return flyway;
    }

Not sure if this is the best approach, but it worked for me. If you guys have improvement suggestions, they are welcome.

[albertscholtz]

Solution provided by @jluiz20 was the only one that worked for me, after trying Gradle and Spring properties related suggestions, i.e. I had to create a custom Flyway bean. Gist

Using Spring Boot 3.2.4 and Flyway 10.11.0. I'm guessing it depends whether flyway is run from the Gradle plugin or spring boot app during startup which solution will have the desired effect.