Enums
ALTER TYPE ... ADD VALUE on PostgreSQL 11 and earlier
PostgreSQL 11 and earlier
Lock mode ACCESS EXCLUSIVE
Risk MEDIUM
Blocks reads, writes, other DDL
pgfence rule
alter-enum-add-value ALTER TYPE order_status ADD VALUE 'refunded';What this does
Before PostgreSQL 12, adding an enum value takes ACCESS EXCLUSIVE on the type object and cannot run inside a transaction block at all, blocking every concurrent use of the enum type while it runs.
Safe rewrite
Upgrade to PostgreSQL 12+, where ADD VALUE is instant and non-blocking
-- On PG12+: ALTER TYPE ADD VALUE is instant and non-blocking.
-- On PG < 12: there is no safe alternative. Minimize lock duration by ensuring no long-running queries.