Postgres: Modifying enum fails migration if the enum is used in an array column

[dapetcu21]

Issue Description

If an enum is used as the array item type in a column with array: true, then adding new enum values to said enum causes a crash when migrating. See code sample below.

Expected Behavior

The migration should complete without a hitch.

Actual Behavior

This crash:

query: SELECT * FROM current_schema()
query: SELECT "udt_schema", "udt_name" FROM "information_schema"."columns" WHERE "table_schema" = 'public' AND "table_name" = 'user' AND "column_name"='roles'
query: ALTER TYPE "public"."_user_roles_enum" RENAME TO "user_roles_enum_old"
query failed: ALTER TYPE "public"."_user_roles_enum" RENAME TO "user_roles_enum_old"
error: error: cannot alter array type user_roles_enum[]
    at Parser.parseErrorMessage (/Users/da_petcu21/Work/rethink-backend/node_modules/pg-protocol/src/parser.ts:357:11)
    at Parser.handlePacket (/Users/da_petcu21/Work/rethink-backend/node_modules/pg-protocol/src/parser.ts:186:21)
    at Parser.parse (/Users/da_petcu21/Work/rethink-backend/node_modules/pg-protocol/src/parser.ts:101:30)
    at Socket.<anonymous> (/Users/da_petcu21/Work/rethink-backend/node_modules/pg-protocol/src/index.ts:7:48)
    at Socket.emit (events.js:315:20)
    at Socket.EventEmitter.emit (domain.js:486:12)
    at addChunk (_stream_readable.js:309:12)
    at readableAddChunk (_stream_readable.js:284:9)
    at Socket.Readable.push (_stream_readable.js:223:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:188:23) {
  length: 177,
  severity: 'ERROR',
  code: '42809',
  detail: undefined,
  hint: 'You can alter type user_roles_enum, which will alter the array type as well.',
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'typecmds.c',
  line: '3305',
  routine: 'RenameType'
}
query: ROLLBACK
[1608669311589] ERROR    (QueryFailedError/39871 on salem.local): cannot alter array type user_roles_enum[]
    QueryFailedError: cannot alter array type user_roles_enum[]
        at new QueryFailedError (/Users/da_petcu21/Work/rethink-backend/src/error/QueryFailedError.ts:9:9)
        at Query.callback (/Users/da_petcu21/Work/rethink-backend/src/driver/postgres/PostgresQueryRunner.ts:220:30)
        at Query.handleError (/Users/da_petcu21/Work/rethink-backend/node_modules/pg/lib/query.js:128:19)
        at Client._handleErrorMessage (/Users/da_petcu21/Work/rethink-backend/node_modules/pg/lib/client.js:335:17)
        at Connection.emit (events.js:315:20)
        at Connection.EventEmitter.emit (domain.js:486:12)
        at /Users/da_petcu21/Work/rethink-backend/node_modules/pg/lib/connection.js:115:12
        at Parser.parse (/Users/da_petcu21/Work/rethink-backend/node_modules/pg-protocol/src/parser.ts:102:9)
        at Socket.<anonymous> (/Users/da_petcu21/Work/rethink-backend/node_modules/pg-protocol/src/index.ts:7:48)
        at Socket.emit (events.js:315:20)

Steps to Reproduce

1. Create the following entity.
2. Run the server once.
3. Add a new enum value to UserRole.
4. Run the server again and watch the crash happen.

export enum UserRole {
  PLAYER = 'PLAYER',
  FULL_GAME = 'FULL_GAME',
  SUPERVISOR = 'SUPERVISOR',
  REPORTS = 'REPORTS',
  ADMIN = 'ADMIN',
}

@Entity()
export class User {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column({
    type: 'enum',
    enum: UserRole,
    array: true,
    default: [UserRole.PLAYER],
  })
  roles: UserRole[];
}

My Environment

Dependency	Version
Operating System	macOS 11.0.1
Node.js version	v14.15.1
Typescript version	v4.1.3
TypeORM version	v0.2.29

Additional Context

Relevant Database Driver(s)

• aurora-data-api
• aurora-data-api-pg
• better-sqlite3
• cockroachdb
• cordova
• expo
• mongodb
• mysql
• nativescript
• oracle
• postgres
• react-native
• sap
• sqlite
• sqlite-abstract
• sqljs
• sqlserver

Are you willing to resolve this issue by submitting a Pull Request?

• Yes, I have the time, and I know how to start.
• Yes, I have the time, but I don't know how to start. I would need guidance.
• No, I don't have the time, although I believe I could do it if I had the time...
• No, I don't have the time and I wouldn't even know how to start.

[karousn]

any workaround for this issue ?

[pmsfc]

This is a bug from Typeorm, it's adding a _ on the rename query (happens on up and down migration).
If you look closely "public"."_user_roles_enum" should be "public"."user_roles_enum"

query failed: ALTER TYPE "public"."_user_roles_enum" RENAME TO "user_roles_enum_old"

Just remove it and you should be fine.

The problem occurs on

typeorm/src/driver/postgres/PostgresQueryRunner.ts

Line 2121 in d27dd2a

const result = await this.query(`SELECT "udt_schema", "udt_name" ` +

    protected async getEnumTypeName(table: Table, column: TableColumn) {
        const currentSchemaQuery = await this.query(`SELECT * FROM current_schema()`);
        const currentSchema = currentSchemaQuery[0]["current_schema"];
        let [schema, name] = table.name.split(".");
        if (!name) {
            name = schema;
            schema = this.driver.options.schema || currentSchema;
        }
        const result = await this.query(`SELECT "udt_schema", "udt_name" ` +
            `FROM "information_schema"."columns" WHERE "table_schema" = '${schema}' AND "table_name" = '${name}' AND "column_name"='${column.name}'`);
        return {
            enumTypeSchema: result[0]["udt_schema"],
            enumTypeName: result[0]["udt_name"]
        };
    }

Because udt_name starts with an underscore when the enum is an array on that table.
I can submit a PR since most of the investigation is done.

[geric]

I also encountered it on typeorm 0.2.29. I followed @pmsfc advice and it works perfectly. The cause was indeed the appended "_".

[niederschlag]

There's a PR for this issue: #6126

It's ready for merge but stuck for a couple of months though