extending enum type natively in postgres using alembic

[Alexander]

Dear colleagues,

I would like to extend enum type in postgres using alembic and currently i have to do the following:

name = 'my_type'

old_enum = sa.dialects.postgresql.ENUM('value1', 'value2', name=name)

new_enum = sa.dialects.postgresql.ENUM('value1', 'value2', 'value3', name=name)    

op.execute(f'ALTER TYPE {name} RENAME TO _{name}')

new_enum.create(op.get_bind())

op.execute(f'ALTER TABLE table1 ALTER COLUMN col1 TYPE {name} USING col1::text::{name}')

op.execute(f'DROP TYPE _{name}')

Since 9.1 postgres supports adding new values to enum natively (added queries below), is it possible to perform such request in alembic?

ALTER TYPE enum_type ADD VALUE 'new_value'; -- appends to list

ALTER TYPE enum_type ADD VALUE 'new_value' BEFORE 'old_value';

ALTER TYPE enum_type ADD VALUE 'new_value' AFTER 'old_value';

--

Kind regards,
Alexander.

[Mike Bayer]

you are using op.execute() already to emit the "ALTER TYPE" command directly.   Just emit the "ALTER TYPE .. ADD VALUE" commands via op.execute() instead.  

I happen to recall that there is a restriction on "ADD VALUE" that it can't run in a transaction block, and even though you didn't note this, I would assume this is the actual problem you are having (if this is the case, please include details like these, that is, what you tried and why it is not working, as we would not usually know what the problem you are having is otherwise).    

To deal with this restriction Alembic has a fairly recent feature as of 1.2.0 called autocommit_block:

https://alembic.sqlalchemy.org/en/latest/api/runtime.html#alembic.runtime.migration.MigrationContext.autocommit_block

the documentation above refers to the specific example of emitting "ADD VALUE" on PostgreSQL.

--

Kind regards,

Alexander.

--

You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alem...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy-alembic/CAMXowCahmsmL4VFsFCWy6L1HmoM_OuA7F6C1s3H27UCFnoa8jA%40mail.gmail.com.

[Alexander]

Mike, thank you very much for so fast and detailed response!

чт, 31 окт. 2019 г. в 17:45, Mike Bayer <mik...@zzzcomputing.com>:

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy-alembic/a80e1792-82c6-4685-8ca4-3851141f872f%40www.fastmail.com.

--

Kind regards,
Alexander.