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

Question

I am trying to add new type value to my existing types in PostgreSQL. But I get the following error

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

The query I used to add a new value to the type is

ALTER TYPE public.request_type ADD VALUE "Check";

I am actually running above query in migrations file which is created using node-pg-migrate

Here public is my schema.

Any idea why this is failing?

Edit:

The below query executes fine when execute it in pgadmin

ALTER TYPE public.request_type ADD VALUE "Check";

But when I run above command through node-pg-migrate migrations it fails and throws above error

Answer 1

As it was mentioned above you can't edit enum within transaction block. But you can create the new one. Here are the steps:

1. Change type from request_type to varchar for all columns/tables which use this type:

ALTER TABLE table_name
    ALTER COLUMN column_name TYPE VARCHAR(255);

2. Drop and create again request_type enum:

DROP TYPE IF EXISTS request_type;
CREATE TYPE request_type AS ENUM (
    'OLD_VALUE_1',
    'OLD_VALUE_2',
    'NEW_VALUE_1',
    'NEW_VALUE_2'
);

3. Revert type from varchar to request_type for all columns/tables (revert step one):

ALTER TABLE table_name
    ALTER COLUMN column_name TYPE request_type
    USING (column_name::request_type);

Answer 2

The reason is given in the following comment in AlterEnum in src/backend/commands/typecmds.c:

/*
 * Ordinarily we disallow adding values within transaction blocks,
 * because we can't cope with enum OID values getting into indexes and
 * then having their defining pg_enum entries go away.  However, it's
 * okay if the enum type was created in the current transaction, since
 * then there can be no such indexes that wouldn't themselves go away
 * on rollback.  (We support this case because pg_dump
 * --binary-upgrade needs it.)

Note that this restriction has been removed in commit 212fab99; the commit message reads:

To prevent possibly breaking indexes on enum columns, we must keep
uncommitted enum values from getting stored in tables, unless we
can be sure that any such column is new in the current transaction.

Formerly, we enforced this by disallowing ALTER TYPE ... ADD VALUE
from being executed at all in a transaction block, unless the target
enum type had been created in the current transaction.  This patch
removes that restriction, and instead insists that an uncommitted enum
value can't be referenced unless it belongs to an enum type created
in the same transaction as the value.  Per discussion, this should be
a bit less onerous.  It does require each function that could possibly
return a new enum value to SQL operations to check this restriction,
but there aren't so many of those that this seems unmaintainable.

So you might want to upgrade to PostgreSQL v12 some time soon :^)

Answer 3

Workaround for earlier versions of PostgreSQL shown here:

Note this will require special permissions because it changes a system table.

• Replace 'NEW_ENUM_VALUE' with the value you want.
• Replace'type_egais_units' with the oid of the enum you want to change. (Use SELECT * FROM pg_enum to find the enum you want to update, in my case it was a 5-digit number like '19969')

The statement:

INSERT INTO pg_enum (
    enumtypid, 
    enumlabel, 
    enumsortorder
)
SELECT 
    'type_egais_units'::regtype::oid, 
    'NEW_ENUM_VALUE', 
    (SELECT MAX(enumsortorder) + 1 FROM pg_enum WHERE enumtypid = 'type_egais_units'::regtype)

Of course , upgrading PostgreSQL as suggested in the accepted answer, is probably the best.

Does anyone know how to avoid using transactions when running queries from pgAdmin Version 3.5? (i.e. when executing with F5?)

Answer 4

You can change your query to

COMMIT;
ALTER TYPE public.request_type ADD VALUE "Check";

Answer 5

Specifically how to do this with node-pg-migrate is to disable transactions for the migration:

exports.up = (pgm) => {
  pgm.noTransaction()
  pgm.addTypeValue('foo', 'BAR', { ifNotExists: true })
}