What this usually means
PostgreSQL's ALTER TYPE ... ADD VALUE is not transactional—it cannot be rolled back and cannot run inside a transaction block. Many migration tools wrap DDL statements in transactions, causing this error. Additionally, enums have ordering constraints: new values can only be added before or after existing ones, not arbitrarily inserted. If you try to add a value that already exists or in an invalid position, you'll get an error. Also, if you add a value but don't commit before using it in another session, you'll see errors about missing enum values.
The first ten minutes — establish facts before touching code.
- 1Run your migration script manually in psql: if you see 'ALTER TYPE ... ADD VALUE cannot run inside a transaction block', your tool is wrapping it in a transaction.
- 2Check migration tool docs: for Alembic, set transactional_ddl = False for this migration; for Flyway, use a non-transactional script.
- 3Verify if the enum value already exists: SELECT enum_range(NULL::your_enum_type);
- 4Check if you're using the enum value in the same transaction after adding it: that can cause errors because the new value isn't visible yet.
- 5Review migration order: if you add a value and then use it in a column default or constraint in the same migration, it will fail because the new value isn't committed.
The specific files, logs, configs, and dashboards that usually own this bug.
- searchMigration script files (e.g., alembic/versions/*.py, flyway/V*.sql)
- searchpsql logs or migration tool logs (e.g., alembic.log, flyway-*.log)
- searchPostgreSQL error log: typically /var/log/postgresql/postgresql-*.log or via pg_stat_activity
- searchDatabase schema dump: pg_dump --schema-only your_db
- searchEnum type definition: SELECT oid, typname FROM pg_type WHERE typcategory='E'; then SELECT enumlabel FROM pg_enum WHERE enumtypid=oid;
- searchCurrent transaction state: SELECT pg_current_xact_id_if_assigned(); (if inside transaction, returns an XID)
Practical causes, not theory. These are the things you will actually find.
- warningMigration tool wraps DDL in a transaction (Alembic's transactional_ddl=True by default)
- warningAdding enum value inside a BEGIN/COMMIT block in raw SQL migration
- warningAttempting to add an enum value that already exists (duplicate value error)
- warningAdding a value in the wrong order (e.g., trying to add 'b' AFTER 'd' when 'c' doesn't exist? actually that's allowed; but if you specify BEFORE/AFTER a non-existent value, error)
- warningUsing the new enum value in the same transaction before it's committed (other sessions don't see it)
- warningRunning multiple migrations concurrently that both add values to the same enum type, causing deadlocks
Concrete fix directions. Pick the one that matches your root cause.
- buildFor Alembic: set transactional_ddl = False in the migration's run_migrations_offline context, or use autocommit isolation level.
- buildFor Flyway: name the migration file with a 'N' prefix (e.g., V2_N__add_enum_value.sql) to run non-transactionally.
- buildFor raw SQL: remove BEGIN/COMMIT around the ALTER TYPE statement, or use a separate connection with autocommit.
- buildIf you must add inside a transaction, use a workaround: create a new enum type, drop the old one, rename. But this is heavy and requires updating all columns.
- buildAdd the enum value in a separate migration step that runs before any code that uses it.
- buildUse ALTER TYPE ... ADD VALUE ... BEFORE/AFTER to maintain ordering if needed.
A fix you cannot prove is a guess. Close the loop.
- verifiedAfter running the migration, connect to the database and run SELECT enum_range(NULL::your_enum_type); to see the new value.
- verifiedVerify that new rows can be inserted with the new enum value.
- verifiedCheck that no errors appear in application logs when using the new value.
- verifiedRun the migration a second time (idempotency check)—it should skip or gracefully handle if the value already exists.
- verifiedTest rollback: attempt to drop the value (if needed) or ensure rollback doesn't break the database.
Things that make this bug worse or harder to find.
- warningDo not add the enum value and then use it in the same transaction—it will cause errors because the value isn't committed yet.
- warningDo not assume ALTER TYPE ... ADD VALUE is transactional—it's not and cannot be rolled back.
- warningDo not add duplicate values—Postgres will throw an error; always check existing values first.
- warningDo not ignore the ordering: if your application expects a specific order, use BEFORE/AFTER appropriately.
- warningDo not run multiple migrations that add values to the same enum type concurrently—they can deadlock.
Production outage: Enum migration fails during deploy causing rollback chaos
Timeline
- 09:15Deploy starts: migration to add new status 'ARCHIVED' to 'order_status' enum
- 09:17Alembic migration fails: 'ALTER TYPE ... ADD VALUE cannot run inside a transaction block'
- 09:18CircleCI marks job as failed, but migration was partially applied because DDL is non-transactional
- 09:20On-call engineer manually adds the enum value via psql to unblock
- 09:25Second migration that uses 'ARCHIVED' in a column default runs and commits successfully
- 09:30Rollback triggered: Alembic tries to rollback, but enum value is not removed (non-transactional)
- 09:35Application crashes because code expects 'ARCHIVED' but enum has it, but schema mismatch?
- 09:45Manual cleanup: drop enum value, restore schema, rerun migrations with transactional_ddl=False
Our CI/CD pipeline runs Alembic migrations inside a transaction by default. When we tried to add a new value 'ARCHIVED' to the 'order_status' enum, PostgreSQL rejected it because ALTER TYPE ... ADD VALUE cannot be executed inside a transaction block. The migration script had the statement wrapped in a transaction by Alembic's transactional_ddl=True. The job failed, but the error message was clear.
The on-call engineer, not fully aware of the non-transactional nature, manually ran the ALTER TYPE statement via psql to unblock the deployment. That worked, and subsequent migrations ran fine. However, when the deployment was rolled back due to a different issue, Alembic attempted to roll back the migration. Since the ALTER TYPE cannot be rolled back, the enum value remained. The rollback then failed, leaving the database in an inconsistent state.
The application code that expected the enum to have only 'PENDING', 'SHIPPED', 'DELIVERED' suddenly encountered 'ARCHIVED' in some rows (inserted during the brief window). This caused unexpected behavior in status transitions. We had to manually remove the enum value (which required dropping and recreating the type, affecting all columns using it), restore the schema from a backup, and then re-run the migration with transactional_ddl=False set in the migration script. Lesson: never use transactional DDL for ALTER TYPE ENUM.
Root cause
Alembic's default transactional_ddl=True wraps ALTER TYPE ... ADD VALUE in a transaction, causing it to fail. The manual intervention and rollback made it worse.
The fix
Set transactional_ddl = False in the migration's run_migrations_offline context and ensure the migration is run outside a transaction block. Also, add a check for existing enum values to make the migration idempotent.
The lesson
Understand which DDL statements are non-transactional in PostgreSQL (like ALTER TYPE ... ADD VALUE, CREATE INDEX CONCURRENTLY, etc.) and configure your migration tool accordingly. Always test migrations in isolation.
PostgreSQL's enum types are stored in system catalogs (pg_enum). Adding a new value requires an exclusive lock on the enum type, and the operation is not WAL-logged for rollback. The PostgreSQL documentation explicitly states: 'ADD VALUE ... cannot be executed inside a transaction block.' This is a design decision to keep enum additions lightweight and avoid complex rollback scenarios.
The main implication is that once you add a value, it's permanent. If you need to roll back a migration that added an enum value, you must manually revert it (e.g., by dropping the value, which also requires dropping all columns using the enum). Migration tools that rely on transactional DDL will fail with the error. You must run these statements outside any explicit transaction.
The safest approach is to run ALTER TYPE ... ADD VALUE in a separate migration file that is executed outside a transaction. For Alembic, you can set `transactional_ddl = False` in the migration's `run_migrations_offline` context or use the `op.execute` with `autocommit=True`. For Flyway, prefix the migration with 'N' (e.g., V2_N__add_enum_value.sql).
Additionally, make the migration idempotent by checking if the value already exists. In PostgreSQL 9.1+, you can use `DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_enum WHERE enumlabel = 'new_value' AND enumtypid = 'enum_type'::regtype) THEN ALTER TYPE enum_type ADD VALUE 'new_value'; END IF; END $$;` This prevents errors on re-run.
By default, new values are added at the end of the enum's sort order. If you need the new value to appear in a specific position (e.g., for sorting by TEXT representation), you can use `ALTER TYPE enum_type ADD VALUE 'new_value' BEFORE 'existing_value'` or `AFTER`. However, you cannot specify a relative position that is invalid (e.g., BEFORE a non-existent value).
If you need to reorder enum values, you must create a new enum type with the desired order, update all columns to use the new type, and drop the old type. This is a heavy operation and should be avoided if possible. In practice, most applications don't depend on enum ordering.
When multiple sessions attempt to add values to the same enum type concurrently, they may deadlock because each ADD VALUE acquires an exclusive lock on the enum type. PostgreSQL's lock manager will detect the deadlock and abort one of the transactions. To avoid this, ensure that enum additions are serialized (e.g., run as part of a single migration or use advisory locks).
A common pattern is to add all needed enum values in one migration step, rather than multiple incremental migrations. This reduces the chance of concurrent modifications.
Frequently asked questions
Can I use ALTER TYPE ... ADD VALUE inside a stored procedure or function?
No. Stored procedures and functions run inside a transaction block (unless you use SET autocommit, but that's not typical). The error will occur. You must execute ALTER TYPE outside any function or procedure, directly via SQL client or migration tool outside transaction.
How do I remove an enum value if I accidentally added it?
PostgreSQL does not support dropping a single enum value. You must create a new enum type without the unwanted value, update all columns using the old enum to the new type (using ALTER COLUMN TYPE ... USING), then drop the old type. This is a multi-step process that can lock tables. Alternatively, you can rename the enum type and recreate it with the desired values.
Does the same restriction apply to ALTER TYPE ... RENAME VALUE or ALTER TYPE ... SET SCHEMA?
RENAME VALUE and SET SCHEMA are also DDL operations and they also cannot run inside a transaction block. In fact, most ALTER TYPE variants are non-transactional. Always check the PostgreSQL documentation for the specific command.
What if I'm using a migration tool that doesn't support non-transactional scripts?
You can work around it by using raw SQL execution with a separate connection that has autocommit enabled. For example, in Alembic, you can use `with op.get_context().autocommit_block():` (available in Alembic 1.8+). Or you can manually execute the SQL using `op.execute` with `execution_options={'autocommit': True}`. For older tools, consider splitting the migration into a pre-migration script that runs outside the migration framework.
Can I add multiple enum values in one ALTER TYPE statement?
No, PostgreSQL's ALTER TYPE ... ADD VALUE syntax only allows adding one value at a time. You need to execute multiple ALTER TYPE statements, each outside a transaction block. You can group them in a single migration file but each statement must be executed separately (not in a single transaction).