flyway: Flyway >=9.1.2 hanging forever on concurrent index creation

Please note that this breaking change was introduced in a minor version without calling it out as a breaking change in the release notes, which mentions it as a “new feature”.

If you allow a suggestion, in this case, the default should not have changed, but rather you could have given an option to enable transaction lock for whoever uses PgBouncer and keep the product backward compatible for everyone else, then in the next major version make the breaking change and enable transactional lock by default and call it out in the release notes so people are aware when they make the decision to upgrade to the next major.

any ideas how to set flyway.postgresql.transactional.lock with gradle?

Update, worked like this:

flyway {
    pluginConfiguration = [
        postgresqlTransactionalLock: "false"
    ]
    //...
}

I’ve tried with this previous minimal example and changing Flyway version to 9.19.4 or 9.21.1, but it still hangs when creating the index. Is there anything else that needs to be done to make it work? https://github.com/AvaPL/flyway-postgres-concurrent-index-issue

I don’t understand any of this configuration stuff, but I’ve been able to fix this for myself with the following code:

import org.flywaydb.core.api.configuration.FluentConfiguration
import org.springframework.boot.autoconfigure.flyway.FlywayConfigurationCustomizer
import org.springframework.context.annotation.Configuration

@Configuration
class FlywayConfig : FlywayConfigurationCustomizer {
    override fun customize(configuration: FluentConfiguration) {
        // Disable the transactional lock in Flyway that breaks all non-transactional migrations since v9.1.2 of the plugin
        // See https://github.com/flyway/flyway/issues/3508
        configuration.configuration(
            mapOf(
                "flyway.postgresql.transactional.lock" to "false",
            )
        )
    }
}

I suspect that this might be related to https://github.com/flyway/flyway/issues/2895 which was released in Flyway 9.1.2

The release notes say the following:

Use transactional locks in PostgreSQL by default, which enables support for PgBouncer (session locks can be enabled again by setting flyway.postgresql.transactional.lock=false)

I have also found a probably related issue https://github.com/flyway/flyway/issues/3492

My workaround currently is to set the property in Maven instead of the Flyway plugin configuration like this

    <properties>
        <!--
        Disable the transactional lock in Flyway that breaks all non-transactional migrations since v9.1.2 of the plugin
        See https://github.com/flyway/flyway/issues/3508
        -->
        <flyway.postgresql.transactional.lock>false</flyway.postgresql.transactional.lock>
    </properties>

Gradle plugin has exactly the same problem and for exactly the same reason. And the workaround is also similar:

ext['flyway.postgresql.transactional.lock'] = 'false'

Hi guys! Are you by any chance going to fix this issue? That’s kind of funny when someone uses your framework for a few years for big projects. And one day after the version update it just hangs. 40 days we are waiting for some fix, and there is no fix. Backward compatibility? Never heard of that. You should now configure additional properties to fix the issue in our new version. But why should we fix it from the user’s side? Maybe Flyway developers should set this property to false by default? Do you understand that all migrations that create an index for Postgres database users can’t be completed? Because using them without CONCURENTLY keyword on production env is just impossible.

@bhavik-gandhi-0101

If you looking for flyway developers to change the default, is a separate discussion for them. There has to be some reason, why they change the default for which they should provide an explaination.

Let me guess, the reason is that new versions of Flyway are just not working with the Postgres? Or maybe, just maybe because they changed the default 40 days ago and it broke backward compatibility? Why do developers who decided to use the Flyway have to justify themselves here and set up something urgently on their side a month later? Because PgBouncer and Spring are more popular than others? I’ve never seen such bad moves from dev teams before.

This has worked for me for Spring boot 3.1.5 and Flyway 9.22.3

@MrMaxxan you need to disable the Postgresql transactional lock behaviour

diff --git a/src/main/scala/Main.scala b/src/main/scala/Main.scala
index 5609318..7b934b0 100644
--- a/src/main/scala/Main.scala
+++ b/src/main/scala/Main.scala
@@ -10,6 +10,7 @@ object Main extends App {
 
   val flyway = Flyway
     .configure()
+    .configuration(java.util.Map.of("flyway.postgresql.transactional.lock","false"))
     .dataSource(dataSource)
     .load()

The only thing that would work for me was to call the API: PostgreSQLConfigurationExtension configurationExtension = configuration.getPluginRegister().getPlugin(PostgreSQLConfigurationExtension.class) configurationExtension.setTransactionalLock(false);

I guess this is because we call the migrate from the code, so setting the property is not sufficient.

If I got this right, below is an explanation of this behaviour.

Flyway creates two sessions in transactional mode: main and migration (link). The Main connection is responsible for locking on flyway_schema_history table, while the migration one for scripts applying.

Creating an index with a concurrent option is a multi-staged process. It is described here.

The problem is because of the second scan: “After the second scan, the index build must wait for any transactions that have a snapshot (see Chapter 13) predating the second scan to terminate, including transactions used by any phase of concurrent index builds on other tables, if the indexes involved are partial or have columns that are not simple column references.”

So creating an index process in the migration connection is waiting for transaction on flyway_schema_history table in the main one and vice versa. There is deadlock.

I reproduced this scenario on an empty table with enabled logs in PostgreSQL:

2023-05-05 22:23:05.492 UTC [56] LOG:  execute <unnamed>: BEGIN
2023-05-05 22:23:05.492 UTC [56] LOG:  execute <unnamed>: SELECT pg_try_advisory_xact_lock(77433833903597)
2023-05-05 22:23:05.492 UTC [56] LOG:  execute S_2: SET ROLE 'flyway'
2023-05-05 22:23:05.493 UTC [56] LOG:  execute S_3: SELECT EXISTS (
	  SELECT 1
	  FROM   pg_catalog.pg_class c
	  JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
	  WHERE  n.nspname = $1
	  AND    c.relname = $2
	  AND    c.relkind = 'r'
	)
2023-05-05 22:23:05.493 UTC [56] DETAIL:  parameters: $1 = 'public', $2 = 'flyway_schema_history'
2023-05-05 22:23:05.494 UTC [56] LOG:  execute <unnamed>: /*NO LOAD BALANCE*/
	SELECT "installed_rank","version","description","type","script","checksum","installed_on","installed_by","execution_time","success" FROM "public"."flyway_schema_history" WHERE "installed_rank" > $1 ORDER BY "installed_rank"
2023-05-05 22:23:05.494 UTC [56] DETAIL:  parameters: $1 = '-1'
2023-05-05 22:23:05.497 UTC [57] LOG:  execute <unnamed>: SET ROLE 'flyway'
2023-05-05 22:23:05.498 UTC [56] LOG:  execute S_4: SELECT COUNT(*) FROM pg_namespace WHERE nspname=$1
2023-05-05 22:23:05.498 UTC [56] DETAIL:  parameters: $1 = 'public'
2023-05-05 22:23:05.498 UTC [57] LOG:  execute <unnamed>: SELECT set_config('search_path', $1, false)
2023-05-05 22:23:05.498 UTC [57] DETAIL:  parameters: $1 = '"public","$user", public'
2023-05-05 22:23:05.499 UTC [57] LOG:  execute <unnamed>: SELECT version()
2023-05-05 22:23:05.500 UTC [57] LOG:  execute <unnamed>: create index concurrently flyway_demo_concurrent_index on flyway_demo(concurrent)

I also made a request to show blocked processes:

select pid, 
       usename, 
       pg_blocking_pids(pid) as blocked_by, 
       query as blocked_query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0;

pid|usename|blocked_by|blocked_query|
57|flyway |{56}     |create index concurrently flyway_demo_concurrent_index on flyway_demo(concurrent)

After I tried to reproduce it manually, but didn’t succeed.

@NJAldwin Which version are you using? For me, prefixing filenames with NT just leads to them being ignored and a message saying 1 SQL migrations were detected but not run because they did not follow the filename convention.

I think most of us ended up here because after bumping version we expect it continue to work, but it didn’t.

If I understand correctly, starting from version 9.1.2 default configuration works for pgbouncer, but not for plain postgresql server

• Solution is provided by flyway - https://flywaydb.org/documentation/configuration/parameters/postgresqlTransactionalLock
• Tested out and its working.
• I made the recommended changes as a part of flyway.conf and other solution is to make it part of individual SQL Script ex: Say there is a SQL Script V1.011__CreatingIndex.sql, which has the SQL statement creating concurrent index, then create a file “V1.011__CreatingIndex.sql.conf” and the content of the conf file will be “flyway.postgresql.transactional.lock=false”

I’ve encountered the same problem today using Flyway 9.3.1. Here is my minimal example in Scala: https://github.com/AvaPL/flyway-postgres-concurrent-index-issue.

My workaround currently is to set the property in Maven instead of the Flyway plugin configuration like this

    <properties>
        <!--
        Disable the transactional lock in Flyway that breaks all non-transactional migrations since v9.1.2 of the plugin
        See https://github.com/flyway/flyway/issues/3508
        -->
        <flyway.postgresql.transactional.lock>false</flyway.postgresql.transactional.lock>
    </properties>