CREATE CONCURRENT INDEX waits indefinitely on PostgessDB

[Maverick19841972]

We upgraded from Flyway 9.0.4 to 9.22.3 (Provided by Spring-Boot 3.2.2) and ran into a problem regarding the concurrent creation of indexes on a Postgres DB. The problem seems to originate from the flyway-core class PostgreSQLParser. This class has a detectCanExecuteInTransaction method which contains the following condition !CREATE_INDEX_CONCURRENTLY_REGEX.matcher(simplifiedStatement).matches(). This evaluates to false (!true because we do a CREATE INDEX CONCURRENTLY) which, in turn, causes the method to return false. This causes the CREATE INDEX CONCURRENT statement to run non-transactional. Consequently the CREATE INDEX CONCURRENT statement hangs indefinitely.

Flyway 9.0.4 seems to be de higest 9.x version for which we do not have this problem.

For testing purposes we are running an Embedded Postgress (io.zonky.test.db.postgres.embedded version 2.0.4) but I think its also applicable for non embedded Progresses.

We implemented the following work around: use maven-dependency management to downgrade to Flyway 9.0.4. We also tries to supply an extra .conf file with executeInTransaction=true. This is picked-up but results in de following error, which is sort-of logical:

SQL State : 25001
Error Code : 0
Message : ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block

---

Which version and edition of Flyway are you using: 9.22.3

If this is not the latest version, can you reproduce the issue with the latest one as well? (Many bugs are fixed in newer releases and upgrading will often resolve the issue): I believe 9.22.3 is the highest 9.x version

Which client are you using? (Command-line, Java API, Maven plugin, Gradle plugin): Spring-Boot 3.2.2 Java application, using io.zonky.test.db.postgres.embedded version 2.0.4 embedded Postgress (PostgresSQL 14.8 according to the Flyway DatabaseTypeRegister-class)

Which database are you using? (Type & version): see above.

Which operating system are you using?: not applicable

What did you do? (Please include the content causing the issue, any relevant configuration settings, the SQL statement(s) that failed (if any), and the command you ran): Flyway ran trough a migrate-method call on the FluentConfiguration.

What did you expect to see? Creation of indexes

What did you see instead? Execution of script hangs indefinitely.

[JasonLuo-Redgate]

Hi @Maverick19841972 , this is a topic which was solved in #3682.

To avoid this issue, two conditions need to be met:

• Flyway 9.19.4 version or higher
• postgresql.transactional.lock set to false