CODEWITHSUNDEEP
postgresqlenumsalter
Hemadri Dasari
Hemadri Dasari

Reputation: 34014

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

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

Upvotes: 41

Views: 38146

Answers (7)

Rehman Akram
Rehman Akram

Reputation: 21

Just got into this problem as using old PostgreSQL version 11. New updated solution alter table without transaction is as in TypeORM when we use provided queryRunner in migration file as

public async up(queryRunner: QueryRunner): Promise<any>

PostgreSQL runs this in transaction. So to avoid running migration in transaction you can use queryRunner from dataSource (if you are using typeorm 0.3+) or from getConnection() (if you are using 0.2).

while using dataSource export datasource and use datasource throughout your application to create queryRunner as

const queryRunner = dataSource.createQueryRunner()

or

const queryRunner = getConnection().queryRunner

and then run your query.

Supporting document: https://typeorm.biunav.com/en/changelog.html#_0-3-0-2022-03-17

Upvotes: 0

Anton Vulfert
Anton Vulfert

Reputation: 1

If you changelog in xml format, use <changeSet id="name" author="author" runInTransaction="false">

Upvotes: 0

vesse
vesse

Reputation: 5078

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 })
}

Upvotes: 0

Nasar Kushnir
Nasar Kushnir

Reputation: 751

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);
  1. 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'
);
  1. 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);

Upvotes: 75

VAG
VAG

Reputation: 69

You can change your query to

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

Upvotes: 5

Nate Anderson
Nate Anderson

Reputation: 21156

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?)

Upvotes: 6

Laurenz Albe
Laurenz Albe

Reputation: 247665

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 :^)

Upvotes: 30

Related Questions