ALTER TYPE ADD VALUE Inside a Transaction: The Error Changed in Postgres 12

On PostgreSQL 12 and later, ALTER TYPE ... ADD VALUE runs fine inside a transaction. The statement that fails is the next one, the one that uses the value you just added:

ERROR:  unsafe use of new value "refunded" of enum type order_status
HINT:  New enum values must be committed before they can be used.

On PostgreSQL 11 and earlier, the ALTER TYPE never runs at all:

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

That is the whole answer. One migration file, two errors, and which one you get is decided by your major version, not by your migration tool. The fixes differ too: on 11 you have to get the statement out of the transaction, on 12 and later you have to get the use of the value into a later transaction. The rest of this page is the source-verified detail, including why the lock this takes is not on any table of yours, and why the same file passes on a freshly built test database.

Why the fix you find first is usually the wrong one

Search either error string and the first page is migration-tool issue threads, most filed before PostgreSQL 12 shipped on 2019-10-03. They converge on one answer: turn the transaction off for this migration. On PostgreSQL 11 that is correct, and it is the only option available. It is also a setting other statements genuinely need, so it is easy to reach for: see CREATE INDEX CONCURRENTLY in a transaction is a silent footgun.

On 12 and later it usually makes the error go away too, and a fix that works is a fix that spreads. With the wrapper gone each statement commits on its own, so by the time the UPDATE runs, the new label is committed and the safety check lets it through. What you traded for that is the migration’s atomicity. The ALTER TYPE now commits independently of everything after it, and PostgreSQL has no ALTER TYPE ... DROP VALUE in any version, so a failure later in the same file leaves a value nothing can remove. That is the enum exit ramp problem arriving by accident instead of by decision.

Quick reference: what these statements actually lock

Statement	Lock mode	Blocks reads	Blocks writes	Blocks other DDL
CREATE TYPE ... AS ENUM	None on any existing object	No	No	No
ALTER TYPE ... ADD VALUE (PG12+)	EXCLUSIVE, on the type object, not on any table	No	No	Yes, another ALTER TYPE or DROP TYPE on the same enum
ALTER TYPE ... RENAME VALUE	EXCLUSIVE, on the type object	No	No	Yes, same as above

CREATE TYPE takes no lock on any object of yours, which is what the first row says. pgfence still scores it at ACCESS SHARE, the weakest mode in the matrix and the analyzer’s floor for a statement that blocks nothing, so explain prints Blocks: other DDL next to it: that line is the generic translation of ACCESS SHARE, not a claim about this statement. Full map: lock safety checks.

The migration that does this

orders has 40 million rows and a status column of type order_status, an enum created 18 months ago. The product adds refunds:

-- migrations/0147_orders_status_refunded.sql
BEGIN;
ALTER TYPE order_status ADD VALUE 'refunded';
UPDATE orders SET status = 'refunded' WHERE refund_id IS NOT NULL;
COMMIT;

To watch it fail, build the starting state and let it commit first. That separation is load-bearing, for a reason two sections below:

-- Setup. Run this on its own and let it commit.
CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped');

CREATE TABLE orders (
  id        bigserial PRIMARY KEY,
  status    order_status NOT NULL DEFAULT 'pending',
  refund_id bigint
);

INSERT INTO orders (status, refund_id)
SELECT 'shipped', CASE WHEN i % 1000 = 0 THEN i ELSE NULL END
FROM generate_series(1, 100000) AS i;

Now run the migration. On every version from 12 onwards:

BEGIN
ALTER TYPE
ERROR:  unsafe use of new value "refunded" of enum type order_status
HINT:  New enum values must be committed before they can be used.

The ALTER TYPE succeeded. The UPDATE is what failed, under SQLSTATE 55P04, unsafe_new_enum_value_usage, and it takes the whole transaction down with it.

What it looks like in production

This is not a lock incident, which is worth saying plainly, because the reflex when a migration fails is to hunt for a lock queue. That failure looks completely different. Here there is nothing to find: pg_stat_activity shows one failed statement and nothing waiting behind it. The 40 million rows were never scanned. The error comes out of enum_in(), the enum input function in src/backend/utils/adt/enum.c, which calls check_safe_enum_use() before converting the literal 'refunded' into a stored value, so it fires on the literal in the first millisecond.

What the on-call engineer sees depends on whether the transaction wrapper was there. With it, the whole transaction rolls back, the new label with it, and the file can be re-run unchanged. Without it, the ALTER TYPE has already committed and only the UPDATE failed: the migration is recorded as failed, the schema is one value ahead of where the tool thinks it is, and re-running the same file dies somewhere new.

ERROR:  enum label "refunded" already exists

That is what turns a five minute fix into a manual repair mid-deploy, and the transaction opt-out is what produces it.

What actually locks, and why it is not your table

AddEnumLabel() in src/backend/catalog/pg_enum.c takes exactly one lock, and it is not on a relation:

/*
 * Acquire a lock on the enum type, which we won't release until commit.
 * This ensures that two backends aren't concurrently modifying the same
 * enum type.  Without that, we couldn't be sure to get a consistent view
 * of the enum members via the syscache.  Note that this does not block
 * other backends from inspecting the type; see comments for
 * RenumberEnumType.
 */
LockDatabaseObject(TypeRelationId, enumTypeOid, 0, ExclusiveLock);

That line is identical in every branch from REL_11_STABLE through REL_18_STABLE. LockDatabaseObject() locks a catalog object, not a table, so it appears in pg_locks with locktype = 'object', a classid of pg_type, and a null relation:

-- Second session, while the ALTER TYPE transaction is still open
SELECT locktype, classid::regclass AS catalog, objid::regtype AS enum_type,
       mode, granted
FROM pg_locks
WHERE locktype = 'object' AND classid = 'pg_type'::regclass;

Nothing that touches a table waits on that. SELECT, INSERT, UPDATE and DELETE against every table with a column of that enum type keep running, and so does every ALTER TABLE, CREATE INDEX and VACUUM on those tables. So does anything reading the type definition, which the comment states outright: “this does not block other backends from inspecting the type”.

Two things do wait, symmetrically. Another ALTER TYPE ... ADD VALUE or RENAME VALUE on the same enum takes the same ExclusiveLock on the same object, and two EXCLUSIVE requests on one object conflict. DROP TYPE on that enum takes AccessExclusiveLock on the object through AcquireDeletionLock() in src/backend/catalog/dependency.c. The lock is held until commit, but the work underneath it is a single catalog insert. pgfence classifies this as EXCLUSIVE at LOW risk from PostgreSQL 12 onwards: see ALTER TYPE … ADD VALUE on PG12+.

What Postgres 12 changed, exactly

On PostgreSQL 11 and earlier, AlterEnum() in src/backend/commands/typecmds.c refuses up front with PreventInTransactionBlock(isTopLevel, "ALTER TYPE ... ADD"), under SQLSTATE 25001. The statement name passed there is ALTER TYPE ... ADD, not ADD VALUE, which is why the error text reads the way it does. PostgreSQL 9.6 and 10 pass the identical string to the older PreventTransactionChain(), so the message goes back unchanged. The reason sits in the comment above that call: “we can’t cope with enum OID values getting into indexes and then having their defining pg_enum entries go away.”

From the PostgreSQL 12.0 release notes:

Previously, ALTER TYPE … ADD VALUE could not be called in a transaction block, unless it was part of the same transaction that created the enumerated type. Now it can be called in a later transaction, so long as the new enumerated value is not referenced until after it is committed.

The refusal was not moved elsewhere, it was replaced by a visibility check. typecmds.c contains zero calls to PreventInTransactionBlock() in every branch from REL_12_STABLE through REL_18_STABLE. What enforces the rule now is check_safe_enum_use() in enum.c, and its header comment explains why the rule exists at all:

We need to make sure that uncommitted enum values don’t get into indexes. If they did, and if we then rolled back the pg_enum addition, we’d have broken the index because value comparisons will not work reliably without an underlying pg_enum entry.

An enum value is stored as the OID of its pg_enum row, so an index entry pointing at a rolled-back row would compare against nothing. Rather than track which columns happen to be indexed, PostgreSQL refuses SQL-level use of any enum value whose defining row belongs to a transaction still in flight. The current documentation states the user-facing half of that: “the new value cannot be used until after the transaction has been committed.” Branches checked for this article: REL9_6_STABLE, REL_10_STABLE, REL_11_STABLE, and REL_12_STABLE through REL_18_STABLE.

It still fails if your runner sends the file as one query string

Per the PostgreSQL protocol documentation: “When a simple Query message contains more than one SQL statement (separated by semicolons), those statements are executed as a single transaction, unless explicit transaction control commands are included to force a different behavior.” The same page names the mechanism: those statements run “in an implicit transaction block unless there is some explicit transaction block for them to run in.”

So a runner that hands the whole migration file to one query() call puts every statement in one transaction, with no BEGIN anywhere in your code, and the UPDATE fails exactly as before. On PostgreSQL 11 the same setup produces the transaction-block error, because IsTransactionBlock() in src/backend/access/transam/xact.c returns true inside an implicit block too. On 9.6 and 10 there are no implicit blocks, so the error is a different one: exec_simple_query() in src/backend/tcop/postgres.c sets isTopLevel = (list_length(parsetree_list) == 1) under the comment “If more than one, it’s effectively a transaction block”, and a multi-statement string therefore trips the last branch of PreventTransactionChain() and reports ALTER TYPE ... ADD cannot be executed from a function or multi-command string. Either way the fix is one statement per round trip, not another transaction setting.

Why it passes on a fresh database and fails in production

One case makes a brand new enum value usable in the transaction that created it, documented in pg_enum.c:

The motivation for treating enum values as safe if their type OID is in the first hash is to allow CREATE TYPE AS ENUM; ALTER TYPE ADD VALUE; followed by a use of the value in the same transaction. This pattern is really just as safe as creating the value during CREATE TYPE.

It is safe because if that transaction rolls back, the type goes away and takes any index built on its values with it. The exemption applies only at the outermost transaction level, guarded by GetCurrentTransactionNestLevel() == 1, so a savepoint does not get it.

So the same file behaves differently depending on how old the type is. Against production, where order_status was committed 18 months ago, it fails. Against a database where the type was created in the transaction now adding to it, it succeeds. That case is not hypothetical: pg_restore -1 does it, and so does any test harness that replays the whole migration set in one transaction to get a clean database per run. There the migration passes, review passes, and production is the first place the enum type is older than the transaction touching it.

The rewrite that is actually safe

Two migrations, two transactions, in this order. Keep the transaction wrapper on both.

-- Migration 1: add the label. Runs in milliseconds.
SET lock_timeout = '2s';

ALTER TYPE order_status ADD VALUE IF NOT EXISTS 'refunded' AFTER 'shipped';

IF NOT EXISTS makes the migration re-runnable. Per the ALTER TYPE documentation: “If IF NOT EXISTS is specified, it is not an error if the type already contains the new value: a notice is issued but no other action is taken.” Without it, a retry after a failure anywhere in the deploy dies on enum label "refunded" already exists.

AFTER 'shipped' is a decision you get to make once. Per the enumerated types documentation: “Existing values cannot be removed from an enum type, nor can the sort ordering of such values be changed, short of dropping and re-creating the enum type.” Omit the position and the value is appended at the end, which the ALTER TYPE page notes is also the cheaper option, since comparisons “will sometimes be slower” for a value placed anywhere else. Append when the ordering carries no meaning, position deliberately when it does, and decide it now either way.

Between the two migrations the database sits in a state that is safe to leave indefinitely: the label exists, its position is settled, nothing references it. Application code that can write 'refunded' deploys after this migration commits, for the same reason the UPDATE has to.

-- Migration 2: separate file, separate deploy, after migration 1 has committed.
SET lock_timeout = '2s';

UPDATE orders
SET status = 'refunded'
WHERE refund_id IS NOT NULL
  AND status <> 'refunded';

On 40 million rows that UPDATE is its own problem, unrelated to enums, and belongs in batches out of band: see expand and contract. SET lock_timeout belongs on both files even though neither contends for a table, because migration 1 can still queue behind another session holding that type object lock, and an unbounded wait is how a lock queue death spiral starts.

On PostgreSQL 11 and earlier

The split above does not help on its own, because the ALTER TYPE is rejected before it ever runs. There is one way through, and it is the one those issue threads found: the statement has to reach the server outside any transaction, which means outside an explicit BEGIN and outside a multi-statement query string.

Every runner has an opt-out for that. Knex takes exports.config = { transaction: false } inside the migration file. Flyway takes executeInTransaction=false in a .conf file named after the script, so V0147__orders_status_refunded.sql.conf. TypeORM’s is migration:run --transaction none, which is not per-file: it drops the wrapper for every migration in that run, so scope the run to this one file.

Use it on migration 1 and nowhere else, and understand that you are buying exactly what this article argues against on 12 and later. The ALTER TYPE commits on its own, and a failure after it leaves a value that no version of Postgres can remove. Keep migration 2 in a transaction, where it belongs.

The durable fix is the upgrade. PostgreSQL 11 reached end of life on 2023-11-09, and from 12 onwards the two-migration split above is the whole answer.

What pgfence does with this

pgfence resolves this rule against minPostgresVersion, which defaults to 14, so the default answer is the PostgreSQL 12 and later answer:

npx @flvmnt/pgfence explain "ALTER TYPE order_status ADD VALUE 'refunded' AFTER 'shipped'"

Statement:
  ALTER TYPE order_status ADD VALUE 'refunded' AFTER 'shipped';

[LOW] alter-enum-add-value
  ALTER TYPE "order_status" ADD VALUE 'refunded': instant on PG12+, but the new value cannot be used in the same transaction. Lock is on the type object, not on any table.
  Lock: EXCLUSIVE
  Blocks: writes, other DDL

  Safe rewrite:
  Safe on PG12+, but the new enum value is not usable in the same transaction
    -- COMMIT after ADD VALUE before inserting rows that use the new value.
    -- Using the new value in the same transaction causes: "unsafe use of new value"

Drop the AFTER 'shipped' and a second check, alter-enum-no-ordering, fires alongside it, because the position you did not choose is the position you are stuck with. The Blocks: line is the generic translation of what EXCLUSIVE conflicts with in the table-level matrix, and the message is the line that qualifies it. Run the original file through pgfence analyze --min-pg-version 11 and a policy error joins it: below 12, pgfence routes ALTER TYPE ... ADD VALUE into the same autocommit-only check as CREATE INDEX CONCURRENTLY, so a statement inside BEGIN and COMMIT, or inside an ORM format that wraps migrations by default, is reported as failing at runtime.

What pgfence does not do is prove that a later statement in the same transaction uses the label you just added. That means resolving a string literal against a column’s declared type across statements, and a rule that guessed would be wrong in both directions. It states the fact that decides the split instead: the value is unusable until commit.

Review checklist

1. Match the error to the version. cannot run inside a transaction block is the PostgreSQL 11 and earlier problem, unsafe use of new value is the 12 and later one, and the second is about the statement after the ALTER TYPE.
2. Reject any migration that adds an enum value and uses it in the same file. Split it in two and let the first commit before the second deploys.
3. Do not accept a transaction opt-out as the fix on 12 or later. It buys nothing the split does not, and it makes a half-applied migration possible against a type with no DROP VALUE.
4. Require IF NOT EXISTS on every ALTER TYPE ... ADD VALUE, so a retry does not die on enum label already exists.
5. Ask where the new label belongs in the ordering and answer it with BEFORE or AFTER. The position cannot be changed later without recreating the type.

Related

• CREATE INDEX CONCURRENTLY in a transaction is a silent footgun
• REFRESH MATERIALIZED VIEW CONCURRENTLY runs fine inside a transaction block
• Postgres ALTER TABLE hangs: find the blocker, then decide who to cancel
• ALTER TYPE … ADD VALUE lock reference
• CREATE TYPE … AS ENUM lock reference