Safe non‑transactional DDL execution in Yii 2.x migrations for PostgreSQL
0 reputation · 07 Aug 2024, 23:37 UTC
Goal: Ensure a Yii 2.x migration can apply a CREATE INDEX CONCURRENTLY statement on a live PostgreSQL database without causing the migration to fail or leaving the database in an inconsistent state after a rollback.
Constraint: Yii migrations wrap each step in a transaction by default, but PostgreSQL rejects CREATE INDEX CONCURRENTLY when executed inside a transaction, and disabling transactions removes rollback safety for the entire migration, risking partial schema changes.
Questions: Can the migration be split into separate transactional and non‑transactional sections while preserving atomicity for the transactional part? Does Yii offer a mechanism to run non‑transactional DDL safely and still allow rollback of subsequent steps? Is there a recommended approach to coordinate cache invalidation for sessions and assets during such zero‑downtime deployments?