PostgreSQL ALTER TYPE ADD VALUE cannot run inside a transaction block

What this failure means

A database migration that adds a value to a PostgreSQL ENUM type fails because PostgreSQL does not permit ALTER TYPE … ADD VALUE inside a transaction block. Migration frameworks that wrap each migration in a transaction (Alembic, Django, Flyway) hit this restriction by default.

Symptoms

Faultline looks for one or more of these log fragments:

ALTER TYPE ... ADD cannot run inside a transaction block
ALTER TYPE ... ADD VALUE cannot run inside a transaction block

Diagnosis

PostgreSQL prohibits adding a new value to an existing ENUM type inside a transaction. Migration frameworks wrap every migration in a BEGIN/COMMIT block for atomicity. When a migration calls:

ALTER TYPE my_enum ADD VALUE 'new_value';

PostgreSQL raises:

ALTER TYPE ... ADD cannot run inside a transaction block

The migration is immediately aborted. Any test run that depends on migrations being applied will fail with “current transaction is aborted” errors on all subsequent statements within the same migration.

Note: some migration files also contain raw SQL syntax errors (e.g., syntax error at or near "REMOVE") from typos or DB-dialect mismatches, which produce a separate but related abort cascade.

Fix steps

Option A — Split the migration (recommended)

Move the ALTER TYPE ... ADD VALUE statement into its own migration file so the framework can run it outside the normal transaction:

• Alembic: use connection.execute() with COMMIT before the ALTER and restart a new transaction after, or declare the migration as transaction_per_migration = False and manage the transaction manually:

  # alembic migration
  def upgrade():
      op.execute("COMMIT")
      op.execute("ALTER TYPE my_enum ADD VALUE IF NOT EXISTS 'new_value'")
      op.execute("BEGIN")
  

• Django: use RunSQL with atomic=False:

  migrations.RunSQL(
      "ALTER TYPE my_enum ADD VALUE 'new_value'",
      atomic=False,
  )
  

Option B — Replace ENUM with VARCHAR (simpler for frequent changes)

Convert the column to VARCHAR and validate values in application code or with a CHECK constraint. This avoids the ALTER TYPE restriction entirely.

Option C — Use IF NOT EXISTS to make repeated runs safe

ALTER TYPE my_enum ADD VALUE IF NOT EXISTS 'new_value';

This requires PostgreSQL 9.3+. It does not remove the transaction restriction but prevents the failure when the value already exists.

Validation

• Re-run the migration: alembic upgrade head or python manage.py migrate.
• Confirm no ALTER TYPE ... ADD cannot run inside a transaction block error.
• Re-run the test suite. No current transaction is aborted cascade errors.

Why it matters

The PostgreSQL restriction on ALTER TYPE inside a transaction is a longstanding limitation that surprises teams migrating from MySQL or SQLite. The abort cascade means the test log shows tens of “current transaction is aborted” errors obscuring the single root-cause line near the top. Identifying ALTER TYPE ... ADD cannot run inside a transaction block as the first real error simplifies resolution.

Prevention

• Isolate every ENUM value addition into its own migration file.
• Prefer VARCHAR + CHECK constraint over ENUM for columns that change frequently.
• Run all migrations in CI against a real PostgreSQL instance (not SQLite) so these restrictions surface before merging.

Try it locally

alembic upgrade head
python manage.py migrate --run-syncdb
alembic upgrade head
python manage.py migrate

How Faultline detects it

Use faultline explain postgres-enum-add-in-transaction to see the full playbook.

faultline analyze build.log
faultline explain postgres-enum-add-in-transaction

Generated from playbooks/bundled/log/test/postgres-enum-add-in-transaction.yaml. Do not edit directly.