A non-robust migration that fails after partially applying may fail again when retried.
You may have a migration with two steps:
- add new column named
- create index
Since step 2 is concurrent, we cannot run this migration in a transaction.
Without a transaction, step 1 can succeed while step 2 fails. This would leave
us with the new
billing_email column, but without the index from step 2.
When we rerun our migration, the migration will fail at step 1, since the field
billing_email field already exists.
To make this change robust, we can follow the "add column" and
"add index" examples shown below and use
IF NOT EXISTS when
adding our column and index.
With this change, we can run our migration multiple times without erroring.
To appease this rule you can use guards like
IF NOT EXISTS or wrap all your
statements in a transaction.
CREATE INDEX CONCURRENTLY failing will leave behind
INVALID index objects
that still occupy their respective names. This means that if a name is not
specified, a failing migration being run multiple times will create multiple
duplicate indexes with consecutive names, all of which are
If you provide a name and
IF NOT EXISTS, the second run will falsely succeed
because the index exists, even though it is invalid. This is another footgun,
albeit a checkable (see the
pg_index catalog) one.
#solution for alembic and sqlalchemy
Alembic doesn't support
IF NOT EXISTS. You must use raw SQL via
op.execute(). See this GitHub Issue tracking
IF NOT EXISTS support for more information.