Description
Describe the bug
When creating a non-primary key column with a sequence in postgres, alembic generates the migration as expected. However, after applying that migration, and then autogenerating a new revision, alembic sees that there is a ::regclass cast on the nextval() call, and tries to remove it in a migration.
Expected behavior
Alembic doesn't attempt to remove the ::regclass cast as it's added by postgres.
To Reproduce
Please try to provide a Minimal, Complete, and Verifiable example, with the migration script and/or the SQLAlchemy tables or models involved.
See also Reporting Bugs on the website.
Model:
class Post(Base):
__tablename__ = "posts"
post_id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
user_id: Mapped[int] = mapped_column(BigInteger, nullable=False, index=True)
sequential_id_seq = Sequence("post_sequential_id_seq", metadata=Base.metadata, start=1)
sequential_id: Mapped[int] = mapped_column(
Integer,
sequential_id_seq,
server_default=sequential_id_seq.next_value(),
)Initial migration:
def upgrade() -> None:
"""Apply this migration."""
# ### commands auto generated by Alembic - please adjust! ###
op.execute(sa.schema.CreateSequence(sa.Sequence("post_sequential_id_seq", start=1)))
op.create_table(
"posts",
sa.Column("post_id", sa.BigInteger(), nullable=False),
sa.Column("user_id", sa.BigInteger(), nullable=False),
sa.Column(
"sequential_id", sa.Integer(),
server_default=sa.text("nextval('post_sequential_id_seq')"),
nullable=False,
),
sa.PrimaryKeyConstraint("post_id", name=op.f("posts_pk")),
)
op.create_index(op.f("posts_user_id_ix"), "posts", ["user_id"], unique=False)
# ### end Alembic commands ###
def downgrade() -> None:
"""Revert this migration."""
# ### commands auto generated by Alembic - please adjust! ###
op.drop_index(op.f("posts_user_id_ix"), table_name="posts")
op.drop_table("posts")
# ### end Alembic commands ###Every subseqent migration:
def upgrade() -> None:
"""Apply this migration."""
# ### commands auto generated by Alembic - please adjust! ###
op.alter_column(
"posts",
"sequential_id",
existing_type=sa.INTEGER(),
server_default=sa.text("nextval('post_sequential_id_seq')"),
existing_nullable=False,
)
# ### end Alembic commands ###
def downgrade() -> None:
"""Revert this migration."""
# ### commands auto generated by Alembic - please adjust! ###
op.alter_column(
"posts",
"sequential_id",
existing_type=sa.INTEGER(),
server_default=sa.text("nextval('post_sequential_id_seq'::regclass)"),
existing_nullable=False,
)
# ### end Alembic commands ###Versions.
- OS: debian:bookworm-slim Docker image (via python:3-slim)
- Python: 3.12
- Alembic: 1.13.2
- SQLAlchemy: 2.0.31
- Database: PostgreSQL 16
- DBAPI: postgresql+psycopg_async
Additional context
I found this code which seems to handle ::regclass, however based on the log.info below I am assuming this only handles serial primary key sequences, but I may be wrong.
Have a nice day!