Odtnhj

I have a trouble with altering existing postgresql.ENUM column with SQLAlchemy and Alembic.

I want to add / remove a value to postgresql.ENUM type column in alembic.

In specific, current enum type is created by the following two alembic revisions:

# revision 1
def upgrade():
 op.create_table('kernels',
 sa.Column('status', sa.String(), nullable=True),
 ...
 )


# revision 2
kernelstatus_choices = (
 'PREPARING', 'BUILDING', 'RUNNING',
 'RESTARTING', 'RESIZING', 'SUSPENDED',
 'TERMINATING', 'TERMINATED', 'ERROR',
)
kernelstatus = postgresql.ENUM(
 *kernelstatus_choices,
 name='kernelstatus')

def upgrade():
 op.alter_column('kernels', column_name='status', 
 type_=sa.Enum(*kernelstatus_choices, name='kernelstatus'),
 postgresql_using='status::kernelstatus')

Now, I want to add 'PENDING' status to kernelstatus type. So I implemented like below, by referencing some articles.

prev_kernelstatus_choices = (
 'PREPARING', 'BUILDING', 'RUNNING',
 'RESTARTING', 'RESIZING', 'SUSPENDED',
 'TERMINATING', 'TERMINATED', 'ERROR',
)

prev_kernelstatus = postgresql.ENUM(
 *prev_kernelstatus_choices,
 name='kernelstatus')

curr_kernelstatus_choices = ('PENDING',) + prev_kernelstatus_choices

curr_kernelstatus = postgresql.ENUM(
 *curr_kernelstatus_choices,
 name='kernelstatus')

def upgrade():
 op.execute('ALTER TYPE kernelstatus RENAME TO kernelstatus_old;')
 curr_kernelstatus.create(op.get_bind())
 op.alter_column('kernels', column_name='status', 
 type_=sa.Enum(*curr_kernelstatus_choices, name='kernelstatus'),
 postgresql_using='status::text::kernelstatus')
 op.execute('DROP TYPE kernelstatus_old;')

But it keeps generating the following error:

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) operator does not exist: kernelstatus <> kernelstatus_old
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
[SQL: 'ALTER TABLE kernels ALTER COLUMN status TYPE kernelstatus USING status::text::kernelstatus']

I already tried a solution with adding value to enum type, but this does not work in with Alembic since each Alembic revision runs in a transaction and ALTER TYPE statement cannot run in a transaction. Also, there should be a code for downgrade() and there is no statement for removing a value from enum type in PostgreSQL, so just adding a value to the enum type cannot be the ultimate solution in my case.

Could somebody give me a help?