How do I create indexes concurrently with flyway in postgres?

Question

Postgres allows the creation of indexes without locking the table using the CONCURRENTLY option. This options is not allowed in a transaction, so when it is added to a flyway migration script flyway fails:

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

ActiveRecord supports the disable_ddl_transaction! to address this scenario. Does flyway support some way to run such a script outside of a transaction?

Mark Rotteveel · Answer 1 · 2021-08-31T09:28:44.077

This was implemented in Flyway 4.1, see ticket 851 and Non-Transactional PostgreSQL Support in Flyway (link broken).

This functionality works by detecting that a statement should be executed without a transaction. As far as I know, this is currently only supported for PostgreSQL.

The first hurdle is detecting when a migration needs to be run outside of a transaction. Two paths forward are evident. The first path, [..], is to create a filename annotation. The second path is to add a statement parser to Flyway that detects non-transactional commands and runs them in the appropriate isolation level. Despite the extra work it would require on their part, the Boxfuse team was steadfast on following the second path. It provides the better user experience for Flyway users in the long term.

By default a migration that is executed this way, should only contain statements that need to be executed without a transaction. Mixing it with transactional statements is not allowed by default, and needs to be enabled with a property.

Creating a condition that migrations must not mix transactional with non-transactional statements creates a restriction that may introduce an unseen problem for a user in the future. In case this unknown situation requiring DDL that normally would be run inside of a transaction needs to be handled, a property called flyway.allowMixedMigrations is added to the configuration. By default it is false. I strongly suggest to never turn it on.

The statements that are handled non-transactionally are determined by code in PostgreSQLParser:

@Override
protected Boolean detectCanExecuteInTransaction(String simplifiedStatement, List<Token> keywords) {
    if (CREATE_DATABASE_TABLESPACE_SUBSCRIPTION_REGEX.matcher(simplifiedStatement).matches()
            || ALTER_SYSTEM_REGEX.matcher(simplifiedStatement).matches()
            || CREATE_INDEX_CONCURRENTLY_REGEX.matcher(simplifiedStatement).matches()
            || REINDEX_REGEX.matcher(simplifiedStatement).matches()
            || VACUUM_REGEX.matcher(simplifiedStatement).matches()
            || DISCARD_ALL_REGEX.matcher(simplifiedStatement).matches()) {
        return false;
    }

    boolean isDBVerUnder12 = true;
    try {
        isDBVerUnder12 = !parsingContext.getDatabase().getVersion().isAtLeast("12");
    } catch (Exception e) {
        LOG.debug("Unable to determine database version: " + e.getMessage());
    }
    
    if (isDBVerUnder12 && ALTER_TYPE_ADD_VALUE_REGEX.matcher(simplifiedStatement).matches()) {
        return false;
    }

    return null;
}

@ZhongxiaZhou I updated the link to the class in Flyway code (and the code), but the second link simply no longer exists and I could find no new location, nor is it in archive.org. I have left the link in place as I quote parts from it. — Mark Rotteveel, Aug 31 '21 at 09:33

score 3 · Accepted Answer · answered Dec 03 '13 at 11:58

3

Not at this point. All migrations currently run within a transaction. Feel free to file an enhancement request in the issue tracker.

answered Dec 03 '13 at 11:58

Axel Fontaine

34,542
16
106
137

3

2015 - it is still not possible to run create index concurrently with flyway ;-) – Regenschein Aug 28 '15 at 12:37
Correction: just an example seems to be missing, feature already implemented: https://github.com/flyway/flyway/issues/1072 – Regenschein Aug 28 '15 at 12:40
Seems like someone built this feature for the Java API Can this be done via command-line version of flyway? – Raymond26 Mar 02 '16 at 00:27

Pavel Evstigneev · Answer 3 · 2022-10-04T18:29:34.983

1

For flyway >= 9.1.2, it tries to be compatible with pgbouncer by default, and creating concurrent index will hang the process.

To fix it we need to set postgresqlTransactionalLock - https://flywaydb.org/documentation/configuration/parameters/postgresqlTransactionalLock

Example for gradle

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

Example for custom runner

FluentConfiguration flywayConfiguration = Flyway.configure()
        .baselineOnMigrate(true)
        .dataSource(url, username, password) // FROM CONFIG
        .outOfOrder(true)
        .table("flyway_schema_history");
flywayConfiguration.getPluginRegister()
        .getPlugin(PostgreSQLConfigurationExtension.class)
        .setTransactionalLock(false);
Flyway flyway = flywayConfiguration.load();
flyway.migrate();

Taken from github issue: https://github.com/flyway/flyway/issues/3508

edited Oct 04 '22 at 18:29

answered Oct 02 '22 at 18:09

Pavel Evstigneev

4,918
31
21

Hi Pavel - adding the configuration to the Gradle build script isn't effective in my experience, although defining the configuration on the command line does work. I've tried Gradle 6.8.3 and 7.4. What version of Gradle are you using that this configuration works with? – Mike Partridge Oct 04 '22 at 15:53
gradle 7.4.2. What error did you get? – Pavel Evstigneev Oct 04 '22 at 18:26
No error. Applying a migration with CREATE INDEX CONCURRENTLY just hangs. I'm testing with Flyway v9.4.0. – Mike Partridge Oct 04 '22 at 18:42
The problem script is labeled as `[non-transactional]` by Flyway. I do get the `cannot run inside a transaction block` error when trying a script containing `REINDEX TABLE CONCURRENTLY`. – Mike Partridge Oct 04 '22 at 18:50
2

I tried again, my migrations hangs too, running with env variable works `FLYWAY_POSTGRESQL_TRANSACTIONAL_LOCK=false` and if initializing flywayConfiguration in code works too. might be a bug in gradle plugin – Pavel Evstigneev Oct 10 '22 at 17:42

score -2 · Answer 4 · edited May 23 '17 at 12:10

-2

According to this post:

Flyway migration hangs for postgres CREATE INDEX CONCURRENTLY

You can just add NT as a prefix to your migration so

V201609121806__create_index_for_table.sql

becomes

NTV201609121806__create_index_for_table.sql

edited May 23 '17 at 12:10

Community

1
1

answered Sep 22 '16 at 20:19

Benjamin Liu

313
1
8

2

This actually doesn't work. Refer to Axel Fontaine's answer for this. The custom resolver PR never got merged and I'm not sure how to actually solve this. – Benjamin Liu May 22 '17 at 18:45

How do I create indexes concurrently with flyway in postgres?

4 Answers4