CODEWITHSUNDEEP
postgresqlflyway
Anthony Galea
Anthony Galea

Reputation: 310

How do I create indexes concurrently with flyway in postgres?

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?

Upvotes: 16

Views: 22309

Answers (4)

Pavel Evstigneev
Pavel Evstigneev

Reputation: 5116

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

Upvotes: 1

Mark Rotteveel
Mark Rotteveel

Reputation: 109014

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;
}

Upvotes: 13

Benjamin Liu
Benjamin Liu

Reputation: 313

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

Upvotes: -2

Axel Fontaine
Axel Fontaine

Reputation: 35169

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

Upvotes: 3

Related Questions