Wsrtjtyk

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?