Safe non‑transactional DDL execution in Yii 2.x migrations for PostgreSQL

Answer

Use the up() and down() methods instead of safeUp()/safeDown() so Yii does not wrap the whole migration in a transaction. Then manually control transaction boundaries for the parts that need atomicity and run the concurrent index statement outside any transaction.

Steps

1. Define the migration class with public function up() and public function down() (no safeUp/safeDown).
2. Begin a transaction for the transactional DDL that precedes the index:

$transaction = $this->db->beginTransaction();
try {
    // e.g., add a column, create a regular index, etc.
    $this->addColumn('{{%table}}', 'new_col', $this->string()->null());
    $transaction->commit();
} catch (\Exception $e) {
    $transaction->rollBack();
    throw $e;
}

3. Execute the concurrent index statement outside any transaction (Yii will be in autocommit mode after the commit):

$this->db->createCommand('CREATE INDEX CONCURRENTLY idx_table_new_col ON {{%table}} (new_col)')->execute();

4. If further transactional changes are needed after the index, start a new transaction and repeat the try/commit/rollBack pattern.
5. In down(), reverse the steps in opposite order, using transactions for the transactional parts and a simple DROP INDEX IF EXISTS for the concurrent index (which can run inside a transaction).

$this->db->createCommand('DROP INDEX IF EXISTS idx_table_new_col')->execute();
// then rollback transactional changes in reverse order

6. After a successful migration, flush Yii caches to avoid stale schema or data cache:

Yii::$app->cache->flush();
Yii::$app->getDb()->getSchema()->refresh();

Explanation vs. Confirmed Facts

• Confirmed: Yii 2 wraps safeUp/safeDown in a transaction; PostgreSQL rejects CREATE INDEX CONCURRENTLY inside any transaction block.
• Confirmed: Using up()/down() disables automatic transaction wrapping, allowing manual beginTransaction/commit calls.
• Likely: Splitting the migration into separate transactional sections preserves atomicity for those sections while the concurrent index runs in autocommit mode.
• Uncertain (needs verification): Whether your specific Yii 2.x version still does not wrap up()/down() in a transaction. If it does, the approach must be adjusted.

Missing Diagnostic Detail

Confirm that your Yii version does not automatically wrap up()/down() in a transaction (check yii\db\Migration::wrapInTransaction or run a test migration with a simple BEGIN; statement and see if it errors). This detail changes the recommendation because if wrapping still occurs, you would need to extend the migration class to disable it.