How to fix 55P04: unsafe_new_enum_value_usage in PostgreSQL

-- Check for recent enum alterations
SELECT schemaname, typname, nspname, obj_description(oid, 'pg_type')
FROM pg_type t
JOIN pg_namespace n ON t.typnamespace = n.oid
WHERE t.typtype = 'e'
AND t.typname LIKE '%your_enum%';

-- Find active transactions that might be using old enum definitions
SELECT pid, usename, application_name, client_addr, backend_start, query_start, state, query
FROM pg_stat_activity
WHERE state = 'active'
AND query LIKE '%your_enum%';

-- For read-only transactions, try to commit them
-- (if they haven't done any writes that need to be rolled back)

-- To terminate a specific transaction:
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid = <problematic_pid>;

-- Or terminate all transactions using a specific enum:
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE query LIKE '%your_enum%'
AND state = 'active'
AND pid <> pg_backend_pid();

-- Unsafe (default) - new value cannot be used in existing transactions:
ALTER TYPE status_enum ADD VALUE 'pending_review';

-- Safe - new value can be used in existing transactions:
ALTER TYPE status_enum ADD VALUE 'pending_review' BEFORE 'approved';
-- or
ALTER TYPE status_enum ADD VALUE 'pending_review' AFTER 'submitted';

# For applications using connection pools:
# 1. Drain the pool (stop accepting new connections)
# 2. Wait for existing connections to complete
# 3. Restart the pool

# For PgBouncer or other connection poolers:
pgbouncer -R /etc/pgbouncer/pgbouncer.ini

# For application-level solutions, implement a connection refresh:
# - Catch the 55P04 error
# - Close and reopen the database connection
# - Retry the operation

-- Deployment checklist for enum changes:
-- 1. Schedule during low-traffic periods
-- 2. Drain connection pools before alteration
-- 3. Use SAFE enum addition when possible
-- 4. Deploy application code that uses new enum value AFTER the alteration
-- 5. Monitor for 55P04 errors and have rollback plan

-- Example safe deployment script:
BEGIN;
-- Drain connections (application-specific)
-- Perform safe enum alteration
ALTER TYPE user_role ADD VALUE 'moderator' AFTER 'user';
-- Wait for all connections to refresh (application-specific)
-- Deploy new application version
COMMIT;