CreateColumnCommentOp and postgresql_using Autogeneration in Alembic

[schwarzichet]

CreateColumnCommentOp

Alembic already has CreateTableCommentOp, but there’s no operation for adding or modifying column comments. Would adding a CreateColumnCommentOp be a good idea?
My use case involves categorizing schema changes into "safe" (e.g., adding columns, tables, or comments) and "unsafe" (e.g., dropping columns or changing column types). So apparently table comment and column comment should be considered safe but in column we only have AlterColumnOp . Maybe I can custom such a operation but I believe it would be nice if alembic will have a build-in CreateColumnCommentOp like CreateTableCommentOp

postgresql_using in Autogenerate

When altering column types in PostgreSQL, the USING clause is often required for type casting. Currently, Alembic does not autogenerate the postgresql_using parameter, so I need to manually add it to the revision files.
Would it be possible for Alembic to autogenerate this when it comes to type change?

[zzzeek]

column comments are part of the AlterColumnOp since adding a comment involves using ALTER COLUMN.

if I understand USING correctly, this is a directive given at ALTER COLUMN time which indicates what transformation should be applied to existing values in the column. This expression is not part of the SQLAlchemy Column definition itself that one would see inside of python-side database metadata, nor is it something that's stored in the database's catalog tables. Therefore there's no source of information autogenerate could use here to produce a postgresql_using for a particular alter column operation.

you can if you wanted add your own "postgresql_using" note to the Column.info dictionary and consume it during autogenerate using a rewriter to help render this from your models.

[schwarzichet]

I understand column comments are part of the AlterColumnOp. In my case, modifying comments should be considered as "safe" operation while other column changes such as changing type is unsafe, so that's why I want a special case of AlterColumnOp, just like CreateTableCommentOp is a special case of AlterTableOp. With this I can leverage the directives results in process_revision_directives by just judging each operation type. Without this, maybe I need to check whether this AlterColumnOp has and has only comment change, or drop everything in default directives and try to get information in my own custom compare_metadata and produce_migrations

when it comes to type change in column, alembic can auto generate such scripts,

  op.alter_column('table_name', 'column_name',
             existing_type=sa.TEXT(),
             type_=sa.JSON(),
             existing_nullable=True)

it will fail when applied in postgresql directly. One should use

  op.alter_column('table_name', 'column_name',
             existing_type=sa.TEXT(),
             type_=sa.JSON(),
             existing_nullable=True,
             postgresql_using="column_name::json")

if this is the only case that I need to handle in USING, maybe I can just use rewriter to handle this and do not need to add something in Column.info?

and I have another question. When faced with a situation where both the type and the comment of a column are changed, does autogenerate generate a single AlterColumnOp to handle both changes, or does it split them into two separate operations?

[zzzeek]

I understand column comments are part of the AlterColumnOp. In my case, modifying comments should be considered as "safe" operation while other column changes such as changing type is unsafe, so that's why I want a special case of AlterColumnOp, just like CreateTableCommentOp is a special case of AlterTableOp.

this is just an artifact of how the ops module is organized; for AlterTableOp, there is no actual ops method registered; it breaks out into individual ops for "RenameTable", "CreateTableComment", "AddColumn", "AlterColumn". Within AlterColumn, that is organized to support the "alter_column" operation which uses keywords to indicate basically everything you can do with a column. This architecture loosely derives from how the typical ALTER set of keywords exist in the database, so alembic's command to change a column comment is part of the alter_column command just like everything else about changing a column.

A concept that does not exist in alembic's ops nor am I familiar with is the notion that for a particular table or column in a database, an operator would have permission to change the comment on the object, but not change its name or type or anything like that. This concept is not modeled within alembic and it would be arbitrary to make a design decision that models this for just one small element of a column, and not across the board for all elements of a column like type, name, nullability, default, etc. nor does it make much sense to me that if we did want to break out individual modifications like "name", "type", "comment" into individual "things that are safe vs not safe", that we'd necessarily use the presence of "XYZOp" classes to indicate this although it looks like you are looking for a certain level of convenience within a process_revision_directives recipe.

With this I can leverage the directives results in process_revision_directives by just judging each operation type.

So this is also proposing that autogenerate itself abandon the use of alter_column for the comment change, and indeed to change alembic for just this one element of a column would be arbitrary, for this proposal to be done in a consistent way would require that alembic re-architect autogenerate and altercolumnop into individual operation-per-element-of-the-object in order to suit this particular case that you want to flag operations coming in, that is, alter_column would go away and be replaced by alter_column_name, alter_column_type, alter_column_default, etc. Besides being a really big change that isn't really needed to make your use case possible, it would also break the typical use of alter_column which is that for many databases you can in fact bundle multiple changes to a single column into one command like on MySQL, and would also be an enormous change for the whole userbase of Alembic.

Let's look into the problem you actually have (Edit: indeed you did state your "X" problem as well. But framing it all in terms of "Y" is still a big distraction for me):

Without this, maybe I need to check whether this AlterColumnOp has and has only comment change

yes, that's what you should do here. Receive the AlterColumnOp and just look at what elements it has defined. This is a little more work on your end however would save millions of person-hours in not having to deliver a major foundational reorganization of the API to the whole userbase.

when it comes to type change in column, alembic can auto generate such scripts,

  op.alter_column('table_name', 'column_name',
             existing_type=sa.TEXT(),
             type_=sa.JSON(),
             existing_nullable=True)

it will fail when applied in postgresql directly. One should use

  op.alter_column('table_name', 'column_name',
             existing_type=sa.TEXT(),
             type_=sa.JSON(),
             existing_nullable=True,
             postgresql_using="column_name::json")

if this is the only case that I need to handle in USING, maybe I can just use rewriter to handle this and do not need to add something in Column.info?

Let's ask though why PostgreSQL itself raises an error if you don't provide this USING expression. Why cant PostgreSQL assume a simple cast? For whatever reason they have decided they dont want to make this choice for you. Therefore I think it's appropriate that Alembic does not either. However, if you want to use a rewriter to hardcode postgresql_using to that particular expression, then sure - the important part is that's a choice your particular application makes.

and I have another question. When faced with a situation where both the type and the comment of a column are changed, does autogenerate generate a single AlterColumnOp to handle both changes, or does it split them into two separate operations?

it depends on the database. For PostgreSQL I think it breaks them into two ALTER operations. For MySQL it should be using just one.

[schwarzichet]

Thank you for your detailed response. I have learned a lot from it. I apologize for the XY problem; part of the reason might be that while I was aware of a possible solution, I still hoped to resolve it in another way, which may have caused some confusion—my apologies for that.

I respect Alembic's design logic. I understand that Alembic is not designed for fully automated operations and still requires significant human intervention and validation to ensure the system runs correctly, which is necessary. However, I am trying to use Alembic for some automation tasks, such as batch-modifying comments for thousands of columns. So this is my scenario: an operator (my program) should only have permission to modify object comments but no other permissions.

Thank you again for your explanation. I think I now understand the approach I need to take to solve my problem.