Migrations break on 5.3 on CREATE INDEX CONCURRENTLY

[djeux]

• Laravel Version: 5.3.*
• PHP Version: *
• Database Driver & Version: postgres 10.4

Description:

If a migration contains DB::statement('CREATE INDEX CONCURRENTLY') , which is a way in postgres to create indexes without locking. The migration will fail due to postgres unable to wrap this statement inside a transaction

Illuminate\Database\QueryException: SQLSTATE[25001]: Active sql transaction: 7 ERROR:  CREATE INDEX CONCURRENTLY cannot run inside a transaction block 

The only workaround for this is disabling them all together for migrations

$this->app->afterResolving('migrator', function(Migrator $migrator)
        {
            $connection = $migrator->resolveConnection(null);
            // NoTransactionPostgresGrammar override PostgresGrammar and sets $transactions = false
            $connection->setSchemaGrammar(new NoTransactionsPostgresGrammar());
            return $migrator;
        });

Steps To Reproduce:

Add DB::statement('CREATE INDEX CONCURRENTLY ...') inside a migration up() method.

[ahinkle]

Hi @djeux,

Patches and minor fixes for 5.3 are no longer supported. See https://laravel.com/docs/5.7/releases#support-policy

Are you able to replicate this issue on 5.5 LTS or 5.7?

[driesvints]

Heya, unfortunately we don't support this version anymore. Please check out our support policy on which versions we are currently supporting. Can you please try to upgrade to the latest version and see if your problem persists? If so feel free to reply and we'll try to have a look.

[mfn]

The "problem" still exists (I'm on 5.7 with Postgres).

However one can argue the change itself is a deliberate design decision hence not necessarily a bug.

The "feature request" would be about making it easier and more specific when to disable DDL transactions for certain operations.

I think this is best left for a discussion at https://github.com/laravel/ideas followed by a PR when there's consensus.

[staudenmeir]

As a workaround, you can commit the transaction before executing your query:

DB::commit();
DB::statement('CREATE INDEX CONCURRENTLY ...');

In Laravel 5.5+, you can add this to your migration (#22757):

public $withinTransaction = false;